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.

Create Table EmployeeData
(
   EmpID int identity (1,1) Primary Key,
   EmpName varchar(30),
   Contact nchar(15),
   EmailId nvarchar(50)
)

Step 2: Create MVC application


Figure 1: Application Name


Figure 2: MVC template


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:


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:


Figure 5: Server Explorer surface area


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:


Figure 7: Select Controller


Figure 8: Add Controller Name

Step 4: Controller

Create a default controller that looks as in the following:

namespace MVC.Controllers
{
    public class EmployeeInfoController : Controller
    {

        public ActionResult Index()
        {
            return View();
        }
    }
}

Now create a select controller, an insert controller, an edit controller and a the Delete controller as in the following:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

using MVC.Models;

namespace MVC.Models
{
    public class EmployeeInfoController : Controller
    {
        EmpDBDataContext db = new EmpDBDataContext();
        public ActionResult Index() //Record Select
        {
            List<EmployeeData> EmployeeDatas = db.EmployeeDatas.OrderByDescending(x => x.EmpID).ToList<EmployeeData>();
            return View(EmployeeDatas);
        }
        [HttpGet]
        public PartialViewResult Create()   //Insert PartialView
        {
        return PartialView(new MVC.Models.EmployeeInfo());
        }
        [HttpPost]
        public JsonResult Create(MVC.EmployeeData Emp) // Record Insert
        {
            EmpDBDataContext db = new EmpDBDataContext();
            db.EmployeeDatas.InsertOnSubmit(Emp);
            db.SubmitChanges();
            return Json(Emp, JsonRequestBehavior.AllowGet);
        }

        [HttpGet]
        public PartialViewResult Edit(Int32 empid)  // Update PartialView
        {
            EmpDBDataContext db = new EmpDBDataContext();
            EmployeeData emp = db.EmployeeDatas.Where(x => x.EmpID == empid).FirstOrDefault();
            EmployeeInfo empinfo = new EmployeeInfo();

            empinfo.EmailId = emp.EmpID.ToString();
            empinfo.EmpName = emp.EmpName;
            empinfo.Contact = emp.Contact;
            empinfo.EmailId = emp.EmailId;

            return PartialView(empinfo);
        }

        [HttpPost]
        public JsonResult Edit(MVC.EmployeeData employee)  // Record Update
        {

            EmpDBDataContext db = new EmpDBDataContext();
            EmployeeData empdt = db.EmployeeDatas.Where(x => x.EmpID == employee.EmpID).FirstOrDefault();


            empdt.EmpName = employee.EmpName;
            empdt.Contact = employee.Contact;
            empdt.EmailId = employee.EmailId;
            db.SubmitChanges();

            return Json(empdt, JsonRequestBehavior.AllowGet);
        }

        public JsonResult Delete(Int32 empid)
        {
            EmployeeData emp = db.EmployeeDatas.Where(x => x.EmpID == empid).FirstOrDefault();
            db.EmployeeDatas.DeleteOnSubmit(emp);
            db.SubmitChanges();
            return Json(true, JsonRequestBehavior.AllowGet);
        }
    }
}

Step 5: View

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

Figure 9: Add view to controller

1. Index View (select data)


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

@model List<MVC.EmployeeData>

@{
    ViewBag.Title = "Index";
    Layout = "~/Views/Shared/_Layout.cshtml";
    <style type="text/css">
        .grid {
            width: 100%;
        }
    </style>
}

<div style="padding:7px 0;">
    <input type="button" value="Add New Employee" onclick="CreateEmployee()" />
</div>

<div id='OpenDilog'></div>

<h3>Employee Information List</h3>

<div style="width:100%;">
    @{
    WebGrid grid = new WebGrid(Model);
    @grid.GetHtml(
         tableStyle: "grid",
         fillEmptyRows: false,
         mode: WebGridPagerModes.All,
         firstText: "<< First",
         previousText: "< Prev",
         nextText: "Next >",
         lastText: "Last >>",
         columns: new[] {
         grid.Column("EmpID",header: "ID"),
         grid.Column("EmpName",header: "Name"),
         grid.Column("Contact"),
         grid.Column("EmailId"),
         grid.Column("EmpID", header: "Action", canSort:false,

         format: @<text>
    @Html.Raw("<img src='/images/edit.png' title='Edit' onclick='EditEmployee(" + item.EmpID + ")' />")
    @Html.Raw("<img src='/images/delete.png' title='Edit' onclick='DeleteEmployee(" + item.EmpID + ")' />")
           </text>
        )
     })
    }
</div>

<link href="@Url.Content("~/Content/Site.css")" rel="stylesheet" type="text/css" />
<link href="@Url.Content("~/jquery-ui-1.10.4/themes/base/jquery-ui.css")" rel="stylesheet" type="text/css" />
<script src="@Url.Content("~/jquery-ui-1.10.4/ui/minified/jquery-ui.min.js")" type="text/javascript"></script>

