--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

This article introduces basic Create, Read, Update and Delete (CRUD) operations using LINQ to SQL in MVC. We are developing an application for User Entity on which we can perform Create, Read, Update and Delete operations.

Create Table

We create a table in a database for the user to store user information. The user table creation code is as in the following:

1. CREATE TABLE [User]  
2. (  
3.      Id int Primary Key Identity(1,1),  
4.      Name nvarchar(50) not null,  
5.      Age int not null check(Age < 35),  
6.      Email nvarchar(50)not null,  
7.      CreateOn dateTime default Getdate()  
8. )

Create an MVC Application

I will create an MVC application using Visual Studio 2012. So let's see the procedure for creating an MVC application.

Step 1: Go to "File" -> "New" -> "Project...".

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

Step 3: Now choose the Project Template "Empty" and select "Razor" as the view engine from the dropdown list.

Adding a LINQ to SQL Class

Entity classes are created and stored in LINQ to SQL Classes files (.dbml files). The O/R Designer opens when you open a .dbml file. It is a DataContext class that contains methods and properties for connecting to a database and manipulating the data in the database. The DataContext name corresponds to the name that you provided for the .dbml file

Step 1: Right-click on the Models folder in the Solution Explorer then go to "Add" and click on "Class."

Step 2: Choose "LINQ to SQL Classes" from the list and provide the name "User" for the dbml name. After that click on "Add".

Step 3: Drag the User table from the database in Server Explorer and drop it onto the O/R Designer surface of the "User.dbml" file.


Figure 1.1: Table in User.dbml file

Create Model Class

The MVC Model contains all application logic (business logic, validation logic, and data access logic), except pure view and controller logic. We create a class for UserModel (UserModel.cs file) under the Models folder, The UserModel Class is in the Models folder; that filename is UserModel.cs as in the following:

1. namespace UserRegistration.Models  
2. {  
3.     public class UserModel  
4.     {  
5.         public int Id { get; set; }  
6.         public string Name { get; set; }  
7.         public string Email { get; set; }  
8.         public int Age { get; set; }  
9.     }  
10. }

Using the Repository Pattern

I implement the Repository pattern by defining one repository class for a domain model entity that requires specialized data access methods. A repository class contains the specialized data access methods required for its corresponding domain model entity. When you create the repository class, you create an interface that represents all of the methods used by the repository class. Within your controllers, you write your code against the interface instead of the repository. That way, you can implement the repository using various data access technologies in the future. So first of all you need to create an interface "IUserRepository" under the Models folder that contains basic CRUD operations access methods for users.

1. using System.Collections.Generic;   
2. namespace UserRegistration.Models  
3. {  
4.    public interface IUserRepository  
5.     {  
6.         IEnumerable<UserModel> GetUsers();  
7.         UserModel GetUserById(int userId);  
8.         void InsertUser(UserModel user);  
9.         void DeleteUser(int userId);  
10.         void UpdateUser(UserModel user);  
11.     }  
12. }

Thereafter create a repository class "UserRepository" that implements the "IUserRepository" interface under the Models folder.

