ASP.Net MVC CRUD Operations Using jQuery JSON and LINQ To SQL Class

In this article you will learn about MVC CRUD operations with jQuery JSON.

So this article explains how to insert, select, update and delete with a webgrid. This explains CRUD operations step-by-step.

Step 1: Database

Create an EmployeeData table in a SQL database as in the following.

  1. Create Table EmployeeData  
  2. (  
  3.    EmpID int identity (1,1) Primary Key,  
  4.    EmpName varchar(30),  
  5.    Contact nchar(15),  
  6.    EmailId nvarchar(50)  
  7. )  
Step 2: Create MVC application

Application Name
Figure 1: Application Name

MVC template
Figure 2: MVC template

Default URL
Figure 3: Default URL

Step 3: LINQ to SQL class

Create a LINQ to SQL class to read data from the table as in the following:

Add Linq to Sql Class
Figure 4: Add LINQ to SQL Class

After using the Server Explorer and adding an EmployeeData table in the surface area as in the following:

server explorer surface area
Figure 5: Server Explorer surface area

Add Table
Figure 6: Add Table

Now add a controller to the CRUD operations for EmployeeData.

So open the Solution Explorer and right-click on the controller folder and add a controller as in the following:

Select Controller
Figure 7: Select Controller

Add Controller Name
Figure 8: Add Controller Name

Step 4: Controller

Create a default controller that looks as in the following:
  1. namespace MVC.Controllers  
  2. {  
  3.     public class EmployeeInfoController : Controller  
  4.     {  
  5.           
  6.         public ActionResult Index()  
  7.         {  
  8.             return View();  
  9.         }  
  10.     }  
  11. }  
Now create a select controller, an insert controller, an edit controller and a the Delete controller as in the following:
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.Mvc;  
  6.   
  7. using MVC.Models;  
  8.   
  9. namespace MVC.Models  
  10. {  
  11.     public class EmployeeInfoController : Controller  
  12.     {  
  13.         EmpDBDataContext db = new EmpDBDataContext();  
  14.         public ActionResult Index() //Record Select  
  15.         {  
  16.             List<EmployeeData> EmployeeDatas = db.EmployeeDatas.OrderByDescending(x => x.EmpID).ToList<EmployeeData>();  
  17.             return View(EmployeeDatas);  
  18.         }  
  19.         [HttpGet]  
  20.         public PartialViewResult Create()   //Insert PartialView  
  21.         {  
  22.         return PartialView(new MVC.Models.EmployeeInfo());  
  23.         }  
  24.         [HttpPost]  
  25.         public JsonResult Create(MVC.EmployeeData Emp) // Record Insert  
  26.         {  
  27.             EmpDBDataContext db = new EmpDBDataContext();  
  28.             db.EmployeeDatas.InsertOnSubmit(Emp);  
  29.             db.SubmitChanges();  
  30.             return Json(Emp, JsonRequestBehavior.AllowGet);  
  31.         }  
  32.   
  33.         [HttpGet]  
  34.         public PartialViewResult Edit(Int32 empid)  // Update PartialView  
  35.         {  
  36.             EmpDBDataContext db = new EmpDBDataContext();  
  37.             EmployeeData emp = db.EmployeeDatas.Where(x => x.EmpID == empid).FirstOrDefault();  
  38.             EmployeeInfo empinfo = new EmployeeInfo();  
  39.   
  40.             empinfo.EmailId = emp.EmpID.ToString();  
  41.             empinfo.EmpName = emp.EmpName;  
  42.             empinfo.Contact = emp.Contact;  
  43.             empinfo.EmailId = emp.EmailId;  
  44.   
  45.             return PartialView(empinfo);  
  46.         }  
  47.   
  48.         [HttpPost]  
  49.         public JsonResult Edit(MVC.EmployeeData employee)  // Record Update 
  50.         {  
  51.               
  52.             EmpDBDataContext db = new EmpDBDataContext();  
  53.             EmployeeData empdt = db.EmployeeDatas.Where(x => x.EmpID == employee.EmpID).FirstOrDefault();  
  54.   
  55.   
  56.             empdt.EmpName = employee.EmpName;  
  57.             empdt.Contact = employee.Contact;  
  58.             empdt.EmailId = employee.EmailId;  
  59.             db.SubmitChanges();  
  60.   
  61.             return Json(empdt, JsonRequestBehavior.AllowGet);  
  62.         }  
  63.   
  64.         public JsonResult Delete(Int32 empid)  
  65.         {  
  66.             EmployeeData emp = db.EmployeeDatas.Where(x => x.EmpID == empid).FirstOrDefault();  
  67.             db.EmployeeDatas.DeleteOnSubmit(emp);  
  68.             db.SubmitChanges();  
  69.             return Json(true, JsonRequestBehavior.AllowGet);  
  70.         }  
  71.     }  
  72. }  

