Email: Password:       Forgot Password 
    .netCodeSG
A Saarsha Group Online Community for dot net codes group like C#, Asp.NET, VB.NET, Sharepoint, JavaScript, JQuery, Ajax, SQL, WCF, WPF.
 
TECHNOLOGIES:
 

CRUD Operation in ASP.NET MVC 4 Application Using LINQ To SQL

Article:
Viewed:  21260 
Posted On:  10/01/2014 12:59:37 
How we can perform CRUD opeartion in asp.net MVC application using LINQ to SQL ? 

 

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) 

Create Project 1.png

Image 1.

Create Project 2.png

Image 2.

Now right click on Solution Explorer and New Item

3.png

Image 3.

Now View -> Server Explorer -> Add New Server

4.png

Image 4.

Select Your Server  -> Log On To the Server -> Select your DB.

Select your DB -> Expand DB -> Select your table.

5.png

Image 5.

After it add a new class EmployeeModel.cs in Model folder

6.png

Image 6.

7.png

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 { getset; }
        public string Emp_ID { getset; }
        public string Name { getset; }
        public string Dept { getset; }
        public string City { getset; }
        public string State { getset; }
        public string Country { getset; }
        public string Mobile { getset; }
    }
}

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);
        } 

        [HttpPostActionName("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

8.png


Image 8.

@model IEnumerable
<CRUD_Using_LINQ_2_SQL_in_MVC.Models.EmployeeModel>

      @{

    ViewBag.Title = "All Employee Listing";
}
<h2> <span style="font-family:Verdanafont-size:14ptcolor:blue;"> All Employee Listing</span></h2>
 
<p>
   <span style="font-family:Verdanafont-size:10ptcolor: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 })
        </td>
    </tr>
}
 
</table> 

2.    To Show Employee Detail 

 Right click on Details -> Add View


9.png

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

10.png

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

11.png


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

12.png


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

13.png

Image 13.

14.png


Image 14.


15.png

Image 15.

 16.png

Image 16.

17.png

Image 17.

  Comment:
 
By Alberto  On  08/06/2016 00:40:21
HI Very good but I couldn't find: EmployeeDataContext Class Thnks
 
By Hemant  On  06/01/2016 04:17:55
Very informative article
 
By Anil  On  06/11/2014 05:09:26
i learned of thing about MVC and Linq , it's very nice...thank you so much
 
By Anil  On  06/11/2014 05:08:07
Thank you so much
         HOME   |   Submit Article   |   Contact Us   |   About Us   |   Terms & Condition   |   Advertise With us