Upgrade Your Data Layer With Dapper (a .NET Micro ORM)
A developer gives a tutorial on how to get started with Dapper, a light-weight .NET ORM (object relation mapper), running through all the code you need.
Join the DZone community and get the full member experience.Join For Free
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.
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:
For MySQL, install the following package:
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
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.
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 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:
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.
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!
Published at DZone with permission of Jawad Hasan Shani. See the original article here.
Opinions expressed by DZone contributors are their own.