Post
Ask Question

CRUD Operations In ASP.NET MVC 5 Using Dapper ORM

Background

There are many open source ORM to map the classes to the databases but now in this article we will learn about Dapper ORM. We can consider it as the king of ORM. In this article we will demonstrate Dapper ORM by implementing CRUD operations in MVC with step by step approach. I have written this article focusing on beginners so they can understand the basics of Dapper ORM. Please read my previous article using the following links to understand the basics about MVC:

So let us understand in brief about Dapper ORM

What is Dapper ?

Dapper is the Open source ORM which is used to map Microsoft platform .NET classes to the database.

Step 1 : Create an MVC Application.

Now let us start with a step by step approach from the creation of simple MVC application as in the following:

  1. "Start", then "All Programs" and select "Microsoft Visual Studio 2015".

  2. "File", then "New" and click "Project", then select "ASP.NET Web Application Template", then provide the Project a name as you wish and click OK. After clicking, the following window will appear:



  3. As shown in the preceding screenshot, click on Empty template and check MVC option, then click OK. This will create an empty MVC web application whose Solution Explorer will look like the following:


Step 2 : Add The Reference of Dapper ORM into Project.

Now next step is to add the reference of Dapper ORM into our created MVC Project. Here are the steps:

  1. Right click on Solution ,find Manage NuGet Package manager and click on it.
  2. After as shown into the image and type in search box "dapper".
  3. Select Dapper as shown into the image .
  4. Choose version of dapper library and click on install button.

    dapper

    After installing the Dapper library, it will be added into the References of our solution explorer of MVC application as:

    select dapper
I hope you have followed the same steps and installed dapper library.

Step 3: Create Model Class.

Now let us create the model class named EmpModel.cs by right clicking on model folder as in the following screenshot:


Note: It is not mandatory that Model class should be in Model folder, it is just for better readability you can create this class anywhere in the solution explorer. This can be done by creating different folder name or without folder name or in a separate class library.

EmpModel.cs class code snippet:
  1. public class EmpModel    
  2. {    
  3.       [Display(Name = "Id")]    
  4.       public int Empid { get; set; }    
  5.     
  6.       [Required(ErrorMessage = "First name is required.")]    
  7.       public string Name { get; set; }    
  8.     
  9.       [Required(ErrorMessage = "City is required.")]    
  10.       public string City { get; set; }    
  11.     
  12.       [Required(ErrorMessage = "Address is required.")]    
  13.       public string Address { get; set; }    
  14.     
  15. }   
In the above model class we have added some validation on properties with the help of DataAnnotations.

Step 4 : Create Controller.

Now let us add the MVC 5 controller as in the following screenshot:
 


After clicking on Add button it will show the following window. Now specify the Controller name as Employee with suffix Controller as in the following screenshot:


Note: The controller name must be having suffix as 'Controller' after specifying the name of controller.

After clicking on Add button controller is created with by default code that support CRUD operations and later on we can configure it as per our requirements. 

Step 5 : Create Table and Stored procedures.

Now before creating the views let us create the table name Employee in database according to our model fields to store the details:


I hope you have created the same table structure as shown above. Now create the stored procedures to insert, update, view and delete the details as in the following code snippet:

To Insert Records
  1. Create procedure [dbo].[AddNewEmpDetails]  
  2. (  
  3.    @Name varchar (50),  
  4.    @City varchar (50),  
  5.    @Address varchar (50)  
  6. )  
  7. as  
  8. begin  
  9.    Insert into Employee values(@Name,@City,@Address)  
  10. End  
To View Added Records
  1. CREATE Procedure [dbo].[GetEmployees]    
  2. as    
  3. begin    
  4.    select Id as Empid,Name,City,Address from Employee  
  5. End   
To Update Records
  1. Create procedure [dbo].[UpdateEmpDetails]  
  2. (  
  3.    @EmpId int,  
  4.    @Name varchar (50),  
  5.    @City varchar (50),  
  6.    @Address varchar (50)  
  7. )  
  8. as  
  9. begin  
  10.    Update Employee  
  11.    set Name=@Name,  
  12.    City=@City,  
  13.    Address=@Address  
  14.    where Id=@EmpId  
  15. End  
