What is Dapper micro-ORM in C#?

First, we need to understand what is ORM (Object Relational Mapper) before understanding Dapper.

ORM is a technique for converting data between C# objects and relational databases. The primary goal of this model is to cut down the database-related development task like creating Stored Procedures or SQL queries and to focus on the object model. We can write ORM code in a class file to perform CRUD operations and during runtime, it has the mechanism to translate the query language into the appropriate syntax for the target database.

Entity Framework is an ORM for .NET application by Microsoft but there might be a situation where implementing such ORM is not suitable because of your project’s nature or requirement. In such cases, you can still achieve the same with a micro-ORM tool called Dapper.

What is Dapper?

Dapper is a lightweight, open source, also known as Micro-ORM for the Microsoft .NET platform, that fulfills the mapping of native query output to a domain class or C# objects. It helps to get rid of a traditional relational database approach that consumes the developer’s time in mapping query results from ADO.NET data readers to instances of those objects.

When to choose Dapper?

• Existing database in your project where plenty of stored procedures are already written.
• Lack of knowledge of Entity Framework Core or developer is experienced only with the classic method like SQL or ADO.Net

Advantages of Using Dapper

• Open-Source, lightweight and high performance
• Works with any database – SQL Server, MySQL, SQLite, SqlCE, Firebird, etc.
• Object Mapper
• Simplifies data access
  • Fewer lines of code
  • Simplified handling of SQL Query and Stored Procedure
  • Extends the IDbConnection interface to perform database operations
• Returns generic results for any data type
• Static and Dynamic Object Binding.
• Inserting Bulk data into a table.
• Allows multiple database connections
• Multiple Query Support – getting multiple results based on multiple inputs.

How to install Dapper?

A NuGet package is available under the Apache license that can be added to any .NET project for database operations. Dapper can be installed from the NuGet Package Manager interface or through the Console command line in your project.

or command line from Package Manager Console

install-package Dapper

Dapper extends your IDbConnection interface and below are some extension methods which perform actions like insert, update, delete and select methods:

• Execute, ExecuteReader, ExecuteScalar
• Query, QueryFirst, QueryFirstOrDefault, QuerySingle, QuerySingleOrDefault
• QueryMultiple
• Read, ReadFirst, ReadFirstOrDefault, ReadSingle, ReadSingleOrDefault

Below are the common parameters of Dapper

• cnn – connection string.
• sql – The command to execute.
• param – The command parameters (default = null).
• transaction – The transaction to use for query (default = null).
• commandTimeout – Number of seconds before command execution timeout (default = null).
• commandType – Is it a stored procedure or text or a batch? (default = null)

Execute query() using dapper

Insert code:

//Insert Customer data    
public int Insert(Customer customer)
{
	using (var connection = new SqlConnection(_connectionString))
	{
		connection.Open();
		int rowsAffected = connection.Execute("Insert into Customer (CustName, CustAddress) values (@CustName, @CustAddress)", new { CustName = customer.CustomerName, CustAddress = customer.CustomerAddress });
		connection.Close();
		return rowsAffected;
	}
}

Update code:

//Update Customer data   
public int Update(Customer customer)
{
	using (var connection = new SqlConnection(_connectionString))
	{
		connection.Open();
		int rowsAffected = connection.Execute("Update Customer set CustName = @CustName, CustAddress = @CustAddress Where Id = @Id", new { Id = customer.Id, CustName = customer.CustomerName, CustAddress = customer.CustomerAddress });
		connection.Close();
		return rowsAffected;
	}
}

Get code:

//Get Customer details
public List<Customer> GetAllCustomer()
{
	List<Customer> customers;
	using (var connection = new SqlConnection(_connectionString))
	{
		connection.Open();
		customers = connection.Query<Customer>("Select CustId, CustName, CustAddress").ToList();
		connection.Close();
	}
	return customers;
}

Delete code:

//Delete Customer data    
public int Delete(int customerID)
{
	using (SqlConnection connection = new SqlConnection(_connectionString))
	{
		connection.Open();
		int rowsAffected = connection.Execute("Delete from Customer Where Id = @Id", new { Id = customerID });
		connection.Close();
		return rowsAffected;
	}
}

Hope you liked it! Happy Coding!!

Thanks 🙂