CRUD Operations In ASP.NET MVC Using ADO.NET

 

In this post, I’m going to discuss about the CRUD (Create, Read, Update and Delete) Operations in an ASP.NET MVC application by using raw ADO.NET. Most of the new learners, who started to learn MVC asked this frequently. That’s the main reason I wrote this tutorial. In this article I'm going to explain step by step procedure from DB table creation to all MVC files.

Software Requirements

For this particular application, I have used the following configuration:

  1. Visual Studio 2015
  2. SQL Server 2008

In order to keep my hand clean, I have used a simple table to do the CRUD operation.

Step 1: Execute the following script on your DB.

  1. SET ANSI_NULLS ON  
  2. GO  
  3.   
  4. SET QUOTED_IDENTIFIER ON  
  5. GO  
  6.   
  7. SET ANSI_PADDING ON  
  8. GO  
  9.   
  10. CREATE TABLE [dbo].[tblStudent](  
  11. [student_id] [int] IDENTITY(1,1) NOT NULL,  
  12. [student_name] [varchar](50) NOT NULL,  
  13. [stduent_age] [intNOT NULL,  
  14. [student_gender] [varchar](6) NOT NULL,  
  15. CONSTRAINT [PK_tblStudent] PRIMARY KEY CLUSTERED  
  16. (  
  17. [student_id] ASC  
  18. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  19. ON [PRIMARY]  
  20.   
  21. GO  
  22.   
  23. SET ANSI_PADDING OFF  
  24. GO 

Step 2: Create an “Empty” ASP.NET MVC application in Visual Studio 2015.

 

Step 3:

Add an “Empty” Controller by right clicking on the “Controller” folder. Select “Add” then select “Controller..”. In the popup select “MVC 5 Controller”, then click “Add”. In the next popup you should give the name  “CRUDController”.


After adding the controller, you may notice as per the “convention” in ASP.NET MVC under the “Views” folder, a new folder named “CRUD” also created.


Step 4:

Our DB access code are going to be placed inside the “Models” folder. Model is just a “Class” file. So we are going to create a Model class for our purpose as below:

  1. Right click on the “Model” folder. In the context menu select “Add” then choose “New item..”.
  2. In the popup, select “Code” then choose “Class” and name the class file as “CRUDModel” then click “Add”. That’s all!


Step 5:

Till now we created classes for “Controller” and “Model”. We didn’t create any views till now. In this step we are going to create a view, which is going to act as a “home” page for our application. In order to create the view:

  1. Right click on the “CRUD” folder under the “Views” folder in the context menu select “Add” then choose “View..”.
  2. In the popup, give “View name” and uncheck the “Use a layout page” checkbox. Finally, click the “Add” button.
 

Step 6:

We don’t have a controller named “Default”, which is specified in the “RouteConfig.cs” file. We need to change the controller’s name as “CRUD”, in the default route values.

Route.config

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.Mvc;  
  6. using System.Web.Routing;  
  7.   
  8. namespace MVCWithADO  
  9. {  
  10. public class RouteConfig  
  11. {  
  12. public static void RegisterRoutes(RouteCollection routes)  
  13. {  
  14. routes.IgnoreRoute("{resource}.axd/{*pathInfo}");  
  15.   
  16. routes.MapRoute(  
  17. name: "Default",  
  18. url: "{controller}/{action}/{id}",  
  19. defaults: new { controller = "CRUD", action = "Index", id = UrlParameter.Optional }  
  20. );  
  21. }  
  22. }  
  23. }  

After the above change, just press F5 in Visual Studio, to verify that our application works fine without any error.

Step 7:

In order to achieve the complete CRUD operation, I’m going to add number of views as described in the Step 5. Here is the complete list of views and it’s purpose.

ViewPurpose
Home.cshtmlThis is the default view. Loaded when the application launched. Will display all the records in the table
Create.cshtmlDisplays control’s to insert the record. Will be rendered when the “Add New Record” button clicked on the “Home.cshtml” view.
Edit.cshtmlDisplays control’s to edit the record. Will be rendered when the “Edit” button clicked on the “Home.cshtml” view.

Step 8:

The model class, contains all the “Data Access” logic. In other words, it will interact with Database and give it back to “View” through “Controller”.

CRUDModel.cs
  1. using System.Data;  
  2. using System.Data.SqlClient;  
  3.   
  4. namespace MVCWithADO.Models  
  5. {  
  6.     public class CRUDModel  
  7.     {  
  8.         /// <summary>  
  9.         /// Get all records from the DB  
  10.         /// </summary>  
  11.         /// <returns>Datatable</returns>  
  12.         public DataTable GetAllStudents()  
  13.         {  
  14.             DataTable dt = new DataTable();  
  15.             string strConString =@"Data Source=WELCOME-PC\SQLSERVER2008;Initial Catalog=MyDB;Integrated Security=True";  
  16.             using (SqlConnection con = new SqlConnection(strConString))  
  17.             {  
  18.                 con.Open();  
  19.                 SqlCommand cmd = new SqlCommand("Select * from tblStudent", con);  
  20.                 SqlDataAdapter da = new SqlDataAdapter(cmd);  
  21.                 da.Fill(dt);  
  22.             }  
  23.             return dt;  
  24.         }  
  25.   
  26.         /// <summary>  
  27.         /// Get student detail by Student id  
  28.         /// </summary>  
  29.         /// <param name="intStudentID"></param>  
  30.         /// <returns></returns>  
  31.         public DataTable GetStudentByID(int intStudentID)  
  32.         {  
  33.             DataTable dt = new DataTable();  
  34.   
  35.             string strConString = @"Data Source=WELCOME-PC\SQLSERVER2008;Initial Catalog=MyDB;Integrated Security=True";  
  36.   
  37.             using (SqlConnection con = new SqlConnection(strConString))  
  38.             {  
  39.                 con.Open();  
  40.                 SqlCommand cmd = new SqlCommand("Select * from tblStudent where student_id=" + intStudentID, con);  
  41.                 SqlDataAdapter da = new SqlDataAdapter(cmd);  
  42.                 da.Fill(dt);  
  43.             }  
  44.             return dt;  
  45.         }  
  46.   
  47.         /// <summary>  
  48.         /// Update the student details  
  49.         /// </summary>  
  50.         /// <param name="intStudentID"></param>  
  51.         /// <param name="strStudentName"></param>  
  52.         /// <param name="strGender"></param>  
  53.         /// <param name="intAge"></param>  
  54.         /// <returns></returns>  
  55.         public int UpdateStudent(int intStudentID, string strStudentName, string strGender, int intAge)  
  56.         {  
  57.             string strConString = @"Data Source=WELCOME-PC\SQLSERVER2008;Initial Catalog=MyDB;Integrated Security=True";  
  58.   
  59.             using (SqlConnection con = new SqlConnection(strConString))  
  60.             {  
  61.                 con.Open();  
  62.                 string query = "Update tblStudent SET student_name=@studname, student_age=@studage , student_gender=@gender where student_id=@studid";  
  63.                 SqlCommand cmd = new SqlCommand(query, con);  
  64.                 cmd.Parameters.AddWithValue("@studname", strStudentName);  
  65.                 cmd.Parameters.AddWithValue("@studage", intAge);  
  66.                 cmd.Parameters.AddWithValue("@gender", strGender);  
  67.                 cmd.Parameters.AddWithValue("@studid", intStudentID);  
  68.                 return cmd.ExecuteNonQuery();  
  69.             }  
  70.         }  
  71.   
  72.         /// <summary>  
  73.         /// Insert Student record into DB  
  74.         /// </summary>  
  75.         /// <param name="strStudentName"></param>  
  76.         /// <param name="strGender"></param>  
  77.         /// <param name="intAge"></param>  
  78.         /// <returns></returns>  
  79.         public int InsertStudent(string strStudentName, string strGender, int intAge)  
  80.         {  
  81.             string strConString = @"Data Source=WELCOME-PC\SQLSERVER2008;Initial Catalog=MyDB;Integrated Security=True";  
  82.   
  83.             using (SqlConnection con = new SqlConnection(strConString))  
  84.             {  
  85.                 con.Open();  
  86.                 string query = "Insert into tblStudent (student_name, student_age,student_gender) values(@studname, @studage , @gender)";  
  87.                 SqlCommand cmd = new SqlCommand(query, con);  
  88.                 cmd.Parameters.AddWithValue("@studname", strStudentName);  
  89.                 cmd.Parameters.AddWithValue("@studage", intAge);  
  90.                 cmd.Parameters.AddWithValue("@gender", strGender);  
  91.                 return cmd.ExecuteNonQuery();  
  92.             }  
  93.         }  
  94.   
  95.         /// <summary>  
  96.         /// Delete student based on ID  
  97.         /// </summary>  
  98.         /// <param name="intStudentID"></param>  
  99.         /// <returns></returns>  
  100.         public int DeleteStudent(int intStudentID)  
  101.         {  
  102.             string strConString = @"Data Source=WELCOME-PC\SQLSERVER2008;Initial Catalog=MyDB;Integrated Security=True";  
  103.   
  104.             using (SqlConnection con = new SqlConnection(strConString))  
  105.             {  
  106.                 con.Open();  
  107.                 string query = "Delete from tblStudent where student_id=@studid";  
  108.                 SqlCommand cmd = new SqlCommand(query, con);  
  109.                 cmd.Parameters.AddWithValue("@studid", intStudentID);  
  110.                 return cmd.ExecuteNonQuery();  
  111.             }  
  112.         }  
  113.     }  

CRUDController.cs

In an MVC application, controller is the entry point. The following code contains all the action methods for the complete CRUD operation.
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.Mvc;  
  6. using System.Data;  
  7. using System.Data.SqlClient;  
  8. using MVCWithADO.Models;  
  9. namespace MVCWithADO.Controllers  
  10. {  
  11.     public class CRUDController : Controller  
  12.     {  
  13.         /// <summary>  
  14.         /// First Action method called when page loads  
  15.         /// Fetch all the rows from DB and display it  
  16.         /// </summary>  
  17.         /// <returns>Home View</returns>  
  18.         public ActionResult Index()  
  19.         {  
  20.             CRUDModel model = new CRUDModel();  
  21.             DataTable dt = model.GetAllStudents();  
  22.             return View("Home",dt);  
  23.         }  
  24.   
  25.         /// <summary>  
  26.         /// Action method, called when the "Add New Record" link clicked  
  27.         /// </summary>  
  28.         /// <returns>Create View</returns>  
  29.         public ActionResult Insert()  
  30.         {  
  31.             return View("Create");  
  32.         }  
  33.   
  34.         /// <summary>  
  35.         /// Action method, called when the user hit "Submit" button  
  36.         /// </summary>  
  37.         /// <param name="frm">Form Collection  Object</param>  
  38.         /// <param name="action">Used to differentiate between "submit" and "cancel"</param>  
  39.         /// <returns></returns>  
  40.         public ActionResult InsertRecord(FormCollection frm, string action)  
  41.         {  
  42.             if (action == "Submit")  
  43.             {  
  44.                 CRUDModel model = new CRUDModel();  
  45.                 string name = frm["txtName"];  
  46.                 int age = Convert.ToInt32(frm["txtAge"]);  
  47.                 string gender = frm["gender"];  
  48.                 int status = model.InsertStudent(name, gender, age);  
  49.                 return RedirectToAction("Index");  
  50.             }  
  51.             else  
  52.             {  
  53.                 return RedirectToAction("Index");  
  54.             }  
  55.         }  
  56.   
  57.         /// <summary>  
  58.         /// Action method called when the user click "Edit" Link  
  59.         /// </summary>  
  60.         /// <param name="StudentID">Student ID</param>  
  61.         /// <returns>Edit View</returns>  
  62.         public ActionResult Edit(int StudentID)  
  63.         {  
  64.             CRUDModel model = new CRUDModel();  
  65.             DataTable dt = model.GetStudentByID(StudentID);  
  66.             return View("Edit", dt);  
  67.         }  
  68.   
  69.         /// <summary>  
  70.         /// Actin method, called when user update the record or cancel the update.  
  71.         /// </summary>  
  72.         /// <param name="frm">Form Collection</param>  
  73.         /// <param name="action">Denotes the action</param>  
  74.         /// <returns>Home view</returns>  
  75.         public ActionResult UpdateRecord(FormCollection frm,string action)  
  76.         {  
  77.             if (action == "Submit")  
  78.             {  
  79.                 CRUDModel model = new CRUDModel();  
  80.                 string name = frm["txtName"];  
  81.                 int age = Convert.ToInt32(frm["txtAge"]);  
  82.                 string gender = frm["gender"];  
  83.                 int id = Convert.ToInt32(frm["hdnID"]);  
  84.                 int status = model.UpdateStudent(id, name, gender, age);  
  85.                 return RedirectToAction("Index");  
  86.             }  
  87.             else  
  88.             {  
  89.                 return RedirectToAction("Index");  
  90.             }  
  91.         }  
  92.   
  93.         /// <summary>  
  94.         /// Action method called when the "Delete" link clicked  
  95.         /// </summary>  
  96.         /// <param name="StudentID">Stutend ID to edit</param>  
  97.         /// <returns>Home view</returns>  
  98.         public ActionResult Delete(int StudentID)  
  99.         {   
  100.             CRUDModel model = new CRUDModel();  
  101.             model.DeleteStudent(StudentID);  
  102.             return RedirectToAction("Index");  
  103.         }  
  104.     }  

Views
 
Views are combination of markup as well as server side code. As you noticed the views "Home" and "Edit" takes the ADO.NET object Datatable as a model. Also, for simplicity i don't use "Layout".
 
Home.cshtml
  1. @using System.Data  
  2. @using System.Data.SqlClient  
  3. @model System.Data.DataTable  
  4. @{  
  5.     Layout = null;  
  6. }  
  7.   
  8. <!DOCTYPE html>  
  9.   
  10. <html>  
  11. <head>  
  12.     <meta name="viewport" content="width=device-width" />  
  13.     <title>Home</title>  
  14. </head>  
  15. <body>  
  16.     <form method="post" name="Display">  
  17.         <h2>Home</h2>  
  18.         @Html.ActionLink("Add New Record""Insert")  
  19.         <br />  
  20.         @{  
  21.             if (Model.Rows.Count > 0)  
  22.             {  
  23.                 <table border="1">  
  24.                     <thead>  
  25.                         <tr>  
  26.                             <td>  
  27.                                 Student ID  
  28.                             </td>  
  29.                             <td>  
  30.                                 Name  
  31.                             </td>  
  32.                             <td>  
  33.                                 Age  
  34.                             </td>  
  35.                             <td>Gender</td>  
  36.                         </tr>  
  37.                     </thead>  
  38.                     @foreach (DataRow dr in Model.Rows)  
  39.                     {  
  40.                         <tr>  
  41.                             <td>@dr["student_id"].ToString()  </td>  
  42.                             <td>@dr["student_name"].ToString()  </td>  
  43.                             <td>@dr["student_age"].ToString()  </td>  
  44.                             <td>@dr["student_gender"].ToString()  </td>  
  45.                             <td>@Html.ActionLink("Edit ""Edit"new { StudentID = dr["student_id"].ToString() })</td>  
  46.                             <td>@Html.ActionLink("| Delete""Delete"new { StudentID = dr["student_id"].ToString() })</td>  
  47.                         </tr>  
  48.                     }  
  49.   
  50.                 </table>  
  51.                 <br />  
  52.             }  
  53.             else  
  54.             {  
  55.                 <span> No records found!!</span>  
  56.             }  
  57.             }  
  58.   
  59.     </form>  
  60. </body>  
  61. </html> 
Create.cshtml
  1. <!DOCTYPE html>  
  2. <html>  
  3. <head>  
  4.     <meta name="viewport" content="width=device-width" />  
  5.     <title>Insert</title>  
  6. </head>  
  7. <body>  
  8.     <form id="frmDetail" method="post" action="@Url.Action("InsertRecord")">  
  9.         Enter Name:<input name="txtName" />  
  10.         <br />  
  11.         Enter Age:<input name="txtAge" />  
  12.         <br />  
  13.         Select Gender: <input type="radio" name="gender" value="male" checked>Male  
  14.         <input type="radio" name="gender" value="female">Female  
  15.         <br />  
  16.         <input type="submit" value="Submit" name="action" />  
  17.         <input type="submit" value="Cancel" name="action" />  
  18.     </form>  
  19. </body>  
  20. </html> 
Edit.cshtml
  1. @using System.Data  
  2. @using System.Data.SqlClient  
  3. @model System.Data.DataTable  
  4. @{  
  5.     ViewBag.Title = "EditView";  
  6. }  
  7. <html>  
  8. <head>  
  9.     <script type="text/javascript">  
  10.     </script>  
  11. </head>  
  12. <body>  
  13.     <form id="frmDetail" method="post" action="@Url.Action("UpdateRecord")">  
  14.         Enter Name:<input name="txtName" value="@Model.Rows[0]["student_name"]" />  
  15.         <br />  
  16.         Enter Age:<input name="txtAge" value="@Model.Rows[0]["student_age"]" />  
  17.         <br />  
  18.         Select Gender:  
  19.   
  20.         @if (Model.Rows[0]["student_gender"].ToString().ToLower() == "male")  
  21.         {  
  22.             <input type="radio" name="gender" value="male" checked /> @Html.Raw("Male")  
  23.             <input type="radio" name="gender" value="female" /> @Html.Raw("Female")  
  24.         }  
  25.         else  
  26.         {  
  27.             <input type="radio" name="gender" value="male">  @Html.Raw("Male")  
  28.             <input type="radio" name="gender" value="female" checked /> @Html.Raw("Female")  
  29.         }  
  30.         <input type="hidden" name="hdnID" value="@Model.Rows[0]["student_id"]" />  
  31.         <br />  
  32.         <input type="submit" value="Submit" name="action" />  
  33.         <input type="submit" value="Cancel" name="action"/>  
  34.     </form>  
  35. </body>  
  36. </html> 
Readers, I hope you like this article. Let me know your thoughts as comments.