Absolute Beginner’s Tutorial on understanding and using Dapper ORM

This article introduces Dapper to absolute beginners. The article is meant for developers who are primarily using ADO.NET to write their data access layers. Many experienced developers will find this article very basic but since the article is written from the perspective of beginners, I’ve tried to keep things simple.

Background

ADO.NET is a very strong framework for data access. ADO.NET has been around since many years and there are a lot of systems running over ADO.NET. Developers who are totally oblivious to the concept of ORMs will probably be asking “What is dapper? What are the benefits of using it and is it an alternative to ADO.NET?”

Let’s first start the discussion by understanding what an ORM is: an ORM or Object-relational mapper in piece of software that help us in converting data between incompatible type systems i.e. relational databases and object-oriented programming languages. There are many ORMs being used like Entity Framework(EF), NHibernate, Dapper etc. Dapper is often called as Micro ORM as it is light weight and does not provide so many features that are provided by other ORMs like NHibernate or Entity Framework. The main focus of Dapper is on performance and simplicity of use.

Other ORMs like Entity Framework generates the queries automatically based on how the Objects are being used/fetched (CRUD) in the application. This process has some performance overhead and the generated queries are often black box for the developers. Dapper, on the other hand, let developers write their own SQL to perform database operations and map the results to C# objects i.e. POCOs (Plain Old CLR Objects). Since we as developers are writing queries and dapper and only doing the mapping to POCOs, it is being called as micro ORM or mini ORM. But because of the same reason, the performance of dapper is almost same as using plain ADO.NET.

In the remaining article, we will try to focus on how to use Dapper for basic database operations. We will be creating a simple API that will let the users perform CRUD operations using the API itself and internally we will use dapper to perform the operations on the database.

Note: Since the focus of this article is on understanding Dapper, some shortcuts have been taken in API design to keep things simple.

Dapper Essentials

Lets start by understanding the basic constructs of dapper so that we can use it effectively.

The best way to understand dapper is to think of as an extension(enhanced features) to existing IDbConnectionobject. Dapper is nothing but a set of extension methods for IDbConnection. This is what makes is so powerful in terms of performance but leaves the scope for defining queries to the user much like vanilla ADO.NET. All these extension methods reside in “Dapper” namespace and to use dapper methods, we need to include this namespace in our code.

Now lets look at the basic commands that are available in dapper that are required to perform the CRUD operations.

  • Execute: This extension method is used to execute INSERT, UPDATE and DELETE queries. This is also used when we want to execute a stored procedure using dapper.
  • Query: This extension method is used to execute SELECT queries.

Apart from these 2 basic methods, there are other methods like QueryFirst, QueryFirstOrDefault, QuerySingleand QuerySingleOrDefault. For someone who was used LINQ these are self explanatory and we will not be discussing them to avoid digression for beginners. But there is one important command QueryMultiplethat can be used to execute multiple queries in one command itself. We will be not be covering this command in this article to keep it free from digression for beginners but for it is highly advisable to read about this.

Using the Code

Let us try to understand the concepts further by creating a small toy application. The application that we will be creating is a small books information application which will

  • Retrieve a list of books
  • Retrieve the details of selected book.
  • Add new book
  • Delete a Book

Lets start by looking at the database schema for our Books database.

Here is the script to create this Bookstable:

Now That we have our database ready, we will install Dapper in our application. Dapper can be installed as a Nuget package in our applications.

Once Dapper is installed in our project, first thing we need to do is to create the POCOs for our database tables. In our case we need to create the class for our Bookobject.

Now since we are dealing with database operations, lets create an abstraction on top of our database calls i.e. a repository class. We will use this repository class to perform all the Bookrelated database operations. Lets start by defining a contract for our BooksRepositoryclass and define the basic CRUD operations in this contract.

With this abstraction in place, lets create a concrete BooksRepositoryclass that will contain all the database CRUD operation logic for our Bookentity.

Now we have all the boilerplate code ready for our test application. Let’s now start looking at the basic CRUD operations using Dapper.

Create

Since we know that dapper expects developers to take care of SQL queries, let’s first define our SQL query that we will use to insert the data into our Bookstable.

We are going to define this query(and all other queries) in our application config file along with other settings. Now that we have our query defined. Let’s look at the code that is inserting the data in our BookTable:

Read

Now that we have seen the code to insert the data in the table, lets look at the code to read the data from the database. First lets look at how we can retrieve all the items from the table. Lets start by looking at the SQL query for this operation.

Now lets look at the implementation of GetAllmethod in our repository.

The above function will return list of all books. Now lets look at the code where we will retrieve one single book record with a given ID. Lets start by looking at the SQL for this.

Now lets look at the implementation of GetByIdmethod in our repository.

Update

Now that we are done with the read operations, lets start with the update operation. lets look at the SQL query for the updateoperation.

Now lets look at the implementation of our Update method in our repository.

Delete

Finally Lets look at how we can implement the Deleteoperation.

Now lets look at the implementation of our Delete method in our repository.

With this we have the basic CRUD operations implemented for our Bookentity. The important thing to note here is that using Dapper is almost same as using ADO.NET. Dapper simply does the translation of results obtained from relational to objects and vice versa. This is where the real power of dapper lies. If someone is familiar with ADO.NET, using dapper is simply a piece of cake for them. In the next section, lets look at how to execute stored procedures and use transactions with dapper.

Executing Stored Procedures

Using stored procedure is as simple as specifying the commandTypein the Execute method.

Using Transactions

Transactions will be same as they are in ADO.NET. We can either using Transactionor TransactionScopein our code. Following sections shows an example snippet.

With this we have a fair idea on how we can perform basic CRUD operations using Dapper in our applications. Before closing the article, lets look at the configuration file containing all our app settings and our API controller code that can be testing using any rest client like Postman. The reference code can be found in the attached demo project.

Note: The demo project is an ASP.NET Core API application.

Point of interest

In this article we looked at how we can use Dapper to perform basic CRUD operations in our .NET applications. This article has been written from a beginner’s perspective. I hope this has been somewhat informative.

Download sample code for this article here: DapperTest