How To Select Insert, Update And Delete With ASP.NET MVC

How To Select Insert, Update And Delete With ASP.NET MVC

CheapASPNETHostingReview.com | Best and cheap ASP.NET MVC hosting. 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.

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.

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.

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.

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.

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.

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

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.

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

In Step3 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.

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.