To Delete Records
  1. Create procedure [dbo].[DeleteEmpById]  
  2. (  
  3.    @EmpId int  
  4. )  
  5. as  
  6. begin  
  7.    Delete from Employee where Id=@EmpId  
  8. End  
Step 6: Create Repository class.

Now create Repository folder and Add EmpRepository.cs class for database related operations, after adding, the solution explorer will look like the following screenshot:


Now create methods in EmpRepository.cs to handle the CRUD operation as in the following code snippet:

EmpRepository.cs
  1. public class EmpRepository      
  2. {      
  3.     public SqlConnection con;      
  4.     //To Handle connection related activities      
  5.     private void connection()      
  6.     {      
  7.         string constr = ConfigurationManager.ConnectionStrings["SqlConn"].ToString();      
  8.         con = new SqlConnection(constr);      
  9.   
  10.     }      
  11.     //To Add Employee details      
  12.     public void AddEmployee(EmpModel objEmp)      
  13.     {      
  14.   
  15.         //Additing the employess      
  16.         try      
  17.         {      
  18.             connection();      
  19.             con.Open();      
  20.             con.Execute("AddNewEmpDetails", objEmp, commandType: CommandType.StoredProcedure);      
  21.             con.Close();      
  22.         }      
  23.         catch (Exception ex)      
  24.         {      
  25.   
  26.             throw ex;      
  27.         }      
  28.   
  29.     }      
  30.     //To view employee details with generic list       
  31.     public List<EmpModel> GetAllEmployees()      
  32.     {      
  33.         try      
  34.         {      
  35.             connection();      
  36.             con.Open();      
  37.             IList<EmpModel> EmpList = SqlMapper.Query<EmpModel>(      
  38.                               con, "GetEmployees").ToList();      
  39.             con.Close();      
  40.             return EmpList.ToList();      
  41.         }      
  42.         catch (Exception)      
  43.         {      
  44.   
  45.             throw;      
  46.         }      
  47.     }      
  48.   
  49.     //To Update Employee details      
  50.     public void UpdateEmployee(EmpModel objUpdate)      
  51.     {      
  52.         try      
  53.         {      
  54.             connection();      
  55.             con.Open();      
  56.             con.Execute("UpdateEmpDetails", objUpdate, commandType: CommandType.StoredProcedure);      
  57.             con.Close();      
  58.         }      
  59.         catch (Exception)      
  60.         {      
  61.   
  62.             throw;      
  63.         }      
  64.   
  65.     }      
  66.     //To delete Employee details      
  67.     public bool DeleteEmployee(int Id)      
  68.     {      
  69.         try      
  70.         {      
  71.             DynamicParameters param = new DynamicParameters();      
  72.             param.Add("@EmpId", Id);      
  73.             connection();      
  74.             con.Open();      
  75.             con.Execute("DeleteEmpById", param, commandType: CommandType.StoredProcedure);      
  76.             con.Close();      
  77.             return true;      
  78.         }      
  79.         catch (Exception ex)      
  80.         {      
  81.             //Log error as per your need       
  82.             throw ex;      
  83.         }      
  84.     }      
  85. }   
Note
  1. In the above code we are manually opening and closing connection, however you can directly pass the connection string to the dapper without opening it. Dapper will automatically handle it.

  2. Log the exception in database or text file as per you convenienc , since in the article I have not implemented it .
Step 7: Create Methods into the EmployeeController.cs file.

