MVC Data Access and Filter Using Entity Framework and HTML Helpers - Part 2

Before reading this article, I highly recommend reading my previous part:

• MVC Data Access Using Entity Framework and HTML Helpers

Download the source code here.

Objective

We want a list of the student's standards in which they are studying. When the initial page loads, it should show the standards available in the database table. When a standard is clicked, it should only show those students belonging to that standard and when the name of the student is clicked, it should show the details of that student. In addition to this, we need to also provide links on each page to perform back action.

Step 1

Open the previous created project. In case you don't have that project, just download it from the links provided above or create a new ASP.NET web application with MVC.

Step 2

Open SQL Server Management Studio and execute the following script.

CREATE DATABASE MVC;
USE MVC;
CREATE TABLE dbo.Students( ID         INT,
                           Name       VARCHAR(50),
                           Gender     VARCHAR(6),
                           Fees       INT,
                           standardId INT );
INSERT INTO dbo.Students
VALUES( 1, 'Harry', 'Male', 2500, 9 );
INSERT INTO dbo.Students
VALUES( 2, 'Jane', 'Female', 2400, 9 );
INSERT INTO dbo.Students
VALUES( 3, 'Emma', 'Female', 2100, 9 );
INSERT INTO dbo.Students
VALUES( 4, 'Roster', 'Male', 2500, 9 );
INSERT INTO dbo.Students
VALUES( 5, 'Chris', 'Male', 2900, 10 );
INSERT INTO dbo.Students
VALUES( 6, 'Evan', 'Male', 2200, 10 );
INSERT INTO dbo.Students
VALUES( 7, 'Cathlie', 'Female', 2550, 10 );
INSERT INTO dbo.Students
VALUES( 8, 'Jack', 'Male', 2500, 10 );
INSERT INTO dbo.Students
VALUES( 9, 'Jone', 'Male', 2900, 11 );
INSERT INTO dbo.Students
VALUES( 10, 'Videra', 'Female', 2550, 11 );
INSERT INTO dbo.Students
VALUES( 11, 'Sara', 'Female', 2900, 11 );
INSERT INTO dbo.Students
VALUES( 12, 'Mak', 'Male', 2500, 11 );
INSERT INTO dbo.Students
VALUES( 13, 'Max', 'Male', 2550, 12 );
INSERT INTO dbo.Students
VALUES( 14, 'Brock', 'Male', 2900, 12 );
INSERT INTO dbo.Students
VALUES( 15, 'Eddie', 'Male', 2500, 12 );
INSERT INTO dbo.Students
VALUES( 16, 'Edna', 'Female', 2500, 12 );
SELECT Students.ID,
       Students.Name,
       Students.Gender,
       Students.Fees,
       Students.standardId
FROM dbo.Students;
CREATE TABLE dbo.Standard( Id   INT,
                           Info VARCHAR(20));
INSERT INTO dbo.Standard
VALUES( 9, 'Ninth' );
INSERT INTO dbo.Standard
VALUES( 10, 'Tenth' );
INSERT INTO dbo.Standard
VALUES( 11, 'Eleventh' );
INSERT INTO dbo.Standard
VALUES( 12, 'Twelth' );
SELECT *
FROM Students;
SELECT *
FROM Standard;

Our database tables will look like this:



Step 3

Add a class file to the Models folder of your project and name it Standard.cs and add the following code to it.

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;

namespace MVCDataAccessByEntityFrame.Models
{
    [Table("Standard")]
    public class Standard
    {
        public int Id { get; set; }
        public string Info { get; set; }
        public List<Students> Students { get; set; }
    }
}

Step 4

Replace with the following code for the StudentsContext.cs file already available in the models folder of the project.

Using System.Data.Entity;
namespace MVCDataAccessByEntityFrame.Models
{
    public class StudentsContext : DbContext
    {
        public DbSet<Students> Students { get; set; }
        public DbSet<Standard> Standard { get; set; }
    }
}

