Sameer Gaikwad CRUD Operations Using LINQ to SQL in MVC mesameergaikwad

  Sameer Gaikwad CRUD Operation SQL stored procedure mesameergaikwad

This article was originally published on mesameergaikwad as Sameer Gaikwad CRUD Operations Using LINQ to SQL in MVC mesameergaikwad

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.

1. CREATE TABLE [dbo].[Employee]  
2. (  
3.     [id] [int] IDENTITY(1,1) NOT NULL,  
4.     [EmpName] [varchar](50) NOT NULL,  
5.     [EmpAddress] [varchar](50) NOT NULL,  
6.     [EmpEmailId] [varchar](50) NOT NULL,  
7. )  

Create MVC Application

We will create an 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 select 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 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 employee class under the Model Folder.

1. namespace crudoperation_linq.Models  
2. {  
3.     public class employe  
4.     {  
5.         public int id { get; set; }  
6.         public string employename { get; set; }  
7.         public string employeaddress { get; set; }  
8.         public string employeemailid { get; set; } 
9.     }  
10. }  

Create a controller class for employe

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

1. namespace crudoperation_linq.Controllers  
2. {  
3.     public class empController : Controller  
4.     {  
5.         // GET: emp  
6.   
7.         DataClasses1DataContext db = new DataClasses1DataContext();  
8.         public ActionResult Index()  
9.         {  
10.             IList<employe> employelist = new List<employe>();  
11.             var query = from qrs in db.Employees select qrs;  
12.             var listdata = query.ToList();  
13.   
14.             foreach (var employedata in listdata)  
15.             {  
16.                 employelist.Add(new employe()  
17.                 {  
18.                     id = employedata.id,  
19.                     employename = employedata.EmpName,  
20.                     employeaddress = employedata.EmpAddress,  
21.                     employeemailid = employedata.EmpEmailId,  
22.                 });                
23.             }             
24.             return View(employelist);  
25.         }  
26.   
27.         public ActionResult Create()  
28.         {  
29.             employe emps = new employe();  
30.             return View(emps);  
31.         }  
32. 
    
33.         [HttpPost]  
34.         public ActionResult Create(employe mod)  
35.         {  
36.             Employee emps = new Employee();    
37.             emps.EmpName = mod.employename;  
38.             emps.EmpAddress = mod.employeaddress;  
39.             emps.EmpEmailId = mod.employeemailid;  
40.             db.Employees.InsertOnSubmit(emps);  
41.             db.SubmitChanges();  
42.             return RedirectToAction("Index");    
43.        }
44.   
45.         [HttpGet]  
46.         public ActionResult Edit(int id)  
47.         {  
48.             employe model = db.Employees.Where(val => val.id ==id).Select(val => new employe()  
49.             {  
50.                 id = val.id,  
51.                 employename = val.EmpName,  
52.                 employeaddress = val.EmpAddress,  
53.                 employeemailid = val.EmpEmailId  
54.             }).SingleOrDefault();  
55.   
56.             return View(model);  
57.           
58.         }  
59. 
    
60.         public ActionResult Edit(employe mod)  
61.         {  
62.             Employee emp = db.Employees.Where(val => val.id == mod.id).Single<Employee>();  
63.             emp.id = mod.id;  
64.             emp.EmpName = mod.employename;  
65.             emp.EmpAddress = mod.employeaddress;  
66.             emp.EmpEmailId = mod.employeemailid;               
67.             db.SubmitChanges();    
68.             return RedirectToAction("index");            
69.         }  
70.   
71.         public ActionResult Delete(int id)  
72.         {  
73.             employe emp = db.Employees.Where(val => val.id == id).Select(val => new employe()  
74.                {  
75.                     id= val.id,  
76.                    employename  = val.EmpName,  
77.                    employeaddress = val.EmpAddress,  
78.                    employeemailid = val.EmpEmailId  
79.                 }).SingleOrDefault();  
80.   
81.             return View(emp);   
82.         }  
83. 
    
84.         [HttpPost]  
85.         public ActionResult Delete(employe mod)  
86.         {  
87.             Employee emp = db.Employees.Where(val => val.id == mod.id).Single<Employee>();  
88.             db.Employees.DeleteOnSubmit(emp);  
89.             db.SubmitChanges();                
90.             return RedirectToAction("Index");  
91.          }  
92.   
93.         public ActionResult Details (int id)  
94.         {  
95.            employe  emp = db.Employees.Where(val => val.id == id).Select(val => new employe()  
96.             {  
97.                 id = val.id,  
98.                 employename = val.EmpName,  
99.                 employeaddress = val.EmpAddress,  
100.                 employeemailid = val.EmpEmailId,  
101.             }).SingleOrDefault();  
102.             return View(emp);                  
103.         }    
104.     }     
105. }  

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