Now open the EmployeeController.cs and create the following action methods:
  1. public class EmployeeController : Controller      
  2. {      
  3.    
  4.     // GET: Employee/GetAllEmpDetails      
  5.     public ActionResult GetAllEmpDetails()      
  6.     {      
  7.         EmpRepository EmpRepo = new EmpRepository();      
  8.         return View(EmpRepo.GetAllEmployees());      
  9.     }      
  10.     // GET: Employee/AddEmployee      
  11.     public ActionResult AddEmployee()      
  12.     {      
  13.         return View();      
  14.     }      
  15.    
  16.     // POST: Employee/AddEmployee      
  17.     [HttpPost]      
  18.     public ActionResult AddEmployee(EmpModel Emp)      
  19.     {      
  20.         try      
  21.         {      
  22.             if (ModelState.IsValid)      
  23.             {      
  24.                 EmpRepository EmpRepo = new EmpRepository();      
  25.                 EmpRepo.AddEmployee(Emp);      
  26.    
  27.                 ViewBag.Message = "Records added successfully.";      
  28.    
  29.             }      
  30.    
  31.             return View();      
  32.         }      
  33.         catch      
  34.         {      
  35.             return View();      
  36.         }      
  37.     }      
  38.    
  39.     // GET: Bind controls to Update details      
  40.     public ActionResult EditEmpDetails(int id)      
  41.     {      
  42.         EmpRepository EmpRepo = new EmpRepository();      
  43.         return View(EmpRepo.GetAllEmployees().Find(Emp => Emp.Empid == id));      
  44.    
  45.     }      
  46.    
  47.     // POST:Update the details into database      
  48.     [HttpPost]      
  49.     public ActionResult EditEmpDetails(int id, EmpModel obj)      
  50.     {      
  51.         try      
  52.         {      
  53.             EmpRepository EmpRepo = new EmpRepository();      
  54.    
  55.             EmpRepo.UpdateEmployee(obj);      
  56.    
  57.             return RedirectToAction("GetAllEmpDetails");      
  58.         }      
  59.         catch      
  60.         {      
  61.             return View();      
  62.         }      
  63.     }      
  64.    
  65.     // GET: Delete  Employee details by id      
  66.     public ActionResult DeleteEmp(int id)      
  67.     {      
  68.         try      
  69.         {      
  70.             EmpRepository EmpRepo = new EmpRepository();      
  71.             if (EmpRepo.DeleteEmployee(id))      
  72.             {      
  73.                 ViewBag.AlertMsg = "Employee details deleted successfully";      
  74.    
  75.             }      
  76.             return RedirectToAction("GetAllEmpDetails");      
  77.         }      
  78.         catch      
  79.         {      
  80.             return RedirectToAction("GetAllEmpDetails");      
  81.         }      
  82.     }      
  83. }    
Step 8: Create Views.

Create the Partial view to Add the employees

To create the Partial View to add Employees, right click on ActionResult method and then click Add view. Now specify the view name, template name and model class in EmpModel.cs and click Add button as in the following screenshot:

Code

After clicking on Add button it generates the strongly typed view whose code is given below:

AddEmployee.cshtml
  1. @  
  2. model CRUDUsingMVC.Models.EmpModel@ using(Html.BeginForm()) {@  
  3.     Html.AntiForgeryToken()  
  4.   
  5.     < div class = "form-horizontal" >  
  6.         < h4 > Add Employee < /h4>   < div > @Html.ActionLink("Back to Employee List", "GetAllEmpDetails") < /div>   < hr / > @Html.ValidationSummary(true, "", new {@  
  7.             class = "text-danger"  
  8.         })  
  9.   
  10.   
  11.     < div class = "form-group" > @Html.LabelFor(model => model.Name, htmlAttributes: new {@  
  12.         class = "control-label col-md-2"  
  13.     }) < div class = "col-md-10" > @Html.EditorFor(model => model.Name, new {  
  14.         htmlAttributes = new {@  
  15.             class = "form-control"  
  16.         }  
  17.     })@ Html.ValidationMessageFor(model => model.Name, "", new {@  
  18.         class = "text-danger"  
  19.     }) < /div>   < /div>    
  20.   
  21.     < div class = "form-group" > @Html.LabelFor(model => model.City, htmlAttributes: new {@  
  22.         class = "control-label col-md-2"  
  23.     }) < div class = "col-md-10" > @Html.EditorFor(model => model.City, new {  
  24.         htmlAttributes = new {@  
  25.             class = "form-control"  
  26.         }  
  27.     })@ Html.ValidationMessageFor(model => model.City, "", new {@  
  28.         class = "text-danger"  
  29.     }) < /div>   < /div>    
  30.   
  31.     < div class = "form-group" > @Html.LabelFor(model => model.Address, htmlAttributes: new {@  
  32.         class = "control-label col-md-2"  
  33.     }) < div class = "col-md-10" > @Html.EditorFor(model => model.Address, new {  
  34.         htmlAttributes = new {@  
  35.             class = "form-control"  
  36.         }  
  37.     })@ Html.ValidationMessageFor(model => model.Address, "", new {@  
  38.         class = "text-danger"  
  39.     }) < /div>   < /div>    
  40.   
  41.     < div class = "form-group" >  
  42.         < div class = "col-md-offset-2 col-md-10" >  
  43.         < input type = "submit"  
  44.     value = "Save"  
  45.     class = "btn btn-default" / >  
  46.         < /div>   < /div>   < div class = "form-group" >  
  47.         < div class = "col-md-offset-2 col-md-10"  
  48.     style = "color:green" > @ViewBag.Message  
  49.   
  50.         < /div>   < /div>   < /div>    
  51.   
  52. } < script src = "~/Scripts/jquery-1.10.2.min.js" > < /script>   < script src = "~/Scripts/jquery.validate.min.js" > < /script>   < script src = "~/Scripts/jquery.validate.unobtrusive.min.js" > < /script>  
