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

Rakesh
7y
128.5k
0
8

Article

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:

Add view to controller

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.

Views

Figure 13: Views

Step: Models

Now create a Model for EmployeeInfo as in the following:

Figure 14: Add Models class

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

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.

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.

Up Next

Ebook Download

View all

Printing in C# Made Easy

Read by 10.8k people

Download Now!

Learn

View all