Post
Ask Question

Select Insert, Update And Delete With ASP.NET MVC

Introduction

In this article we will learn how to perform CRUD operations in an ASP.Net MVC application. I think you are now clear about MVC from previous articles about the MVC framework and you are also familiar with creating MVC applications; if not then please go through the following articles:

  1. ASP.NET MVC Basics: Part 1
  2. ASP.NET MVC Basics: Part 2
  3. ASP.NET MVC Basics: Part 3
  4. Routing in MVC
  5. Custom Login in ASP.NET MVC
  6. Display Graph in ASP.NET MVC

In all the above articles I have explained about ASP.Net MVC. Now I think you are clear about ASP.Net MVC and creating simple ASP.Net MVC applications. Whenever we are developing the applications we are related to a database and in the database driven applications we need to perform CRUD operations. On various blogs and sites you will find CRUD operation samples about ASP.Net MVC. But on many the sites I have seen they are all using Entity Framework to perform the CRUD. Since it is the standard, we have to follow it while developing ASP.Net MVC applications instead of using DataSet, Command, Connection, DataAdapter and DataReader. But in many cases we have to use these ADO.Net 2.0 operations. In this  article we will perform all the operations using ADO.NET instead of the ADO.NET Entity Framework. But use these ADO.Net only where you need to else use the ADO.Net Entity Framework only. So let's start performing CRUD using ADO.Net.

Step 1 : Create the table in your database with the following script.

  1. Create Table Authors    
  2. (    
  3. AuthorId int Identity(1,1)Primary Key,    
  4. Fname Varchar(50),    
  5. Lname Varchar(50)    
  6. )   
Step 2 : Now create a new ASP.Net MVC3 Web application with an empty template. This will create one predefined structure with Controllers, Modes and Views folders. All these folders are empty; now we have to add the content to these folders.

Step 3 : As you know, first we have to create a controller so let's add a new controller with the name Home in the controllers folder. Now our empty controller is ready with an Index method. On the index method we will call the dataset from the database to display the existing Authors in our table. For this we will create a model which will return the dataset, then we will display this data on the view through the Index method of the Home controller. So let's create one model i.e. class in Models folder with the name SelectModel and write the following code in it to retrieve the data from the database.

  1. public DataSet GetAllAuthors()  
  2. {  
  3.     SqlConnection cn = new SqlConnection(@"Data Source=NARESHIT-PC\DOTNET116;User Id=sa;Password=123;DataBase=DEMODB.MDF");  
  4.     SqlCommand cmd = new SqlCommand("Select AuthorId,Fname,Lname From Authors", cn);  
  5.     DataSet ds = new DataSet();  
  6.     SqlDataAdapter da = new SqlDataAdapter(cmd);  
  7.     da.Fill(ds);  
  8.     return ds;  
  9. }  
In the selectModel class we have one method called GetAllAuthors which returns the dataset of all authors. Now modify your Index method of Home Controller like Entity Framework.
  1. public ActionResult Index(SelectInsertUpdateDelete.Models.SelectModel selectmodel)  
  2. {  
  3.     DataSet ds = selectmodel.GetAllAuthors();  
  4.     ViewBag.AuthorList = ds.Tables[0];  
  5.     return View();  
  6. }  

In the above line of code we called the GetAllAuthors method from SelectModel which will return the dataset object and simply we put this dataset in the viewbag. As you know in C# 4.0 we have dynamic programming; you can see one excellent example with viewbag. Here we have written ViewBag.AuthorList which will create dynamically on Author list on runtime for us. Still now we are finished up to calling the dataset and transferring it to the view but still we don't have any view so right-click in the Index method and add a new empty view to display our authorlist and write the following markup to display the results.

  1. <table>  
  2.     <tr>                    
  3.         <td style="background-color: #800080; color: #FFFFFF; font-family: 'Times New Roman', Times, serif; font-size: large; border-style: inset; border-width: thin">Fname:</td>  
  4.         <td style="background-color: #800080; color: #FFFFFF; font-family: 'Times New Roman', Times, serif; font-size: large; border-style: inset; border-width: thin">Lname:</td>  
  5.     </tr>  
  6.     <%foreach (System.Data.DataRow dr in ViewBag.AuthorList.Rows)  
  7.       {%>  
  8.     <tr>  
  9.         <td>  
  10.             <%= Html.ActionLink("Edit", "Edit", new { id = dr["AuthorId"].ToString() })%>  
  11.             <%= Html.ActionLink("Delete", "Delete", new { id = dr["AuthorId"].ToString() })%>  
  12.         </td>  
  13.         <td><%=dr["Fname"].ToString()%></td>  
  14.         <td><%=dr["Lname"].ToString() %></td>  
  15.     </tr>  
  16.     <%}%>  
  17. </table>  
  18. <%= Html.ActionLink("Add New Author", "Add") %>  
In the above markup we are displaying our authorlist by creating a HTML table with having Edit and Delete links and lastly having one more ActionLink to add a new author record.

Step 4 : At this stage we are ready with our Index or select operation; now we can add more methods to our Home Controller to perform Edit, Update and Delete operation so let's start with adding a new record i.e. inserting a new record in the database. In the last step we added an Add New Author ActionLink with Add method so we need to add two more methods in our Home Controller, one for displaying the fields and another for inserting the values; but before that we need to create our model. So add one Model class called InsertModel in your models folder and write the code like below.

  1. [Required]  
  2. [Display(Name="First Name:")]  
  3. public string Fname { get; set; }  
  4. [Required]  
  5. [Display(Name="Last Name:")]  
  6. public string Lname { get; set; }  
  7. public int Insert(string _fname, string _lname)  
  8. {  
  9.     SqlConnection cn = new SqlConnection(@"Data Source=NARESHIT-PC\DOTNET116;User Id=sa;Password=123;DataBase=DEMODB.MDF");  
  10.     SqlCommand cmd = new SqlCommand("Insert Into Authors(Fname,Lname)Values('"+_fname+"','"+_lname+"')", cn);  
  11.     cn.Open();  
  12.     return cmd.ExecuteNonQuery();  
  13. }  
The above code contains some properties with attributes that are used for validation on our view as well as the InsertModel contains Insert method for inserting values in database. Now our InsertModel is ready, so you can add two methods for adding the record in the database add one ADD method with [HttpGet] and a second ADD method with [HttpPost] attributes. These attributes are all of you known. So create two add methods like below.
  1. [HttpGet]  
  2. public ActionResult Add()  
  3. {  
  4.     return View();  
  5. }  
  6. [HttpPost]  
  7. public ActionResult Add(SelectInsertUpdateDelete.Models.InsertModel insertmodel)  
  8. {  
  9.     if (ModelState.IsValid)  
  10.     {  
  11.         int _records = insertmodel.Insert(insertmodel.Fname, insertmodel.Lname);  
  12.         if (_records>0)  
  13.         {  
  14.             return RedirectToAction("Index", "Home");  
  15.         }  
  16.         else  
  17.         {  
  18.             ModelState.AddModelError("", "Can Not Insert");  
  19.         }  
  20.     }  
  21.     return View(insertmodel);  
  22. }  
In the above code, first one add method simply returns the view to take some input from the user and the second add method gets the values from the view and validates it and inserts these values into the database by calling the Insert method of InserModel.

Step 5 : Still now we have only finished Select and Insert operations; now we will see Edit and update. In step 3 we have added one edit link bind with AuthorId. On the basis of this AuthorId we will Edit the record and update the record in the database. As in previous steps we need one more Model class so add a Model class called UpdateModel and write the code like below.

  1. public int AuthorId { get; set; }  
  2. [Required]  
  3. [Display(Name = "First Name:")]  
  4. public string Fname { get; set; }  
  5. [Required]  
  6. [Display(Name = "Last Name:")]  
  7. public string Lname { get; set; }  
  8. public int Update(string _fname, string _lname,int _authorid)  
  9. {  
  10.     SqlConnection cn = new SqlConnection(@"Data Source=NARESHIT-PC\DOTNET116;User Id=sa;Password=123;DataBase=DEMODB.MDF");  
  11.     SqlCommand cmd = new SqlCommand("Update Authors Set Fname='"+_fname+"',Lname='"+_lname+"' Where AuthorId="+_authorid, cn);  
  12.     cn.Open();  
  13.     return cmd.ExecuteNonQuery();  
  14. }  

