Enroll Now: Full-Stack Blazor Development Course

Become a member
C# Corner Login
Post Ask Question

CRUD Operations In ASP.NET MVC 5 Using ADO.NET

Vithal Wadje Updated date Dec 02, 2020 699.6k 64 40

Download Free .NET & JAVA Files API

Try Free File Format APIs for Word/Excel/PDF

CRUDUsingMVCwithAdoDotNet.zip

Background

After awesome response of an published by me in the year 2013: Insert, Update, Delete In
GridView Using ASP.Net C#. It now has more than 140 K views, therefore to help beginners I
decided to rewrite the article i with stepbystep approach using ASP.NET MVC, since it is a hot
topic in the market today. I have written this article focusing on beginners so they can understand
the basics of MVC. Please read my previous article using the following links to understand the
basics about MVC:

ActionResult in ASP.NET MVC

Creating an ASP.NET MVC Application

Step 1 : Create an MVC Application.

Now let us start with a stepbystep approach from the creation of simple MVC application as in the
following:

1. "Start", then "All Programs" and select "Microsoft Visual Studio 2015".

2. "File", then "New" and click "Project..." then select "ASP.NET Web Application Template",
then provide the Project a name as you wish and click on OK. After clicking, the following
window will appear:
3. As shown in the preceding screenshot, click on Empty template and check MVC option, then
click OK. This will create an empty MVC web application whose Solution Explorer will look
like the following:
Step 2: Create Model Class

Now let us create the model class named EmpModel.cs by right clicking on model folder as in the
following screenshot:

Note: It is not mandatory that Model class should be in Model folder, it is just for better
readability you can create this class anywhere in the solution explorer. This can be done by
creating different folder name or without folder name or in a separate class library.

EmpModel.cs class code snippet:

01. public class EmpModel  
02.   {  
03.       [Display(Name = "Id")]  
04.       public int Empid { get; set; }  
05.   
06.       [Required(ErrorMessage = "First name is required.")]  
07.       public string Name { get; set; }  
08.   
09.       [Required(ErrorMessage = "City is required.")]  
10.       public string City { get; set; }  
11.   
12.       [Required(ErrorMessage = "Address is required.")]  
13.       public string Address { get; set; }  
14.   
15.   } 

In the above model class we have added some validation on properties with the help of
DataAnnotations.

Step 3:  Create Controller.

Now let us add the MVC 5 controller as in the following screenshot:

After clicking on Add button it will show the following window. Now specify the Controller name
as Employee with suffix Controller as in the following screenshot:

Note: The controller name must be having suffix as 'Controller' after specifying the name of
controller.

After clicking on Add button controller is created with by default code that support CRUD
operations and later on we can configure it as per our requirements.

Step 4 : Create Table and Stored procedures.

Now before creating the views let us create the table name Employee in database according to
our model fields to store the details:
I hope you have created the same table structure as shown above. Now create the stored
procedures to insert, update, view and delete the details as in the following code snippet:

To Insert Records
01. Create procedure [dbo].[AddNewEmpDetails]  
02. (  
03.    @Name varchar (50),  
04.    @City varchar (50),  
05.    @Address varchar (50)  
06. )  
07. as  
08. begin  
09.    Insert into Employee values(@Name,@City,@Address)  
10. End 

To View Added Records

01. Create Procedure [dbo].[GetEmployees]  
02. as  
03. begin  
04.    select *from Employee  
05. End 

To Update Records
01. Create procedure [dbo].[UpdateEmpDetails]  
02. (  
03.    @EmpId int,  
04.    @Name varchar (50),  
05.    @City varchar (50),  
06.    @Address varchar (50)  
07. )  
08. as  
09. begin  
10.    Update Employee   
11.    set Name=@Name,  
12.    City=@City,  
13.    Address=@Address  
14.    where Id=@EmpId  
15. End 

To Delete Records

