Dapper is a micro ORM tool which can be used for data access in Asp.Net MVC applications. If you are new to Dapper, I suggest you to take a look at this Quick Start article What is Dapper? How to Use Dapper in Asp.Net MVC? to have an overview of Dapper framework.
In this article, let us build a simple Asp.Net MVC application for demonstrating the use of Dapper framework by developing CRUD operations on Employee-Department model. Create a new Asp.Net MVC 5.0 application using Visual Studio 2015 or 2017. Include the Dapper Nuget packages into the project using Manage package manager. Create an Employee-Department mode as seen in the below class diagram.
Include a Controller and name it as EmployeeController. Initialize the IDBConnection object to use Dapper for data access.
public class EmployeeController : Controller
{
private IDbConnection _db = new SqlConnection(ConfigurationManager.ConnectionStrings["DBModel"].ConnectionString);
Include System.Data, System.Data.SqlClient and Dapper namespace for the above code to work.
List Operation
Now, let’s build an Employee Summary screen seen below to display the list of employees in the database.
The below action method will do that.
public ActionResult EmployeeSummary()
{
IList<Employee> empModel = _db.Query<Employee>("EmployeeList", commandType: CommandType.StoredProcedure).ToList();
return View(empModel);
}
EmployeeList SP:
CREATE PROCEDURE [dbo].[EmployeeList]
AS
SELECT * FROM Employees
For brevity, I have not included the view code here. The stored procedures used for the CRUD is very basic and so I have not included in the article here. All the stored procedures used in this article are copied to App_Data folder in the source code attachment seen at the end of this article.
Create Operation
The below Create Post action uses ExecuteScalar() method to create new employee row.
[HttpPost]
public ActionResult Create(Employee empModel)
{
if (ModelState.IsValid)
{
if (empModel != null)
{
object returnValue = _db.ExecuteScalar("InsertEmployee",
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
}, commandType: CommandType.StoredProcedure);
return Redirect(Url.Action("details", new { id = int.Parse(returnValue.ToString()) }));
}
}
ViewBag.DepartmentListItems = _db.Query<Department>("Select * from Departments").Select(i => new SelectListItem() { Text = i.DepartmentName, Value = i.DepartmentId.ToString() }).ToList();
return View(empModel);
}
Update Operation
The below Create Post action uses Execute() method to create new employee row.
[HttpPost]
public ActionResult edit(Employee empModel)
{
if (ModelState.IsValid)
{
Employee emp = _db.QueryFirstOrDefault<Employee>("EmployeeGet ", new { EmployeeId = empModel.EmployeeId }, commandType: CommandType.StoredProcedure);
if (emp != null)
{
_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);
return Redirect(Url.Action("details", new { id = emp.EmployeeId }));
}
}
ViewBag.DepartmentListItems = _db.Query<Department>("Select * from Departments").Select(i => new SelectListItem() { Text = i.DepartmentName, Value = i.DepartmentId.ToString() }).ToList();
return View(empModel);
}
Delete Operation
The below delete() action will help to delete the employee row.
[HttpPost]
public ActionResult delete(int id)
{
Employee emp = _db.QueryFirstOrDefault<Employee>("EmployeeGet ", new { EmployeeId = id }, commandType: CommandType.StoredProcedure);
if (emp != null)
{
_db.Execute("EmployeeDelete", new { EmployeeId = id }, commandType: CommandType.StoredProcedure);
return Content("Deleted Successfully!");
}
return Content("Error!");
}
Details Operation
The below details() action will help to display the details of a selected employee.
public ActionResult details(int id)
{
Employee emp = _db.QueryFirstOrDefault<Employee>("EmployeeGet", new { EmployeeId = id }, commandType: CommandType.StoredProcedure);
return View(emp);
}
Download the source and see it in action!
Note - The attached demo project uses Entity Framework for initial database creation and to pump default data. The EmployeeController uses Dapper for the CRUD operation listed above.