Dapper.NET Tutorial III

In the last two posts, we discuss how to use Dapper.net to simplify the CRUD operation and how the APIs are enhanced by using Dapper Contrib project. Today, I am going to show another existing enhancement built on top of Dapper, Dapper Rainbow. Dapper Rainbow is shipped as a separate dll, you can reference it into your project by using NuGet.

The main purpose of Rainbow, I think, is to response the feedback from the users that the original APIs for Insert and update are more like raw ADO.net than a ORM. Rainbow project provides a Table class and some extension methods to encapsulate the tedious Sql statements.

  • Insert A new entity

    First we need to create a Database class inherited from Dapper.Rainbow.Database, with a property as type of Table<Supplier>. The table class includes a set of extension methods for the operations of CRUD. Another interesting feature is it can provide an anonymous object for insert.

  • public class NorthwindDatabase : Database
    {
        public Table<Supplier> Suppliers { get; set;  } 
    }
    
     using (var sqlConnection
                 = new SqlConnection(Constant.DatabaseConnection))
    {
        sqlConnection.Open();
    
        var db = NorthwindDatabase.Init(sqlConnection, commandTimeout: 2);
        int? supplierId = db.Suppliers.Insert(
                                new
                                 {
                                    CompanyName = Guid.NewGuid().ToString()
    
                                 });
    
        Console.WriteLine(string.Format("New Supplier Id is {0}", supplierId.Value));
    }
  • Update A entity The enhancement on Update function is to provide a SnapShotter class, which can help track if the any field has changes. It will generate update statement only if any field has been changed. The update Sql statement will only include the fields have been changed. Both of them optimize the overall update performance. 
  • using (var sqlConnection
                       = new SqlConnection(Constant.DatabaseConnection))
    {
        sqlConnection.Open();
        
        var db = NorthwindDatabase.Init(sqlConnection, commandTimeout: 3);
    
        var supplier = db.Suppliers.Get(9);
    
        // snapshotter tracks which fields change on the object 
    
        var s = Snapshotter.Start(supplier);
    
        supplier.CompanyName += "_" + Guid.NewGuid().ToString().Substring(1, 4);
    
        db.Suppliers.Update(9, s.Diff());
    
        // reload it from database 
        supplier = db.Suppliers.Get(9);
    
        ObjectDumper.Write(supplier);
    }
  • Select All/One entity If you like to use the Table API for query, you probably will only end up with some simple scenarios. Table provides ALL, First and Get methods, which is good enough for simple case. If you need to build complex query, you had better switch to user Query method.
using (var sqlConnection = new SqlConnection(Constant.DatabaseConnection))
{
    sqlConnection.Open();

    var db = NorthwindDatabase.Init(sqlConnection, commandTimeout: 2);
    
    var result = db.Suppliers.All();

    foreach (var supplier in result)
    {
        ObjectDumper.Write(supplier);
    }

}
    • Wrap Up

    In the those three posts, I show how to use Dapper and its related projects to create simple and elegant data access function. Well, personally I really like those approaches. In my current point of view, data access layer should be simple and flexible. Don’t get me wrong, I still like NHibernate and Linq2Sql. I just think it is not a “either/or” solution. We can have all of them working together in one single project. Recently I more and more give up the “Repository” pattern and use Query/Command instead. I think Query/Command provides and more flexible way to encapsulate the data persistence. Data access should be easier and that is what it should be.

    Again, here is the code used in those posts.

Happy Programming!

Dapper .NET Tutorial II

In previous blog, I showed some common scenario about how to use Dapper .NET. Today, I will show how to apply Dapper Contrib project to enhance the API.

Dapper Contrib is a set of extension methods built upon the core of Dapper (SqlMapper). At this moment, there is no a separate dll can be referenced into your project, which means you cannot use NuGet to add it into the project. You can open the file and put into your project.

