CRUD Operation In ASP.NET MVC Using jQuery JSON With Dapper ORM

In my last article CRUD Operations In ASP.NET MVC 5 Using Dapper ORM we have learned how create CRUD operations in ASP.NET MVC with the help of Dapper ORM but if you closely observed there is a whole page post back, lots of code and many forms which maximize the process time, and also huge server utilization, so to overcome this we will learn another approach to create the CRUD operations in ASP.NET MVC Using jQuery JSON with Dapper ORM, which will cover the drawbacks of first approach.
I have written this article focusing on beginners so they can understand the basics of CRUD operations in ASP.NET MVC Using jQuery Json. Please read my previous articles 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 a 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 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:

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,

Installing Dapper ORM Into MVC 5 Project

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:

--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       
     
 --To View Added Records    
 CREATE Procedure [dbo].[GetEmployees]        
 as        
 begin        
    select Id as Empid,Name,City,Address from Employee      
 End     
     
 --To Update Records    
 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       
     
 --To Delete Records     
 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;    
        //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)    
        {    
              
            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;    
            }    
        }    
        //To view employee details     
        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    
            {    
                  
      
                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;    
            }    
        }    
        //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;    
            }    
        }    
    }

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    
    {    
        //Get Employee List with json data    
        public JsonResult GetEmpDetails()    
        {    
            EmpRepository EmpRepo = new EmpRepository();    
            return Json(EmpRepo.GetAllEmployees(),JsonRequestBehavior.AllowGet);    
      
        }    
          
        public ActionResult AddEmployee()    
        {    
               
            return View();    
        }    
        //Get record by Empid for edit    
        public ActionResult Edit(int?id)    
        {    
            EmpRepository EmpRepo = new EmpRepository();    
            return View(EmpRepo.GetAllEmployees().Find(Emp => Emp.Id == id));    
        }    
      //Add Employee details with json data    
        [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,");    
            }    
        }    
      
     //Delete the records by id    
        [HttpPost]    
        public JsonResult Delete(int id)    
        {               
            EmpRepository EmpRepo = new EmpRepository();    
            EmpRepo.DeleteEmployee(id);    
           return Json("Records deleted successfully.", JsonRequestBehavior.AllowGet);    
        }    
     //Updated edited records    
        [HttpPost]         
        public JsonResult Edit(EmpModel EmpUpdateDet)    
        {            
            EmpRepository EmpRepo = new EmpRepository();    
            EmpRepo.UpdateEmployee(EmpUpdateDet);    
            return Json("Records updated successfully.", JsonRequestBehavior.AllowGet);    
        }    
        //Get employee list of Partial view     
        [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()  
    {  
        //firing function on button click     
        $("#btnsave").click(function()  
        {  
            //Creating Javascript array to post it as json data    
            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);  
                },  
                //After successfully inserting records    
                success: function(response)  
                {  
                    //Reload Partial view to fetch latest added records    
                    $('#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 () {  
        //firing function on button click  
        $("#btnsave").click(function () {  
            //Creating Javascript array to post it as json data  
            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);  
  
                },  
                //After successfully inserting records  
                success: function (response) {  
  
                    //Reload Partial view to fetch latest added records  
                    $('#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()  
    {  
        //Get the list of employee records in json format    
        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);  
            });  
        });  
        //Delete the records    
        $('#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()  
        {  
            //Get the list of employee records in json format    
            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);  
                });  
            });  
            //Delete the records    
            $('#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()  
    {  
        //firing function on button click to update records    
        $("#btnUpdate").click(function()  
        {  
            //creating javascript array to pass it as json data    
            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);  
                    //redirect to the AddEmployee view after successfully updating records    
                    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 () {  
        //firing function on button click to update records  
        $("#btnUpdate").click(function () {  
            //creating javascript array to pass it as json data  
            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);  
                    //redirect to the AddEmployee view after successfully updating records  
                    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 wants 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.

Download Sample

CrudOperationUsingDapperWihtjQueryJson.zip

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.
Download Aspose : API To Create and Convert Files

Compilemode

Featured Posts

Learn IoT : What is Internet of Things (IoT)?

Getting Started with Azure IoT Hub

Getting Started with Azure IoT Hub Device Provisioning Service

CRUD Operation In ASP.NET MVC Using jQuery JSON With Dapper ORM

Post a Comment

Trending up

Categories

www.CodeNirvana.in