CRUD operations in MVC using jquery

byZafar Ali Khan 1 Comments
CRUD (Create, Read, Update, Delete) in asp.net MVC 5 using jquery
jquery and bootstrap is included by default in MVC5. We can easily develop ajax application easily in MVC5 using jquery. Json can easily be return from action method by setting return type to JsonResult and by marking that method with [HttpPost]
[HttpPost]
public JsonResult AddEmployee(Employee emp)
{
return Json(true, JsonRequestBehavior.AllowGet);
}

In this Article. I will show you how can we develop a single page application. with CRUD operations using (Microsoft Entity Framework 6.0) in Visual Studio 2013
Run the following script to create tables(Employee,Position) in sql server 2008 (SSMS) 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
USE [HRDB]
GO

CREATE TABLE [dbo].[Position](
 [PositionID] [int] NOT NULL,
 [Position] [varchar](50) NULL,
 CONSTRAINT [PK_Position] PRIMARY KEY CLUSTERED 
(
 [PositionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Employee](
 [EmployeeID] [int] NOT NULL,
 [Name] [nvarchar](200) NULL,
 [FatherName] [nvarchar](200) NULL,
 [PositionID] [int] NULL,
 [PhoneNo] [varchar](50) NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
 [EmployeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
steps from creating application
  • Create the database in Sql Server 2008 i used the database named(HRDB)
  • Create new MVC project in visual studio 2013
  • Right Click on Models folder select Add->New Item->Visual C#->Data->ADO.NET Entity Data Model->EF Designer from Database
  • Name it (HRDBModel)
  • Add Empty Controller (EmployeeController)
Employee Controller should like this. 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.Mvc;
using MyApp.Models;
namespace MyApp.Controllers
{
    public class EmployeeController : Controller
    {
        HRDBEntities db = new HRDBEntities();
        // GET: Employee
        public ActionResult CRUD_OP()
        {
            var emps = db.Employees.Include(e => e.Position).ToList();
            ViewBag.PositionID = new SelectList(db.Positions, "PositionID", "Position1");
            return View(emps);
        }
        [HttpPost]
        public JsonResult GetPositions()
        {
            var positions = (from p in db.Positions select new {PositionID=p.PositionID,PositionName=p.Position1 }).ToList();
            return Json(positions, JsonRequestBehavior.AllowGet);
        }
        [HttpPost]
        public JsonResult getEmployeeByID(int empid)
        {
            var emp = (from e in db.Employees
                       join p in db.Positions on e.PositionID equals (p.PositionID) where e.EmployeeID==empid
                       select new { 
                       EmployeeID=e.EmployeeID,
                       Name=e.Name,
                       FatherName=e.FatherName,
                       PhoneNo=e.PhoneNo,
                       Position=p.Position1
                       }).ToList();
            return Json(emp, JsonRequestBehavior.AllowGet);
        }
        [HttpPost]
        public JsonResult AddEmployee(Employee emp)
        {
            var empid = db.Employees.Max(x =>(int?) x.EmployeeID)??0;
            empid++;
            emp.EmployeeID = empid;
            db.Employees.Add(emp);
            db.SaveChanges();
            return Json(empid, JsonRequestBehavior.AllowGet);
        }
        [HttpPost]
        public JsonResult DeleteEmployee(int empid)
        {
            var emp = db.Employees.Where(x => x.EmployeeID == empid).ToList().FirstOrDefault();
            db.Employees.Remove(emp);
            db.SaveChanges();
            return Json(true, JsonRequestBehavior.AllowGet);
        }
        [HttpPost]
        public JsonResult UpdateEmployee(Employee emp)
        {
            var Eemp = db.Employees.Where(x => x.EmployeeID == emp.EmployeeID).ToList().FirstOrDefault();
            db.Entry(Eemp).CurrentValues.SetValues(emp);
            db.Entry(Eemp).State = EntityState.Modified;
            db.SaveChanges();
            return Json(true, JsonRequestBehavior.AllowGet);
        }
    }
}
Right Click on CRUD_OP() action method and select Add View with options shown in screenshot


CRUD_OP.cshtml should look like this 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
@model IEnumerable<MyApp.Models.Employee>

@{
    ViewBag.Title = "CRUD_OP";
    Layout = "~/Views/Shared/_Layout.cshtml";
}

<h2>Employee CRUD Operations</h2>
<hr />
@using (Html.BeginForm())
{
    <div class="form-horizontal">
        <div class="form-group">
            <div class="col-lg-3">
                <input class="form-control" id="txtEmployeeName" type="text" placeholder="Name" />
            </div>
            <div class="col-lg-3">
                <input class="form-control" id="txtFatherName" type="text" placeholder="Father Name" />
            </div>
            <div class="col-lg-3">
                @Html.DropDownList("PositionID", null, "Select Position", htmlAttributes: new { @class = "form-control" })
            </div>
            <div class="col-lg-2">
                <input class="form-control" id="txtPhoneNo" type="number" placeholder="Phone No" />
            </div>
            <div class="col-lg-1">
                <a href="#" class="btn btn-success btn-sm" id="btnSave"><i class="glyphicon glyphicon-plus"></i></a>
            </div>
        </div>
    </div>
}


<table class="table table-hover table-bordered" id="tblemps" width="100%">
    <thead>
        <tr>
            <th>
                Name
            </th>
            <th>
                Father Name
            </th>
            <th>
                Phone No
            </th>
            <th>
                Position
            </th>
            <th></th>
        </tr>
    </thead>
    <tbody>
        @foreach (var item in Model)
        {
            <tr>
                <td>
                    @Html.HiddenFor(modelitem=>item.EmployeeID)
                    <span id="employeename">@item.Name</span>
                    @*@Html.DisplayFor(modelItem => item.Name)*@
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.FatherName)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.PhoneNo)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.Position.Position1)
                </td>
                <td>
                    <a href="#" class="btn btn-success btn-sm" id="btnEdit" onclick="EditEmployee(this);"><i class="glyphicon glyphicon-pencil"></i></a>
                    <a href="#" class="btn btn-danger btn-sm" id="btnDelete" onclick="DeleteEmployee(this);"><i class="glyphicon glyphicon-trash"></i></a>
                </td>
            </tr>
        }
    </tbody>
</table>
@section scripts{
<script src="~/Scripts/employeeOp.js"></script>
    }
To Add employeeOp.js: right click on scripts folder->Add->Javascript file name it employeeOp.js add the following code to it. 
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
$(function () {
    $('#btnSave').click(function () { SaveEmployee(this); });
});

function SaveEmployee(ref) {
    debugger;
    
    var _empName = $('#txtEmployeeName').val();
    var _fatherName = $('#txtFatherName').val();
    var _positionid = $('#PositionID').val();
    var _position = $("#PositionID :selected").text();
    var phno = $('#txtPhoneNo').val();
    var emp = { EmployeeID: 0, Name: _empName, FatherName: _fatherName, PositionID: _positionid, PhoneNo: phno };
    var _tr = "<tr>";
    $.ajax({
        url: "AddEmployee",
        type: "POST",
        data: JSON.stringify({ 'emp': emp }),
        contentType: 'application/json; charset=utf-8',
        async:false,
        success: function (data) {
            console.log(data);
            _tr += "<td><input type='hidden' value='" + data + "' id='item_EmployeeID' name='item.EmployeeID' /> <span id='employeename'>" + _empName + "</span></td>";           
                },
        error: function (request) {
            // ...
        }
    });   
    
    _tr += "<td>" + _fatherName + "</td>";
    _tr += "<td>" + phno + "</td>";
    _tr += "<td>" + _position + "</td>";
    _tr += "<td><a href='#' class='btn btn-success btn-sm' id='btnEdit' onclick='EditEmployee(this);'><i class='glyphicon glyphicon-pencil'></i></a>";
    _tr += "&nbsp;<a href='#' class='btn btn-danger btn-sm' id='btnDelete' onclick='DeleteEmployee(this);'><i class='glyphicon glyphicon-trash'></i></a>";
    _tr += "</td></tr>";
    $('#tblemps tbody').append(_tr);
}
function EditEmployee(ref) {
    debugger;
    var _tr = $(ref).parent('td').parent('tr');
    var _empid = $(_tr).find('#item_EmployeeID').val();

    var _td1 = $(_tr).children('td')[0];
    var _name = $(_td1).find('#employeename').html();
    var _td2 = $(_tr).children('td')[1];
    var _td3 = $(_tr).children('td')[2];
    var _td4 = $(_tr).children('td')[3];
    var _td5 = $(_tr).children('td')[4];
    $(_td1).html("<input type='hidden' value='" + _empid + "' id='item_EmployeeID' name='item.EmployeeID' /><input type='text' id='Name' value='" + _name + "' class='form-control'/>");
    $(_td2).html("<input type='text' id='txtFatherName' value='" + $(_td2).html().trim() + "' class='form-control'/>");
    $(_td3).html("<input type='text' id='txtPhoneNo' value='" + $(_td3).html().trim() + "' class='form-control'/>");

    $.ajax({
        url: "GetPositions",
        type: "POST",
        data: '{}',
        contentType: 'application/json; charset=utf-8',
        success: function (data) {
            var _ddl = "<select class='form-control' id='PositionID' name='PositionID'>";
            $.each(data, function (i, x) {
                _ddl += "<option value=" + data[i].PositionID + ">" + data[i].PositionName + "</option>";
            });
            _ddl += "</select>"
            $(_td4).html(_ddl);
        },
        error: function (request) {
            // ...
        }
    });
    $(_td5).html("<a href='#' class='btn btn-success btn-sm' id='btnupdate' onclick='UpdateEmployee(this);'><i class='glyphicon glyphicon-ok'></i></a>&nbsp;<a href='#' class='btn btn-danger btn-sm' onclick='CancelEdit(this);' id='btncancel'><i class='glyphicon glyphicon-remove'></i></a>");
}
function CancelEdit(ref) {
    debugger;
    var _tr = $(ref).parent('td').parent('tr');
    var _empid = $(_tr).find('#item_EmployeeID').val();
    var _td1 = $(_tr).children('td')[0];
    var _td2 = $(_tr).children('td')[1];
    var _td3 = $(_tr).children('td')[2];
    var _td4 = $(_tr).children('td')[3];
    var _td5 = $(_tr).children('td')[4];

    $.ajax({
        url: "getEmployeeByID",
        type: "POST",
        data: JSON.stringify({ 'empid': _empid }),
        contentType: 'application/json; charset=utf-8',
        success: function (data) {
            console.log(data);
            $(_td1).html("<input type='hidden' id='item_EmployeeID' name='item.EmployeeID' value='" + data[0].EmployeeID + "'/><span id='employeename'>" + data[0].Name + "</span>");
            $(_td2).html(data[0].FatherName);
            $(_td3).html(data[0].PhoneNo);
            $(_td4).html(data[0].Position);
            $(_td5).html("<a href='#' class='btn btn-success btn-sm' id='btnEdit' onclick='EditEmployee(this);'><i class='glyphicon glyphicon-pencil'></i></a>" +
                    "&nbsp;<a href='#' class='btn btn-danger btn-sm' id='btnDelete' onclick='DeleteEmployee(this);'><i class='glyphicon glyphicon-trash'></i></a>");
        },
        error: function (request) {
            // ...
        }
    });
}
function DeleteEmployee(ref) {
    var _tr = $(ref).parent('td').parent('tr');
    var _employeeid = $(_tr).find('#item_EmployeeID').val();
    if (confirm('do you want to delete this employee')) {

        $.ajax({
            url: "DeleteEmployee",
            type: "POST",
            data: JSON.stringify({ 'empid': _employeeid }),
            contentType: 'application/json; charset=utf-8',
            success: function (data) {
                },
            error: function (request) {
                // ...
            }
        });


        $(_tr).fadeOut(3000);
    }
}

function UpdateEmployee(ref)
{
    var _tr = $(ref).parent('td').parent('tr');
    
    var _td1 = $(_tr).children('td')[0];
    var _td2 = $(_tr).children('td')[1];
    var _td3 = $(_tr).children('td')[2];
    var _td4 = $(_tr).children('td')[3];
    var _td5 = $(_tr).children('td')[4];

    var _empid = $(_tr).find('#item_EmployeeID').val();
    var _empName = $(_tr).find('#Name').val();
    var _fname = $(_tr).find('#txtFatherName').val();
    var _phno = $(_tr).find('#txtPhoneNo').val();
    var _positionid = $(_tr).find('#PositionID').val();
    var emp = { EmployeeID: _empid, Name: _empName, FatherName: _fname, PositionID: _positionid, PhoneNo: _phno };

    $.ajax({
        url: "UpdateEmployee",
        type: "POST",
        data: JSON.stringify({ 'emp': emp }),
        contentType: 'application/json; charset=utf-8',
        async: false,
        success: function (data) {
            console.log(data);
           
        },
        error: function (request) {
            // ...
        }
    });

    $.ajax({
        url: "getEmployeeByID",
        type: "POST",
        data: JSON.stringify({ 'empid': _empid }),
        contentType: 'application/json; charset=utf-8',
        success: function (data) {
            console.log(data);
            $(_td1).html("<input type='hidden' id='item_EmployeeID' name='item.EmployeeID' value='" + data[0].EmployeeID + "'/><span id='employeename'>" + data[0].Name + "</span>");
            $(_td2).html(data[0].FatherName);
            $(_td3).html(data[0].PhoneNo);
            $(_td4).html(data[0].Position);
            $(_td5).html("<a href='#' class='btn btn-success btn-sm' id='btnEdit' onclick='EditEmployee(this);'><i class='glyphicon glyphicon-pencil'></i></a>" +
                    "&nbsp;<a href='#' class='btn btn-danger btn-sm' id='btnDelete' onclick='DeleteEmployee(this);'><i class='glyphicon glyphicon-trash'></i></a>");
        },
        error: function (request) {
            // ...
        }
    });
}

Now run the application navigate to localhost: /Employee/CRUD_OP it will be fully functional.
Tagged In:
Previous Post Next Post

1 Comments

  1. weqarJuly 31, 2017 at 8:09 PM

    Thank you sir ji, but we can also achieve the same with builtin ASP.NET AJAX.BEGINFORM(), if someone is not coming from JavaScript/jQuery background then it will be easy to use that built in functionality of ASP.NET MVC.

    ReplyDelete

Post a Comment