Dapper Contrib, not only provide intuitive APIs for CRUD, but also add couple interesting features, like SqlBuilder and “Dirty” Tracking.

  • Get a item

    Instead of using Query method, it provides a Get method to take the value of Id

    using (var sqlConnection = new SqlConnection(Constant.DatabaseConnection)) { sqlConnection.Open(); var result = sqlConnection.Get(9); ObjectDumper.Write(result); }

  • Insert a item

    Compare the Sql-like syntax, this Insert method has improved a lot from code readable perspective.

    using (var sqlConnection
                = new SqlConnection(Constant.DatabaseConnection))
    {
        sqlConnection.Open();
    
        var supplier = new Supplier()
                           {
                               Address = "10 Main Street",
                               CompanyName = "ABC Corporation"
                           };
    
        var supplierId = sqlConnection.Insert(supplier); 
    
        sqlConnection.Close();
    
        Console.WriteLine(string.Format("New Supplier Id is {0}", supplierId));
    
        Console.WriteLine("Done. ");
    }
  • Update a item

    Similar as inserting item, Dapper Contrib allows to update a entity object, instead of using raw Sql-like syntax.

    using (var sqlConnection = new SqlConnection(Constant.DatabaseConnection))
    {
        sqlConnection.Open();
    
        var entity = sqlConnection.Get(9);
        entity.ContactName = "John Smith"; 
    
        sqlConnection.Update(entity);
    
        var result = sqlConnection.Get(9);
    
        ObjectDumper.Write(result.ContactName);
    }
  • Update a item by using tracking feature

    Dapper .net provides a nice feature to determine if the update statement is really required. If the value does not change, it won’t generate the Sql statement, which is very handy performance optimization. The only requirement is we need to declare a interface for the object. Here is how to use:

// declare a interface for the object
public interface ISupplier
{
    
    int Id { get; set; }

    string CompanyName { get; set; }
    string ContactName { get; set; }
    string ContactTitle { get; set; }
    string Address { get; set; }
    string City { get; set; }
    string PostalCode { get; set; }
    string Country { get; set; }
    IEnumerable Products { get; set; } 
}

public class Supplier : ISupplier
{
    public int Id { get; set; }
    public string CompanyName { get; set; }
    public string ContactName { get; set; }
    public string ContactTitle { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public string PostalCode { get; set; }
    public string Country { get; set; }
    public IEnumerable Products { get; set;  }  
}
using (var sqlConnection = new SqlConnection(Constant.DatabaseConnection))
{
    sqlConnection.Open();

    // using interface to track "Isdirty"
    var supplier = sqlConnection.Get(9);
    //supplier.CompanyName = "Manning"; 

    // should return false, becasue there is no change. 
    ObjectDumper.Write(string.Format("Is Update {0}", sqlConnection.Update(supplier)));
    
    supplier.CompanyName = "Manning";

    // should return true
    ObjectDumper.Write(string.Format("Is Update {0}", sqlConnection.Update(supplier)));

}
  • Sql Builder

      Dapper Contrib includes a SqlBuilder class, which can be used to create a dynamic Sql statement. It will be very helpful to be used in for a customized searching scenario with multiple options. It is very common need when developing application. From the example below, you see, a template Sql statement is redefined. In this case, I need to set up the “Select” and “Where” clause dynamically. We define the place holder as “/**select**/” and “/**where**/” respectively. Then we can add code to define what we need.

    using (var sqlConnection = new SqlConnection(Constant.DatabaseConnection))
    {
        sqlConnection.Open();
        var builder = new SqlBuilder();
    
        // /**select**/  -- has to be low case
        var selectSupplierIdBuilder = builder.AddTemplate("Select /**select**/ from Suppliers /**where**/ ");
        builder.Select("Id");
        builder.Where("City = @City", new { City = "Tokyo"}); // pass an anonymous object 
    
        var supplierIds = sqlConnection.Query(selectSupplierIdBuilder.RawSql,
                                                   selectSupplierIdBuilder.Parameters);
        
        ObjectDumper.Write(supplierIds);
    
        sqlConnection.Close();
        
    }

Wrap up

In this post, I show how to improve the API by using Dapper Contrib project. Also SqlBuilder class is handy utility to help build dynamic Sql. In the next post, I will show some example using Dapper RainBow. Stay tuned! Again, you can get the source code from here.

Happy Programming!

Dapper .NET Tutorial I

Introduction

One of the common operations when developing a business application is to access the data from relational database. As a .NET developer, we have gone through a very interesting journey to learn how to apply the “best” approach for the data access layer.

Not long ago, “stored procedure” is still the default approach. Until .NET 3.5 was released, more and more .NET developers started to use Linq2Sql, then recently Entity Framework. Some other ALT .Net developers might like to use open source ORM like NHibernate. There is nothing wrong with any of the options there, as long as you know how to use it correctly.

But things don’t go well some time. It requires developers have good knowledge of the ORM tools they are using. It is so easy to get wrong. The well-known “Select + 1” issue is just one of them. Besides that, the sql code generated by ORM, like Linq2Sql and Entity Framework is too complicate, far from optimized. It can make DBA cry.

Both of the framework heavily utilize Linq Expression, which cause big performance overhead when translating to underneath native SQL statements. Fortunately, some smart developers have already realized that. They take a different approach to create so-called micro-ORM. Those frameworks are built directly upon ADO.NET. They are using reflection (dynamic from .NET 4.0) to generate object from the result of data reader. They are simple and perform well. Some of them include:

How to get Dapper

Today, I like to talk about Dapper .net, which is part of the building blocks used in Stackoverflow. This framework is original created by Sam Saffron who is generous to offer it as open source for the whole .NET community. Dapper perform very well because it uses dynamic method generation (MSIL) to assign column values to properties.

There have two ways to reference Dapper in your project.

