Introduction
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 is a piece of software that helps 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 generate 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, lets 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 the 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
Let's 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 IDbConnection object. Dapper is nothing but a set of extension methods for IDbConnection. This is what makes it 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 let's 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, QuerySingle and QuerySingleOrDefault. For someone who has used LINQ, these are self explanatory and we will not be discussing them to avoid digression for beginners. But there is one important command QueryMultiple that 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 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
Let's start by looking at the database schema for our Books database.
Here is the script to create this Books table:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Books](
[ID] [int] IDENTITY(1,1) NOT NULL,
[BookName] [nvarchar](200) NULL,
[ISBN] [nvarchar](50) NULL,
CONSTRAINT [PK_Books] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, _
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
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 Book object.
public class Book
{
public int ID { get; set; }
public string BookName { get; set;}
public string ISBN { get; set; }
}
Now since we are dealing with database operations, let's create an abstraction on top of our database calls, i.e., a repository class. We will use this repository class to perform all the Book related database operations. Let's start by defining a contract for our BooksRepository class and define the basic CRUD operations in this contract.
interface IRepository<T> where T : class
{
List<T> GetAll();
bool Add(T employee);
T GetById(int id);
bool Update(T employee);
bool Delete(int id);
}
With this abstraction in place, let's create a concrete BooksRepository class that will contain all the database CRUD operation logic for our Book entity.
using Dapper;
public class BooksRepository : IRepository<Book>
{
private IDbConnection dbConnection = null;
public BooksRepository()
{
dbConnection = new SqlConnection(ConfigReader.ConnectionString);
}
public bool Add(Book book)
{
throw new NotImplementedException();
}
public bool Delete(int id)
{
throw new NotImplementedException();
}
public List<Book> GetAll()
{
throw new NotImplementedException();
}
public Book GetById(int id)
{
throw new NotImplementedException();
}
public bool Update(Book employee)
{
throw new NotImplementedException();
}
}
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 Books table.
INSERT INTO [dbo].[Books] ([BookName], [ISBN]) VALUES (@bookName ,@isbn)
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 Book Table:
public bool Add(Book book)
{
var result = false;
try
{
string sql = ConfigReader.InsertCommand;
var count = dbConnection.Execute(sql, book);
result = count > 0;
}
catch { }
return result;
}
Read
Now that we have seen the code to insert the data in the table, let's look at the code to read the data from the database. First let's look at how we can retrieve all the items from the table. Let's start by looking at the SQL query for this operation.
SELECT * from Books
Now let's look at the implementation of GetAll method in our repository.
public List<Book> GetAll()
{
string sql = ConfigReader.ReadAllCommand;
var queryResult = dbConnection.Query<Book>(sql);
return queryResult.ToList();
}
The above function will return a list of all books. Now let's look at the code where we will retrieve one single book record with a given ID. Let's start by looking at the SQL for this.
SELECT * from Books WHERE Id=@Id
Now let's look at the implementation of GetById method in our repository.
public Book GetById(int id)
{
Book book = null;
string sql = ConfigReader.ReadOneCommand;
var queryResult = dbConnection.Query<Book>(sql, new { Id = id});
if(queryResult != null)
{
book = queryResult.FirstOrDefault();
}
return book;
}
Update
Now that we are done with the read operations, let's start with the update operation. Let's look at the SQL query for the update operation.
UPDATE Books SET BookName = @BookName, ISBN = @ISBN WHERE ID = @ID
Now let's look at the implementation of our Update method in our repository.
public bool Update(Book book)
{
string sql = ConfigReader.UpdateCommand;
var count = dbConnection.Execute(sql, book);
return count > 0;
}
Delete
Finally, let's look at how we can implement the Delete operation.
DELETE FROM Books WHERE ID = @Id
Now let's look at the implementation of our Delete method in our repository.
public bool Delete(int id)
{
string sql = ConfigReader.DeleteCommand;
var count = dbConnection.Execute(sql, new { Id = id });
return count > 0;
}
With this, we have the basic CRUD operations implemented for our Book entity. 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, let's look at how to execute stored procedures and use transactions with dapper.
Executing Stored Procedures
Using stored procedure is as simple as specifying the commandType in the Execute method.
string sql = "MyStoredProc";
var result = dbConnection.Execute(sql, commandType: CommandType.StoredProcedure);
Using Transactions
Transactions will be the same as they are in ADO.NET. We can either using Transaction or TransactionScope in our code. Following sections show an example snippet.
using (var tx = connection.BeginTransaction())
{
tx.Commit();
}
using (var ts = new TransactionScope())
{
ts.Complete();
}
With this, we have a fair idea about how we can perform basic CRUD operations using Dapper in our applications. Before closing the article, let's look at the configuration file containing all our app settings and our API controller code that can be tested 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.
[Route("api/[controller]")]
[ApiController]
public class BookController : ControllerBase
{
IRepository<Book> booksRepository = null;
public BookController()
{
booksRepository = new BooksRepository();
}
[HttpGet]
public IActionResult Get()
{
IList<Book> books = booksRepository.GetAll();
return Ok(books);
}
[HttpGet("{id}", Name = "Get")]
public IActionResult Get(int id)
{
Book book = booksRepository.GetById(id);
if (book != null)
{
return Ok(book);
}
return NotFound();
}
[HttpPost]
public IActionResult Post(Book book)
{
if (!ModelState.IsValid)
{
return BadRequest(ModelState);
}
if (true == booksRepository.Add(book))
{
return Ok(book);
}
return BadRequest();
}
[HttpPut("{id}")]
public IActionResult Put(int id, Book book)
{
if (!ModelState.IsValid)
{
return BadRequest(ModelState);
}
book.ID = id;
var result = booksRepository.Update(book);
if (result == true)
{
return Ok(book);
}
return NotFound();
}
[HttpDelete("{id}")]
public IActionResult Delete(int id)
{
if (id <= 0)
{
return BadRequest("invalid id");
}
var result = booksRepository.Delete(id);
if (result == true)
{
return Ok();
}
return NotFound();
}
}
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.
History
- 3rd August, 2018: First version
