Select Insert, Update And Delete With ASP.NET MVC

Krishna Garad

7y872k33100Article

MVC TutorialCRUD in Asp.net MVCDelete In Asp.net MVCUpdate in Asp.net MVC

33 Comments

Join the conversation! Your thoughts help the community grow.

kucink garonk8y ago
Why7 the linked isn't downloaded?
kishan mistry10y ago
hey krishna you are providing code that is helpful but its essential for beginner to show how to take model and controller bcoz its more important to define in which controller which file is to place
sreeranga prasad sane11y ago
SelectInsertUpdateDelete.Models.SelectModel selectmodel) -----in index method what is this
- Arun Solanki9y ago
  You can write just as (SelectModel selectmodel)
Santosh Kumar Singh11y ago
@foreach (System.Data.DataRow dr in ViewBag.AuthorList.Rows) Cannot perform runtime binding on a null reference. If any one please give answer soon sir. Thanks & Regards
mohamed raghep12y ago
very good
mahmood ahmad12y ago
Hi Krishna. it is very helpful. can i implement the above stuff in MVC 4?
Mukesh Kumar Tiwari12y ago
nice one...
kamruzzaman polash12y ago
thnaks a lot bro..
prashant mishra12y ago
while using razor plz implement this </tr> @foreach (System.Data.DataRow dr in ViewBag.EmployeeList.Rows) {@dr["ID"].ToString() @dr["FirstName"].ToString() @dr["LastName"].ToString() @dr["ProjectName"].ToString() @Html.ActionLink("Edit", "Edit", new { ID = dr["ID"].ToString() });@Html.ActionLink("Delete", "Delete", new { ID = dr["ID"].ToString() });}@Html.ActionLink("Add New Employee", "Add")
Naresh Jadapalli13y ago
Very nice article for learners.. Thank you.. I am the One :)
Christ X13y ago
pls update the above article for MVC 4.
Christ X13y ago
Hi Krishna. it is very helpful. can i implement the above stuff in MVC 4?
anil babu13y ago
I am taking Razor Please give me the View code,I am writting following code but not working<table> Fname: Lname: <%foreach (System.Data.DataRow dr in ViewBag.AuthorList.Rows) {%> <%= Html.ActionLink("Edit", "Edit", new { id = dr["AuthorId"].ToString() })%> <%= Html.ActionLink("Delete", "Delete", new { id = dr["AuthorId"].ToString()})%> <%=dr["Fname"].ToString()%> <%=dr["Lname"].ToString() %> <%}%> <%= Html.ActionLink("Add New Author", "Add") %>
Santosh Yadav13y ago
nice article indeed...Thankyou
rakesh ranjan13y ago
really nice article.
Julius Garidan13y ago
very helpful, thanks..
abhishek tiwari14y ago
This is really nice article.
Former member14y ago
This is really nice article. while surfing i found one more. http://www.dotnetpools.com/Article/ArticleDetiail/?articleId=50&title=Insert,Update,Delete%20Operation%20By%20Using%20Stored%20Procedure%20In%20MVC3
sunil pathak14y ago
very nice article
ravi14y ago
Hi , I am new to MVC ...I am gettting below error when I click on any button....Can someone plz explain on what needs to be done The view 'Edit' or its master was not found or no view engine supports the searched locations. The following locations were searched: ~/Views/Home/Edit.aspx ~/Views/Home/Edit.ascx ~/Views/Shared/Edit.aspx ~/Views/Shared/Edit.ascx ~/Views/Home/Edit.cshtml ~/Views/Home/Edit.vbhtml ~/Views/Shared/Edit.cshtml ~/Views/Shared/Edit.vbhtml
Yogendra Gupta14y ago
nice Article
Nutan Patel14y ago
Very nice post.....
chandan panda14y ago
Good Article. Thanks but if someone new to MVC like MVC 1.0 then Some parts need to be Modified.
user user14y agoedited
good article
Krishna Garad14y ago
MVC2 does not contains ViewBag instead use ViewData["VariableName"].
Mukesh Rawat14y ago
The name 'ViewBag' does not exist in the current context. I am facing this error. MVC 2 with framework 4.0
dybsland14y ago
You shouldn't use [HttpGet] to delete a record. It is a security issue. It would be better to use the verb [HttpDelete] if possible. See link below: http://www.joe-stevens.com/2010/02/16/creating-a-delete-link-with-mvc-using-post-to-avoid-security-issues/
Dorababu Meka14y ago
I need small help, is it Possible to do the same with grid view control instead of HTML action
Dorababu Meka14y ago
I am getting this error, I am using MVC2 what to do inorder to rectify this error
Adora Krause14y ago
Hi Krishna. As you have written really I have enjoyed this article. Its quite interesting concept.
- Krishna Garad14y ago
  thanks
