Database First Approach in MVC 5: Part 3

This article describes the way to modify the database and modify the application and data annotation in Visual Studio 2013.

Introduction

In this part I will make some changes in my database and enhance my web application to interact with the database. This is the third part of the Database First Approach in MVC 5 series that follows Part 1 and Part 2.

In that context, I have divided this article into the following 3 sections:

  • Database and Application Modification
  • Data Annotation
  • View Enhancement

Database and Application Modification

In this section we will add another field in the database table and generate the field in the web application. To generate the added field in the web application, we need to do some changes in our web application. We will see it later. So, let's start with the following procedure.

Step 1: Open the New Query wizard from your Data Connection in the Server Explorer.

New Query

Step 2: Enter the following command: 

ALTER TABLE Cricketers

ADD Grade varchar(5)

Step 3: Execute the command and check out the changes in the table.

Cricketer Model

Now, you have added the new field in the database. This new field will not be present in your model in the application, you must improve your model for this new added field. Use the following procedure for that.

Step 4: Select your Cricketers Model from the CricketerModel.edmx file. Now, right-click on your workspace and select "Update Model from Database".

Update Model From Database

You can see that the Grade field is not present.

Step 5: In the next wizard, click on the Refresh Tab and select your table.

Update Wizard

Step 6: The new field added in the CricketerModel.

Cricketer Model

Step 4: Save the file and build your solution.

You have successfully updated your field in the model, but this new field is not present in the corresponding view. There are two options for updating the view; either Re-Scaffolding or Manually adding the property in the view. I am here using Re-Scaffolding.

Step 5: Remove the Cricketer from the View.

Delete View

Step 6: Add Scaffold again from the Controller folder named CricketerController. Click OK for replacing the existing file.

Re-Scaffolding

Visual Studio automatically replaces the existing file with the new CricketerController. It also creates the Cricketer folder in the View folder.

Step 7: Open the Index.cshtml file in the browser. Click on "Create New" to enter details.

Create Cricketer

Data Annotation

In this section we apply the validations rules using Data Annotation. This is to be applied to the specific property. Use the following procedure.

Step 1: Open the Cricketer.cs file from the Model folder in Solution Explorer and modify your code with the following code:

namespace CricketerApplication.Models

{

    using System;

    using System.Collections.Generic;

    using System.ComponentModel.DataAnnotations;

   

    public partial class Cricketer

    {

        public Cricketer()

        {

            this.Cricketer_Details = new HashSet<Cricketer_Details>();

            this.Cricketer_ODI_Statistics = new HashSet<Cricketer_ODI_Statistics>();

            this.Cricketer_Test_Statistics = new HashSet<Cricketer_Test_Statistics>();

        }

   

        public int ID { get; set; }

 

        [Required]

        [StringLength(50, MinimumLength = 4)]

        public string Name { get; set; }

 

        [Required]

        [Range(1, 500)]

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

 

        [Required]

        [Range(1, 500)]

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

 

        [Required]

        public string Grade { get; set; }

   

        public virtual ICollection<Cricketer_Details> Cricketer_Details { get; set; }

        public virtual ICollection<Cricketer_ODI_Statistics> Cricketer_ODI_Statistics { get; set; }

        public virtual ICollection<Cricketer_Test_Statistics> Cricketer_Test_Statistics { get; set; }

    }

}

Step 2: Build your solution. Browse the page and see the validation as shown below:

Validation

View Enhancement

We are watching the view that was created automatically by Visual Studio. Now let's make some changes to enhance the view depending on our condition. Suppose we want to see the Cricketer Details in the Cricketer page, then we need to do some changes in our application. Start with the following procedure.

Step 1: Open the Cricketer/Details.cshtml file. Add the following code before the </fieldset> and below the </dl> tag:

<table class="table">

   <tr>

       <th>Team</th>

       <th>Odi Runs</th>

       <th>Test Runs</th>

       <th>Wickets</th>

   </tr>

 

   @foreach (var item in Model.Cricketer_Details)

   {

       <tr>

           <td>

               @Html.DisplayFor(ModelItem => item.Team)

           </td>

           <td>

               @Html.DisplayFor(ModelItem => item.ODI_Runs)

           </td>

           <td>

               @Html.DisplayFor(ModelItem => item.Test_Runs)

           </td>

           <td>

               @Html.DisplayFor(ModelItem => item.Wickets)

           </td>

       </tr>

   }

</table>

Step 2: Build the solution and Open the Index.cshtml file in the browser and click on the Details link of any Cricketer.

Cricketer Details

Click on Details.

Details

Summary

This part covered all the important sections like Data Annotation and Database Modification. In addition my Database First Approach parts provide you generated code from an existing database and perform the CRUD operations.