  1. Since the core of Dapper.net is only one file. It can be easily added to the project. You can directly access its repository and grab the file
  2. In the meanwhile, it is can be obtained from Nuget. If you already installed Nuget. You can install it directly downloads it.  Dapper-Nuget

Using Dapper

Dapper is really simple to use. In this post, I will demonstrate some common usage. In the following blogs, I will show how to use Dapper Contrib and Dapper Rain bow.

Before we start access the database, let’s build our domain objects. Using Northwind as the backend database, I mainly use Product and Supplier table to demonstrate.

First let’s create the POCO objects for Product and Supplier:

public class Product { public int Id { get; set; } public string ProductName { get; set; } public int SupplierID { get; set; } public int CategoryID { get; set; } public string QuantityPerUnit { get; set; } public decimal UnitPrice { get; set; } public short? UnitsInStock { get; set; } public short? UnitsOnOrder { get; set; } public short? ReorderLevel { get; set; } public bool Discontinued { get; set; } // reference public Supplier Supplier { get; set; } }

// Supplier public class Supplier { public int Id { get; set; } public string CompanyName { get; set; } public string ContactName { get; set; } public string ContactTitle { get; set; } public string Address { get; set; } public string City { get; set; } public string PostalCode { get; set; } public string Country { get; set; } public IEnumerable<Product> Products { get; set; } }

  • Select List

    using (var sqlConnection = new SqlConnection(Constant.DatabaseConnection)) { sqlConnection.Open(); IEnumerable products = sqlConnection.Query("Select * from Products"); foreach (Product product in products) { ObjectDumper.Write(product); }

    sqlConnection.Close(); }

    When you run the sample, you can see a list of POCO product objects returned from a ADO-like command. How simple is that! Internally, Dapper .net use MSIL to access data reader then convert to the domain object.

    • Select items by applying parameters To prevent SQL injection, Dapper provide the parameters, similar as using ADO.NET stored procedure.  It is convenient to take an anonymous object as the parameter

    using (var sqlConnection = new SqlConnection(Constant.DatabaseConnection)) { sqlConnection.Open(); IEnumerable products = sqlConnection .Query("Select * from Products where Id = @ProductId", new {ProductID = 2}); foreach (Product product in products) { ObjectDumper.Write(product); }

    sqlConnection.Close(); }

    • Return dynamic object You can return a list of dynamic object (.NET 4.0), which simplified  the code a lot by removing the code to define customized DTO objects if you need to build a object by using the data from different tables.

    using (var sqlConnection = new SqlConnection(connectionString)) { sqlConnection.Open(); IEnumerable products = sqlConnection .Query("Select * from Products where Id = @Id", new {Id = 2}); foreach (dynamic product in products) { ObjectDumper.Write(string.Format("{0}-{1}", product.Id, product.ProductName)); }

    sqlConnection.Close(); }

    • Retrieve multiple objects with one query

      With the feature as QueryMutiple, dapper can return multiple object/list in one query. It definitely improve the performance.

    using (var sqlConnection = new SqlConnection(Constant.DatabaseConnection))
    {
        sqlConnection.Open();
    
        var query = @"
                   SELECT * FROM dbo.Suppliers WHERE Id = @Id
    
                   SELECT * FROM dbo.Products WHERE SupplierID = @Id
    
                    ";
    
        // return a GridReader
        using (var result = sqlConnection.QueryMultiple(query, new {Id = 1}))
        {
            var supplier = result.Read().Single();
            var products = result.Read().ToList();
    
            ObjectDumper.Write(supplier);
    
            Console.WriteLine(string.Format("Total Products {0}", products.Count));
            
            ObjectDumper.Write(products);
        }   sqlConnection.Close(); 
    }
    • Retrieve object with referenced object