To View Added Employees

To view the employee details let us create the partial view named GetAllEmpDetails:


Now click on add button, it will create GetAllEmpDetails.cshtml strongly typed view whose code is given below:

GetAllEmpDetails.CsHtml
  1. @model IEnumerable < CRUDUsingMVC.Models.EmpModel >  
  2.   
  3.     < p > @Html.ActionLink("Add New Employee", "AddEmployee") < /p>    
  4.   
  5. < table class = "table" >  
  6.     < tr >  
  7.   
  8.     < th > @Html.DisplayNameFor(model => model.Name) < /th>   < th > @Html.DisplayNameFor(model => model.City) < /th>     
  9.     < th > @Html.DisplayNameFor(model => model.Address) < /th>   < th > < /th>   < /tr>    
  10.   
  11.     @foreach(var item in Model)   
  12.     {  
  13.         @Html.HiddenFor(model => item.Empid)   
  14.         < tr >  
  15.   
  16.             < td > @Html.DisplayFor(modelItem => item.Name) < /td>     
  17.             < td > @Html.DisplayFor(modelItem => item.City) < /td>     
  18.             < td > @Html.DisplayFor(modelItem => item.Address) < /td>     
  19.             < td > @Html.ActionLink("Edit", "EditEmpDetails", new {  
  20.                     id = item.Empid  
  21.                 }) | @Html.ActionLink("Delete", "DeleteEmp", new {  
  22.                     id = item.Empid  
  23.                  }, new {  
  24.                  onclick = "return confirm('Are sure wants to delete?');"  
  25.                 })   
  26.             < /td>     
  27.         < /tr>    
  28.   
  29.     }  
  30.   
  31. < /table>  
To Update Added Employees

Follow the same procedure and create EditEmpDetails view to edit the employees. After creating the view the code will be like the following:

EditEmpDetails.cshtml
  1. @model CRUDUsingMVC.Models.EmpModel  
  2.   
  3.   
  4. @ using(Html.BeginForm()) {@  
  5.     Html.AntiForgeryToken()  
  6.   
  7.     < div class = "form-horizontal" >  
  8.         < h4 > Update Employee Details < /h4>   < hr / >  
  9.         < div > @Html.ActionLink("Back to Details", "GetAllEmployees") < /div>   < hr / > @Html.ValidationSummary(true, "", new {@  
  10.             class = "text-danger"  
  11.         })@ Html.HiddenFor(model => model.Empid)  
  12.   
  13.     < div class = "form-group" > @Html.LabelFor(model => model.Name, htmlAttributes: new {@  
  14.         class = "control-label col-md-2"  
  15.     }) < div class = "col-md-10" > @Html.EditorFor(model => model.Name, new {  
  16.         htmlAttributes = new {@  
  17.             class = "form-control"  
  18.         }  
  19.     })@ Html.ValidationMessageFor(model => model.Name, "", new {@  
  20.         class = "text-danger"  
  21.     }) < /div>   < /div>    
  22.   
  23.     < div class = "form-group" > @Html.LabelFor(model => model.City, htmlAttributes: new {@  
  24.         class = "control-label col-md-2"  
  25.     }) < div class = "col-md-10" > @Html.EditorFor(model => model.City, new {  
  26.         htmlAttributes = new {@  
  27.             class = "form-control"  
  28.         }  
  29.     })@ Html.ValidationMessageFor(model => model.City, "", new {@  
  30.         class = "text-danger"  
  31.     }) < /div>   < /div>    
  32.   
  33.     < div class = "form-group" > @Html.LabelFor(model => model.Address, htmlAttributes: new {@  
  34.         class = "control-label col-md-2"  
  35.     }) < div class = "col-md-10" > @Html.EditorFor(model => model.Address, new {  
  36.         htmlAttributes = new {@  
  37.             class = "form-control"  
  38.         }  
  39.     })@ Html.ValidationMessageFor(model => model.Address, "", new {@  
  40.         class = "text-danger"  
  41.     }) < /div>   < /div>    
  42.   
  43.     < div class = "form-group" >  
  44.         < div class = "col-md-offset-2 col-md-10" >  
  45.         < input type = "submit"  
  46.     value = "Update"  
  47.     class = "btn btn-default" / >  
  48.         < /div>   < /div>   < /div>    
  49. } < script src = "~/Scripts/jquery-1.10.2.min.js" > < /script>   < script src = "~/Scripts/jquery.validate.min.js" > < /script>   < script src = "~/Scripts/jquery.validate.unobtrusive.min.js" > < /script>  
Step 9 : Configure Action Link to Edit and delete the records as in the following figure:



The above ActionLink I have added in GetAllEmpDetails.CsHtml view because from there we will delete and update the records.

Step 10: Configure RouteConfig.cs to set default action as in the following code snippet:
  1. public class RouteConfig  
  2. {  
  3.    public static void RegisterRoutes(RouteCollection routes)  
  4.    {  
  5.       routes.IgnoreRoute("{resource}.axd/{*pathInfo}");  
  6.   
  7.       routes.MapRoute(  
  8.          name: "Default",  
  9.          url: "{controller}/{action}/{id}",  
  10.          defaults: new { controller = "Employee", action = "AddEmployee", id = UrlParameter.Optional }  
  11.       );  
  12.    }  
  13. }  
From the above RouteConfig.cs the default action method we have set is AddEmployee. It means that after running the application the AddEmployee view will be executed first.

Now after adding the all model, views and controller our solution explorer will be like the following screenshot:



Step 11: Run the Application

Now run the application the AddEmployee view appears as in the following screenshot



Click on save button, the model state validation will fire, as per validation we have set into the EmpModel.cs class:



Now enter the details and on clicking save button. The records get added into the database and the following message appears.



Now click on Back to Employee List hyperlink, it will be redirected to employee details grid as in the following screenshot:



Now similar to above screenshot, add another record, then the list will be as in the following screenshot:



Now click on Edit button of one of the record, then it will be redirected to Edit view as in the following screenshot:



Now click on Update button, on clicking, the records will be updated into the database. Click Back to Details hyperlink then it will be redirected to the Employee list table with updated records as in the following screenshot:



Now click on delete button for one of the records, then the following confirmation box appears (we have set configuration in ActionLink).



Now click on OK button, then the updated Employee list table will be like the following screenshot:


From the preceding examples we have learned how to implement CRUD operations in ASP.NET MVC using Dapper ORM.

Note:
  • Configure the database connection in the web.config file depending on your database server location.
  • Download the Zip file of the sample application for a better understanding.
  • Since this is a demo, it might not be using proper standards, so improve it depending on your skills.
  • This application is created completely focusing on beginners.
  • In the Repository code we manually opening and closing connection, however you can directly pass the connection string to the dapper without opening it, dapper will automatically handled.
  • Log the exception in database or text file as per you convenience, since in this article I have not implemented it.
Summary

My next article explains the types of controllers in MVC. I hope this article is useful for all readers. If you have any suggestion please contact me.


Similar Articles