Step 5

Add a controller to the Controllers folder and name it StandardController.cs and add the following code to it.

using MVCDataAccessByEntityFrame.Models;
using System.Collections.Generic;
using System.Linq;
using System.Web.Mvc;

namespace MVCDataAccessByEntityFrame.Controllers
{
    public class StandardController : Controller
    {
        public ActionResult Index()
        {
            StudentsContext studentsContext = new StudentsContext();
            List<Standard> standard = new List<Standard>();
            standard = studentsContext.Standard.ToList();
            return View(standard);
        }

    }
}

Step 6

Add a view to the index method of the preceding controller and replace with the following code.

@using MVCDataAccessByEntityFrame.Models;
@model IEnumerable<Standard>

@{
    ViewBag.Title = "Student Standard";
}

<h2>Student Standard List</h2>
<ul>
    @foreach (Standard standard in @Model)
    {
        <li>
            @Html.ActionLink(standard.Info, "Index", "Students", new { standardId = standard.Id }, null)
        </li>
    }

</ul>

Step 7

Replace the code of the following files with their respective code mentioned below.

Students.cs

using System.ComponentModel.DataAnnotations.Schema;

namespace MVCDataAccessByEntityFrame.Models
{
    [Table("Students")]
    public class Students
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public string Gender { get; set; }
        public int Fees { get; set; }

        public int standardId { get; set; }
    }
}

StudentController.cs

using MVCDataAccessByEntityFrame.Models;
using System.Collections.Generic;
using System.Linq;
using System.Web.Mvc;

namespace MVCDataAccessByEntityFrame.Controllers
{
    public class StudentsController : Controller
    {
        public ActionResult Index(int standardId)
        {
            StudentsContext studentsContext = new StudentsContext();
            List<Students> students = studentsContext.Students.Where(std => std.standardId == standardId).ToList();
            return View(students);
        }
        public ActionResult Details(int id)
        {
            StudentsContext studentsContext = new StudentsContext();
            Students students = studentsContext.Students.Single(stu => stu.ID == id);
            return View(students);
        }

    }
}

Students/Index.cshtml

@model IEnumerable<MVCDataAccessByEntityFrame.Models.Students>
@using MVCDataAccessByEntityFrame.Models;
@{
    ViewBag.Title = "Students List";
}

<h2>Students List</h2>
<ol start="1">
    @foreach (Students students in @Model)
    {
        <li id="item">
            @Html.ActionLink(students.Name, "Details", new { id = students.ID })
        </li>
    }
</ol>
@Html.ActionLink("Back to Standard List", "Index","Standard")

Students/Details.cshtml

@model MVCDataAccessByEntityFrame.Models.Students

@{
    ViewBag.Title = "Students Details";
}

<table border="1">
    <tr>
        <td><b>ID:</b></td>
        <td>
            @Model.ID
        </td>
    </tr>
    <tr>
        <td><b>Name:</b></td>
        <td>@Model.Name</td>
    </tr>
    <tr>
        <td><b>Gender:</b></td>
        <td>@Model.Gender</td>
    </tr>
    <tr>
        <td><b>Fees:</b></td>
        <td>@Model.Fees</td>
    </tr>
</table>
<br />
@Html.ActionLink("Back to Students List", "Index", new { standardId = @Model.standardId })

Step 8

Make the following changes to the Route.config file present in the App_Start folder.



Step 9

Ensure that the Solution Explorer has all the following folders and files.



Step 10

Finally, run the project by pressing F5 and you will see the following.



When you click on any one of the preceding options you will be redirected to the Index page of the Students Controller that will show the list of students filtered on the basis of the ID of the preceding selected option.



When you click on a student name, it should show the details of the student. If you select the Back option, you will be redirected to the first page.



When you click the Back button in the preceding image, you will return to the Students List.

So, finally we have completed our objective.

See this article in my other blogs.