Abhi Kumar14y ago
Useful article. Good explained. Thanks.

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:

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.

Create Table Authors
(
AuthorId int Identity(1,1)Primary Key,
Fname Varchar(50),
Lname Varchar(50)
)

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.

public DataSet GetAllAuthors()
{
SqlConnection cn = new SqlConnection(@"Data Source=NARESHIT-PC\DOTNET116;User Id=sa;Password=123;DataBase=DEMODB.MDF");
SqlCommand cmd = new SqlCommand("Select AuthorId,Fname,Lname From Authors", cn);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds;
}

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.

public ActionResult Index(SelectInsertUpdateDelete.Models.SelectModel selectmodel)
{
DataSet ds = selectmodel.GetAllAuthors();
ViewBag.AuthorList = ds.Tables[0];
return View();
}

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.

<table>
<tr>
<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>
<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>
</tr>
<%foreach (System.Data.DataRow dr in ViewBag.AuthorList.Rows)
{%>
<tr>
<td>
<%= Html.ActionLink("Edit", "Edit", new { id = dr["AuthorId"].ToString() })%>
<%= Html.ActionLink("Delete", "Delete", new { id = dr["AuthorId"].ToString() })%>
</td>
<td><%=dr["Fname"].ToString()%></td>
<td><%=dr["Lname"].ToString() %></td>
</tr>
<%}%>
</table>
<%= 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.

[Required]
[Display(Name="First Name:")]
public string Fname { get; set; }
[Required]
[Display(Name="Last Name:")]
public string Lname { get; set; }
public int Insert(string _fname, string _lname)
{
SqlConnection cn = new SqlConnection(@"Data Source=NARESHIT-PC\DOTNET116;User Id=sa;Password=123;DataBase=DEMODB.MDF");
SqlCommand cmd = new SqlCommand("Insert Into Authors(Fname,Lname)Values('"+_fname+"','"+_lname+"')", cn);
cn.Open();
return cmd.ExecuteNonQuery();
}

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.

[HttpGet]
public ActionResult Add()
{
return View();
}
[HttpPost]
public ActionResult Add(SelectInsertUpdateDelete.Models.InsertModel insertmodel)
{
if (ModelState.IsValid)
{
int _records = insertmodel.Insert(insertmodel.Fname, insertmodel.Lname);
if (_records>0)
{
return RedirectToAction("Index", "Home");
}
else
{
ModelState.AddModelError("", "Can Not Insert");
}
}
return View(insertmodel);
}

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.

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

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.

[HttpGet]
public ActionResult Edit( int id,SelectInsertUpdateDelete.Models.UpdateModel updatemodel)
{
SqlConnection cn = new SqlConnection(@"Data Source=NARESHIT-PC\DOTNET116;User Id=sa;Password=123;DataBase=DEMODB.MDF");
SqlCommand cmd = new SqlCommand("Select AuthorId,Fname,Lname From Authors Where AuthorId=" + id, cn);
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
updatemodel.AuthorId = Convert.ToInt32(dr["AuthorId"].ToString());
updatemodel.Fname = dr["Fname"].ToString();
updatemodel.Lname = dr["Lname"].ToString();
}
else
{
dr.Close();
}
dr.Close();
cn.Close();
return View(updatemodel);
}
[HttpPost]
public ActionResult Edit(SelectInsertUpdateDelete.Models.UpdateModel updatemodel,FormCollection form,int id)
{
if (ModelState.IsValid)
{
int _records = updatemodel.Update(updatemodel.Fname, updatemodel.Lname,id);
if (_records > 0)
{
return RedirectToAction("Index", "Home");
}
{
ModelState.AddModelError("", "Can Not Update");
}
}
return View(updatemodel);
}

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.

public int Delete(int id)
{
SqlConnection cn = new SqlConnection(@"Data Source=NARESHIT-PC\DOTNET116;User Id=sa;Password=123;DataBase=DEMODB.MDF");
SqlCommand cmd = new SqlCommand("Delete From Authors Where AuthorId=" + id, cn);
cn.Open();
return cmd.ExecuteNonQuery();
}

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.

[HttpGet]
public ActionResult Delete(int id,SelectInsertUpdateDelete.Models.DeleteModel deletemodel)
{
int records = deletemodel.Delete(id);
if (records>0)
{
return RedirectToAction("Index", "Home");
}
else
{
ModelState.AddModelError("", "Can Not Delete");
return View("Index");
}
}

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.