      Dapper not only can get the domain directly, but also can retrieve the reference objects together.

    using (var sqlConnection = new SqlConnection(Constant.DatabaseConnection)) { sqlConnection.Open(); IEnumerable products = sqlConnection .Query( @"select Products.*, Suppliers.* from Products join Suppliers on Products.SupplierId = Suppliers.Id and suppliers.Id = 2", (a, s) => { a.Supplier = s; return a; }); // use splitOn, if the id field is not Id or ID foreach (Product product in products) { ObjectDumper.Write(product.Supplier); }

    sqlConnection.Close(); }

    • Select one object with collection of child object Although, by default, dapper .net does not support this feature. But you can find the code posted by Sam from Statckoverflow. It uses QueryMutiple to return multiple lines, then use a dictionary to group the related key (product.SupplierId, in the sample above) together.

     

    private static IEnumerable QuerySupplier()
    {
       using (var sqlConnection = new SqlConnection(Constant.DatabaseConnection))
        {
            sqlConnection.Open();
    
            var query =
                @"
                   SELECT * FROM dbo.Suppliers WHERE ContactName = 'Charlotte Cooper'
    
                   SELECT * FROM dbo.Products WHERE SupplierID 
                IN (SELECT Id FROM dbo.Suppliers WHERE ContactName = 'Charlotte Cooper')
                 ";
    
            return sqlConnection
                .QueryMultiple(query)
                .Map(supplier => supplier.Id,
                                             product => product.SupplierID,
                                            (supplier, products) => 
                                            { supplier.Products = products; });
        }
        sqlConnection.Close(); 
    }
    public static IEnumerable Map
            (this SqlMapper.GridReader reader,
             Func firstKey, 
             Func secondKey, 
             Action> addChildren    
           )
    {
                   var first = reader.Read().ToList();
                   var childMap = reader
                       .Read()
                       .GroupBy(s => secondKey(s))
                       .ToDictionary(g => g.Key, g => g.AsEnumerable());
    
                   foreach (var item in first)
                   {
                       IEnumerable children;
                       if(childMap.TryGetValue(firstKey(item), out children))
                       {
                           addChildren(item,children);
                       }
                   }
    
           return first;
    }
    • Insert using Query interface

      If we like to retrieve the new Id after insertion, we can use Query interface to retrieve the new identifier directly

    using (var sqlConnection = new SqlConnection(Constant.DatabaseConnection))
    {
        sqlConnection.Open();
    
        var supplier = new Supplier()
        {
            Address = "10 Main Street",
            CompanyName = "ABC Corporation"
        };
    
        supplier.Id = sqlConnection.Query(
                            @"
                                insert Suppliers(CompanyName, Address)
                                values (@CompanyName, @Address)
                                select cast(scope_identity() as int)
                            ", supplier).First(); 
    
        sqlConnection.Close();
    
        Console.WriteLine(supplier.Id);
    
        Console.WriteLine("Done. ");
    }
    • Insert using Sql Extension . Insertion can be applied by using a simple Insert statement.  
    using (var sqlConnection
                = new SqlConnection(Constant.DatabaseConnection))
    {
        sqlConnection.Open();
    
        var supplier = new Supplier()
        {
            Address = "10 Main Street",
            CompanyName = "DEF Corporation"
        };
    
        sqlConnection.Execute(
                            @"
                               insert Suppliers(CompanyName, Address)
                               values (@CompanyName, @Address)
                            ",  supplier); 
    
        sqlConnection.Close();
    
        Console.WriteLine("Done. ");
    }
    • Update Item Updating a item has similar syntax as inserting. It uses update Sql statement with an anonymous object as the parameter.
    using (var sqlConnection = new SqlConnection(Constant.DatabaseConnection))
    {
        sqlConnection.Open();
    
        var updateStatement = @"Update Products Set UnitPrice = @UnitPrice
                                Where Id = @ProductId
                                ";
    
        sqlConnection.Execute(updateStatement, new
                                                   {
                                                       UnitPrice = 100.0m,
                                                       ProductId = 50
                                                   });
        sqlConnection.Close();
    }

      Wrap up

      In this post, we show some common data access scenarios by applying the “Core” implementation of Dapper.NET. Besides that, Dapper.NET has provided some other different API to make the data operation more intuitively. In the next post, we will show how to use Dapper Contri

      You can get the source code from here.

      Happy Programming!