Dapper ORM: An Introduction to the Lightning Fast Micro ORM

What is Dapper?

• A very fast object relational mapper (ORM). Its performance is on par with regular ole ADO.NET.
• A lightweight ORM. Dapper extends the IDbConnection interface.

What is Dapper NOT?

• A fully featured ORM. There's no code-first migrations, no change tracking, or other "heavyweight" features. For that, I'd recommend Entity Framework Core.
• Preventing you from writing SQL. Unlike Entity Framework, Dapper is going to require that you write out all of your SQL.

Which Is Better, Dapper or Entity Framework?

The answer is... yes. Seriously though, it depends. If you have an application where data access performance is absolutely paramount and your development team is filled with individuals with strong SQL skills then perhaps Dapper would be the better choice. If you're willing to concede that you won't get the absolute best performance, you're looking for potentially faster development time, or you don't have a lot of skilled SQL developers on your staff, then Entity Framework Core might be the best choice.

With that said, Entity Framework performance has improved lots over the years. The latest framework, Entity Framework Core, by itself has many performance improvements over Entity Framework 6. In my blog post about comparing Entity Framework Core Vs. Entity Framework 6, I show where there's a potential for several times performance improvement by switching from Entity Framework 6 to Entity Framework Core.

Compatibility with Database Management Systems (DBMS)

Dapper is compatible with almost any DBMS since it extends the IDBConnection interface. You're responsible for providing the connection string and the SQL to your database provider of choice, and Dapper does the rest.

In the worked examples I show below, I'll be using SQL Server, but if you wanted to use Postgres, SQLite, MySQL/MariaDB, or Oracle, that wouldn't be an issue. Simply provide the connection string and your SQL to the SqlConnection, and you're off to the races with Dapper.

Is Dapper Safe?

Yes, Dapper is safe, provided that you use good practices and parameterize your queries. Dapper provides a very easy means to parameterize your SQL statements as I'll show in the worked examples below.

Getting Started with Dapper ORM

To start using Dapper, simply add the Dapper nuget package to your project.

Package Manager

If you prefer using the package manager console, run the following to add Dapper to your project.

Install-Package Dapper

.NET Core CLI

If you want to add Dapper to a .NET Core project, you can run the following command in your favorite command line.

dotnet add package Dapper

Worked Examples - Setting Up a SQL Server Database

In the coming examples, I'll be querying my local SQL Server Express instance. I'll be using the same BooksDB that I've used in prior articles, such as my performance comparison of Entity Framework 6 to Entity Framework Core. Feel free to look at that code here on my Github.

Query Data with Dapper

Dapper provides several methods to fetch data. Typically the methods take in the generic parameter of the type you want to map, and the first parameter is your SQL statement, and the second parameter are the database parameters you wish to parameterize with your SQL statement. For my examples, I'll be using the Book object within the benchmarking project I used in comparing the performance of Entity Framework 6 to Entity Framework Core.

public class Book
{
    public int BookId { get; set; }
    public string Name { get; set; }

    public int AuthorId { get; set; }
    public virtual Author Author { get; set; }

    public virtual IList Copies { get; set; }
}

Query a Single Record

To fetch a single record in Dapper, Dapper has the QueryFirst<>, QueryFirstOrDefault<>, QuerySingle<>, QuerySingleOrDefault<> methods and their async counterparts. In the code below, I'm going to fetch the Book with a BookId of 1 using QueryFirst<>.

using var db = new SqlConnection(_connString);
return db.QueryFirst<Book>(
    @"SELECT * FROM Book WHERE BookId = @BookId",
    new
    {
        BookId = 1
    }
);}

Below are other methods we can use to query a single record and some example code.

await db.QueryFirstAsync<Book>(
    @"SELECT * FROM Book WHERE BookId = @BookId",
    new
    {
        BookId = 1
    }
);}

db.QueryFirstOrDefault<Book>(
    @"SELECT TOP (1) * FROM Book"
);

await db.QueryFirstOrDefaultAsync<Book>(
    @"SELECT * FROM Book WHERE BookId = @BookId",
    new
    {
        BookId = 1
    }
);}

db.QuerySingle<Book>(
    @"SELECT * FROM Book WHERE BookId = @BookId",
    new
    {
        BookId = 1
    }
);}

await db.QuerySingleAsync<Book>(
    @"SELECT * FROM Book WHERE BookId = @BookId",
    new
    {
        BookId = 1
    }
);}

db.QuerySingleOrDefault<Book>(
    @"SELECT * FROM Book WHERE BookId = @BookId",
    new
    {
        BookId = 1
    }
);}

await db.QuerySingleOrDefaultAsync<Book>(
    @"SELECT * FROM Book WHERE BookId = @BookId",
    new
    {
        BookId = 1
    }
);}

Query Multiple Records

To fetch a collection of objects from the database, Dapper has the Query<> method and of course its async counterpart QueryAsync<>.

using var db = new SqlConnection(_connString);
return db.Query<Book>(
    @"SELECT * FROM Book WHERE BookName LIKE '%book%'"
);

The above will retrieve all books with "book" in the name. How would the asynchronous version look?

await db.QueryAsync<Book>(
    @"SELECT * FROM Book WHERE BookName LIKE '%book%'"
);

QueryAsync is the same as Query except that it returns a Task<IEnumerable<Book>>, so you will have to deal with that accordingly.

Execute SQL Commands with Dapper

If we wanted to issue an UPDATE, INSERT, DELETE, or other DML to our database, Dapper provides us the Execute method. The method will execute the SQL string you provide against the database, and return an int that tells us how many rows were affected by the statement.

var parameters = new
{
    BookId = 1,
    Name = "How to Name a Book for Dummies"
};
using var db = new SqlConnection(_connString);
db.Execute(
    "UPDATE Book SET [Name] = @Name WHERE BookId = @BookId",
    parameters
);

Dapper Has Many Other Capabilities

While this is mostly an introduction to using Dapper for your data access code, Dapper has many other more advanced features such as using stored procedures, multi-mapping multiple types with a single query, and mapping your objects to multiple result sets within the same query.

Check out many of Dapper's other advanced features on their Github readme. Their page also contains results of benchmark across many other popular data access libraries, such as a EF Core, EF6, PetaPoco, ADO.NET, and more. I find it interesting Dapper actually performs faster than certain equivalent ADO.NET benchmarks.

Wrapping Up

In summary, Dapper provides an easy way to write very fast data access code in your .NET apps. It's not going to provide all the features as other more fully featured ORMs, such as Entity Framework. However, it will perform equivalent tasks faster than Entity Framework.

If performance is at the top of your priorities, Dapper is worth exploring. It performs just as fast, if not faster, in some cases as regular ADO.NET, but without writing a lot of the extra boilerplate ADO.NET code.

I've seen cases where the developers will have all of the data modification code written in Entity Framework for it's change tracking capabilities and no need to maintain SQL within the application code. But for the presentation of read-only data, which can often be the bulk of an app's workload, queries are written in Dapper.

In the end, it's worth exploring to see what best suits your app's and your development team's needs. I hope this provided a gentle exposure to Dapper and how it can benefit your present and future app development.

Happy coding!