Post
Ask Question

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

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 { get; set; }  
  15.   
  16.       [Required(ErrorMessage = "Can not be blank Contact")]  
  17.       public string Contact { get; set; }  
  18.   
  19.       [Required(ErrorMessage = "Can not be blank Email Id")]  
  20.       public string EmailId { get; set; }  
  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.


Similar Articles