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

For the demo we will be using the following two 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...".



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.



Select an Empty template and choose View engine as Razor.

Click Ok.



Currently our project will look like this.



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.



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 the Model Contents as EF Designer from the database and click Next.



Click on the New Connection button.



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



You will see the auto-generated connection strings.

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



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



StudenntDataModel will be generated.



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.



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.



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.



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



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



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.



If we insert a new record:



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



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.



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

namespace StudentAdoModel.Models
{
   using System;
   using System.Collections.Generic;

   public partial class Student
   {
      public int StudentId { get; set; }
      public string StudentName { get; set; }
      public string StudentGender { get; set; }
      public Nullable<int> Course_Id { get; set; }

      public virtual Course Course { get; set; }
   }
}

Course.cs

namespace StudentAdoModel.Models
{
   using System;
   using System.Collections.Generic;

   public partial class Course
   {
      public Course()
      {
         this.Students = new HashSet<Student>();
      }

      public int CourseId { get; set; }
      public string CourseName { get; set; }

      public virtual ICollection<Student> Students { get; set; }
   }
}

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.



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.



So, we have added a new class.

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



Add another class “StudentMetaData” in the same cs file and add the following properties.

class StudentCustomData {
   [Required]
   public string StudentName { get; set; }
   [Required]
   public string StudentGender { get; set; }

   [Required]
   public Nullable<int> Course_Id { get; set; }
}

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.

[MetadataType(typeof(StudentCustomData))]
public partial class Student {

}

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

Build and run the application.

Click the Create button without passing any data.



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.

[Required(ErrorMessage="The Course field is required")]

public Nullable<int> Course_Id { get; set; }

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.

<div class="editor-field">
   @Html.EditorFor(model => model.StudentGender)
</div>

Replacing it with a dropdownlist.


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.

<div class="editor-field">
@Html.DropDownList("Gender", new List<SelectListItem>{
   new SelectListItem{Text = "Male", Value="Male"},
   new SelectListItem{Text = "Female", Value="Female"}
})

Run the application.



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.

<div class="editor-field">
   @Html.DropDownListFor(model => model.StudentGender, new List<SelectListItem> {
      new SelectListItem{Text = "Male", Value="Male"},
      new SelectListItem{Text = "Female", Value="Female"}
   }, "Select Gender")

For the Course dropdownlist.

@Html.DropDownList("Course_Id","Select Course")

Run the application.



So, we have customized our Create.cshtml view.

Step 7

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



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:

<div class="editor-field">
   @Html.EditorFor(model => model.StudentName)
</div>

With the following:

@Html.DisplayFor(model => model.StudentName)
Gender

Replace the following:

<div class="editor-field">
   @Html.EditorFor(model => model.StudentGender)
</div>

With this.

@Html.DropDownListFor(model => model.StudentGender, new List<SelectListItem> {
   new SelectListItem{Text = "Male", Value="Male"},
   new SelectListItem{Text = "Female", Value="Female"}
}, "Select Gender")

Run the application.



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.



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.

<div class="editor-field">
   @Html.DisplayFor(model => model.StudentName)
   @Html.HiddenFor(model => model.StudentName)

   @Html.ValidationMessageFor(model => model.StudentName)
</div>

Run the application.



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.



Click the Edit link.

The edit request is generated in Fiddler.



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



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



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



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.



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.

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Edit(Student student) {
   //retrieve record of the current student from the database
   Student studentFromDB = db.Students.Single(x => x.StudentId == student.StudentId);

   //harvest the values from the student object in the StudentFromDB object manually
   studentFromDB.StudentGender = student.StudentGender;
   studentFromDB.Course_Id = student.Course_Id;
   //retrieve the StudentName from the database and assign it to the student object StudentName
   student.StudentName = studentFromDB.StudentName;

   if(ModelState.IsValid) {
      db.Entry(studentFromDB).State = EntityState.Modified;// pass student entity as StudentFromDB
      db.SaveChanges();
      return RedirectToAction("Index");
   }
   ViewBag.Course_Id = new SelectList(db.Courses, "CourseId", "CourseName", student.Course_Id);
   return View(student);
}

Run the application and do the same step again using Fiddler.

Change the name from LOL to James.



Click Execute.

Re-run the MVC application.

You will see the student name is still LOL.



Step 9

Let's see how to delete a record.

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



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.