1. using System.Collections.Generic;  
2. using System.Linq;   
3. namespace UserRegistration.Models  
4. {  
5.     public class UserRepository : IUserRepository  
6.     {  
7.         private UserDataContext _dataContext;   
8.         public UserRepository()  
9.         {  
10.             _dataContext = new UserDataContext();  
11.         }   
12.         public IEnumerable<UserModel> GetUsers()  
13.         {  
14.             IList<UserModel> userList = new List<UserModel>();  
15.             var query = from user in _dataContext.Users  
16.                         select user;             
17.             var users = query.ToList();  
18.             foreach(var userData in users )  
19.             {  
20.                 userList.Add(new UserModel()  
21.                 {  
22.                     Id= userData.Id,  
23.                     Name = userData.Name,  
24.                     Email = userData.Email,  
25.                     Age = userData.Age  
26.                 });  
27.             }  
28.             return userList;  
29.         }   
30.         public UserModel GetUserById(int userId)  
31.         {  
32.             var query = from u in _dataContext.Users  
33.                         where u.Id == userId  
34.                         select u;  
35.             var user = query.FirstOrDefault();  
36.             var model = new UserModel()  
37.             {  
38.                 Id = userId,  
39.                 Name = user.Name,  
40.                 Email = user.Email,  
41.                 Age = user.Age  
42.             };  
43.             return model;  
44.         }   
45.         public void InsertUser(UserModel user)  
46.         {  
47.             var userData = new User()  
48.             {  
49.                 Name = user.Name,  
50.                 Email = user.Email,  
51.                 Age = user.Age  
52.             };  
53.             _dataContext.Users.InsertOnSubmit(userData);  
54.             _dataContext.SubmitChanges();  
55.         }   
56.         public void DeleteUser(int userId)  
57.         {  
58.             User user = _dataContext.Users.Where(u => u.Id == userId).SingleOrDefault();  
59.             _dataContext.Users.DeleteOnSubmit(user);  
60.             _dataContext.SubmitChanges();  
61.         }   
62.         public void UpdateUser(UserModel user)  
63.         {  
64.             User userData = _dataContext.Users.Where(u => u.Id == user.Id).SingleOrDefault();  
65.             userData.Name = user.Name;  
66.             userData.Email = user.Email;  
67.             userData.Age = user.Age;             
68.             _dataContext.SubmitChanges();  
69.         }   
70.     }  
71. }

Create Controller and Views for User CRUD operations

You need to create a controller to handle the request from the browser. In this application, I created the "UserController" controller under the Controllers folder.

We need the following "using" in the controller to perform CRUD operations:

1. using System.Data;  
2. using System.Web.Mvc;  
3. using UserRegistration.Models;

We create an instance of the User Repository interface in the User Controller and initialize the user repository in the constructor of the user Controller (UserController.cs) as in the following:

1. private IUserRepository _repository;  
2. private IUserRepository _repository;   
3. public UserController()  
4.    : this(new UserRepository())  
5. {  
6. }   
7. public UserController(IUserRepository repository)  
8. {  
9.     _repository = repository;  
10. }

We will use Scaffold templates to create a view for the CRUD operations. We use five scaffold templates, List, Create, Edit, Delete, and Details. To create a controller that has post and get action results depending on the operation.

Operation 1: Create New User

Create two actions in the controller, one for the new user to create a view (Get Action) and another for submitting new user details to the repository (Post Action). These have the same name, Create.

1. public ActionResult Create()  
2. {  
3.      return View(new UserModel());  
4. }   
5. [HttpPost]  
6. public ActionResult Create(UserModel user)  
7. {  
8.     try  
9.     {  
10.          if (ModelState.IsValid)  
11.          {  
12.               _repository.InsertUser(user);                     
13.               return RedirectToAction("Index");  
14.           }  
15.      }  
16.      catch (DataException)  
17.      {  
18.           ModelState.AddModelError("", "Unable to save changes. Try again, and if the problem persists see your system administrator.");  
19.       }  
20.       return View(user);  
21. }

Now we create a view. To create the view use the following procedure:

1. Right-click on the Action Method Create (GET).
2. The View Name is already filled in so don't change it.
3. The View Engine already selected Razor so don't change it.
4. Check the checkbox "Create a strongly-typed-view" because we are creating a strongly typed view.
5. Choose the Model class "UserModel" so it can be bound with the view.
6. Choose "Create" from the Scaffold template so we can do rapid development and we get the view for creating the new user.
7. Check both checkboxes "Reference script libraries" and "Use a layout or master page".