Let's see each view with code.

Create a view to adding a new employee.

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

1. @model crudoperation_linq.Models.employe  
2.   
3. @Scripts.Render("~/bundles/jquery")  
4. @Scripts.Render("~/bundles/jqueryval")  
5.   
6. @using (Html.BeginForm())   
7. {  
8.     @Html.AntiForgeryToken()   
9.      <div class="form-horizontal">  
10.         <h4>employe</h4>  
11.         <hr />  
12.         @Html.ValidationSummary(true, "", new { @class = "text-danger" })  
13.         <div class="form-group">                
14.              @Html.LabelFor(model => model.employename, htmlAttributes: new { @class = "control-label col-md-2" })  
15.             <div class="col-md-10">  
16.                 @Html.EditorFor(model => model.employename, new { htmlAttributes = new { @class = "form-control" } })  
17.                 @Html.ValidationMessageFor(model => model.employename, "", new { @class = "text-danger" })  
18.             </div>  
19.         </div>  
20.   
21.         <div class="form-group">  
22.             @Html.LabelFor(model => model.employeaddress, htmlAttributes: new { @class = "control-label col-md-2" })  
23.             <div class="col-md-10">  
24.                 @Html.EditorFor(model => model.employeaddress, new { htmlAttributes = new { @class = "form-control" } })  
25.                 @Html.ValidationMessageFor(model => model.employeaddress, "", new { @class = "text-danger" })  
26.             </div>  
27.         </div>  
28.   
29.         <div class="form-group">  
30.             @Html.LabelFor(model => model.employeemailid, htmlAttributes: new { @class = "control-label col-md-2" })  
31.             <div class="col-md-10">  
32.                 @Html.EditorFor(model => model.employeemailid, new { htmlAttributes = new { @class = "form-control" } })  
33.                 @Html.ValidationMessageFor(model => model.employeemailid, "", new { @class = "text-danger" })  
34.             </div>  
35.         </div>  
36.   
37.         <div class="form-group">  
38.             <div class="col-md-offset-2 col-md-10">  
39.                 <input type="submit" value="Create" class="btn btn-default" />  
40.             </div>  
41.         </div>  
42.     </div>  
43. }  
44.   
45. <div>  
46.     @Html.ActionLink("Back to List", "Index")  
47. </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.

1. @model IEnumerable<crudoperation_linq.Models.employe>  
2.   
3. <p>  
4.     @Html.ActionLink("Create New", "Create")  
5. </p>  
6. <table class="table">  
7.     <tr>  
8.         <th>  
9.             @Html.DisplayNameFor(model => model.employename)  
10.         </th>  
11.         <th>  
12.             @Html.DisplayNameFor(model => model.employeaddress)  
13.         </th>  
14.         <th>  
15.             @Html.DisplayNameFor(model => model.employeemailid)  
16.         </th>  
17.         <th></th>  
18.     </tr>  
19.   
20. @foreach (var item in Model) {  
21.     <tr>  
22.         <td>  
23.             @Html.DisplayFor(modelItem => item.employename)  
24.         </td>  
25.         <td>  
26.             @Html.DisplayFor(modelItem => item.employeaddress)  
27.         </td>  
28.         <td>  
29.             @Html.DisplayFor(modelItem => item.employeemailid)  
30.         </td>  
31.         <td>  
32.             @Html.ActionLink("Edit", "Edit", new { id=item.id }) |  
33.             @Html.ActionLink("Details", "Details", new { id=item.id }) |  
34.             @Html.ActionLink("Delete", "Delete", new { id=item.id })  
35.         </td>  
36.     </tr>  
37. }  
38.   
39. </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.

