How to use the Dapper ORM in C#

Object relational mappers (ORMs) have been in use for a long time to eliminate the impedance mismatch that exists between the object models of programming languages and the data models in relational databases. Dapper is an open source, lightweight ORM developed by the Stack Overflow team. Dapper is very fast compared to other ORMs primarily because of its light weight.

Dapper was built with performance and ease of use in mind. It provides support for both static and dynamic object binding using transactions, stored procedures, or bulk inserts of data.

Download and install Dapper ORM

To get started using Dapper, follow these steps:

Open Visual Studio

Click on File -> New -> Project

Select “Web -> ASP.Net Web Application” from the “New Project” dialog

Specify a name for the web project

Select the empty project template for ASP.Net

Click OK to save the project

This creates an empty ASP.Net web application project.

If you have NuGet installed, you can install Dapper using NuGet—just select the project in the Solution Explorer window, right click on “Manage NuGet Packages…” and find Dapper. Then click on Install to start installation of Dapper. Once Dapper has been successfully installed, you are good to go.

CRUD in .Net using Dapper ORM

Let’s now write some code using Dapper to perform CRUD operations against a database. Consider a database named IDG that contains a table called Author with the following fields.

You should create an entity class (POCO class) for this database table for simplicity when working with Dapper. Here’s the entity class named Author that corresponds to the Author table in the IDG database.

The Query() extension method in Dapper enables you to retrieve data from the database and populate data in your object model. The following method retrieves all the records from the Author table, stores them in memory, and returns the collection.

The Execute() method of the Dapper framework can be used to insert, update, or delete data into a database. This method returns an integer value that implies the number of rows that have been affected on execution of the query.

The following method illustrates how you can update a record using the Dapper framework.

As you can see in the above code snippet, the Update() method returns the number of rows that have been affected, meaning the number of records that have been updated. In this example, just one record has been updated and hence the method would return 1 on success.

Stored procedures using Dapper ORM

To work with stored procedures using Dapper, you should mention the command type explicitly when calling the Query or the Execute methods. Here is an example that shows how you can use stored procedures with Dapper.

The Dapper framework also supports transactions, i.e., you can use transactional operations if needed. To do this, you can take advantage of the BeginTransaction() and EndTransaction() methods as you usually do when working with transactions in ADO.Net. You would then need to write your transactional statements inside the BeginTransaction and EndTransaction method calls.

The Dapper micro ORM is extremely lightweight and simple to use. It doesn’t generate your SQL for you, but makes it easy to map the results of queries to your POCOs (plain old CLR objects). Best of all, you get much faster speed of execution than you do with Entity Framework—almost the same as ADO.Net, in fact.