1. @model UserRegistration.Models.UserModel   
2. @{  
3.     ViewBag.Title = "Create";  
4. }   
5. <h2>Create</h2>   
6. @using (Html.BeginForm()) {  
7.     @Html.ValidationSummary(true)   
8.     <fieldset>  
9.         <legend>UserModel</legend>   
10.         <div class="editor-label">  
11.             @Html.LabelFor(model => model.Name)  
12.         </div>  
13.         <div class="editor-field">  
14.             @Html.EditorFor(model => model.Name)  
15.             @Html.ValidationMessageFor(model => model.Name)  
16.         </div>   
17.         <div class="editor-label">  
18.             @Html.LabelFor(model => model.Email)  
19.         </div>  
20.         <div class="editor-field">  
21.             @Html.EditorFor(model => model.Email)  
22.             @Html.ValidationMessageFor(model => model.Email)  
23.         </div>   
24.         <div class="editor-label">  
25.             @Html.LabelFor(model => model.Age)  
26.         </div>  
27.         <div class="editor-field">  
28.             @Html.EditorFor(model => model.Age)  
29.             @Html.ValidationMessageFor(model => model.Age)  
30.         </div>   
31.         <p>  
32.             <input type="submit" value="Create" />  
33.         </p>  
34.     </fieldset>  
35. }   
36. <div>  
37.     @Html.ActionLink("Back to List", "Index")  
38. </div>  
39. @section Scripts {  
40.     @Scripts.Render("~/bundles/jqueryval")  
41. }

Run the application and call the create action for the user list.


Figure 1.2 New user create

Operation 2: Show List of All Users

Create an action in the controller named Index. The Index action returns a list of users.

1. public ActionResult Index()  
2. {  
3.      var users = _repository.GetUsers();  
4.      return View(users);  
5. }

Now we create a view. To create the view use the following procedure:

1. Compile the source code successfully
2. Right-click on Action Method Index.
3. The View Name is already filled in so don't change it.
4. The View Engine already selected Razor so don't change it.
5. Check the checkbox "Create a strongly-typed-view" because we are creating a strongly typed view.
6. Choose the Model class "UserModel" so it can be bound with the view.
7. Choose "List" from the Scaffold template so rapid development can be done and we get the view with the code for showing the list of Users.
8. Check both checkboxes "Reference script libraries" and "Use a layout or master page".

1. @model IEnumerable<UserRegistration.Models.UserModel>   
2. @{  
3.     ViewBag.Title = "Index";  
4. }   
5. <h2>Index</h2>   
6. <p>  
7.     @Html.ActionLink("Create New", "Create")  
8. </p>  
9. <table>  
10.     <tr>  
11.         <th>  
12.             @Html.DisplayNameFor(model => model.Name)  
13.         </th>  
14.         <th>  
15.             @Html.DisplayNameFor(model => model.Email)  
16.         </th>  
17.         <th>  
18.             @Html.DisplayNameFor(model => model.Age)  
19.         </th>  
20.         <th></th>  
21.     </tr>   
22. @foreach (var item in Model) {  
23.     <tr>  
24.         <td>  
25.             @Html.DisplayFor(modelItem => item.Name)  
26.         </td>  
27.         <td>  
28.             @Html.DisplayFor(modelItem => item.Email)  
29.         </td>  
30.         <td>  
31.             @Html.DisplayFor(modelItem => item.Age)  
32.         </td>  
33.         <td>  
34.             @Html.ActionLink("Edit", "Edit", new { id=item.Id }) |  
35.             @Html.ActionLink("Details", "Details", new { id=item.Id }) |  
36.             @Html.ActionLink("Delete", "Delete", new { id=item.Id })  
37.         </td>  
38.     </tr>  
39. }  
40. </table>

Run the application and call the index action for the user list.


Figure 1.3 All User list

Operation 3: Show Details of User

Create an action in the controller named Details. The Details action returns the details of the user.

1. public ActionResult Details(int id)  
2. {  
3.      UserModel model = _repository.GetUserById(id);  
4.      return View(model);  
5. }

Now we create the view. To create the view use the following procedure:

1. Right-click on Action Method Details.
2. The View Name is already filled in so don't change it.
3. The View Engine already selected Razor so don't change it.
4. Check the checkbox "Create a strongly-typed-view" because we are creating a strongly typed view.
5. Choose the Model class "UserModel" so it can be bound with the view.
6. Choose "Details" from the Scaffold template so we can do rapid development and we get the view with the code for showing the details of the user.
7. Check both the checkboxes "Reference script libraries" and "Use a layout or master page".

1. @model UserRegistration.Models.UserModel   
2. @{  
3.     ViewBag.Title = "Details";  
4. }   
5. <h2>Details</h2>   
6. <fieldset>  
7.     <legend>UserModel</legend>   
8.     <div class="display-label">  
9.          @Html.DisplayNameFor(model => model.Name)  
10.     </div>  
11.     <div class="display-field">  
12.         @Html.DisplayFor(model => model.Name)  
13.     </div>   
14.     <div class="display-label">  
15.          @Html.DisplayNameFor(model => model.Email)  
16.     </div>  
17.     <div class="display-field">  
18.         @Html.DisplayFor(model => model.Email)  
19.     </div>   
20.     <div class="display-label">  
21.          @Html.DisplayNameFor(model => model.Age)  
22.     </div>  
23.     <div class="display-field">  
24.         @Html.DisplayFor(model => model.Age)  
25.     </div>  
26. </fieldset>  
27. <p>  
28.     @Html.ActionLink("Edit", "Edit", new { id=Model.Id }) |  
29.     @Html.ActionLink("Back to List", "Index")  
30. </p>

Run the application and click on the detail link in the user list.


Figure 1.4 Show details of a single user

Operation 4: Update User Details

Create two actions in the controller, one for an existing user edit view (Get Action) and another for submitting the updated user details to the repository (Post Action). These have the same name Edit. The Get action fills in the user details on the form by the id of the user so we would pass the id to the action.

1. public ActionResult Edit(int id)  
2. {  
3.      UserModel model = _repository.GetUserById(id);  
4.      return View(model);  
5. }    
6. [HttpPost]  
7. public ActionResult Edit(UserModel user)  
8. {  
9.      try  
10.      {  
11.          if (ModelState.IsValid)  
12.          {  
13.              _repository.UpdateUser(user);                    
14.              return RedirectToAction("Index");  
15.           }  
16.       }  
17.      catch (DataException)  
18.      {  
19.           ModelState.AddModelError("", "Unable to save changes. Try again, and if the problem persists see your system administrator.");  
20.      }  
21.      return View(user);  
22. }

Now we create the view. To create the view use the following procedure:

1. Right-click on Action Method Edit (GET).
2. The View Name is already filled in so don't change it.
3. The View Engine already selected Razor so don't change it.
4. Check the checkbox "Create a strongly-typed-view" because we are creating a strongly typed view.
5. Choose the Model class "UserModel" so it can be bound with the view.
6. Choose "Edit" from the Scaffold template so we can do rapid development and we get the view for updating an existing user.
7. Check both checkboxes "Reference script libraries" and "Use a layout or master page".

1. @model UserRegistration.Models.UserModel   
2. @{  
3.     ViewBag.Title = "Edit";  
4. }   
5. <h2>Edit</h2>   
6. @using (Html.BeginForm()) {  
7.     @Html.ValidationSummary(true)   
8.     <fieldset>  
9.         <legend>UserModel</legend>   
10.         @Html.HiddenFor(model => model.Id)   
11.         <div class="editor-label">  
12.             @Html.LabelFor(model => model.Name)  
13.         </div>  
14.         <div class="editor-field">  
15.             @Html.EditorFor(model => model.Name)  
16.             @Html.ValidationMessageFor(model => model.Name)  
17.         </div>   
18.         <div class="editor-label">  
19.             @Html.LabelFor(model => model.Email)  
20.         </div>  
21.         <div class="editor-field">  
22.             @Html.EditorFor(model => model.Email)  
23.             @Html.ValidationMessageFor(model => model.Email)  
24.         </div>   
25.         <div class="editor-label">  
26.             @Html.LabelFor(model => model.Age)  
27.         </div>  
28.         <div class="editor-field">  
29.             @Html.EditorFor(model => model.Age)  
30.             @Html.ValidationMessageFor(model => model.Age)  
31.         </div>   
32.         <p>  
33.             <input type="submit" value="Save" />  
34.         </p>  
35.     </fieldset>  
36. }   
37. <div>  
38.     @Html.ActionLink("Back to List", "Index")  
39. </div>   
40. @section Scripts {  
41.     @Scripts.Render("~/bundles/jqueryval")  
42. }

