CRUD Operations Using LINQ to SQL in MVC

Atul Rawat
Mar 20, 2015
51.6k
0
2
Article

We will first create a simple MVC application for Employees.

Database structure

Create a table in a database for employees to store employee information.

Let us see Create Table code.

CREATE TABLE [dbo].[Employee]
(
[id] [int] IDENTITY(1,1) NOT NULL,
[EmpName] [varchar](50) NOT NULL,
[EmpAddress] [varchar](50) NOT NULL,
[EmpEmailId] [varchar](50) NOT NULL,
)

Create MVC Application

We will create a MVC application in Visual Studio 2013. So let us see the step-by-step procedure for creating MVC applications.

Step 1: Go to File => New => Project.

Step 2: Choose "ASP.NET MVC 4 Web Application" from the list, then provide the application name the "crudoperation_linq" and set the path in the location input where you want to create the application.

Step 3: Now choose the Project Template "Empty

Adding a LINQ to SQL Class

Step 1: Right-click on the project then seelct Add new item then select Data from templates.

Step 2: Choose "LINQ to SQL classes" from the list and provide the name. Then click on Add. After clicking Add we can see the .dbml file in our project.

Step 3: Drag the employee table from the database in the Server Explorer.

Create Model Class

The MVC model contains all the application logic validation, Logic Business Logic and data access logic. We can create an employe class under the Model Folder.

namespace crudoperation_linq.Models
{
public class employe
{
public int id { get; set; }
public string employename { get; set; }
public string employeaddress { get; set; }
public string employeemailid { get; set; }
}
}

Create a controller class for employe

Now we can create an emp controller that has an action for all CRUD operations of the employee.

namespace crudoperation_linq.Controllers
{
public class empController : Controller
{
// GET: emp
DataClasses1DataContext db = new DataClasses1DataContext();
public ActionResult Index()
{
IList<employe> employelist = new List<employe>();
var query = from qrs in db.Employees select qrs;
var listdata = query.ToList();
foreach (var employedata in listdata)
{
employelist.Add(new employe()
{
id = employedata.id,
employename = employedata.EmpName,
employeaddress = employedata.EmpAddress,
employeemailid = employedata.EmpEmailId,
});
}
return View(employelist);
}
public ActionResult Create()
{
employe emps = new employe();
return View(emps);
}
[HttpPost]
public ActionResult Create(employe mod)
{
Employee emps = new Employee();
emps.EmpName = mod.employename;
emps.EmpAddress = mod.employeaddress;
emps.EmpEmailId = mod.employeemailid;
db.Employees.InsertOnSubmit(emps);
db.SubmitChanges();
return RedirectToAction("Index");
}
[HttpGet]
public ActionResult Edit(int id)
{
employe model = db.Employees.Where(val => val.id ==id).Select(val => new employe()
{
id = val.id,
employename = val.EmpName,
employeaddress = val.EmpAddress,
employeemailid = val.EmpEmailId
}).SingleOrDefault();
return View(model);
}
public ActionResult Edit(employe mod)
{
Employee emp = db.Employees.Where(val => val.id == mod.id).Single<Employee>();
emp.id = mod.id;
emp.EmpName = mod.employename;
emp.EmpAddress = mod.employeaddress;
emp.EmpEmailId = mod.employeemailid;
db.SubmitChanges();
return RedirectToAction("index");
}
public ActionResult Delete(int id)
{
employe emp = db.Employees.Where(val => val.id == id).Select(val => new employe()
{
id= val.id,
employename = val.EmpName,
employeaddress = val.EmpAddress,
employeemailid = val.EmpEmailId
}).SingleOrDefault();
return View(emp);
}
[HttpPost]
public ActionResult Delete(employe mod)
{
Employee emp = db.Employees.Where(val => val.id == mod.id).Single<Employee>();
db.Employees.DeleteOnSubmit(emp);
db.SubmitChanges();
return RedirectToAction("Index");
}
public ActionResult Details (int id)
{
employe emp = db.Employees.Where(val => val.id == id).Select(val => new employe()
{
id = val.id,
employename = val.EmpName,
employeaddress = val.EmpAddress,
employeemailid = val.EmpEmailId,
}).SingleOrDefault();
return View(emp);
}
}
}

Now we will create a view for each operation of employee.

Let's see each view with code.

Create a view to add a new employee.

Now we can create a view (Create.cshtml). By defalut this view is creatd under the View/emp Floder.

@model crudoperation_linq.Models.employe
@Scripts.Render("~/bundles/jquery")
@Scripts.Render("~/bundles/jqueryval")
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div class="form-horizontal">
<h4>employe</h4>
<hr />
@Html.ValidationSummary(true, "", new { @class = "text-danger" })
<div class="form-group">
@Html.LabelFor(model => model.employename, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.employename, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.employename, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.employeaddress, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.employeaddress, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.employeaddress, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.employeemailid, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.employeemailid, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.employeemailid, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
<div class="col-md-offset-2 col-md-10">
<input type="submit" value="Create" class="btn btn-default" />
</div>
</div>
</div>
}
<div>
@Html.ActionLink("Back to List", "Index")
</div>

The following is the output of the preceding code:

Show list of all Employee

We can create a view "Index.cshtml" under the view/emp folder.

@model IEnumerable<crudoperation_linq.Models.employe>
<p>
@Html.ActionLink("Create New", "Create")
</p>
<table class="table">
<tr>
<th>
@Html.DisplayNameFor(model => model.employename)
</th>
<th>
@Html.DisplayNameFor(model => model.employeaddress)
</th>
<th>
@Html.DisplayNameFor(model => model.employeemailid)
</th>
<th></th>
</tr>
@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.employename)
</td>
<td>
@Html.DisplayFor(modelItem => item.employeaddress)
</td>
<td>
@Html.DisplayFor(modelItem => item.employeemailid)
</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id=item.id }) |
@Html.ActionLink("Details", "Details", new { id=item.id }) |
@Html.ActionLink("Delete", "Delete", new { id=item.id })
</td>
</tr>
}
</table>

The following is the output of the preceding code:

Edit Employe

We can create a view "Edit.cshtml" under the View/Emp folder that uses two action methods of the controller for the Get request and another for the Post request.

@model crudoperation_linq.Models.employe
@Scripts.Render("~/bundles/jquery")
@Scripts.Render("~/bundles/jqueryval")
@using (Html.BeginForm())
{
@Html.AntiForgeryToken()
<div class="form-horizontal">
<h4>employe</h4>
<hr />
@Html.ValidationSummary(true, "", new { @class = "text-danger" })
@Html.HiddenFor(model => model.id)
<div class="form-group">
@Html.LabelFor(model => model.employename, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.employename, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.employename, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.employeaddress, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.employeaddress, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.employeaddress, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.employeemailid, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.employeemailid, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.employeemailid, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
<div class="col-md-offset-2 col-md-10">
<input type="submit" value="Save" class="btn btn-default" />
</div>
</div>
</div>
}
<div>
@Html.ActionLink("Back to List", "Index")
</div>

The following is the output of the preceding code:

Detail of employee

We can create a view "Detail.cshtml" under the View/Emp folder that uses one action method (Details) of the controller for the Get request.

@model crudoperation_linq.Models.employe
<div>
<h4>employe</h4>
<hr />
<dl class="dl-horizontal">
<dt>
@Html.DisplayNameFor(model => model.employename)
</dt>
<dd>
@Html.DisplayFor(model => model.employename)
</dd>
<dt>
@Html.DisplayNameFor(model => model.employeaddress)
</dt>
<dd>
@Html.DisplayFor(model => model.employeaddress)
</dd>
<dt>
@Html.DisplayNameFor(model => model.employeemailid)
</dt>
<dd>
@Html.DisplayFor(model => model.employeemailid)
</dd>
</dl>
</div>
<p>
@Html.ActionLink("Edit", "Edit", new { id = Model.id }) |
@Html.ActionLink("Back to List", "Index")
</p>

The following is the output of the preceding code:

Delete Employe

We can create a view "Delete.cshtml" under the View/Emp folder that uses two action methods of the controller for the Get request and another for the Post request.