<script type="text/javascript">

    function CreateEmployee() {
        var div = $("#OpenDilog");
        div.load("/EmployeeInfo/Create", function () {
            div.dialog({
                modal: true,
                width: 500,
                height: 400,
                title: "Add New Employee",
                resizable: false
            });
        });
    }

    function EditEmployee(E_ID) {
        var ph = $("#OpenDilog");
        ph.load("/EmployeeInfo/Edit?EmpID=" + E_ID, function () {
            ph.dialog({
                modal: true,
                width: 500,
                height: 400,
                title: "Edit Employee",
                resizable: false
            });
        });
    }

    function DeleteEmployee(E_ID) {
        if (confirm("Are You Sure Delete Selected Employee Record No.? " + E_ID)) {
            var data = { 'EmpID': E_ID }
            $.post('/EmployeeInfo/Delete', data,
            function (data) {
                if (data == true)
                    location = location.href;
                else
                    alert("Not delete something Wrong");
            });
        }
    }

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 .


Figure 11: Add Create view

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

Create View Code

@model MVC.Models.EmployeeInfo

@using (Html.BeginForm())
{
    @Html.ValidationSummary(true)

    <fieldset>
        <legend></legend>

        <div class="editor-label">
           Employee Name
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.EmpName)
            @Html.ValidationMessageFor(model => model.EmpName)
        </div>

        <div class="editor-label">
            Contact
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.Contact)
            @Html.ValidationMessageFor(model => model.Contact)
        </div>

        <div class="editor-label">
          Email ID
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.EmailId)
            @Html.ValidationMessageFor(model => model.EmailId)
       </div>

        <p>
            <input type="button" value="Create" onclick="SaveEmployee()" />
        </p>
    </fieldset>
}
<div>
    @Html.ActionLink("Close", "Index")
</div>

<link href="@Url.Content("~/Content/Site.css")" rel="stylesheet" type="text/css"/>
<link href="@Url.Content("~/jquery-ui-1.10.4/themes/base/jquery-ui.css")" rel="stylesheet" type="text/css" />
<script src="@Url.Content("~/jquery-ui-1.10.4/ui/minified/jquery-ui.min.js")" type="text/javascript"></script>

<script type="text/javascript">
    function SaveEmployee() {
        var EmpName = $("#EmpName").val();
        var Contact = $("#Contact").val();
        var EmailId = $("#EmailId").val();

        var Employee = {
            "EmpName": EmpName, "Contact": Contact,
            "EmailId": EmailId
        };

        $.post("/EmployeeInfo/Create", Employee,
        function (data) { if (data == 0) { location = location.href; } }, 'json');
    }

3. Edit View (Update Employee)

Also create an Editview as a Partial view.


Figure 12: Add Edit View

Edit view code

@model MVC.Models.EmployeeInfo

@using (Html.BeginForm())
{
    @Html.ValidationSummary(true)

    <fieldset>
        <legend></legend>

        <div class="editor-label">
           Employee Name
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.EmpName)
            @Html.ValidationMessageFor(model => model.EmpName)
        </div>

        <div class="editor-label">
            Contact
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.Contact)
            @Html.ValidationMessageFor(model => model.Contact)
        </div>

        <div class="editor-label">
          Email ID
        </div>
        <div class="editor-field">
            @Html.EditorFor(model => model.EmailId)
            @Html.ValidationMessageFor(model => model.EmailId)
       </div>

        <p>
            <input type="button" value="Create" onclick="SaveEmployee()" />
        </p>
    </fieldset>
}
<div>
    @Html.ActionLink("Close", "Index")
</div>

<link href="@Url.Content("~/Content/Site.css")" rel="stylesheet" type="text/css"/>
<link href="@Url.Content("~/jquery-ui-1.10.4/themes/base/jquery-ui.css")" rel="stylesheet" type="text/css" />
<script src="@Url.Content("~/jquery-ui-1.10.4/ui/minified/jquery-ui.min.js")" type="text/javascript"></script>

<script type="text/javascript">
    function SaveEmployee() {
        var EmpName = $("#EmpName").val();
        var Contact = $("#Contact").val();
        var EmailId = $("#EmailId").val();

        var Employee = {
            "EmpName": EmpName, "Contact": Contact,
            "EmailId": EmailId
        };

        $.post("/EmployeeInfo/Create", Employee,
        function (data) { if (data == 0) { location = location.href; } }, 'json');
    }
</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.


Figure 13: Views

Step: Models

Now create a Model for EmployeeInfo as in the following:


Figure 14: Add Models class


Figuer 15: Class Name

Models Class Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;

namespace MVC.Models
{
   public class EmployeeInfo
   {
      public int EmpID;

      [Required(ErrorMessage = "Can not be blank Name")]
      public string EmpName { get; set; }

      [Required(ErrorMessage = "Can not be blank Contact")]
      public string Contact { get; set; }

      [Required(ErrorMessage = "Can not be blank Email Id")]
      public string EmailId { get; set; }
   }
}

Now run your MVC example in a browser.


Figure 16: Run Application.

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


Figure 17: Add Record

Now delete the record without 130 with the delete button.


Figure 18: Record Delete


Figure 19: Selected record opened with the Edit dialog

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


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.