What is Dapper? How to Use Dapper in ASP.Net Core?

Mithun Pattankar   Source Code  Print 
27 Nov 2018
28 Nov 2018
Intermediate
189

Working with data in the .NET world was dominated by ADO.NET for many years. With the advent of ORMs, many new database access frameworks(ORMs) emerged. Today, we will learn about Micro ORM called Dapper.

What is Dapper

Dapper is simple/ micro ORM for the .NET world. It's a NuGet library that can be added to any .NET project for database operations. ORM stands for Object Relational Mapping, meaning the entire database can be operated in terms of OO classes, Interfaces etc. ORM creates a "virtual database" in terms of classes and provides methods to work with those classes. Dapper is a Micro ORM as it's architected to focus on the most important task of working with database tables instead of creating, modifying the database schema, tracking changes etc.

Hey, I never heard of Dapper, who uses it? Dapper is in production use at Stack Overflow. Really? Yes, Dapper was created by StackOverflow team to address their issues and open source it. Dapper used at Stack Overflow itself showcases its strength.

Why use Dapper

  • Dapper is a NuGet library, can be used with any .NET project. Quite lightweight, high performance.

  • Drastically reduces the database access code.

  • Focus on getting database tasks done instead of being full-on ORM. We cover more on this

  • Work with any database - SQL Server, Oracle, SQLite, MySQL, PostgreSQL etc.

  • For an existing database, using Dapper is an optimal choice.

Choosing Dapper over EF Core

EF Core and Dapper are both great technologies, but choosing Dapper would be based on your requirements and here are mine

  • Existing database with lots of stored procedure fetching a good amount of records.

  • Developer's familiarity in working with raw SQL or ADO.NET.

  • The application mainly involves fetching for dashboards, reports.

  • Dapper uses underlying SQLConnection to work with the database, so easy it's quite easy to use a different database at the same time i.e. I would use Dapper for SQL Server, Oracle or MySQL in the same application. In real world apps, we usually don't deal with a single database.

How Dapper Works

Dapper is a NuGet library that can be added to any project. It extends the IDbConnection interface. The IDbConnection interface represents an open connection to data source implemented by the .NET framework. Every database provider extends this interface to for their database i.e. SQL Server, Oracle, MySQL etc. Dapper uses this connection, has its own set of methods to work with database independent of which database being chosen. This design goal of Dapper makes it easy to with any database almost in its own way. The following image shows the IDbConnection extended in the SQLConnection class (SQL Server provider).

CRUD demo with Dapper in ASP.NET Core

Let's get into an example by writing CRUD based ASP.NET Core web application with Dapper as data access technology.

What's in the demo

  • Demo scope.

  • Creating Entities aka Models, Data access library.

  • Create a web application using ASP.NET Core.

  • CRUD operations in action

Pre-requites

This demo is written in ASP.NET Core 2.1 on Visual Studio IDE 2017 Preview 15.9 on Windows 10 with SQL Server. However, you can use Visual Studio Code on Windows 7/8/10. The sample database used is the NorthWind database, available freely on this link.

Demo scope

The NorthWind database is a sample store database consisting of employees, products, orders, customers and their order. In this demo, we will create an ASP.NET Core MVC project to showcase CRUD operations for Northwind store employees. We will start by creating Northwind entities (POCO) classes, data access consisting of the Dapper library and ASP.NET Core MVC web application.

NorthWind Entities