Step 5: View

Now create a view and a partialview on a right-click corresponding to the controller as in the following:

Add view to controller

Figure 9: Add view to controller

1. Index View (select data)

Add Index view
Figure 10: Add Index view

In this figure see the view name is auto create, it's not changed. After selecting a Template and Model class as shown in the following figure.

Index code

  1. @model List<MVC.EmployeeData>  
  2.   
  3. @{  
  4.     ViewBag.Title = "Index";  
  5.     Layout = "~/Views/Shared/_Layout.cshtml";  
  6.     <style type="text/css">  
  7.         .grid {  
  8.             width: 100%;  
  9.         }  
  10.     </style>  
  11. }  
  12.   
  13. <div style="padding:7px 0;">  
  14.     <input type="button" value="Add New Employee" onclick="CreateEmployee()" />  
  15. </div>  
  16.   
  17. <div id='OpenDilog'></div>  
  18.   
  19. <h3>Employee Information List</h3>  
  20.   
  21. <div style="width:100%;">  
  22.     @{  
  23.     WebGrid grid = new WebGrid(Model);  
  24.     @grid.GetHtml(  
  25.          tableStyle: "grid",  
  26.          fillEmptyRows: false,  
  27.          mode: WebGridPagerModes.All,  
  28.          firstText: "<< First",  
  29.          previousText: "< Prev",  
  30.          nextText: "Next >",  
  31.          lastText: "Last >>",  
  32.          columns: new[] {  
  33.          grid.Column("EmpID",header: "ID"),  
  34.          grid.Column("EmpName",header: "Name"),  
  35.          grid.Column("Contact"),  
  36.          grid.Column("EmailId"),  
  37.          grid.Column("EmpID", header: "Action", canSort:false,  
  38.            
  39.          format: @<text>  
  40.     @Html.Raw("<img src='/images/edit.png' title='Edit' onclick='EditEmployee(" + item.EmpID + ")' />")  
  41.     @Html.Raw("<img src='/images/delete.png' title='Edit' onclick='DeleteEmployee(" + item.EmpID + ")' />")  
  42.            </text>  
  43.         )  
  44.      })  
  45.     }  
  46. </div>  
  47.   
  48. <link href="@Url.Content("~/Content/Site.css")" rel="stylesheet" type="text/css" />  
  49. <link href="@Url.Content("~/jquery-ui-1.10.4/themes/base/jquery-ui.css")" rel="stylesheet" type="text/css" />  
  50. <script src="@Url.Content("~/jquery-ui-1.10.4/ui/minified/jquery-ui.min.js")" type="text/javascript"></script>  
  51.   
  52. <script type="text/javascript">  
  53.   
  54.     function CreateEmployee() {  
  55.         var div = $("#OpenDilog");  
  56.         div.load("/EmployeeInfo/Create", function () {  
  57.             div.dialog({  
  58.                 modal: true,  
  59.                 width: 500,  
  60.                 height: 400,  
  61.                 title: "Add New Employee",  
  62.                 resizable: false  
  63.             });  
  64.         });  
  65.     }  
  66.   
  67.     function EditEmployee(E_ID) {  
  68.         var ph = $("#OpenDilog");  
  69.         ph.load("/EmployeeInfo/Edit?EmpID=" + E_ID, function () {  
  70.             ph.dialog({  
  71.                 modal: true,  
  72.                 width: 500,  
  73.                 height: 400,  
  74.                 title: "Edit Employee",  
  75.                 resizable: false  
  76.             });  
  77.         });  
  78.     }   
  79.   
  80.     function DeleteEmployee(E_ID) {  
  81.         if (confirm("Are You Sure Delete Selected Employee Record No.? " + E_ID)) {  
  82.             var data = { 'EmpID': E_ID }  
  83.             $.post('/EmployeeInfo/Delete', data,  
  84.             function (data) {  
  85.                 if (data == true)  
  86.                     location = location.href;  
  87.                 else  
  88.                     alert("Not delete something Wrong");  
  89.             });  
  90.         }  
  91.     }  
