Fasten your Data Layer with Dapper (A .NET Micro ORM)

Introduction

Dapper is .NET based light-weight, fast and simple to use Micro ORM created by brilliant Stack Exchange team. The word ORM means Object Relation Mapper, means it facilitates mapping b/w .NET objects and database and that is its focus.

One great thing about Dapper 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 sample application from this GitHub repo.

Simplified API

The simple API from Dapper, make it very easy to use. Dapper provide 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 dapper main package to our project.

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

I will be using Postgres and another package we need to install is Npgsql:

Other database providers

We’ll be not using those but if you want to use dapper with SqlServer you can install the following package:

System.Data.SqlClient

For MySQL install the following package:

MySql.Data

Database Setup

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

Here is the table information for your reference:

and I have following data inside the table:

Getting data from database using Dapper

Lets start with a simple bare-minimum example. Following is the code to query the database (users table) and work with data:

The code is self explanatory and following is the output of executions:

Getting the database table Column-Names

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, however, I found dapper code a little more concise:

Dynamic C# in Action

Now, I don’t want to distract you, but in two examples above, dynamic C# was in action. You can learn more about it on 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 concern in a repository class just for a little bit Separation of Concern.

First, I created a class to hold User data:

Next, I created a UserRepository class with following Code:

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

and here is the output:

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

Formatting Output with YamlDotNet

This is not related to dapper but you can use YamlDotNet nuget package to format the output easily.

Following extension method wraps this functionality:

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

and we have nicely formatted output as follows:

But, we can always do better, the following change reduce the code more and 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 database.

here is the repo code:

and we can see that data is inserted once we execute the code:

Get Data By Id

This is also 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 similar and check below the code in Main method of our program:

Here is the repository implementation for the method:

The sql 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 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 very less learning curve compare to other ORMs. It works well with popular relational database.

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

Related Articles

• https://hexquote.com/fun-with-sql-using-postgres-and-azure-data-studio/
• https://hexquote.com/dynamic-c-introduction/

My Recent Books