01. Create procedure [dbo].[DeleteEmpById]  
02. (  
 03.    @EmpId int  
04. )  
05. as   
06. begin  
07.    Delete from Employee where Id=@EmpId  
08. End 

Step 5: Create Repository class.

Now create Repository folder and Add EmpRepository.cs class for database related operations,
after adding the solution explorer will look like the following screenshot:

Now create methods in EmpRepository.cs to handle the CRUD operation as in the following
screenshot:

EmpRepository.cs
01. public class EmpRepository    
02. {    
03.   
04.     private SqlConnection con;    
05.     //To Handle connection related activities    
06.     private void connection()    
07.     {    
08.         string constr = ConfigurationManager.ConnectionStrings["getconn"].To
09.         con = new SqlConnection(constr);    
10.   
11.     }    
12.     //To Add Employee details    
13.     public bool AddEmployee(EmpModel obj)    
14.     {    
15.   
16.         connection();    
17.         SqlCommand com = new SqlCommand("AddNewEmpDetails", con);    
18.         com.CommandType = CommandType.StoredProcedure;    
19.         com.Parameters.AddWithValue("@Name", obj.Name);    
20.         com.Parameters.AddWithValue("@City", obj.City);    
21.         com.Parameters.AddWithValue("@Address", obj.Address);    
22.           
23.         con.Open();    
24.         int i = com.ExecuteNonQuery();    
25.         con.Close();    
26.         if (i >= 1)    
27.         {    
28.   
29.             return true;    
30.   
31.         }    
32.         else    
33.         {    
34.   
35.             return false;    
36.         }    
37.   
38.   
39.     }    
40.     //To view employee details with generic list     
41.     public List<EmpModel> GetAllEmployees()    
42.     {    
43.         connection();    
44.         List<EmpModel> EmpList =new List<EmpModel>();    
45.            
46.   
47.         SqlCommand com = new SqlCommand("GetEmployees", con);    
48.         com.CommandType = CommandType.StoredProcedure;    
49.         SqlDataAdapter da = new SqlDataAdapter(com);    
50.         DataTable dt = new DataTable();    
51.           
52.         con.Open();    
53.         da.Fill(dt);    
54.         con.Close();    
55.         //Bind EmpModel generic list using dataRow     
56.         foreach (DataRow dr in dt.Rows)    
57.         {    
58.   
59.             EmpList.Add(    
60.   
61.                 new EmpModel {    
62.   
63.                     Empid = Convert.ToInt32(dr["Id"]),    
64.                     Name =Convert.ToString( dr["Name"]),    
65.                     City = Convert.ToString( dr["City"]),    
66.                     Address = Convert.ToString(dr["Address"])    
67.   
68.                 }   
69.                 );
70.         }    
71.   
72.         return EmpList;
 73.     }    
74.     //To Update Employee details    
75.     public bool UpdateEmployee(EmpModel obj)    
76.     {    
77.   
78.         connection();    
79.         SqlCommand com = new SqlCommand("UpdateEmpDetails", con);    
80.            
81.         com.CommandType = CommandType.StoredProcedure;    
82.         com.Parameters.AddWithValue("@EmpId", obj.Empid);    
83.         com.Parameters.AddWithValue("@Name", obj.Name);    
84.         com.Parameters.AddWithValue("@City", obj.City);    
85.         com.Parameters.AddWithValue("@Address", obj.Address);    
86.         con.Open();    
87.         int i = com.ExecuteNonQuery();    
88.         con.Close();    
89.         if (i >= 1)    
90.         {    
91.                 
92.             return true;
93.         }    
94.         else    
95.         {
96.             return false;    
97.         }
98.     }    
99.     //To delete Employee details    
100.     public bool DeleteEmployee(int Id)    
101.     {    
102.   
103.         connection();    
104.         SqlCommand com = new SqlCommand("DeleteEmpById", con);    
105.   
106.         com.CommandType = CommandType.StoredProcedure;    
107.         com.Parameters.AddWithValue("@EmpId", Id);    
108.            
109.         con.Open();    
110.         int i = com.ExecuteNonQuery();    
111.         con.Close();    
112.         if (i >= 1)    
113.         {
114.             return true;
115.         }    
116.         else    
117.         {    
118.   
119.             return false;    
120.         }
121.     }    
122. }   

