Dapper is a micro ORM or it is a simple object mapper framework which helps to map the native query output to a domain class or a C# class. It is a high performance data access system built by StackOverflow team and released as open source. If your project prefers writing stored procedures or writing native query instead of using a full-fledged ORM tools like EntityFramework or NHibernate then Dapper is obvious choice for you. Using Dapper, it is very easy to fire a SQL query against database and get the result mapped to C# domain class.
For example, the below code gets a Customer object by executing a query against Customers table using Dapper framework.
Customer cust = _db.Query<Customer>("select * from Customers where CustomerId=@CustomerId ", new { CustomerId = id }).FirstOrDefault();
Let’s see how to integrate and start using Dapper in Asp.Net MVC application. For demonstration, let’s create a new Asp.Net MVC 5.0 project in Visual Studio 2015(You can also use Visual Studio 2017). Next, get the Dapper Nuget package installed into the project. To do this, right click your project in solution explorer and select “Manage Nuget Packages..”. to get the Nuget windows as seen below. Type Dapper and enter in Browse tab and click Install to install Dapper.
Let’s use a simple Employee-Department model as seen below and use Dapper to do some most commonly used data access query.
Dapper framework actually extends the IDbConnection interface available in under System.Data namespace. It has many extension methods for data access and mapping the result to a C# type (domain objects) defined under the SqlMapper class found under Dapper namespace. So, in order to use Dapper, first we need to declare an IDbConnection object and initialize it to a SqlConnection to connect the database. The below EmployeeController class declares IDbConnection _db with a database connection using the AppSettings connection string.
public class EmployeeController : Controller
{
private IDbConnection _db = new SqlConnection(ConfigurationManager.ConnectionStrings["DBModel"].ConnectionString);
Include System.Data, System.Data.SqlClient and Dapper namespace.
You can see all the data access methods when you type _db. in the action method. The below image gives you a quick list of available methods.
Executing a Stored Procedure
-
Let’s now see how to call a stored procedure and return an Employee object from the query result. The below code uses QueryFirstOrDefault() method to execute a stored procedure and return an Employee object.
Employee emp = _db.QueryFirstOrDefault<Employee>("EmployeeGet ", new { EmployeeId = id }, commandType: CommandType.StoredProcedure);
EmployeeGet SP:
CREATE PROCEDURE [dbo].[EmployeeGet]
@EmployeeId INT
AS
SELECT * FROM Employees WHERE EmployeeId = @EmployeeId
Alternatively, you can also execute a query directly using Query() method as seen below.
Employee emp = _db.Query<Employee>("select * from Employees where EmployeeId=@EmployeeId ", new { EmployeeId = id }).FirstOrDefault();
-
To execute a stored procedure with DML queries, we can use Execute() method. The below code executes a stored procedure EmployeeUpdate to update the Employee table row.
_db.Execute("EmployeeUpdate",
new { FirstName = empModel.FirstName,
LastName = empModel.LastName,
DepartmentId = empModel.DepartmentId,
Address1 = empModel.Address1,
Address2 = empModel.Address2,
City = empModel.City,
State = empModel.State,
Country = empModel.Country,
PostalCode = empModel.PostalCode,
Email = empModel.Email,
ConfirmEmail = empModel.ConfirmEmail,
DOB = empModel.DOB,
Salary = empModel.Salary,
IsPermanent = empModel.IsPermanent,
Gender = empModel.Gender,
EmployeeId = emp.EmployeeId
}, commandType: CommandType.StoredProcedure);
EmployeeUpdate SP:
CREATE PROCEDURE [dbo].[EmployeeUpdate]
@EmployeeId INT,
@FirstName VARCHAR(50),
@LastName VARCHAR(50),
@DepartmentId INT,
@Address1 VARCHAR(50),
@Address2 VARCHAR(50),
@City VARCHAR(50),
@State VARCHAR(50),
@Country VARCHAR(50),
@PostalCode VARCHAR(50),
@Email VARCHAR(50),
@ConfirmEmail VARCHAR(50),
@DOB DATETIME,
@Gender VARCHAR(50),
@IsPermanent BIT,
@Salary DECIMAL(9,2)
AS
UPDATE Employees
SET FirstName = @FirstName,
LastName = @LastName,
DepartmentId = @DepartmentId,
Address1 = @Address1,
Address2 = @Address2,
City = @City,
State = @State,
Country = @Country,
PostalCode = @PostalCode,
Email = @Email,
ConfirmEmail = @ConfirmEmail,
DOB = @DOB,
Gender = @Gender,
IsPermanent = @IsPermanent,
Salary = @Salary
WHERE EmployeeId = @EmployeeId
Executing a Stored Procedure to return a Complex Object
Sometimes, we will have requirement to join multiple table and return a result which may not directly map to an existing domain object. For example, consider the below stored procedure which joins Employees and Department table and returns some arbitrary columns.
CREATE PROCEDURE [dbo].[GetEmployeesSummary]
AS
SELECT
EmployeeId,
FirstName + ' ' + LastName As Name,
d.DepartmentId as DepartmentId,
d.DepartmentName as DepartmentName
FROM Employees e INNER JOIN Departments d
ON e.DepartmentId = d.DepartmentId
To get the result of this stored procedure, we can define C# class to match the result and which can have another object as a member. The below EmployeeSummary class has Department as child object which can be used to get the result of above stored procedure.
public class EmployeeSummary
{
public int EmployeeId { get; set; }
public string Name { get; set; }
public Department Department { get; set; }
}
To get the result of the stored procedure as EmployeeSummary object, we can use the below Query<TFirst,TSecond,TReturn>() overload as seen below.
public ActionResult EmployeeSummary()
{
IList<EmployeeSummary> empSummary = _db.Query<EmployeeSummary, Department, EmployeeSummary>("[GetEmployeesSummary]",
(emp, dept) => { emp.Department = dept; return emp; },
splitOn: "EmployeeId,DepartmentId").ToList();
return View(empSummary);
}
Thus, with this quick start article we learnt how to begin using Dapper ORM for data access in Asp.Net MVC projects. Hope you liked it! Happy Coding!!