SHARE:

Micro ORMs, alternatives to Entity Framework ? Part 7

 

<< 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.

 

Written by

anthonygiretti

Anthony is a specialist in Web technologies (14 years of experience), in particular Microsoft .NET and learns the Cloud Azure platform. He has received twice the Microsoft MVP award and he is also certified Microsoft MCSD and Azure Fundamentals.
%d bloggers like this: