Retrieve, Insert, Update & Delete in MVC Using ADO.NET

For the demo we will be using the following two tables.

tables

Step 1

The first step is to create a new MVC4 application and for that open Visual Studio then click on "File" -> "New" -> "Project...".

project

Expand the Installed node then the Templates node then the Visual C# node then then the Web node then select Visual Studio then choose ASP.NET MVC 4 Web Application for the project then provide a meaningful name then click OK.

Web Application

Select an Empty template and choose View engine as Razor.

Click Ok.

Razor

Currently our project will look like this.

our project

Step 2

The next step is to add an ADO.NET Entity Data Model.

Right-click on the Models folder then seelct Add then select New Item.

Select new item

Expand Installed then Visual C# then select Data then choose ADO.NET Entity Data Model item then provide a meaningful name then click Add.

choose ADO

Choose the Model Contents as EF Designer from the database and click Next.

database

Click on the New Connection button.

Connection

Specify the server name then select the authentication mode then choose the database then click Ok.

choose the database

You will see the auto-generated connection strings.

Save the connection settings with a name of your choice and click Next.

connection settings

Choose the database objects or tables that you want in your model, provide a meaningful model namespace and click Finish.

Choose the database objects

StudenntDataModel will be generated.

StudenntDataModel

Step 3

Look at the name of the entities in the image above. We don't want our entities to be called tblCourse and tblStudent. So, let's replace tblCourse with Course, tblStudent with Student, tblStudents with Students and tblCourse with Course.

Select the entity name and press F2 to edit the entity name.

Select the entity

Rename all the entities.

Rename all the entities

Build the application.

We have created our Model.

Step 4

The next step is to add a controller and for that right-click on the Controllers folder then select Add then select Controller.

Controller

Provide the controller the name “HomeController”.

Select the template “MVC Controller with read/write actions and views using Entity Framework

Select Student as the model class.

Select StudentContext as the context class.

Click Add.

Click Add

Once you click add, you will see that many files have been added. Like HomeController, Index, Details, Create, Edit and Delete.cshtml files.

Delete

For now just run the application and by default the Index action method will be invoked that will display the Index.shtml file.

Index

So, how did get that output?

Using ADO.NET Entity Data Model. It does all the hard work for us.

When we retrieve the list of records, by default the List scaffold template is selected.

When we want to create and add a new record, the Create scaffold template is selected and so on.

So, if we click on the create link, we will get this layout and this layout is nothing but a Create scaffold template.

Create scaffold

If we insert a new record:

insert a new record

Click Create, you will be redirected to Index.cshtml and you will see Michael's record.

redirected to Index

So, our application is working fine.

But there are some flaws too. Let's see what happens if we don't insert any data into the fields and click the create button.

A blank record will be added.

record

But we don't want that to happen.

So, let's see how to solve this problem.

Step 5

In the Models folder all the files are auto-generated and out of those files there are two class files, Course.cs and Student.cs that contains all the properties.

Student

Student.cs

  1. namespace StudentAdoModel.Models  
  2. {  
  3.    using System;  
  4.    using System.Collections.Generic;  
  5.   
  6.    public partial class Student  
  7.    {  
  8.       public int StudentId { getset; }  
  9.       public string StudentName { getset; }  
  10.       public string StudentGender { getset; }  
  11.       public Nullable<int> Course_Id { getset; }  
  12.   
  13.       public virtual Course Course { getset; }  
  14.    }  
  15. }  
Course.cs
  1. namespace StudentAdoModel.Models  
  2. {  
  3.    using System;  
  4.    using System.Collections.Generic;  
  5.   
  6.    public partial class Course  
  7.    {  
  8.       public Course()  
  9.       {  
  10.          this.Students = new HashSet<Student>();  
  11.       }  
  12.   
  13.       public int CourseId { getset; }  
  14.       public string CourseName { getset; }  
  15.   
  16.       public virtual ICollection<Student> Students { getset; }  
  17.    }  
  18. }  
In the preceding two classes, you will see that none of the fields are marked Required. So, to prevent the fields from having blank records we can make all the properties required.

Since this is auto-generated code, it will not be a good idea to make any changes here. So, how can we solve this problem?

If you look at the image below, you will see the class Student is a partial class.

partial class

A class divided into multiple files is called a Partial Class.

So, what we can do is, we can add a new class file and in that class file we will add our customization.

customization

So, we have added a new class.

Change the name from StudentCustomization to Student and mark this class as partial.

StudentCustomization

Add another class “StudentMetaData” in the same cs file and add the following properties.
  1. class StudentCustomData {  
  2.    [Required]  
  3.    public string StudentName { getset; }  
  4.    [Required]  
  5.    public string StudentGender { getset; }  
  6.   
  7.    [Required]  
  8.    public Nullable<int> Course_Id { getset; }  
  9. }  
Ensure the property name in the StudentCustomData class must match the property name of the properties present in the auto-generated Student class.

Note

The Required attribute is present in Systrem.ComponentModel.DataAnnotations.

We need to associate this StudentCustomData class with a data model class and the model here is Student.
  1. [MetadataType(typeof(StudentCustomData))]  
  2. public partial class Student {  
  3.   
  4. }  
The MetaDataType attribute is present in Systrem.ComponentModel.DataAnnotations.

Build and run the application.

Click the Create button without passing any data.

Create button

So, now the Model binder is validating the fields.

But look at the validation message. We got for the Course field “The Course_Id field is required” but here we are not assigning any Id, we are assigning Course Name. So, let's see how to change this validation message.

In the constructor of the RequiredAttribute class add a custom error message.
  1. [Required(ErrorMessage="The Course field is required")]  
  2.   
  3. public Nullable<int> Course_Id { getset; }  
Run the application.

Run the application

Step 6

To specify a gender, users need to input their gender. But we want to replace this gender's text field with a dropdownlist and in the Course dropdownlist we want to display an optional label, “Select Course”, over an empty string.

In Create.cshtml the following is generating a TextBox control for the Gender.
  1. <div class="editor-field">  
  2.    @Html.EditorFor(model => model.StudentGender)  
  3. </div>  
Replacing it with a dropdownlist.

Replacing
The first parameter expects a name and id for the control.

The second parameter expects a collection of IEnumerable<SelectListItem>, so here we can create a new List<SelectListItem> object.
  1. <div class="editor-field">  
  2. @Html.DropDownList("Gender"new List<SelectListItem>{  
  3.    new SelectListItem{Text = "Male", Value="Male"},  
  4.    new SelectListItem{Text = "Female", Value="Female"}  
  5. })  
Run the application.

Run

We now have a gender dropdownlist which is good. But let's say we don't want any gender to be displayed first but we want an optional label to be shown, “Select Gender”, and for that we can use another overloaded version of this DropdownList HTML helper that expects an Optional label.

DropdownList
  1. <div class="editor-field">  
  2.    @Html.DropDownListFor(model => model.StudentGender, new List<SelectListItem> {  
  3.       new SelectListItem{Text = "Male", Value="Male"},  
  4.       new SelectListItem{Text = "Female", Value="Female"}  
  5.    }, "Select Gender")  
For the Course dropdownlist.
  1. @Html.DropDownList("Course_Id","Select Course")  
Run the application.

output

So, we have customized our Create.cshtml view.

Step 7

Now let's look at the Edit.cshtml view.

Edit

In the Edit view, we don't want the StudentName to be editable. So, what we will do is to mark the StudentName property field read-only and we will also replace the gender TextBox field with a dropdownlist.

Name

Replace the following:
  1. <div class="editor-field">  
  2.    @Html.EditorFor(model => model.StudentName)  
  3. </div>  
With the following:
  1. @Html.DisplayFor(model => model.StudentName)  
  2. Gender  
Replace the following:
  1. <div class="editor-field">  
  2.    @Html.EditorFor(model => model.StudentGender)  
  3. </div>  
With this.
  1. @Html.DropDownListFor(model => model.StudentGender, new List<SelectListItem> {  
  2.    new SelectListItem{Text = "Male", Value="Male"},  
  3.    new SelectListItem{Text = "Female", Value="Female"}  
  4. }, "Select Gender")  
Run the application.

With this

So, now the StudentName field is read-only and StudentGender is now displayed in a dropdownlist control.

Change the course from PHP to ASP.Net and click Save.

Change the course

We got a validation error in the Name field because this StudentName field is a read-only field but we are not posting the value back to the server and when we click the Save button this field becomes null.

So, to retain the value we can add a hidden field.
  1. <div class="editor-field">  
  2.    @Html.DisplayFor(model => model.StudentName)  
  3.    @Html.HiddenFor(model => model.StudentName)  
  4.   
  5.    @Html.ValidationMessageFor(model => model.StudentName)  
  6. </div>  
Run the application.

ValidationMessageFor

Click Save.

Click Save

So, now we are able to make the required changes.

Now as we know this StudentName field is read-only but even after that, this field can be updated using any third-party tools like Fiddler.

Step 8

Updating StudentName read-only using Fiddler.

Download link.

Open Fiddler and run your MVC application.

Open fiddler

Click the Edit link.

The edit request is generated in Fiddler.

Edit request

Click the save button, you will see a post request will be generated in Fiddler.

generated in fiddler

Drag and drop that generated request in the composer window and change the StudentName from James to LOL.

LOL

Click the Execute button and run the MVC application again and you will see the StudentName James is not changed to LOL.

MVC application

Let's look at the reason for this problem and then we will see how to solve it.

In the HomeController.cs look for the HttpPost Edit action method.

Look at how the Edit post action method is implemented.

Edit post action

In the preceding method, notice that the student object is receiving the posted form values. The default model binder of MVC will harvest all the data and populate the student object with those values and then that data will be stored in the database. But we don't want the Name property to be updated and for that we need to make the following changes.
  1. [HttpPost]  
  2. [ValidateAntiForgeryToken]  
  3. public ActionResult Edit(Student student) {  
  4.    //retrieve record of the current student from the database  
  5.    Student studentFromDB = db.Students.Single(x => x.StudentId == student.StudentId);  
  6.   
  7.    //harvest the values from the student object in the StudentFromDB object manually  
  8.    studentFromDB.StudentGender = student.StudentGender;  
  9.    studentFromDB.Course_Id = student.Course_Id;  
  10.    //retrieve the StudentName from the database and assign it to the student object StudentName  
  11.    student.StudentName = studentFromDB.StudentName;  
  12.   
  13.    if(ModelState.IsValid) {  
  14.       db.Entry(studentFromDB).State = EntityState.Modified;// pass student entity as StudentFromDB  
  15.       db.SaveChanges();  
  16.       return RedirectToAction("Index");  
  17.    }  
  18.    ViewBag.Course_Id = new SelectList(db.Courses, "CourseId""CourseName", student.Course_Id);  
  19.    return View(student);  
  20. }  
Run the application and do the same step again using Fiddler.

Change the name from LOL to James.

Change the name from LOL

Click Execute.

Re-run the MVC application.

You will see the student name is still LOL.

still LOL

Step 9

Let's see how to delete a record.

To delete a record click the Delete link in the Index page.

delete a record click

Click Delete.

Click Delete

So, in this article we saw how to Insert, Update and Delete records and with that we have also seen how to prevent un-intended updates.

There is much customization that can be done. So, keep exploring.

I hope you like it. Thank you.