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.
public class Author{public int Id { get; set; }public string FirstName { get; set; }public string LastName { get; set; }}
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.
public List<Author> ReadAll(){using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings[“AdventureWorks”].ConnectionString)){return db.Query<Author>(“Select * From Author”).ToList();}}
Note that you should include the Dapper namespace in your program to leverage the Dapper framework.
The following method illustrates how you can search a particular record from the Author table.
public Author Find(int id){using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings[“AdventureWorks”].ConnectionString)){return db.Query<Author>(“Select * From Author “ +WHERE Id = @Id”, new { id }).SingleOrDefault();}}
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.
public int Update(Author author){using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings[“AdventureWorks”].ConnectionString)){string sqlQuery ="UPDATE Author SET FirstName = @FirstName, " +“ LastName = @LastName “ + “WHERE Id = @Id”;int rowsAffected = db.Execute(sqlQuery, author);return rowsAffected;}}
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.
public List<Author> Read(){using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings[“AdventureWorks”].ConnectionString)){string readSp ="GetAllAuthors";return db.Query<Author>(readSp,commandType: CommandType.StoredProcedure).ToList();}}
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.