Dapper being an ORM (micro), it maps classes with tables. The Dapper execution of SQL (raw or SP) is mapped to strongly typed classes to database result. A simple example class Employee containing properties like Id, Name, Designation, Location would map with respective columns of the Employee table. Other columns will not be mapped to class Employee even if they are part of SQL execution results. To generate Entities (C# class), recommend using any reverse engineering database tools and copy all the classes into a .NET Core class library as shown here.

Database Access library

Create a .NET Core class library, install the Dapper package using NuGet package manager. In this library, we will work on two aspects i.e. Connection factory and EmployeeRepository

DNTConnectionFactory (implements IDNTConnectionFactory) class has two methods GetConnection() and CloseConnection(). The GetConnection() method creates SqlConnection and opens the connection. The SqlConnection takes a connection string to the SQL Server database.

 
 public class DNTConnectionFactory : IDNTConnectionFactory
 {
 private IDbConnection _connection;
 private readonly IOptions<NorthWindConfiguration> _configs;
 
 public DNTConnectionFactory(IOptions<NorthWindConfiguration> Configs)
 {
 _configs = Configs;
 }
 
 public IDbConnection GetConnection
 {
 get
 {
 if (_connection == null)
 {
 _connection = new SqlConnection(_configs.Value.DbConnectionString);
 }
 if (_connection.State != ConnectionState.Open)
 {
 _connection.Open();
 }
 return _connection;
 }
 }
 
 public void CloseConnection()
 {
 if (_connection != null && _connection.State == ConnectionState.Open)
 {
 _connection.Close();
 }
 }
 }

The EmployeeRepository class is Repository type of class implementing IEmployeeRepository, it has five methods as shown. They essential perform our CRUD operation on the Employee class i.e. Employee table in NorthWind database.

 
 using DNT.NorthWind.Models;
 using System.Collections.Generic;
 
 namespace DNT.NorthWind.DataAccess.Interface
 {
 public interface IEmployeesRepository
 {
 IList<Employees>GetEmployeesByQuery();
 Employees GetEmployeesById(int employeeId);
 int AddEmployee(Employees employees);
 bool UpdateEmployee(int employeeId, Employees employees);
 bool DeleteEmployee(int employeeId); 
 }
 }

We will learn how Dapper is used with the various use case in detail. The GetEmployeesByQuery() method is the simplest way of fetching the records using raw SQL.

 
 public IList<Employees> GetEmployeesByQuery()
 {
 var EmpList = new List<Employees>();
 var SqlQuery = @"SELECT [EmployeeID],[LastName],[FirstName],[Title],[TitleOfCourtesy],[City],[Country] FROM [Northwind].[dbo].[Employees]";
 
 using (IDbConnection conn = _connectionFactory.GetConnection)
 { 
 var result = conn.Query(SqlQuery);
 return result.ToList();
 }
 }

the variable "SqlQuery" is the string containing SQL query. Dapper's(SqlMapper) Query method over the Connection factory runs the SQL query, then maps the database result to Employee class and returns as a list of employees.

Note : Only matching class and table properties are mapped to list of employee, they are case sensitive.

The GetEmployeesById() method gets Employee based on its EmployeeId. We use a stored procedure by passing EmployeeId as SQL parameter.

 public Employees GetEmployeesById(int empId)
 {
 var Employees = new Employees();
 var procName = "spEmployeesFetch";
 var param = new DynamicParameters();
 param.Add("@EmployeeId", empId);
 
 try
 {
 using (var multiResult = SqlMapper.QueryMultiple(_connectionFactory.GetConnection,
 procName, param, commandType: CommandType.StoredProcedure))
 {
 Employees = multiResult.ReadFirstOrDefault<Employees>();
 Employees.Territories = multiResult.Read<EmployeesTerritory>().ToList();
 }
 }
 finally
 {
 _connectionFactory.CloseConnection();
 }
 
 return Employees;
 }
  • In this method, we get multiple result sets of SP, map them to respective classes.

  • The QueryMultiple method of Dapper takes in connection object, SP name, SQL parameters and command type as SP.

  • A single employee is mapped in the first result set and list of EmployeesTerritory are mapped in the second result set.

The AddEmployee method takes an Employee object with new employee details, prepares SQL parameters as per the SP (Just like ADO.NET days).

 
 public int AddEmployee(Employees employees)
 {
 string procName = "spEmployeeInsert";
 var param = new DynamicParameters();
 int EmployeeId = 0;
 
 param.Add("@EmployeeId", employees.EmployeeID, null, ParameterDirection.Output);
 param.Add("@Title", employees.Title);
 param.Add("@TitleOfCourtesy", employees.TitleOfCourtesy);
 param.Add("@FirstName", employees.FirstName);
 param.Add("@LastName", employees.LastName);
 param.Add("@Address", employees.Address);
 param.Add("@City", employees.City);
 param.Add("@Region", employees.Region);
 param.Add("@PostalCode", employees.PostalCode);
 param.Add("@HomePhone", employees.HomePhone);
 param.Add("@Country", employees.Country); 
 
 try
 {
 SqlMapper.Execute(_connectionFactory.GetConnection,
 procName, param, commandType: CommandType.StoredProcedure);
 
 EmployeeId = param.Get<int>("@EmployeeId");
 }
 finally
 {
 _connectionFactory.CloseConnection();
 }
 
 return EmployeeId;
 }
 

The Dapper's (SQLMapper) Execute method takes connection object, SP name, parameters and run's it to save the employee and return generated EmployeeId.

The UpdateEmployee method does the same operation as Add method but only checking here is how many rows affected. It does Dapper's Execute method with the connection object, SP name etc.

 public bool UpdateEmployee(int EmployeeId, Employees employees)
 {
 string procName = "spEmployeeUpdate";
 var param = new DynamicParameters();
 bool IsSuccess = true;
 
 param.Add("@EmployeeId", EmployeeId, null, ParameterDirection.Input);
 param.Add("@Title", employees.Title);
 param.Add("@TitleOfCourtesy", employees.TitleOfCourtesy);
 param.Add("@FirstName", employees.FirstName);
 param.Add("@LastName", employees.LastName);
 param.Add("@Address", employees.Address);
 param.Add("@City", employees.City);
 param.Add("@Region", employees.Region);
 param.Add("@PostalCode", employees.PostalCode);
 param.Add("@HomePhone", employees.HomePhone);
 param.Add("@Country", employees.Country);
 
 try
 {
 var rowsAffected = SqlMapper.Execute(_connectionFactory.GetConnection,
 procName, param, commandType: CommandType.StoredProcedure);
 if (rowsAffected <= 0)
 {
 IsSuccess = false;
 } 
 }
 finally
 {
 _connectionFactory.CloseConnection();
 }
 
 return IsSuccess;
 }

The DeleteEmployee method uses Dapper(SQLMapper) Execute method to run a SQL string which is parameterized i.e. EmployeeId is passed on. This approach avoids SQL injection hacks

 public bool DeleteEmployee(int employeeId)
 {
 bool IsDeleted = true; 
 var SqlQuery = @"DELETE FROM Employees WHERE EmployeeID = @Id";
 
 using (IDbConnection conn = _connectionFactory.GetConnection)
 {
 var rowsaffected = conn.Execute(SqlQuery, new { Id = employeeId });
 if (rowsaffected <= 0)
 {
 IsDeleted = false; 
 }
 }
 return IsDeleted;
 }
 

It returns Boolean indicating it's success or failure by comparing rows affected return type of Execute method.

In the EmployeesRepository class, the IDNTConnectionFactory implementation is dependency injected into the constructor (DI - Default feature of ASP.NET Core).

DNT.NorthWind.Web - ASP.NET Core MVC project

Till now we created the entities, build the Employee repository to perform the CRUD operation. Now let's create an MVC project in ASP.NET Core to include the Employee Data Access library. Once done with creating the project, add both the libraries into this project. As the data library, we built using database connection string. Create an appsettings.json file, place a connection string to connect to the database.

 {
 "ConnectionStrings": {
 "DbConnectionString": "server=localhost\\SQLEXPRESS;Database=Northwind;Trusted_Connection=True;"
 }
 }

An essential part here is, the connection factory and the Employee repository needs to be configured so that ASP.NET Core will DI them at appropriate places and the connection string need to be added. The Options pattern of strongly typed configuration settings is used here.

 public void ConfigureServices(IServiceCollection services)
 {
 services.AddMvc();
 
 services.AddOptions();
 
 services.Configure<NorthWindConfiguration>(Configuration.GetSection("ConnectionStrings"));
 
 services.AddTransient<IDNTConnectionFactory, DNTConnectionFactory>(); 
 services.AddScoped<IEmployeesRepository, EmployeesRepository>();
 }

CRUD in action

Until now we have done the groundwork for getting the back-end work for CRUD work. In the EmployeeController, DI the IEmployeesRepository, call the respective methods.

Read Employee

The code snippet shows that we are getting the full list of Employees as well as a employee.

 public ActionResult Index()
 {
 var AllEmployees = _employeeRepository.GetEmployeesByQuery();
 return View(AllEmployees);
 }
 
 // GET: Home/Details/5
 public ActionResult Details(int id)
 {
 var Employee = _employeeRepository.GetEmployeesById(id);
 return View(Employee);
 }

List of Employees and an employee is shown here

Create Employee

The Create method posts the data collected from MVC form to the repository method and to then add to the database.

 [HttpPost]
 [ValidateAntiForgeryToken]
 public ActionResult Edit(int id, Employees data)
 {
 var employeeUpdated = _employeeRepository.UpdateEmployee(id, data);
 if (employeeUpdated)
 {
 return RedirectToAction(nameof(Index));
 }
 else
 {
 return View();
 }
 }
 

An employee entry form is shown here

Update Employee

The Update method puts the data collected from MVC form to the repository method and to then updates the database.

 [HttpPost]
 [ValidateAntiForgeryToken]
 public ActionResult Edit(int id, Employees data)
 {
 var employeeUpdated = _employeeRepository.UpdateEmployee(id, data);
 if (employeeUpdated)
 {
 return RedirectToAction(nameof(Index));
 }
 else
 {
 return View();
 }
 }

An editing of an employee shown here

Delete Employee

The Delete method calls the repository method and deletes the records in the database by clicking “Delete” from view page.

 public ActionResult Delete(int id)
 {
 var deleted = _employeeRepository.DeleteEmployee(id);
 if (deleted)
 {
 return RedirectToAction(nameof(Index));
 }
 else
 {
 return View();
 }
 }

Remember to run the NorthWind database script (includes SP's written for this demo) from the data folder in the Data Access library project.

Summary
  • Got introduced to Dapper

  • Learned about the advantages and when to use Dapper.

  • Created entities, data access library using Dapper

  • Integrated it with ASP.NET Core MVC application

  • Ran the application to see Employee CRUD in action of NorthWind database

Crack Your Technical Interview
 
Join Our Hands-on Training Programs
+