In this index view create a webgrid, script for a window open to insert data, an edit data and a delete dialog box.

2. Create View (Insert data)

Create a view to create a partial view .

Add Create view
Figure 11: Add Create view

This section also created a script for the model data insert into the table.

Create View Code
  1. @model MVC.Models.EmployeeInfo  
  2.   
  3. @using (Html.BeginForm())  
  4. {  
  5.     @Html.ValidationSummary(true)  
  6.       
  7.     <fieldset>  
  8.         <legend></legend>  
  9.   
  10.         <div class="editor-label">  
  11.            Employee Name  
  12.         </div>  
  13.         <div class="editor-field">  
  14.             @Html.EditorFor(model => model.EmpName)  
  15.             @Html.ValidationMessageFor(model => model.EmpName)  
  16.         </div>  
  17.   
  18.         <div class="editor-label">  
  19.             Contact  
  20.         </div>  
  21.         <div class="editor-field">  
  22.             @Html.EditorFor(model => model.Contact)  
  23.             @Html.ValidationMessageFor(model => model.Contact)  
  24.         </div>  
  25.   
  26.         <div class="editor-label">  
  27.           Email ID  
  28.         </div>  
  29.         <div class="editor-field">  
  30.             @Html.EditorFor(model => model.EmailId)  
  31.             @Html.ValidationMessageFor(model => model.EmailId)  
  32.        </div>  
  33.   
  34.         <p>  
  35.             <input type="button" value="Create" onclick="SaveEmployee()" />  
  36.         </p>  
  37.     </fieldset>  
  38. }  
  39. <div>  
  40.     @Html.ActionLink("Close""Index")  
  41. </div>  
  42.   
  43. <link href="@Url.Content("~/Content/Site.css")" rel="stylesheet" type="text/css"/>  
  44. <link href="@Url.Content("~/jquery-ui-1.10.4/themes/base/jquery-ui.css")" rel="stylesheet" type="text/css" />  
  45. <script src="@Url.Content("~/jquery-ui-1.10.4/ui/minified/jquery-ui.min.js")" type="text/javascript"></script>  
  46.   
  47. <script type="text/javascript">  
  48.     function SaveEmployee() {  
  49.         var EmpName = $("#EmpName").val();  
  50.         var Contact = $("#Contact").val();  
  51.         var EmailId = $("#EmailId").val();  
  52.           
  53.         var Employee = {  
  54.             "EmpName": EmpName, "Contact": Contact,  
  55.             "EmailId": EmailId  
  56.         };  
  57.   
  58.         $.post("/EmployeeInfo/Create", Employee,  
  59.         function (data) { if (data == 0) { location = location.href; } }, 'json');  
  60.     }  
3. Edit View (Update Employee)

Also create an Editview as a Partial view.

Add Edit View
Figure 12: Add Edit View

