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

Background

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 postback, 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:

• ActionResult in ASP.NET MVC
• Creating an ASP.NET MVC Application
• CRUD Operations In ASP.NET MVC 5 Using ADO.NET

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:

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.
   

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

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 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 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:

• MVC Dynamic Bar Chart Using WEB API, AngularJS And JQuery
• Error Logging Modules And Handler (ELMAH) In ASP.NET