<< Previous article (Part 6)

Introduction of some Micro ORMs: Dapper

Scenario Used

You can find the scenario used in a last article here.

Dapper

• Developed by the StackExchange team
• Compatible with several relational databases: SQL Server, Oracle, Mysql, SqlLite, PostgreSql, FireBird
• Feature-rich API
• Very active community
• Very interesting performance (fastest mapping)
• Supports .Net Core
• Supports transactions
• Testable unitarily easily (DapperWrapper & DapperParameters)
• Provides Async queries
• Relationships are not supported, provide a mapper easy to use to populate Adhoc objects

Code samples :

Required entities and business objects for our scenario :

public class Orders
    {
        public Orders() { }
        public int Id { get; set; }
        public string ProductName { get; set; }
        public int? Quantity { get; set; }
        public DateTime? Date { get; set; }
    }

    public class WorkOrder
    {
        public WorkOrder() { }

        public int WorkOrderId { get; set; }
        public int ProductID { get; set; }
        public int? OrderQty { get; set; }
        public int? StockedQty { get; set; }
        public int? ScrappedQty { get; set; }
        public DateTime? StartDate { get; set; }
        public DateTime? EndDate { get; set; }
        public DateTime? DueDate { get; set; }
        public int? ScrapReasonID { get; set; }
        public DateTime? ModifiedDate { get; set; }

        public Product Product { get; set; }
    }

    //Db entity
    public class Product
    {
        public int ProductID { get; set; }
        public string Name { get; set; }
    }

Repository sample :

public class DapperRepository
    {
        public DapperRepository()
        { }

        public async Task<List> GetOrders()
        {
            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks2014"].ToString()))
            {

                var t = await connection.QueryAsync(@"SELECT TOP 500 [WorkOrderID] AS Id, P.Name AS ProductName, [OrderQty] AS Quantity, [DueDate] AS Date
                                                 FROM [AdventureWorks2014].[Production].[WorkOrder] AS WO 
                                                 INNER JOIN[Production].[Product] AS P ON P.ProductID = WO.ProductID");

                return t.ToList();
            }
        }

        // Query with Built-in mapper
        public async Task<List> GetOrders2()
        {
            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks2014"].ToString()))
            {

                var t = await connection.QueryAsync<WorkOrder,Product, Orders>(@"SELECT TOP 500 WO.*, P.* 
                                                                        FROM [AdventureWorks2014].[Production].[WorkOrder] AS WO 
                                                                        INNER JOIN[Production].[Product] AS P ON P.ProductID = WO.ProductID", 
                                                                        (w,p) => {
                                                                            return new Orders
                                                                            {
                                                                                Id = w.WorkOrderId,
                                                                                Date = w.DueDate,
                                                                                Quantity = w.OrderQty,
                                                                                ProductName = w.Product.Name
                                                                            };
                                                                        });

                return t.ToList();
            }
        }

        // Multiple sets of data
        public async Task<Tuple<List, List>> GetWorkOrdersAndProducts()
        {
            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks2014"].ToString()))
            {
                var data =  await connection.QueryMultipleAsync("SELECT TOP 500 * FROM[AdventureWorks2014].[Production].[WorkOrder];SELECT * FROM [Production].[Product];");

                return Tuple.Create(data.Read().ToList(), data.Read().ToList());
            }
        }

        public async Task GetOrderById(int id)
        {
            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks2014"].ToString()))
            {

                var t=  await connection.QueryAsync(@"SELECT TOP 500 [WorkOrderID] AS Id, P.Name AS ProductName, [OrderQty] AS Quantity, [DueDate] AS Date
                                 FROM [AdventureWorks2014].[Production].[WorkOrder] AS WO 
                                 INNER JOIN[Production].[Product] AS P ON P.ProductID = WO.ProductID
                                 WHERE WorkOrderID = @Id", new { Id = id });

                return t.FirstOrDefault();
            }
        }

        public async Task Add(WorkOrder workOrder)
        {
            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks2014"].ToString()))
            {
                string sql = @"INSERT INTO [AdventureWorks2014].[Production].[WorkOrder] ([OrderQty], [DueDate]) 
                              VALUES (@OrderQty,@DueDate); 
                              SELECT CAST(SCOPE_IDENTITY() as int)";
                var t = await connection.QueryAsync(sql, workOrder);
                workOrder.WorkOrderId = t.First();

                return workOrder;
            }
        }
        public async Task Update(WorkOrder workOrder)
        {
            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks2014"].ToString()))
            {
                string sql = @"UPDATE [AdventureWorks2014].[Production].[WorkOrder]
                               SET OrderQty = @OrderQty
                               WHERE (@OrderQty,@DueDate)";
                var t = await connection.QueryAsync(sql, workOrder);
            }
        }

        public async Task Delete(WorkOrder workOrder)
        {
            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorks2014"].ToString()))
            {
                string sql = @"DELETE 
                               FROM [AdventureWorks2014].[Production].[WorkOrder] 
                               WHERE (@OrderQty,@DueDate)";
                var t = await connection.QueryAsync(sql, workOrder);
            }
        }
    }

Incredible right?

Now it’s time to compare performances and make some conclusions.