Step 6 : Create Methods into the EmployeeController.cs file.

Now open the EmployeeController.cs and create the following action methods:
01. public class EmployeeController : Controller    
02. {    
03.       
04.     // GET: Employee/GetAllEmpDetails    
05.     public ActionResult GetAllEmpDetails()    
06.     {    
07.           
08.         EmpRepository EmpRepo = new EmpRepository();    
09.         ModelState.Clear();    
10.         return View(EmpRepo.GetAllEmployees());    
11.     }    
12.     // GET: Employee/AddEmployee    
13.     public ActionResult AddEmployee()    
14.     {    
15.         return View();    
16.     }    
17.   
18.     // POST: Employee/AddEmployee    
19.     [HttpPost]    
20.     public ActionResult AddEmployee(EmpModel Emp)    
21.     {    
22.         try    
23.         {    
24.             if (ModelState.IsValid)    
25.             {    
26.                 EmpRepository EmpRepo = new EmpRepository();    
27.   
28.                 if (EmpRepo.AddEmployee(Emp))    
29.                 {    
30.                     ViewBag.Message = "Employee details added successfully";
31.                 }    
32.             }    
33.               
34.             return View();    
35.         }    
36.         catch    
37.         {    
38.             return View();    
39.         }    
40.     }    
41.   
42.     // GET: Employee/EditEmpDetails/5    
43.     public ActionResult EditEmpDetails(int id)    
44.     {    
45.         EmpRepository EmpRepo = new EmpRepository();    
46.   
47.           
48.   
49.         return View(EmpRepo.GetAllEmployees().Find(Emp => Emp.Empid == id));
50.   
51.     }    
52.   
53.     // POST: Employee/EditEmpDetails/5    
54.     [HttpPost]    
55.       
56.     public ActionResult EditEmpDetails(int id,EmpModel obj)    
57.     {    
58.         try    
59.         {    
 60.                 EmpRepository EmpRepo = new EmpRepository();    
61.                   
62.                 EmpRepo.UpdateEmployee(obj);
63.             return RedirectToAction("GetAllEmpDetails");    
64.         }    
65.         catch    
66.         {    
67.             return View();    
68.         }    
69.     }    
70.   
71.     // GET: Employee/DeleteEmp/5    
72.     public ActionResult DeleteEmp(int id)    
73.     {    
74.         try    
75.         {    
76.             EmpRepository EmpRepo = new EmpRepository();    
77.             if (EmpRepo.DeleteEmployee(id))    
78.             {    
79.                 ViewBag.AlertMsg = "Employee details deleted successfully"; 
80.   
81.             }    
82.             return RedirectToAction("GetAllEmpDetails");    
83.   
84.         }    
85.         catch    
86.         {    
87.             return View();    
88.         }    
89.     }
90. }    

Step 7: Create Views.

Create the Partial view to Add the employees

To create the Partial View to add Employees, right click on ActionResult method and then click
Add view. Now specify the view name, template name and model class in EmpModel.cs and click
on Add button as in the following screenshot:
After clicking on Add button it generates the strongly typed view whose code is given below:

AddEmployee.cshtml

