There are many open source ORM to map the classes to the databases, but now in this article we will learn about Dapper ORM, which we can say is the king of ORM. In this article we will demonstrate Dapper ORM by implementing CRUD operation in the ASP.NET MVC using a 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 the ASP.NET MVC.
Now next step is to add the reference of Dapper ORM into our created MVC Project ,follow the following steps
After installing the Dapper library ,It will be added into the References of our solution explorer of MVC application as
I hope you have followed the same steps and installed dapper library.
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:
- ActionResult in ASP.NET MVC.
- Creating an ASP.NET MVC Application.
- CRUD Operations In ASP.NET MVC 5 Using ADO.NET
- Convert DataTable To Generic List In ASP.NET MVC .
- How To Pass Stored Procedure Using Dapper.NET In MVC
- Show Confirm Alert Box on ActionLink Click In ASP.NET MVC.
- Convert DataTable To Generic List Using LINQ In ASP.NET MVC.
- How to Change ActionLink Text Color In ASP.NET MVC .
- How to Call Another Controller View Using ActionLink In ASP.NET MVC .
- Convert DataTable To Generic List Using AsEnumerable In ASP.NET MVC .
- How To Pass Generic List Using Dapper In MVC
What is Dapper ?
Dapper is the Open source ORM which is used to map Microsoft platform .NET classes to database as wel as it also supports complex and advanced database related operations.
Step 1 : Create an ASP.NET MVC Application.
Now let us start with a step by step approach from the creation of simple MVC application as in the following:- "Start", then "All Programs" and select "Microsoft Visual Studio 2015".
- "File", then "New" and click "Project..." then select "ASP.NET Web Application Template", then provide the Project a name as you wish and click on OK. After clicking, the following window will appear:
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 ,follow the following steps
- Right click on Solution ,find Manage NuGet Package manager and click on it
- After as shown into the image and type in search box "dapper"
- Select Dapper as shown into the image .
- Choose version of dapper library and click on install button
After installing the Dapper library ,It will be added into the References of our solution explorer of MVC application as
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:
EmpModel.cs class code snippet:
public class EmpModel { [Display(Name = "Id")] public int Empid { get; set; } [Required(ErrorMessage = "First name is required.")] public string Name { get; set; } [Required(ErrorMessage = "City is required.")] public string City { get; set; } [Required(ErrorMessage = "Address is required.")] public string Address { get; set; } }
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:
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.To Insert Records
Create procedure [dbo].[AddNewEmpDetails] ( @Name varchar (50), @City varchar (50), @Address varchar (50) ) as begin Insert into Employee values(@Name,@City,@Address) End
CREATE Procedure [dbo].[GetEmployees] as begin select Id as Empid,Name,City,Address from Employee End
Create procedure [dbo].[UpdateEmpDetails] ( @EmpId int, @Name varchar (50), @City varchar (50), @Address varchar (50) ) as begin Update Employee set Name=@Name, City=@City, Address=@Address where Id=@EmpId End
Create procedure [dbo].[DeleteEmpById] ( @EmpId int ) as begin Delete from Employee where Id=@EmpId 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.
EmpRepository.cs
using System; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.SqlClient; using CRUDUsingMVC.Models; using Dapper; using System.Linq; namespace CRUDUsingMVC.Repository { public class EmpRepository { public SqlConnection con; //To Handle connection related activities private void connection() { string constr = ConfigurationManager.ConnectionStrings["SqlConn"].ToString(); con = new SqlConnection(constr); } //To Add Employee details public void AddEmployee(EmpModel objEmp) { //Additing the employess try { connection(); con.Open(); con.Execute("AddNewEmpDetails", objEmp, commandType: CommandType.StoredProcedure); con.Close(); } catch (Exception ex) { throw ex; } } //To view employee details with generic list public List<EmpModel> GetAllEmployees() { try { connection(); con.Open(); IList<EmpModel> EmpList = SqlMapper.Query<EmpModel>( con, "GetEmployees").ToList(); con.Close(); return EmpList.ToList(); } catch (Exception) { throw; } } //To Update Employee details public void UpdateEmployee(EmpModel objUpdate) { try { connection(); con.Open(); con.Execute("UpdateEmpDetails", objUpdate, commandType: CommandType.StoredProcedure); con.Close(); } catch (Exception) { throw; } } //To delete Employee details public bool DeleteEmployee(int Id) { try { DynamicParameters param = new DynamicParameters(); param.Add("@EmpId", Id); connection(); con.Open(); con.Execute("DeleteEmpById", param, commandType: CommandType.StoredProcedure); con.Close(); return true; } catch (Exception ex) { //Log error as per your need throw ex; } } } }
- In the above 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 it article so 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:using System.Web.Mvc; using CRUDUsingMVC.Models; using CRUDUsingMVC.Repository; namespace CRUDUsingMVC.Controllers { public class EmployeeController : Controller { // GET: Employee/GetAllEmpDetails public ActionResult GetAllEmpDetails() { EmpRepository EmpRepo = new EmpRepository(); return View(EmpRepo.GetAllEmployees()); } // GET: Employee/AddEmployee public ActionResult AddEmployee() { return View(); } // POST: Employee/AddEmployee [HttpPost] public ActionResult AddEmployee(EmpModel Emp) { try { if (ModelState.IsValid) { EmpRepository EmpRepo = new EmpRepository(); EmpRepo.AddEmployee(Emp); ViewBag.Message = "Records added successfully."; } return View(); } catch { return View(); } } // GET: Bind controls to Update details public ActionResult EditEmpDetails(int id) { EmpRepository EmpRepo = new EmpRepository(); return View(EmpRepo.GetAllEmployees().Find(Emp => Emp.Empid == id)); } // POST:Update the details into database [HttpPost] public ActionResult EditEmpDetails(int id, EmpModel obj) { try { EmpRepository EmpRepo = new EmpRepository(); EmpRepo.UpdateEmployee(obj); return RedirectToAction("GetAllEmpDetails"); } catch { return View(); } } // GET: Delete Employee details by id public ActionResult DeleteEmp(int id) { try { EmpRepository EmpRepo = new EmpRepository(); if (EmpRepo.DeleteEmployee(id)) { ViewBag.AlertMsg = "Employee details deleted successfully"; } return RedirectToAction("GetAllEmpDetails"); } catch { return RedirectToAction("GetAllEmpDetails"); } } } }
Step 8: 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 on Add button as in the following screenshot:AddEmployee.cshtml
To View Added Employees@model CRUDUsingMVC.Models.EmpModel @using (Html.BeginForm()) { @Html.AntiForgeryToken() <div class="form-horizontal"> <h4>Add Employee</h4> <div> @Html.ActionLink("Back to Employee List", "GetAllEmpDetails") </div> <hr /> @Html.ValidationSummary(true, "", new { @class = "text-danger" }) <div class="form-group"> @Html.LabelFor(model => model.Name, htmlAttributes: new { @class = "control-label col-md-2" }) <div class="col-md-10"> @Html.EditorFor(model => model.Name, new { htmlAttributes = new { @class = "form-control" } }) @Html.ValidationMessageFor(model => model.Name, "", new { @class = "text-danger" }) </div> </div> <div class="form-group"> @Html.LabelFor(model => model.City, htmlAttributes: new { @class = "control-label col-md-2" }) <div class="col-md-10"> @Html.EditorFor(model => model.City, new { htmlAttributes = new { @class = "form-control" } }) @Html.ValidationMessageFor(model => model.City, "", new { @class = "text-danger" }) </div> </div> <div class="form-group"> @Html.LabelFor(model => model.Address, htmlAttributes: new { @class = "control-label col-md-2" }) <div class="col-md-10"> @Html.EditorFor(model => model.Address, new { htmlAttributes = new { @class = "form-control" } }) @Html.ValidationMessageFor(model => model.Address, "", new { @class = "text-danger" }) </div> </div> <div class="form-group"> <div class="col-md-offset-2 col-md-10"> <input type="submit" value="Save" class="btn btn-default" /> </div> </div> <div class="form-group"> <div class="col-md-offset-2 col-md-10" style="color:green"> @ViewBag.Message </div> </div> </div> } <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 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
@model IEnumerable<CRUDUsingMVC.Models.EmpModel> <p> @Html.ActionLink("Add New Employee", "AddEmployee") </p> <table class="table"> <tr> <th> @Html.DisplayNameFor(model => model.Name) </th> <th> @Html.DisplayNameFor(model => model.City) </th> <th> @Html.DisplayNameFor(model => model.Address) </th> <th></th> </tr> @foreach (var item in Model) { @Html.HiddenFor(model => item.Empid) <tr> <td> @Html.DisplayFor(modelItem => item.Name) </td> <td> @Html.DisplayFor(modelItem => item.City) </td> <td> @Html.DisplayFor(modelItem => item.Address) </td> <td> @Html.ActionLink("Edit", "EditEmpDetails", new { id = item.Empid }) | @Html.ActionLink("Delete", "DeleteEmp", new { id = item.Empid }, new { onclick = "return confirm('Are sure wants to delete?');" }) </td> </tr> } </table>
EditEmpDetails.cshtml
@model CRUDUsingMVC.Models.EmpModel @using (Html.BeginForm()) { @Html.AntiForgeryToken() <div class="form-horizontal"> <h4>Update Employee Details</h4> <hr /> <div> @Html.ActionLink("Back to Details", "GetAllEmployees") </div> <hr /> @Html.ValidationSummary(true, "", new { @class = "text-danger" }) @Html.HiddenFor(model => model.Empid) <div class="form-group"> @Html.LabelFor(model => model.Name, htmlAttributes: new { @class = "control-label col-md-2" }) <div class="col-md-10"> @Html.EditorFor(model => model.Name, new { htmlAttributes = new { @class = "form-control" } }) @Html.ValidationMessageFor(model => model.Name, "", new { @class = "text-danger" }) </div> </div> <div class="form-group"> @Html.LabelFor(model => model.City, htmlAttributes: new { @class = "control-label col-md-2" }) <div class="col-md-10"> @Html.EditorFor(model => model.City, new { htmlAttributes = new { @class = "form-control" } }) @Html.ValidationMessageFor(model => model.City, "", new { @class = "text-danger" }) </div> </div> <div class="form-group"> @Html.LabelFor(model => model.Address, htmlAttributes: new { @class = "control-label col-md-2" }) <div class="col-md-10"> @Html.EditorFor(model => model.Address, new { htmlAttributes = new { @class = "form-control" } }) @Html.ValidationMessageFor(model => model.Address, "", new { @class = "text-danger" }) </div> </div> <div class="form-group"> <div class="col-md-offset-2 col-md-10"> <input type="submit" value="Update" class="btn btn-default" /> </div> </div> </div> } <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 image.
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.
public class RouteConfig { public static void RegisterRoutes(RouteCollection routes) { routes.IgnoreRoute("{resource}.axd/{*pathInfo}"); routes.MapRoute( name: "Default", url: "{controller}/{action}/{id}", defaults: new { controller = "Employee", action = "AddEmployee", id = UrlParameter.Optional } ); } }
Step 10: Run the Application
The application works like as in the following demo.
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 it article so i have not implemented it .
My next article explains the types of controllers in MVC. I hope this article is useful for all readers. If you have any suggestion then please contact me.
Post a Comment