Run the application and click on the Edit link in the user list.


Figure 1.5 Edit User Information

You can insert new updated user information in the input field and then click on the "Save" button to update the user.

Operation 5: Delete User

Create two actions in the controller, one to show the details of the user after clicking on the Delete link (Get Action) and another to Delete the user (Post Action). One is the Delete action but the other overrides the Delete Action that overrides the DeleteConfirmed method. The Get action fills in user details on the form by the id of the user then the Post action is performed on it.

1. public ActionResult Delete(int id, bool? saveChangesError)  
2. {  
3.       if (saveChangesError.GetValueOrDefault())  
4.       {  
5.             ViewBag.ErrorMessage = "Unable to save changes. Try again, and if the problem persists see your system administrator.";  
6.       }  
7.       UserModel user = _repository.GetUserById(id);  
8.       return View(user);  
9. }   
10. [HttpPost, ActionName("Delete")]  
11. public ActionResult DeleteConfirmed(int id)  
12. {  
13.       try  
14.       {  
15.           UserModel user = _repository.GetUserById(id);  
16.           _repository.DeleteUser(id);                  
17.       }  
18.       catch (DataException)  
19.       {  
20.           return RedirectToAction("Delete",  
21.           new System.Web.Routing.RouteValueDictionary {  
22.           { "id", id },  
23.           { "saveChangesError", true } });  
24.       }  
25.       return RedirectToAction("Index");  
26. } 

Now we create the view. To create the view use the following procedure:

1. Right-click on Action Method Delete.
2. The View Name is already filled in so don't change it.
3. The View Engine already selected Razor so don't change it.
4. Check the checkbox "Create a strongly-typed-view" because we are creating a strongly typed view.
5. Choose the Model class "UserModel" so it can be bound with the view.
6. Choose "Delete" from the Scaffold template so we can do rapid development and we get the view of the delete for the existing User.
7. Check both checkboxes "Reference script libraries" and "Use a layout or master page".

1. @model UserRegistration.Models.UserModel   
2. @{  
3.     ViewBag.Title = "Delete";  
4. }   
5. <h2>Delete</h2>   
6. <h3>Are you sure you want to delete this?</h3>  
7. <fieldset>  
8.     <legend>UserModel</legend>   
9.     <div class="display-label">  
10.          @Html.DisplayNameFor(model => model.Name)  
11.     </div>  
12.     <div class="display-field">  
13.         @Html.DisplayFor(model => model.Name)  
14.     </div>   
15.     <div class="display-label">  
16.          @Html.DisplayNameFor(model => model.Email)  
17.     </div>  
18.     <div class="display-field">  
19.         @Html.DisplayFor(model => model.Email)  
20.     </div>   
21.     <div class="display-label">  
22.          @Html.DisplayNameFor(model => model.Age)  
23.     </div>  
24.     <div class="display-field">  
25.         @Html.DisplayFor(model => model.Age)  
26.     </div>  
27. </fieldset>  
28. @using (Html.BeginForm()) {  
29.     <p>  
30.         <input type="submit" value="Delete" /> |  
31.         @Html.ActionLink("Back to List", "Index")  
32.     </p>  
33. }

Run the application and click on the Delete link in the user list.


Figure 1.6 Delete a user

Now click on the Delete button and the user will be deleted. Now the view and action are ready to perform CRUD operations.

You can download the source code for this application from the zip folder.