01. @model CRUDUsingMVC.Models.EmpModel  
02.   
03.   
04. @using (Html.BeginForm())  
05. {  
06.   
07.     @Html.AntiForgeryToken()  
08.   
09.     <div class="form-horizontal">  
10.         <h4>Add Employee</h4>  
11.         <div>  
12.             @Html.ActionLink("Back to Employee List", "GetAllEmpDetails")  
13.         </div>  
14.         <hr />  
15.         @Html.ValidationSummary(true, "", new { @class = "text-
danger" })  
16.   
17.   
18.         <div class="form-group">  
19.             @Html.LabelFor(model => model.Name, htmlAttributes: new { @class
label col-md-2" })  
20.             <div class="col-md-10">  
21.                 @Html.EditorFor(model => model.Name, new { htmlAttributes = 
control" } })  
22.                 @Html.ValidationMessageFor(model => model.Name, "", new { @c
danger" })  
23.             </div>  
24.         </div>  
25.   
26.         <div class="form-group">  
27.             @Html.LabelFor(model => model.City, htmlAttributes: new { @class
label col-md-2" })  
 28.             <div class="col-md-10">  
29.                 @Html.EditorFor(model => model.City, new { htmlAttributes = 
control" } })  
30.                 @Html.ValidationMessageFor(model => model.City, "", new { @c
danger" })  
31.             </div>  
32.         </div>  
33.   
34.         <div class="form-group">  
35.             @Html.LabelFor(model => model.Address, htmlAttributes: new { @cl
label col-md-2" })  
36.             <div class="col-md-10">  
37.                 @Html.EditorFor(model => model.Address, new { htmlAttributes
control" } })  
38.                 @Html.ValidationMessageFor(model => model.Address, "", new {
danger" })  
39.             </div>  
40.         </div>  
41.   
42.         <div class="form-group">  
43.             <div class="col-md-offset-2 col-md-10">  
44.                 <input type="submit" value="Save" class="btn btn-
default" />  
45.             </div>  
46.         </div>  
47.         <div class="form-group">  
48.             <div class="col-md-offset-2 col-md-10" style="color:green">  
49.                 @ViewBag.Message  
50.   
51.             </div>  
52.         </div>  
53.     </div>  
54.   
55. }  
56.   
57. <script src="~/Scripts/jquery-1.10.2.min.js"></script>  
58. <script src="~/Scripts/jquery.validate.min.js"></script>  
59. <script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script> 

To View Added Employees

To view the employee details let us create the partial view named GetAllEmpDetails:
Now click on add button, it will create GetAllEmpDetails.cshtml strongly typed view whose code is
given below:

GetAllEmpDetails.CsHtml
01. @model IEnumerable<CRUDUsingMVC.Models.EmpModel>  
02.   
03. <p>  
04.     @Html.ActionLink("Add New Employee", "AddEmployee")  
05. </p>  
06.   
07. <table class="table">  
08.     <tr>  
09.   
10.         <th>  
11.             @Html.DisplayNameFor(model => model.Name)  
12.         </th>  
13.         <th>  
14.             @Html.DisplayNameFor(model => model.City)  
15.         </th>  
16.         <th>  
17.             @Html.DisplayNameFor(model => model.Address)  
18.         </th>  
19.         <th></th>  
20.     </tr>  
21.   
22.     @foreach (var item in Model)  
23.     {  
24.         @Html.HiddenFor(model => item.Empid)  
25.         <tr>  
26.   
 27.             <td>  
28.                 @Html.DisplayFor(modelItem => item.Name)  
29.             </td>  
30.             <td>  
31.                 @Html.DisplayFor(modelItem => item.City)  
32.             </td>  
33.             <td>  
34.                 @Html.DisplayFor(modelItem => item.Address)  
35.             </td>  
36.             <td>  
37.                 @Html.ActionLink("Edit", "EditEmpDetails", new { id = item.E
38.                 @Html.ActionLink("Delete", "DeleteEmp", new { id = item.Empi
39.             </td>  
40.         </tr>  
41.   
42.     }  
43.   
44. </table> 

To Update Added Employees

Follow the same procedure and create EditEmpDetails view to edit the employees. After creating
the view the code will be like the following:

EditEmpDetails.cshtml 
01. @model CRUDUsingMVC.Models.EmpModel  
02.   
03.   
04. @using (Html.BeginForm())  
05. {  
06.     @Html.AntiForgeryToken()  
07.   
08.     <div class="form-horizontal">  
09.         <h4>Update Employee Details</h4>  
10.         <hr />  
11.         <div>  
12.             @Html.ActionLink("Back to Details", "GetAllEmployees")  
13.         </div>  
14.         <hr />  
15.         @Html.ValidationSummary(true, "", new { @class = "text-
danger" })  
16.         @Html.HiddenFor(model => model.Empid)  
17.   
18.         <div class="form-group">  
19.             @Html.LabelFor(model => model.Name, htmlAttributes: new { @class
label col-md-2" })  
20.             <div class="col-md-10">  
21.                 @Html.EditorFor(model => model.Name, new { htmlAttributes = 
control" } })  
22.                 @Html.ValidationMessageFor(model => model.Name, "", new { @c
danger" })  
23.             </div>  
24.         </div>  
25.   
 26.         <div class="form-group">  
27.             @Html.LabelFor(model => model.City, htmlAttributes: new { @class
label col-md-2" })  
28.             <div class="col-md-10">  
29.                 @Html.EditorFor(model => model.City, new { htmlAttributes = 
control" } })  
30.                 @Html.ValidationMessageFor(model => model.City, "", new { @c
danger" })  
31.             </div>  
32.         </div>  
33.   
34.         <div class="form-group">  
35.             @Html.LabelFor(model => model.Address, htmlAttributes: new { @cl
label col-md-2" })  
36.             <div class="col-md-10">  
37.                 @Html.EditorFor(model => model.Address, new { htmlAttributes
control" } })  
38.                 @Html.ValidationMessageFor(model => model.Address, "", new {
danger" })  
39.             </div>  
40.         </div>  
41.   
42.         <div class="form-group">  
43.             <div class="col-md-offset-2 col-md-10">  
44.                 <input type="submit" value="Update" class="btn btn-
default" />  
45.             </div>  
46.         </div>  
47.     </div>  
48. }  
49. <script src="~/Scripts/jquery-1.10.2.min.js"></script>  
50. <script src="~/Scripts/jquery.validate.min.js"></script>  
51. <script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script> 

Step 8 : Configure Action Link to Edit and delete the records as in the following figure:

The above ActionLink I have added in GetAllEmpDetails.CsHtml view because from there we will
delete and update the records.

Step 9: Configure RouteConfig.cs to set default action as in the following code snippet:

01. public class RouteConfig  
02.  {  
03.      public static void RegisterRoutes(RouteCollection routes)  
04.      {  
 05.          routes.IgnoreRoute("{resource}.axd/{*pathInfo}");  
06.   
07.          routes.MapRoute(  
08.              name: "Default",  
09.              url: "{controller}/{action}/{id}",  
10.              defaults: new { controller = "Employee", action = "AddEmployee"
11.          );  
12.      }  
13.  } 

From the above RouteConfig.cs the default action method we have set is AddEmployee. It means
that after running the application the AddEmployee view will be executed first.

Now after adding the all model, views and controller our solution explorer will be look like as in
the following screenshot:

Step 10: Run the Application

Now run the application the AddEmployee view will appear as:

Click on save button, the model state validation will fire, as per validation we have set into the
EmpModel.cs class:
Now enter the details and on clicking save button, the records get added into the database and
the following message appears.
Now click on Back to Employee List hyperlink, it will be redirected to employee details grid as in
the following screenshot:
Now similar to above screenshot, add another record, then the list will be as in the following
screenshot:

Now click on Edit button of one of the record, then it will be redirected to Edit view as in the
following screenshot:
Now click on Update button, on clicking, the records will be updated into the database. Click Back
to Details hyperlink then it will be redirected to the Employee list table with updated records as in
the following screenshot:
Now click on delete button for one of the records, then the following confirmation box appears
(we have set configuration in ActionLink):

Now click on OK button, then the updated Employee list table will be like the following
screenshot:

From the preceding examples we have learned how to implement CRUD operations in ASP.NET
MVC using ADO.NET.

Note: