What is Dapper, and why you should consider it for your .NET projects

This post was originally hosted on my site, https://consultwithgriff.com - check it out!

On the speaking circuit, I've been doing a good number of presentations on Dapper. My talk is entitled Better Object Mapping in .NET with Dapper and this is an attempt to catch the eyes of developers who might have heard of object mapping from things like Entity Framework.

What is Dapper?

Dapper is a Micro-ORM or Micro Object Relational Mapper.

Maybe it's better to start at the beginning. If you're using a relational database, such as SQL Server, MySql, or PostgreSQL, you probably have had to deal with the problem of "how do I turn a result set into an object I can actually do work with?"

There are a ton of ways to do this currently:

Write your own mapping from a result set.

This process is really tedious, and I've written my fair share of code that gets the string from ordinal position 0 and the long from ordinal position 52.

I'm not even going to show demo code, because I don't want you to write it. Use Dapper.

EDIT: Ok - here's how'd you write it.

Imagine you have a (relational) database table called Users. It has columns in it.

If you wanted to query that content, you could simply write the following SQL:

SELECT Id, FirstName, LastName, EmailAddress, DateOfBirth
FROM [dbo].[Users]

And if you wanted to get that data into an object that is actually useful... you'd... well..

public class ApplicationUser
{
    public long Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string EmailAddress { get; set; }
    public DateTime DateOfBirth { get; set; }
}

Yeah! Write a DataReader...

var sql = @"SELECT Id, FirstName, LastName, EmailAddress, DateOfBirth
        FROM [dbo].[Users]";
using (var connection = new SqlConnection(CONNECTION_STRING))
{
    await connection.OpenAsync();
    using (var reader = await connection.ExecuteReaderAsync(sql))
    {
        var applicationUsers = new List<ApplicationUser>();
        while (await reader.ReadAsync())
        {
            applicationUsers.Add(new ApplicationUser()
            {
                Id = reader.GetInt64(0), // ordinal position
                FirstName = reader.GetString(1),
                LastName = reader.GetString(2),
                EmailAddress = reader.GetString(3),
                DateOfBirth = reader.GetDateTime(4)
            });
        }
        return applicationUsers;
    }
}

Not bad, right? Maybe not, until you're trying to retrieve a 10+ column result set.

Also, what if you want to change the order of your parameters? You need to reorder the ordinal positions of your readers.

Yes, you can get the ordinal by calling reader.GetOrdinal("EmailAddress");. Please don't do that in the .ReadAsync() loop though. Call it once outside of the loop and cache the results. Calling GetOrdinal for each column on each row read is expensive and will slow down your application.
And the more obvious detail, you need to know the data types for the columsn you're retrieving! Is it a string? Or a datetime? Or an Int16, Int32, or Int64?? Wowza.

Data Tables

The first .NET project I worked on back in 2007 used DataTables exclusively for all data access. This was to the extent where I believed DataTables were the ONLY way to get the results of a SQL Query.

And DataTables aren't too bad. The biggest downside to them is that they're memory hogs, and using them with larger datasets can imped performance.

Example:

var sql = @"SELECT Id, FirstName, LastName, EmailAddress, DateOfBirth
        FROM [dbo].[Users]";

using (var connection = new SqlConnection(CONNECTION_STRING))
{
    await connection.OpenAsync();
    using (var reader = await connection.ExecuteReaderAsync(sql))
    {
        DataTable table = new DataTable();
        table.Load(reader);
        var emailAddress = table.Rows[0]["EmailAddress"];
    }
}

Again - not bad! This approach is more accessible (in my opinion) than the DataReader.

Entity Framework

EF is the 6,000-pound gorilla in the room. It's what Microsoft recommends using for data access. And I have a lot of opinions on why I don't like Entity Framework for data access, but I don't feel this is the place nor the time.

By the way, it's great for demos.

Other ORMS options

Here are some other ORMs that are interesting to look at:

• Massive
• PetaPOCO
• nHibernate

What makes Dapper special?

At its core, Dapper is simply a collection of extension methods off of a SqlConnnection object.

Here is an example of using Dapper to query the database and return a list of objects.

Remember our object?

