Upgrade Your Data Layer With Dapper (a .NET Micro ORM)

Introduction

Dapper is a .NET-based, light-weight, fast, and simple to use micro ORM created by the brilliant Stack Exchange team. The word ORM means Object Relation Mapper, which means it facilitates mapping between .NET objects and databases.

One great thing about Dapper is that it works with any database. So, its not just for SQL Server. You can use it with PostgreSQL, MySQL, or others.

You can download the code for the sample application from this GitHub repo.

Simplified API

The simple API from Dapper makes it very easy to use. Dapper provides three primary helper methods:

• A Query method that maps to strongly typed objects.
• A Query method that maps to dynamic objects.
• An Execute method for commands that do not return results (e.g. Insert).

We will see these functionalities in action by creating a .NET Core application.

Create .NET Core Console Application

I started by creating a .NET Core Console application using Visual Studio:

Install Nuget Packages

First we install the Dapper main package to our project:

As mentioned above, you can use Dapper with a variety of databases and, in order to work with a database, you need to install the corresponding Nuget package.

I will be using Postgres for the database and we need to install another package called Npgsql:

Other Database Providers

We won't be using these, but if you want to use Dapper with SQL Server, you can install the following package:

System.Data.SqlClient

For MySQL, install the following package:

MySql.Data

Database Setup

I have a Postgres database setup with a users table. I will be using this for demos in the post. You can refer to my earlier post on DZone Fun With SQL Using Postgres and Azure Data Studio for more information.

Here is the table information for your reference:

And I have the following data inside the table: 

Getting Data From the Database Using Dapper

Let's start with a simple bare-minimum example. The following is the code to query the database (users table) and work with data:

The code is self explanatory, let me know if you have some questions, and following is the output: 

Getting the Database Table Column-Names

The following code block shows how can we get this information using Dapper. You can achieve this result in some other way, e.g. by using reflection, etc., however, I found Dapper code a little more concise:

Dynamic C# in Action

Now, I don’t want to distract you, but in the two examples above, dynamic C# was in action. You can learn more about it in my post Dynamic C# Introduction.

Dapper With Strongly Typed C# Classes

If you want to map Dapper results into strongly typed C# objects, we can easily do this. However, this time I will encapsulate data access concerns in a repository class just for a little bit of Separation of Concerns.

First, I created a class to hold user data, called User:

Next, I created a UserRepository class with the following code:

Finally, in the Main method, I used the repository and output the result as shown below: 

And here is the output:

One thing to notice here is that the property names (PascalCase) in the User class are different than database column name (lower case). But Dapper automatically does the mapping for us.

Formatting Output With YamlDotNet

This is not related to Dapper, but you can use the YamlDotNet Nuget package to format the output easily.

The following extension method wraps this functionality:

Now, we can use this in our Main method as shown below:

And we have a nicely formatted output as follows:

But, we can always do better. The following change reduces the code to a more precise output: 

But why stop here? You can chain it further as follows:

Next, let’s see the rest of the repository implementation.

Create Data

Let’s insert a new record in the database.

Here is the repo code:

And we can see, data is inserted once we execute the code:

Get Data by ID

This is also a very common requirement and now we will see a simple implementation to get data by id.

Here is the method call:

The repository code is shown below:

And finally the output:

Updating Data

Updating is simple; see the below code in the Main method of our program:

Here is the repository implementation for the method:

Our SQL code is parametrized and safe from SQL injection. Also, user class properties will be automatically mapped to parameters.

Executing the code confirms that data was updated as expected:

Deleting Data

Now complete CRUD operations by implementing the delete method:

The repo code:

And here, the table data to confirm that record with id = 5 is deleted.

Summary

Dapper is a very fast and easy to use ORM. It has very simple API and it is blazing fast. It is easy to use in .NET Core projects and it requires a smaller learning curve compared to other ORMs. It works well with popular relational databases as well.

You can get to the source code for the demo application on the GitHub at this link. Let me know if you have some comments or questions. Till next time, happy coding!