I’m Sold on Dapper

Don’t get me wrong, I love my LINQ. I just have mixed feelings about LINQ2SQL, or anything that promises to make my life easier by allowing me to write fewer SQL statements.

I’ve been burned a few times too many by seemingly innocuous LINQ2SQL queries that ended up ballooning into resource hogs once deployed to the real world. Often, it’s a sleeper; some query that’s been running just fine for years and then, BAM! You get jolted by a spiked CPU like a shovel to the face because an email campaign hit some remote part of the site that hadn’t been pored over. A little digging finds that LINQ2SQL has drunkenly taken over the kegger at your parents’ house, smashing lamps and vases and shoving your friends into the pool, wreaking all sorts of havoc and running your CPU off the charts.

It’s that friend you learn to limit. He may be great in certain situations, like running the basic CRUD (Create, Read, Update, Delete) routines on all those cumbersome admin screens, but once you take him into the real world, once you expose him to all your other friends on your high traffic ecommerce site, once you give him a broader audience, you run the risk that he’ll show his true colors, and you may not like what they see.

Such has become my relationship with any ORM that promises to lift the burden of having to write straight SQL. It’s fine in the right circumstances and saves loads of time writing basic operations. But once you cook up a slightly more complex query and roll it into a public website with tens of thousands of hits an hour, it’s just not enough. Trusting the black box of ORM SQL generation often turns out to be a risky endeavor.

I’d rather be in direct control of what SQL gets executed when writing finely tuned database access. Thus, I’ve come to love what Dapper has to offer. Dapper, by the folks over at stackoverflow.com, is an extremely lightweight data access layer optimized for pulling raw data into your own POCOs (Plain Old C# Objects). It’s that perfect fit between the nauseatingly redundant world of SqlCommands and DataReaders, and the overzealous and overbearing friend you find in LINQ2SQL. No longer do I have to guess at what kind of query an ORM is going to generate. No longer do I have to worry that LINQ2SQL is going to fly off the handle and take up all my CPU trying to compile the same dastardly query over and over again. I can instead write the SQL myself and get it into my POCO of choice with less effort than it takes to bash my head on the keyboard.

For example, let’s say I’ve got this domain object:

public class OmgWtf
{
public string Acronym { get; set; }
public string Sentence { get; set; }
}

All I have to do to yank the data from the database is this:

using (var conn = new SqlConnection(ConnString))
{
conn.Open();

string sql = @"
SELECT TOP 1 omg.Acronym, wtf.Sentence
FROM OnoMatopoeicGiddiness omg
JOIN WordsToFollow wtf ON wtf.OmgID = omg.ID
WHERE wtf.ID = @WtfID";

var omgwtf = conn.Query<OmgWtf>(sql, new { WtfID = 3 }).First();

Console.Write("{0}: {1}", omgwtf.Acronym, omgwtf.Sentence);
}

The result is, of course:

SQL: I Squeal for SQL!

No longer do I have to suffer the fate of black box SQL generation when all I really want is a clean, easy, and fast way to get my SQL or stored procedure results directly into my domain objects. I’m sold on Dapper for many of my high-performing pages. As we maintain our sites and find the occasional bloated LINQ2SQL resource hog, we’re swapping out the queries to straight SQL, stored procedures, and Dapper, and it has really sped things up.

Go ahead, give it a shot yourself. It’s available on the NuGet Gallery, and only imports a single C# file; no extra assemblies required. They’ve got plenty of examples at the project site. I’m wondering how I ever lived without it.

Leave a Reply

Your email address will not be published. Required fields are marked *