MVC Data Access and Filter using Entity Framework and HTML Helpers

This tutorial is in continuation to MVC Data Access using Entity Framework and HTML Helpers. Please read it at technewsinform OR debugsolutions OR C-sharpcorner before proceeding with this tutorial.
Description – In this tutorial, we will learn how to use multiple table and their relationship to fetch data from the database. Also we will learn something more about HTML Helpers.
Download Previous Source code here..
Download new Source Code here..
Objective – We want a list of Student’s standards in which they are studying. When the initial page loads, it should show the Standards available in database table. When a standard is clicked, it should only show those students who belongs to that standard and when the name of student is clicked, it should show the details of that student. In addition to this, we have 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 Models folder of your project and name it as 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 to StudentsContext.cs file already available in models folder of your 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 Controllers folder and Name it as 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 codes 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 Route.config file present in App_Start folder.
Step 9: Make sure that you solution explorer has all the below mentioned 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 above option, you will be redirected to Index page of Students Controller which will show the list of students filtered on the basis of ID of above selected option.
When you click on any student name, it should show the details of Student. If you select Back option, you will be redirected to first page.
When you click Back button in preceding image, you will go back to Students List.
So, finally we completed our objective.

See this article at technewsinform
Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s