1. @model crudoperation_linq.Models.employe  
2.   
3. @Scripts.Render("~/bundles/jquery")  
4. @Scripts.Render("~/bundles/jqueryval")  
5.   
6.   
7. @using (Html.BeginForm())  
8. {  
9.     @Html.AntiForgeryToken()  
10.       
11.     <div class="form-horizontal">  
12.         <h4>employe</h4>  
13.         <hr />  
14.         @Html.ValidationSummary(true, "", new { @class = "text-danger" })  
15.         @Html.HiddenFor(model => model.id)  
16.   
17.         <div class="form-group">  
18.             @Html.LabelFor(model => model.employename, htmlAttributes: new { @class = "control-label col-md-2" })  
19.             <div class="col-md-10">  
20.                 @Html.EditorFor(model => model.employename, new { htmlAttributes = new { @class = "form-control" } })  
21.                 @Html.ValidationMessageFor(model => model.employename, "", new { @class = "text-danger" })  
22.             </div>  
23.         </div>  
24.   
25.         <div class="form-group">  
26.             @Html.LabelFor(model => model.employeaddress, htmlAttributes: new { @class = "control-label col-md-2" })  
27.             <div class="col-md-10">  
28.                 @Html.EditorFor(model => model.employeaddress, new { htmlAttributes = new { @class = "form-control" } })  
29.                 @Html.ValidationMessageFor(model => model.employeaddress, "", new { @class = "text-danger" })  
30.             </div>  
31.         </div>  
32.   
33.         <div class="form-group">  
34.             @Html.LabelFor(model => model.employeemailid, htmlAttributes: new { @class = "control-label col-md-2" })  
35.             <div class="col-md-10">  
36.                 @Html.EditorFor(model => model.employeemailid, new { htmlAttributes = new { @class = "form-control" } })  
37.                 @Html.ValidationMessageFor(model => model.employeemailid, "", new { @class = "text-danger" })  
38.             </div>  
39.         </div>  
40.   
41.         <div class="form-group">  
42.             <div class="col-md-offset-2 col-md-10">  
43.                 <input type="submit" value="Save" class="btn btn-default" />  
44.             </div>  
45.         </div>  
46.     </div>  
47. }  
48.   
49. <div>  
50.     @Html.ActionLink("Back to List", "Index")  
51. </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.

1. @model crudoperation_linq.Models.employe  
2.   
3. <div>  
4.     <h4>employe</h4>  
5.     <hr />  
6.     <dl class="dl-horizontal">  
7.         <dt>  
8.             @Html.DisplayNameFor(model => model.employename)  
9.         </dt>  
10.   
11.         <dd>  
12.             @Html.DisplayFor(model => model.employename)  
13.         </dd>  
14.   
15.         <dt>  
16.             @Html.DisplayNameFor(model => model.employeaddress)  
17.         </dt>  
18.   
19.         <dd>  
20.             @Html.DisplayFor(model => model.employeaddress)  
21.         </dd>  
22.   
23.         <dt>  
24.             @Html.DisplayNameFor(model => model.employeemailid)  
25.         </dt>  
26.   
27.         <dd>  
28.             @Html.DisplayFor(model => model.employeemailid)  
29.         </dd>  
30.   
31.     </dl>  
32. </div>  
33. <p>  
34.     @Html.ActionLink("Edit", "Edit", new { id = Model.id }) |  
35.     @Html.ActionLink("Back to List", "Index")  
36. </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.

1. @model crudoperation_linq.Models.employe  
2.   
3. <h3>Are you sure you want to delete this?</h3>  
4. <div>  
5.     <h4>employe</h4>  
6.     <hr />  
7.     <dl class="dl-horizontal">  
8.         <dt>  
9.             @Html.DisplayNameFor(model => model.employename)  
10.         </dt>  
11.   
12.         <dd>  
13.             @Html.DisplayFor(model => model.employename)  
14.         </dd>  
15.   
16.         <dt>  
17.             @Html.DisplayNameFor(model => model.employeaddress)  
18.         </dt>  
19.   
20.         <dd>  
21.             @Html.DisplayFor(model => model.employeaddress)  
22.         </dd>  
23.   
24.         <dt>  
25.             @Html.DisplayNameFor(model => model.employeemailid)  
26.         </dt>  
27.   
28.         <dd>  
29.             @Html.DisplayFor(model => model.employeemailid)  
30.         </dd>  
31.   
32.     </dl>  
33.   
34.     @using (Html.BeginForm()) {  
35.         @Html.AntiForgeryToken()  
36.   
37.         <div class="form-actions no-color">  
38.             <input type="submit" value="Delete" class="btn btn-default" /> |  
39.             @Html.ActionLink("Back to List", "Index")  
40.         </div>  
41.     }  
42. </div>  

The following is the output of the preceding code:



Summary

In this article, we learned the basic database operations using LINQ to SQL in MVC.