Apr 2013

Embracing Dapper as my ORM of Choice

by Steve Wortham
An Object Relational Mapper, or ORM, can be a great way of improving code readability and resilience.  The idea is to perform your basic CRUD statements against your database with a small layer in between that handles type conversions so you don't have to. For instance, SQL has multiple types for dates and strings whereas .NET really just has one date type, and one string type. Having something to bridge that gap in type systems can really be handy.

My first experience with an ORM was with LINQ to SQL.  Then soon after that I tried Entity Framework.  Working with either of these is similar as the primary way you're going to write queries with these is through LINQ.  The LINQ statements then are translated into SQL, and it's a pretty sweet set up most of the time.  

However, these two ORMs have a lot of functionality behind the scenes which you really have to learn about when troubleshooting performance problems and such.  The problem then is that the abstraction level is so high that when there's a problem it's hard to figure out how to solve it.  And I mean, why spend all of this time refining your LINQ queries to spit out better SQL queries, when you can just write a straight SQL query that does what you want more efficiently?

Dapper: the micro-ORM

So that's what Dapper is all about.  Dapper could be classified as a micro-ORM.  It really is just a bare minimum implementation of an ORM with two main extension methods to work with.  You have Query and Execute.  That's all.  The simplicity of the API makes it beautiful to work with.  It doesn't hurt that it's also one of the fastest and most efficient ORMs out there.

There is one extra step you have to follow when working with Dapper.  And that is, you have to manually write your classes that map to your database.  Both Linq to SQL and Entity Framework have tight integration with Visual Studio and will do this for you.  But with Dapper I had to create a class for my SQL table called CarModel...
public class CarModel
{
    public int CarModelID { get; set; }
    public string Make { get; set; }
    public string Model { get; set; }
    public string ModelUrl { get; set; }
    public int Year { get; set; }
}


Here's a simple query which returns a Dictionary<int, CarModel> ...
using (var conn = Db.OpenConnection)
{
return conn.Query<CarModel>("SELECT * FROM CarModel").ToDictionary(k => k.CarModelID);
}


Here's another example where I'm getting the logged in user's UserID with a parameterized query...
public static int? UserId
{
    get
    {
        if (!HttpContext.Current.User.Identity.IsAuthenticated) return null;
        using (var conn = Db.OpenConnection)
        {
            return conn.Query<int>("SELECT UserId FROM UserProfile WHERE UserName = @UserName",
                                     new { @UserName = HttpContext.Current.User.Identity.Name }).FirstOrDefault();
        }
    }
}

Even though Dapper isn't as rich in features as something like Entity Framework, I still feel like it's saving me time in the long run while also allowing me to write faster and more efficient applications.





comments powered by Disqus