public class ApplicationUser
{
    public long Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string EmailAddress { get; set; }
    public DateTime DateOfBirth { get; set; }
}

Here's the Dapper approach to Querying and Mapping the results to an object (or in our case, a collection of objects).

using (var connection = new SqlConnection(CONNECTION_STRING))
{
    var sql = "SELECT Id, FirstName, LastName, EmailAddress, DateOfBirth From [dbo].[Users]";

    var results = await connection
        .QueryAsync<ApplicationUser>(sql);
}

That's it. No need to .Open the connection. Just create it and call the Query or QueryAsync method.

What about parameters? How do I avoid SQL injection?

That's a great question. Let's go back to the DataReader example and see how it was done there:

var sql = @"SELECT Id, FirstName, LastName, EmailAddress, DateOfBirth
                    FROM [dbo].[Users] WHERE FirstName = @firstName";
using (var connection = new SqlConnection(CONNECTION_STRING))
{
    await connection.OpenAsync();
    using (var command = connection.CreateCommand())
    {
        command.CommandText = sql;
        command.Parameters.AddWithValue("firstName", "Kevin");
        using (var reader = await command.ExecuteReaderAsync())
        {
            var applicationUsers = new List<ApplicationUser>();
            while (await reader.ReadAsync())
            {
                applicationUsers.Add(new ApplicationUser()
                {
                    Id = reader.GetInt64(0), // ordinal position
                    FirstName = reader.GetString(1),
                    LastName = reader.GetString(2),
                    EmailAddress = reader.GetString(3),
                    DateOfBirth = reader.GetDateTime(4)
                });
            }
            return applicationUsers;
        }
    }
}

What's the difference? First, the SQL statement has a WHERE clause in it. I've added a parameter with the @firstName identifier.

Note: Please please please parameterize your SQL queries. This is one of the easiest things you can do to protect your databases.

In order for the query to work, you need to pass the parameter value to the data reader. I had to take an extra step by creating a full SqlCommand instead of just executing a reader. Not a big deal - but it's necessary to add parameters.

command.Parameters.AddWithValue("firstName", "Kevin"); tells the command which parameter I want to map to and the value to use for it.

Now! Let's do the same with Dapper.

var sql = @"SELECT Id, FirstName, LastName, EmailAddress, DateOfBirth
        FROM [dbo].[Users] WHERE FirstName = @firstName";
using (var connection = new SqlConnection(CONNECTION_STRING))
{
    var results = await connection
        .QueryAsync<ApplicationUser>(sql, new { firstName = "Kevin" });
}

The next parameter of QueryAsync (or many other Dapper methods) is a parameter object.

I'm passing an anonymous object for my parameters, and Dapper will assist with telling the database that I want @firstName to map to Kevin. I don't need to worry about any of that work.

It seems like you're over-simplifying?

I totally am. But my goal here is to tell you what Dapper is meant to do.

Most database operations you need to make: SELECT, UPDATE, INSERT, DELETE are supported with a couple of lines of code. I'm planning several follow up posts that cover scenarios around these topics, but I felt this article would become more reference than "hey, this is a tool I like and you should check it out."

Rob made a great point earlier, I'm not really diving deep enough into mapping. There is a rabbit hole of use-cases where Dapper can be combined with other awesome libraries, like AutoMapper. I'm not diving in deep here, but it seems like a great idea for future posts.

Use Stored Procedures? No problem

I also use Dapper in every single application I scaffold out. It's that critical to my project success.

Is it open-source?

Yes! And even better, it's built by the good folks over at StackOverflow. Maybe you've heard of it. It's one of the most frequented websites on the internet, and they do not play around when it comes to performance.

Dapper was created by their team in order to make mapping data to objects as painless as possible while being fast. Check out their performance benchmarks

Give it a try!

If you're jumping into Dapper for the first time, take it easy! There are a lot of great examples in the GitHub repo for getting started.

If you run into problems, feel free to ask me on Twitter. I'm happy to research your questions and write some detailed explanations on more complex use-cases.

Also - if you made it this far, follow me on Twitch. I do a daily stream where I work on random projects. I'd love to chat with you!