In the above code we have properties and one Update method to update the record in the database. In all ModelClasses you can see I'm using ADO.Net only.

Now we have our model class ready so we can add some methods to perform Edit and update operations in our Home Controller so add two more methods called Edit for editing and updating the records like below.
  1. [HttpGet]  
  2. public ActionResult Edit( int id,SelectInsertUpdateDelete.Models.UpdateModel updatemodel)  
  3. {  
  4.     SqlConnection cn = new SqlConnection(@"Data Source=NARESHIT-PC\DOTNET116;User Id=sa;Password=123;DataBase=DEMODB.MDF");  
  5.     SqlCommand cmd = new SqlCommand("Select AuthorId,Fname,Lname From Authors Where AuthorId=" + id, cn);  
  6.     cn.Open();  
  7.     SqlDataReader dr = cmd.ExecuteReader();  
  8.     if (dr.Read())  
  9.     {  
  10.         updatemodel.AuthorId = Convert.ToInt32(dr["AuthorId"].ToString());  
  11.         updatemodel.Fname = dr["Fname"].ToString();  
  12.         updatemodel.Lname = dr["Lname"].ToString();  
  13.     }  
  14.     else  
  15.     {  
  16.         dr.Close();  
  17.     }  
  18.     dr.Close();  
  19.     cn.Close();  
  20.     return View(updatemodel);  
  21. }  
  22. [HttpPost]  
  23. public ActionResult Edit(SelectInsertUpdateDelete.Models.UpdateModel updatemodel,FormCollection form,int id)  
  24. {  
  25.     if (ModelState.IsValid)  
  26.     {  
  27.         int _records = updatemodel.Update(updatemodel.Fname, updatemodel.Lname,id);  
  28.         if (_records > 0)  
  29.         {  
  30.             return RedirectToAction("Index", "Home");  
  31.         }  
  32.         {  
  33.             ModelState.AddModelError("", "Can Not Update");  
  34.         }  
  35.     }  
  36.     return View(updatemodel);  
  37. }  
In the above code you can see first the edit method performs some logic to call the specified id record from the database and display this record on the view and the second Edit method performs the update operations.

Step 6 : Now our last operation still remains, i.e. delete; so to delete, add one more model called DeleteModel and write the following code which contains only a delete method to delete the record of the specified AuthorId from the database.

  1. public int Delete(int id)  
  2. {  
  3.     SqlConnection cn = new SqlConnection(@"Data Source=NARESHIT-PC\DOTNET116;User Id=sa;Password=123;DataBase=DEMODB.MDF");  
  4.     SqlCommand cmd = new SqlCommand("Delete From Authors Where AuthorId=" + id, cn);  
  5.     cn.Open();  
  6.     return cmd.ExecuteNonQuery();  
  7. }  
In Step 3 we have added one ActionLink with delete and given the authorid to it for deleting the specified authorid record from the database. So now we can add one Delete Method in our Home Controller, so add it and write the following code to call the delete method of the DeleteModel to delete the record.

  1. [HttpGet]  
  2. public ActionResult Delete(int id,SelectInsertUpdateDelete.Models.DeleteModel deletemodel)  
  3. {  
  4.     int records = deletemodel.Delete(id);  
  5.     if (records>0)  
  6.     {  
  7.         return RedirectToAction("Index", "Home");  
  8.     }  
  9.     else  
  10.     {  
  11.         ModelState.AddModelError("", "Can Not Delete");  
  12.         return View("Index");  
  13.     }  
  14. }  
Now we have completed all our operations. Now you can run the application and can perform all the Select, Insert, Update and Delete operations.

Conclusion: In this article, we have seen how to add, edit, update and delete the records using ADO.NET in ASP.Net MVC. I hope you enjoyed this article.


Similar Articles