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!

Advertisements

2 thoughts on “Dapper.NET Tutorial III

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