Now let us start with a step by step approach from the creation of a simple MVC application as in 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:
- 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 such as:
If wants to learn how to install correct Dapper library , watch my video tutorial using following link,
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:
- public class EmpModel
- {
- public int Id { get; set; }
- public string Name {get; set; }
- public string City { get; set; }
- 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 window. specify the
Controller name as Home with suffix
Controller:
Note: The controller name must be having suffix as 'Controller' after specifying the name of controller.
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:
-
- 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
Run the above script in sql it will generates the stored procedure for CRUD operation .
Step 6: Create Repository class.
Now create Repository folder and Add EmpRepository.cs class for database related operations, Now create methods in EmpRepository.cs to handle the CRUD operation as in the following code snippet:
- public class EmpRepository
- {
- SqlConnection con;
-
- private void connection()
- {
- string constr = ConfigurationManager.ConnectionStrings["SqlConn"].ToString();
- con = new SqlConnection(constr);
- }
-
- public void AddEmployee(EmpModel objEmp)
- {
-
- try
- {
-
- DynamicParameters ObjParm = new DynamicParameters();
- ObjParm.Add("@Name", objEmp.Name);
- ObjParm.Add("@City", objEmp.City);
- ObjParm.Add("@Address", objEmp.Address);
- connection();
- con.Open();
- con.Execute("AddNewEmpDetails", ObjParm, commandType: CommandType.StoredProcedure);
- con.Close();
- }
- catch (Exception ex)
- {
- throw ex;
- }
- }
-
- 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;
- }
- }
-
- public void UpdateEmployee(EmpModel objUpdate)
- {
- try
- {
-
-
- DynamicParameters objParam = new DynamicParameters();
- objParam.Add("@EmpId", objUpdate.Id);
- objParam.Add("@Name", objUpdate.Name);
- objParam.Add("@City", objUpdate.City);
- objParam.Add("@Address", objUpdate.Address);
- connection();
- con.Open();
- con.Execute("UpdateEmpDetails", objParam, commandType: CommandType.StoredProcedure);
- con.Close();
- }
- catch (Exception)
- {
- throw;
- }
- }
-
- 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)
- {
-
- throw ex;
- }
- }
- }
Note
- 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.
- Log the exception in database or text file as per your convenience, since in the article I have not implemented it .
Step 7: Create Methods into the HomeController.cs file.
Now open the HomeController.cs and create the following action methods:
- public class HomeController : Controller
- {
-
- public JsonResult GetEmpDetails()
- {
- EmpRepository EmpRepo = new EmpRepository();
- return Json(EmpRepo.GetAllEmployees(),JsonRequestBehavior.AllowGet);
-
- }
-
- public ActionResult AddEmployee()
- {
-
- return View();
- }
-
- public ActionResult Edit(int?id)
- {
- EmpRepository EmpRepo = new EmpRepository();
- return View(EmpRepo.GetAllEmployees().Find(Emp => Emp.Id == id));
- }
-
- [HttpPost]
- public JsonResult AddEmployee(EmpModel EmpDet)
-
- {
- try
- {
-
- EmpRepository EmpRepo = new EmpRepository();
- EmpRepo.AddEmployee(EmpDet);
- return Json("Records added Successfully.");
-
- }
- catch
- {
- return Json("Records not added,");
- }
- }
-
-
- [HttpPost]
- public JsonResult Delete(int id)
- {
- EmpRepository EmpRepo = new EmpRepository();
- EmpRepo.DeleteEmployee(id);
- return Json("Records deleted successfully.", JsonRequestBehavior.AllowGet);
- }
-
- [HttpPost]
- public JsonResult Edit(EmpModel EmpUpdateDet)
- {
- EmpRepository EmpRepo = new EmpRepository();
- EmpRepo.UpdateEmployee(EmpUpdateDet);
- return Json("Records updated successfully.", JsonRequestBehavior.AllowGet);
- }
-
- [HttpGet]
- public PartialViewResult EmployeeDetails()
- {
-
- return PartialView("_EmployeeDetails");
-
- }
- }
Step 8: Create Views.
Create the view to Add the employees
To create the View to add Employees, right click on view folder and then click Add view. Now specify the view name as AddEmployee or as you wish, template name and model class in EmpModel.cs and click on Add button.
After clicking on Add button it will creates the AddEmployee view having extension .cshtml , Now write the jQuery Ajax post method to insert the records into the database.
Create jQuery Ajax post method to Add Records
- $(document).ready(function()
- {
-
- $("#btnsave").click(function()
- {
-
- var EmpModel = {
- Name: $("#Name").val(),
- City: $("#City").val(),
- Address: $("#Address").val()
- };
- $.ajax(
- {
- type: "POST",
- URL: "/Home/AddEmployee",
- dataType: "json",
- contentType: "application/json",
- data: JSON.stringify(
- {
- EmpDet: EmpModel
- }),
- error: function(response)
- {
- alert(response.responseText);
- },
-
- success: function(response)
- {
-
- $('#DivEmpList').load("/Home/EmployeeDetails");
- alert(response);
- }
- });
- });
- });
Now after adding jQuery Ajax post method then
AddEmployee.cshtml view source code will be look like as follows.
AddEmployee.cshtml
- @model CrudOperationUsingDapperWihtjQueryJson.Models.EmpModel
-
- @{
- ViewBag.Title = "Add Employee";
- }
- <script src="~/Scripts/jquery-1.10.2.min.js"></script>
- <script src="~/Scripts/jquery-1.10.2.intellisense.js"></script>
- <script>
- $(document).ready(function () {
-
- $("#btnsave").click(function () {
-
- var EmpModel = {
- Name: $("#Name").val(),
- City: $("#City").val(),
- Address: $("#Address").val()
- };
- $.ajax({
- type: "POST",
- URL: "/Home/AddEmployee",
- dataType: "json",
- contentType: "application/json",
- data: JSON.stringify({ EmpDet: EmpModel }),
- error: function (response) {
- alert(response.responseText);
-
- },
-
- success: function (response) {
-
-
- $('#DivEmpList').load("/Home/EmployeeDetails");
- alert(response);
-
- }
- });
-
- });
-
-
- });
- </script>
- <div class="form-horizontal">
- <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" id="divSave">
- <div class="col-md-offset-2 col-md-10">
- <input type="submit" name="SaveData" id="btnsave" value="Save" class="btn btn-primary" />
- </div>
- </div>
- @*Calling partial view of employee list*@
- <div class="form-group" id="DivEmpList">
- <div class="col-md-12">
- @Html.Partial("_EmployeeDetails")
- </div>
- </div>
- </div>
To View Added Employees
To view the employee details let us create the partial view named _EmployeeDetails:
Create the jQuery Ajax function to fetch and delete employee records
- $(document).ready(function()
- {
-
- var tr;
- $.getJSON("/Home/GetEmpDetails", function(json)
- {
- $.each(json, function(i, EmpData)
- {
- var Empid = EmpData.Id;
- tr = $('<tr/>');
- tr.append("<td class='Name'>" + EmpData.Name + "</td>");
- tr.append("<td class='City'>" + EmpData.City + "</td>");
- tr.append("<td class='Address'>" + EmpData.Address + "</td>");
- tr.append("<td>" + "<a Onclick='return false;' class='DeleteCss' href=/Home/Delete/" + Empid + ">Delete</a>" + " | " + "<a class='EditCss' href=/Home/Edit/" + Empid + ">Edit</a>" + "</td>");
- $('#TblEmpDet').append(tr);
- });
- });
-
- $('#TblEmpDet').on('click', 'td a.DeleteCss', function()
- {
- var DeleteUrl = $(this).attr("href");
- if (confirm("Are you sure wants to delete ?."))
- {
- $.ajax(
- {
- url: DeleteUrl,
- dataType: "json",
- type: "POST",
- contentType: "application/json",
- error: function(err)
- {
- alert('Unable to delete record.');
- },
- success: function(response)
- {
- $('#DivEmpList').load("/Home/EmployeeDetails");
- }
- });
- }
- });
- });
Now after adding the above function the _EmployeeDetails.cshtml partial view source code will be look like as follows.
_EmployeeDetails.cshtml
- <script type="text/javascript">
- $(document).ready(function()
- {
-
- var tr;
- $.getJSON("/Home/GetEmpDetails", function(json)
- {
- $.each(json, function(i, EmpData)
- {
- var Empid = EmpData.Id;
- tr = $('<tr/>');
- tr.append("<td class='Name'>" + EmpData.Name + "</td>");
- tr.append("<td class='City'>" + EmpData.City + "</td>");
- tr.append("<td class='Address'>" + EmpData.Address + "</td>");
- tr.append("<td>" + "<a Onclick='return false;' class='DeleteCss' href=/Home/Delete/" + Empid + ">Delete</a>" + " | " + "<a class='EditCss' href=/Home/Edit/" + Empid + ">Edit</a>" + "</td>");
- $('#TblEmpDet').append(tr);
- });
- });
-
- $('#TblEmpDet').on('click', 'td a.DeleteCss', function()
- {
- var DeleteUrl = $(this).attr("href");
- if (confirm("Are you sure wants to delete ?."))
- {
- $.ajax(
- {
- url: DeleteUrl,
- dataType: "json",
- type: "POST",
- contentType: "application/json",
- error: function(err)
- {
- alert('Unable to delete record.');
- },
- success: function(response)
- {
- $('#DivEmpList').load("/Home/EmployeeDetails");
- }
- });
- }
- });
- });
- </script>
- <table id="TblEmpDet" class="table table-bordered table-hover">
- <thead>
- <tr>
- <th> Name </th>
- <th> City </th>
- <th> Address </th>
- <th></th>
- </tr>
- </thead>
- <tbody></tbody>
- </table>
View to Update Added Employees
Follow the same procedure and create Edit,cshtml view to edit the employees and create the following jQuery function to update the records.
jQuery Ajax function to update the records
- $(document).ready(function()
- {
-
- $("#btnUpdate").click(function()
- {
-
- var EmpModel = {
- Id: $("#hdnId").val(),
- Name: $("#Name").val(),
- City: $("#City").val(),
- Address: $("#Address").val()
- };
- $.ajax(
- {
- type: "POST",
- URL: "/Home/Edit",
- dataType: "json",
- contentType: "application/json",
- data: JSON.stringify(
- {
- EmpUpdateDet: EmpModel
- }),
- error: function(response)
- {
- alert(response.responseText);
- },
- success: function(response)
- {
- alert(response);
-
- window.location.href = "/Home/AddEmployee";
- }
- });
- });
- });
After adding the above function the Edit.cshtml view source code will be look like as:
Edit.cshtml
- @model CrudOperationUsingDapperWihtjQueryJson.Models.EmpModel
-
- @{
- ViewBag.Title = "Edit";
- }
- <script src="~/Scripts/jquery-1.10.2.min.js"></script>
- <script src="~/Scripts/jquery-1.10.2.intellisense.js"></script>
- <script>
- $(document).ready(function () {
-
- $("#btnUpdate").click(function () {
-
- var EmpModel = {
- Id: $("#hdnId").val(),
- Name: $("#Name").val(),
- City: $("#City").val(),
- Address: $("#Address").val()
- };
-
- $.ajax({
- type: "POST",
- URL: "/Home/Edit",
- dataType: "json",
- contentType: "application/json",
- data: JSON.stringify({ EmpUpdateDet: EmpModel }),
- error: function (response) {
- alert(response.responseText);
- },
- success: function (response) {
- alert(response);
-
- window.location.href = "/Home/AddEmployee";
-
- }
- });
- });
- });
- </script>
-
-
- <div class="form-horizontal">
-
- <hr />
- @Html.ValidationSummary(true, "", new { @class = "text-danger" })
- @Html.HiddenFor(model => model.Id, new { @id = "hdnId" })
-
- <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="button" id="btnUpdate" value="Update" class="btn btn-default" />
- </div>
- </div>
- </div>
Step 9: Run the Application.
Now run the application the AddEmployee view appears as in the following screenshot.
Now enter the details like as in following screenshot.
And on clicking save button similarly add another record, then the added records get added into the database and it will be displayed in the following table.
Update Record
In the above list of records we added city as USA by mistake so let us edit it by clicking on edit hyperlink then record will be displayed in edit mode correct it as per your need.
Now after correcting records click on update button then the updated records will be shown are as follows:
Deleting Records
To delete the record click on delete button it will ask you for confirmation before deleting records as follows:
Click on ok to delete record or cancel if you can not be wantsdon't want to delete the record. After deleting the record the remaining records will be shown in table as follows:
From the preceding examples we have learned how to implement CRUD Operation In ASP.NET MVC Using jQuery Json with 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.
- Since we are Using jQuery So don't forgot to add the reference of jQuery library.
Summary
My next article will explains about the action filters in MVC. I hope this article is useful for all readers. If you have any suggestion please contact me.
Read more articles on ASP.NET: