In this article I am going to show how we can perform CRUD operation in a MVC 4 application using LINQ to SQL.
Step 1: Create a NEW MVC application (CRUD Using LINQ 2 SQL in MVC)
Image 1.
Image 2.
Now right click on Solution Explorer and New Item
Image 3.
Now View -> Server Explorer -> Add New Server
Image 4.
Select Your Server -> Log On To the Server -> Select your DB.
Select your DB -> Expand DB -> Select your table.
Image 5.
After it add a new class EmployeeModel.cs in Model folder
Image 6.
Image 7.
Add below code in EmployeeModel.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace CRUD_Using_LINQ_2_SQL_in_MVC.Models
{
public class EmployeeModel
{
public int ID { get; set; }
public string Emp_ID { get; set; }
public string Name { get; set; }
public string Dept { get; set; }
public string City { get; set; }
public string State { get; set; }
public string Country { get; set; }
public string Mobile { get; set; }
}
}
Here in this article I am going to perform these CRUD operations by using repository. So for this I added a IEmployeeRepository.cs interface
Right Click on Model folder -> Add New Item -> Select Interface (IEmployeeRepository.cs)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace CRUD_Using_LINQ_2_SQL_in_MVC.Models
{
public interface IEmployeeRepository
{
IEnumerable<EmployeeModel> GetEmployee();
EmployeeModel GetEmployeeByID(int Emp_ID);
void InsertEmployee(EmployeeModel emp_Model);
void DeleteEmployee(int Emp_ID);
void UpdateEmployee(EmployeeModel emp_Model);
}
}
Right Click on Model folder -> Add New Item -> Select class (EmployeeRepository.cs)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace CRUD_Using_LINQ_2_SQL_in_MVC.Models
{
public class EmployeeRepository : IEmployeeRepository
{
private EmployeeDataContext emp_DataContext;
public EmployeeRepository()
{
emp_DataContext = new EmployeeDataContext();
}
public IEnumerable<EmployeeModel> GetEmployee()
{
IList<EmployeeModel> employeeList = new List<EmployeeModel>();
var myQuery = from q in emp_DataContext.Employees
select q;
var emp = myQuery.ToList();
foreach (var empData in emp)
{
employeeList.Add(new EmployeeModel()
{
ID = empData.ID,
Emp_ID = empData.Emp_ID,
Name = empData.Name,
Dept = empData.Dept,
City = empData.City,
State = empData.State,
Country = empData.Country,
Mobile = empData.Mobile
});
}
return employeeList;
}
public EmployeeModel GetEmployeeByID(int Emp_ID)
{
var query = from u in emp_DataContext.Employees
where u.ID == Emp_ID
select u;
var empData = query.FirstOrDefault();
var model = new EmployeeModel()
{
ID = empData.ID,
Emp_ID = empData.Emp_ID,
Name = empData.Name,
Dept = empData.Dept,
City = empData.City,
State = empData.State,
Country = empData.Country,
Mobile = empData.Mobile
};
return model;
}
public void InsertEmployee(EmployeeModel emp_Model)
{
var empData = new Employee()
{
Emp_ID = emp_Model.Emp_ID,
Name = emp_Model.Name,
Dept = emp_Model.Dept,
City = emp_Model.City,
State = emp_Model.State,
Country = emp_Model.Country,
Mobile = emp_Model.Mobile
};
emp_DataContext.Employees.InsertOnSubmit(empData);
emp_DataContext.SubmitChanges();
}
public void DeleteEmployee(int Emp_ID)
{
Employee employee = emp_DataContext.Employees.Where(u => u.ID == Emp_ID).SingleOrDefault();
emp_DataContext.Employees.DeleteOnSubmit(employee);
emp_DataContext.SubmitChanges();
}
public void UpdateEmployee(EmployeeModel emp_Model)
{
Employee EmpData = emp_DataContext.Employees.Where(u => u.ID == emp_Model.ID).SingleOrDefault();
EmpData.Name = emp_Model.Name;
EmpData.Dept = emp_Model.Dept;
EmpData.City = emp_Model.City;
EmpData.State = emp_Model.State;
EmpData.Country = emp_Model.Country;
EmpData.Mobile = emp_Model.Mobile;
emp_DataContext.SubmitChanges();
}
}
}
Now come to the Controller folder -> Add New Controller ( EmployeeController.cs )
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using CRUD_Using_LINQ_2_SQL_in_MVC.Models;
using System.Data;
namespace CRUD_Using_LINQ_2_SQL_in_MVC.Controllers
{
public class EmployeeController : Controller
{
//
// GET: /Employee/
private IEmployeeRepository _repository;
public EmployeeController()
: this(new EmployeeRepository())
{
}
public EmployeeController(IEmployeeRepository repository)
{
_repository = repository;
}
public ActionResult Index()
{
var employee = _repository.GetEmployee();
return View(employee);
}
public ActionResult Details(int id)
{
EmployeeModel model = _repository.GetEmployeeByID(id);
return View(model);
}
public ActionResult Create()
{
return View(new EmployeeModel());
}
[HttpPost]
public ActionResult Create(EmployeeModel employee)
{
try
{
if (ModelState.IsValid)
{
_repository.InsertEmployee(employee);
return RedirectToAction("Index");
}
}
catch (DataException)
{
ModelState.AddModelError("", "Problem in Data Saving");
}
return View(employee);
}
public ActionResult Edit(int id)
{
EmployeeModel model = _repository.GetEmployeeByID(id);
return View(model);
}
[HttpPost]
public ActionResult Edit(EmployeeModel employee)
{
try
{
if (ModelState.IsValid)
{
_repository.UpdateEmployee(employee);
return RedirectToAction("Index");
}
}
catch (DataException)
{
ModelState.AddModelError("", "Problem in Data Saving");
}
return View(employee);
}
public ActionResult Delete(int id, bool? saveChangesError)
{
if (saveChangesError.GetValueOrDefault())
{
ViewBag.ErrorMessage = "Problem in Deleting";
}
EmployeeModel employee = _repository.GetEmployeeByID(id);
return View(employee);
}
[HttpPost, ActionName("Delete")]
public ActionResult DeleteConfirmed(int id)
{
try
{
EmployeeModel user = _repository.GetEmployeeByID(id);
_repository.DeleteEmployee(id);
}
catch (DataException)
{
return RedirectToAction("Delete",
new System.Web.Routing.RouteValueDictionary {
{ "id", id },
{ "saveChangesError", true } });
}
return RedirectToAction("Index");
}
}
}
Now we will add View. I am going to explain step by step.
1. To Show All Employee -> Right Click on Index -> Add a View
Image 8.
@model IEnumerable<CRUD_Using_LINQ_2_SQL_in_MVC.Models.EmployeeModel>
@{
ViewBag.Title = "All Employee Listing";
}
<h2> <span style="font-family:Verdana; font-size:14pt; color:blue;"> All Employee Listing</span></h2>
<p>
<span style="font-family:Verdana; font-size:10pt; color:darkblue;"> @Html.ActionLink("Create New", "Create") </span>
</p>
<table style="color:green;">
<tr>
<th>
@Html.DisplayNameFor(model => model.Emp_ID)
</th>
<th>
@Html.DisplayNameFor(model => model.Name)
</th>
<th>
@Html.DisplayNameFor(model => model.Dept)
</th>
<th>
@Html.DisplayNameFor(model => model.City)
</th>
<th>
@Html.DisplayNameFor(model => model.State)
</th>
<th>
@Html.DisplayNameFor(model => model.Country)
</th>
<th>
@Html.DisplayNameFor(model => model.Mobile)
</th>
<th></th>
</tr>
@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.Emp_ID)
</td>
<td>
@Html.DisplayFor(modelItem => item.Name)
</td>
<td>
@Html.DisplayFor(modelItem => item.Dept)
</td>
<td>
@Html.DisplayFor(modelItem => item.City)
</td>
<td>
@Html.DisplayFor(modelItem => item.State)
</td>
<td>
@Html.DisplayFor(modelItem => item.Country)
</td>
<td>
@Html.DisplayFor(modelItem => item.Mobile)
</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 })
2. To Show Employee Detail
Right click on Details -> Add View
Image 9.
@model CRUD_Using_LINQ_2_SQL_in_MVC.Models.EmployeeModel
@{
ViewBag.Title = "Details";
}
<h2>Details</h2>
<fieldset>
<legend>EmployeeModel</legend>
<div class="display-label">
@Html.DisplayNameFor(model => model.Emp_ID)
</div>
<div class="display-field">
@Html.DisplayFor(model => model.Emp_ID)
</div>
<div class="display-label">
@Html.DisplayNameFor(model => model.Name)
</div>
<div class="display-field">
@Html.DisplayFor(model => model.Name)
</div>
<div class="display-label">
@Html.DisplayNameFor(model => model.Dept)
</div>
<div class="display-field">
@Html.DisplayFor(model => model.Dept)
</div>
<div class="display-label">
@Html.DisplayNameFor(model => model.City)
</div>
<div class="display-field">
@Html.DisplayFor(model => model.City)
</div>
<div class="display-label">
@Html.DisplayNameFor(model => model.State)
</div>
<div class="display-field">
@Html.DisplayFor(model => model.State)
</div>
<div class="display-label">
@Html.DisplayNameFor(model => model.Country)
</div>
<div class="display-field">
@Html.DisplayFor(model => model.Country)
</div>
<div class="display-label">
@Html.DisplayNameFor(model => model.Mobile)
</div>
<div class="display-field">
@Html.DisplayFor(model => model.Mobile)
</div>
</fieldset>
<p>
@Html.ActionLink("Edit", "Edit", new { id=Model.ID }) |
@Html.ActionLink("Back to List", "Index")
</p>
3. To Edit Employee Detail
Right Click on EDIT -> Add A View
Image 10.
@model CRUD_Using_LINQ_2_SQL_in_MVC.Models.EmployeeModel
@{
ViewBag.Title = "Edit";
}
<h2>Edit</h2>
@using (Html.BeginForm()) {
@Html.ValidationSummary(true)
<fieldset>
<legend>EmployeeModel</legend>
@Html.HiddenFor(model => model.ID)
<div class="editor-label">
@Html.LabelFor(model => model.Emp_ID)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.Emp_ID)
@Html.ValidationMessageFor(model => model.Emp_ID)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.Name)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.Name)
@Html.ValidationMessageFor(model => model.Name)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.Dept)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.Dept)
@Html.ValidationMessageFor(model => model.Dept)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.City)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.City)
@Html.ValidationMessageFor(model => model.City)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.State)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.State)
@Html.ValidationMessageFor(model => model.State)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.Country)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.Country)
@Html.ValidationMessageFor(model => model.Country)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.Mobile)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.Mobile)
@Html.ValidationMessageFor(model => model.Mobile)
</div>
<p>
<input type="submit" value="Save" />
</p>
</fieldset>
}
<div>
@Html.ActionLink("Back to List", "Index")
</div>
@section Scripts {
@Scripts.Render("~/bundles/jqueryval")
}
To Delete an Employee
Right Click on Delete -> Add A view
Image 11.
@model CRUD_Using_LINQ_2_SQL_in_MVC.Models.EmployeeModel
@{
ViewBag.Title = "Delete";
}
<h2>Delete</h2>
<h3>Are you sure you want to delete this?</h3>
<fieldset>
<legend>EmployeeModel</legend>
<div class="display-label">
@Html.DisplayNameFor(model => model.Emp_ID)
</div>
<div class="display-field">
@Html.DisplayFor(model => model.Emp_ID)
</div>
<div class="display-label">
@Html.DisplayNameFor(model => model.Name)
</div>
<div class="display-field">
@Html.DisplayFor(model => model.Name)
</div>
<div class="display-label">
@Html.DisplayNameFor(model => model.Dept)
</div>
<div class="display-field">
@Html.DisplayFor(model => model.Dept)
</div>
<div class="display-label">
@Html.DisplayNameFor(model => model.City)
</div>
<div class="display-field">
@Html.DisplayFor(model => model.City)
</div>
<div class="display-label">
@Html.DisplayNameFor(model => model.State)
</div>
<div class="display-field">
@Html.DisplayFor(model => model.State)
</div>
<div class="display-label">
@Html.DisplayNameFor(model => model.Country)
</div>
<div class="display-field">
@Html.DisplayFor(model => model.Country)
</div>
<div class="display-label">
@Html.DisplayNameFor(model => model.Mobile)
</div>
<div class="display-field">
@Html.DisplayFor(model => model.Mobile)
</div>
</fieldset>
@using (Html.BeginForm()) {
<p>
<input type="submit" value="Delete" /> |
@Html.ActionLink("Back to List", "Index")
</p>
}
5. To Create an Employee
Image 12.
@model CRUD_Using_LINQ_2_SQL_in_MVC.Models.EmployeeModel
@{
ViewBag.Title = "Create";
}
<h2>Create</h2>
@using (Html.BeginForm()) {
@Html.ValidationSummary(true)
<fieldset>
<legend>EmployeeModel</legend>
<div class="editor-label">
@Html.LabelFor(model => model.Emp_ID)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.Emp_ID)
@Html.ValidationMessageFor(model => model.Emp_ID)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.Name)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.Name)
@Html.ValidationMessageFor(model => model.Name)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.Dept)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.Dept)
@Html.ValidationMessageFor(model => model.Dept)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.City)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.City)
@Html.ValidationMessageFor(model => model.City)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.State)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.State)
@Html.ValidationMessageFor(model => model.State)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.Country)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.Country)
@Html.ValidationMessageFor(model => model.Country)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.Mobile)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.Mobile)
@Html.ValidationMessageFor(model => model.Mobile)
</div>
<p>
<input type="submit" value="Create" />
</p>
</fieldset>
}
<div>
@Html.ActionLink("Back to List", "Index")
</div>
@section Scripts {
@Scripts.Render("~/bundles/jqueryval")
}
Now run your application
Image 13.
Image 14.
Image 15.
Image 16.
Image 17.