CodeDigest.Com Logo
Featured:

Developing Simple CRUD (Create, Read, Update and Delete) Operations in Asp.Net MVC Using Dapper

Tagged as: Asp.Net MVC Posted By

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.



Feedback

Comments