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!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s