Over a million developers have joined DZone.

Dapper.Net - A Micro ORM that puts you back in control

DZone's Guide to

Dapper.Net - A Micro ORM that puts you back in control

Free Resource

Its fun to reminisce about how database access has evolved over the years. In the early days I used to write parameterized SQL statements directly in my code. Eventually that evolved into using stored procedures. Mainly because by using a stored procedure I could change the way data was gathered without recompiling my source code. Eventually, I abandoned stored procedures altogether when LINQ to SQL was released. Finally, if we fast forward to the current day, my preferred method of accessing data is with EF code first. EF Code first is great because I can spend even less time managing my database and more time solving business problems. With the level of abstraction getting higher and higher it makes you wonder if perhaps the next generation of developers will not even write T-SQL anymore. They will probably laugh at us old timers when we talk about stored procs and T-SQL. 

Wait! Back up a minute! Just because we have abstracted ourselves from the database doesn’t mean that we have evolved into something superior. In my past life as a DBA, I spent many of long afternoons diagnosing performance issues. Most of the issues were due to auto-generated SQL statements from an ORM. Sometimes you could put a band-aid on the problem by adding a few indexes or modifying the source code to force different SQL to be produced. However, I was always frustrated by the fact that I had to trade abstraction for performance. Wouldn’t it be nice to have both….

Well you can. Sam Saffron, one of the geniuses at Stack Overflow, came up with a micro ORM called dapper dot net. Dapper dot net is a single file that you can drop into your project that will extend your IDbConnection interface. As I have said so many times before, let’s have the code do the talking.

Executing a query and mapping the results to a strongly typed list

public class Dog
    public int? Age { get; set; }
    public Guid Id { get; set; }
    public string Name { get; set; }
    public float? Weight { get; set; }

    public int IgnoredProperty { get { return 1; } }
var guid = Guid.NewGuid();
var dog = connection.Query<Dog>("select Age = @Age, Id = @Id", new { Age = (int?)null, Id = guid });



Notice that strange stuff in the middle. Yeah, that’s called a SQL Statement. Anyways, my point is that we get full control over our SQL statement and in addition we also get a strongly typed result set. Pretty sweet huh?


var sql = 
@"select * from #Posts p 
left join #Users u on u.Id = p.OwnerId 
Order by p.Id";
var data = connection.Query<Post, User>(sql, (post, user) => { post.Owner = user; });
var post = data.First();
post.Content.IsEqualTo("Sams Post1");

This is an example of mapping a single row to multiple objects. In EF or Linq to SQL you would accomodate this functionality by leveraging a foreign key relationship. Once again, we have full control of the SQL and strongly typed results.

Multiple Results

var sql = 
select * from Customers where CustomerId = @id
select * from Orders where CustomerId = @id
select * from Returns where CustomerId = @id";
using (var multi = connection.QueryMultiple(sql, new {id=selectedId}))
   var customer = multi.Read<Customer>().Single();
   var orders = multi.Read<Order>().ToList();
   var returns = multi.Read<Return>().ToList();

Multiple resultsets, a single trip to the server and full control of the SQL. Need I say more?

So Should You Abandon Your Current ORM?

In my opinion, a developer should never settle on a single tool. Being abstracted from the nitty gritty details of writing SQL allows a developer to be more productive. In addition, if you are build small to medium size applications you can afford a small performance penalty because you are not dealing with large amounts of data. Unfortunately, you can't always predict the future of your application. You may start out with 10 users and end up with 10 thousand users. So perhaps the strategy is to write most of your application using an ORM like EF Magic unicorn and then use dapper for the parts of your application that need to be performant. This way you get the best of both worlds!


Published at DZone with permission of Michael Ceranski, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}