Edit view code
  1. @model MVC.Models.EmployeeInfo  
  2.   
  3. @using (Html.BeginForm())  
  4. {  
  5.     @Html.ValidationSummary(true)  
  6.       
  7.     <fieldset>  
  8.         <legend></legend>  
  9.   
  10.         <div class="editor-label">  
  11.            Employee Name  
  12.         </div>  
  13.         <div class="editor-field">  
  14.             @Html.EditorFor(model => model.EmpName)  
  15.             @Html.ValidationMessageFor(model => model.EmpName)  
  16.         </div>  
  17.   
  18.         <div class="editor-label">  
  19.             Contact  
  20.         </div>  
  21.         <div class="editor-field">  
  22.             @Html.EditorFor(model => model.Contact)  
  23.             @Html.ValidationMessageFor(model => model.Contact)  
  24.         </div>  
  25.   
  26.         <div class="editor-label">  
  27.           Email ID  
  28.         </div>  
  29.         <div class="editor-field">  
  30.             @Html.EditorFor(model => model.EmailId)  
  31.             @Html.ValidationMessageFor(model => model.EmailId)  
  32.        </div>  
  33.   
  34.         <p>  
  35.             <input type="button" value="Create" onclick="SaveEmployee()" />  
  36.         </p>  
  37.     </fieldset>  
  38. }  
  39. <div>  
  40.     @Html.ActionLink("Close""Index")  
  41. </div>  
  42.   
  43. <link href="@Url.Content("~/Content/Site.css")" rel="stylesheet" type="text/css"/>  
  44. <link href="@Url.Content("~/jquery-ui-1.10.4/themes/base/jquery-ui.css")" rel="stylesheet" type="text/css" />  
  45. <script src="@Url.Content("~/jquery-ui-1.10.4/ui/minified/jquery-ui.min.js")" type="text/javascript"></script>  
  46.   
  47. <script type="text/javascript">  
  48.     function SaveEmployee() {  
  49.         var EmpName = $("#EmpName").val();  
  50.         var Contact = $("#Contact").val();  
  51.         var EmailId = $("#EmailId").val();  
  52.           
  53.         var Employee = {  
  54.             "EmpName": EmpName, "Contact": Contact,  
  55.             "EmailId": EmailId  
  56.         };  
  57.   
  58.         $.post("/EmployeeInfo/Create", Employee,  
  59.         function (data) { if (data == 0) { location = location.href; } }, 'json');  
  60.     }  
  61. </script>  
In this view also create an update record script as in the following.

Finally all the operation views are created and can be seen in the project solution.

Views
Figure 13: Views

Step: Models

Now create a Model for EmployeeInfo as in the following:

Add Models class
Figure 14: Add Models class

class
Figuer 15: Class Name

Models Class Code
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.ComponentModel.DataAnnotations;  
  6.   
  7. namespace MVC.Models  
  8. {  
  9.    public class EmployeeInfo  
  10.    {  
  11.       public int EmpID;  
  12.   
  13.       [Required(ErrorMessage = "Can not be blank Name")]  
  14.       public string EmpName { getset; }  
  15.   
  16.       [Required(ErrorMessage = "Can not be blank Contact")]  
  17.       public string Contact { getset; }  
  18.   
  19.       [Required(ErrorMessage = "Can not be blank Email Id")]  
  20.       public string EmailId { getset; }  
  21.    }  
  22. }   
Now run your MVC example in a browser.

Run Application
Figure 16: Run Application.

Now click the Add New Employee button and insert a record as in the following:

Add Record
Figure 17: Add Record

Now delete the record without 130 with the delete button.

Record Delete
Figure 18: Record Delete

Selected Record open with edit dialog
Figure 19: Selected record opened with the Edit dialog

Press the update button and close the dialog and check the record in the webgrid.

Record Update
Figure 20: Record Update

Finally you have learned how to do Create, Retrieve, Update and Delete (CRUD) operations in MVC with jQuery JSON and LINQ to SQL classes.

Note: Please maintain your database connection for CRUD.

Have a nice Day.