Post
Ask Question

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

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

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.

  1. CREATE DATABASE MVC;  
  2. USE MVC;  
  3. CREATE TABLE dbo.Students( ID         INT,  
  4.                            Name       VARCHAR(50),  
  5.                            Gender     VARCHAR(6),  
  6.                            Fees       INT,  
  7.                            standardId INT );  
  8. INSERT INTO dbo.Students  
  9. VALUES( 1, 'Harry', 'Male', 2500, 9 );  
  10. INSERT INTO dbo.Students  
  11. VALUES( 2, 'Jane', 'Female', 2400, 9 );  
  12. INSERT INTO dbo.Students  
  13. VALUES( 3, 'Emma', 'Female', 2100, 9 );  
  14. INSERT INTO dbo.Students  
  15. VALUES( 4, 'Roster', 'Male', 2500, 9 );  
  16. INSERT INTO dbo.Students  
  17. VALUES( 5, 'Chris', 'Male', 2900, 10 );  
  18. INSERT INTO dbo.Students  
  19. VALUES( 6, 'Evan', 'Male', 2200, 10 );  
  20. INSERT INTO dbo.Students  
  21. VALUES( 7, 'Cathlie', 'Female', 2550, 10 );  
  22. INSERT INTO dbo.Students  
  23. VALUES( 8, 'Jack', 'Male', 2500, 10 );  
  24. INSERT INTO dbo.Students  
  25. VALUES( 9, 'Jone', 'Male', 2900, 11 );  
  26. INSERT INTO dbo.Students  
  27. VALUES( 10, 'Videra', 'Female', 2550, 11 );  
  28. INSERT INTO dbo.Students  
  29. VALUES( 11, 'Sara', 'Female', 2900, 11 );  
  30. INSERT INTO dbo.Students  
  31. VALUES( 12, 'Mak', 'Male', 2500, 11 );  
  32. INSERT INTO dbo.Students  
  33. VALUES( 13, 'Max', 'Male', 2550, 12 );  
  34. INSERT INTO dbo.Students  
  35. VALUES( 14, 'Brock', 'Male', 2900, 12 );  
  36. INSERT INTO dbo.Students  
  37. VALUES( 15, 'Eddie', 'Male', 2500, 12 );  
  38. INSERT INTO dbo.Students  
  39. VALUES( 16, 'Edna', 'Female', 2500, 12 );  
  40. SELECT Students.ID,  
  41.        Students.Name,  
  42.        Students.Gender,  
  43.        Students.Fees,  
  44.        Students.standardId  
  45. FROM dbo.Students;  
  46. CREATE TABLE dbo.Standard( Id   INT,  
  47.                            Info VARCHAR(20));  
  48. INSERT INTO dbo.Standard  
  49. VALUES( 9, 'Ninth' );  
  50. INSERT INTO dbo.Standard  
  51. VALUES( 10, 'Tenth' );  
  52. INSERT INTO dbo.Standard  
  53. VALUES( 11, 'Eleventh' );  
  54. INSERT INTO dbo.Standard  
  55. VALUES( 12, 'Twelth' );  
  56. SELECT *  
  57. FROM Students;  
  58. SELECT *  
  59. FROM Standard;  
Our database tables will look like this:

query output

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.

add class
  1. using System.Collections.Generic;  
  2. using System.ComponentModel.DataAnnotations.Schema;  
  3.   
  4. namespace MVCDataAccessByEntityFrame.Models  
  5. {  
  6.     [Table("Standard")]  
  7.     public class Standard  
  8.     {  
  9.         public int Id { get; set; }  
  10.         public string Info { get; set; }  
  11.         public List<Students> Students { get; set; }  
  12.     }  
  13. }  
Step 4

Replace with the following code for the StudentsContext.cs file already available in the models folder of the project.
  1. Using System.Data.Entity;  
  2. namespace MVCDataAccessByEntityFrame.Models  
  3. {  
  4.     public class StudentsContext : DbContext  
  5.     {  
  6.         public DbSet<Students> Students { get; set; }  
  7.         public DbSet<Standard> Standard { get; set; }  
  8.     }  
  9. }  
Step 5

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

add controller
  1. using MVCDataAccessByEntityFrame.Models;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web.Mvc;  
  5.   
  6. namespace MVCDataAccessByEntityFrame.Controllers  
  7. {  
  8.     public class StandardController : Controller  
  9.     {  
  10.         public ActionResult Index()  
  11.         {  
  12.             StudentsContext studentsContext = new StudentsContext();  
  13.             List<Standard> standard = new List<Standard>();  
  14.             standard = studentsContext.Standard.ToList();  
  15.             return View(standard);  
  16.         }  
  17.   
  18.     }  
  19. }  
Step 6

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

add view page

add view
  1. @using MVCDataAccessByEntityFrame.Models;  
  2. @model IEnumerable<Standard>  
  3.   
  4. @{  
  5.     ViewBag.Title = "Student Standard";  
  6. }  
  7.   
  8. <h2>Student Standard List</h2>  
  9. <ul>  
  10.     @foreach (Standard standard in @Model)  
  11.     {  
  12.         <li>  
  13.             @Html.ActionLink(standard.Info, "Index", "Students", new { standardId = standard.Id }, null)  
  14.         </li>  
  15.     }  
  16.   
  17. </ul>  
Step 7

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

Students.cs
  1. using System.ComponentModel.DataAnnotations.Schema;  
  2.   
  3. namespace MVCDataAccessByEntityFrame.Models  
  4. {  
  5.     [Table("Students")]  
  6.     public class Students  
  7.     {  
  8.         public int ID { get; set; }  
  9.         public string Name { get; set; }  
  10.         public string Gender { get; set; }  
  11.         public int Fees { get; set; }  
  12.   
  13.         public int standardId { get; set; }  
  14.     }  
  15. }  
StudentController.cs
  1. using MVCDataAccessByEntityFrame.Models;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web.Mvc;  
  5.   
  6. namespace MVCDataAccessByEntityFrame.Controllers  
  7. {  
  8.     public class StudentsController : Controller  
  9.     {  
  10.         public ActionResult Index(int standardId)  
  11.         {  
  12.             StudentsContext studentsContext = new StudentsContext();  
  13.             List<Students> students = studentsContext.Students.Where(std => std.standardId == standardId).ToList();  
  14.             return View(students);  
  15.         }  
  16.         public ActionResult Details(int id)  
  17.         {  
  18.             StudentsContext studentsContext = new StudentsContext();  
  19.             Students students = studentsContext.Students.Single(stu => stu.ID == id);  
  20.             return View(students);  
  21.         }  
  22.   
  23.     }  
  24. }  
Students/Index.cshtml
  1. @model IEnumerable<MVCDataAccessByEntityFrame.Models.Students>  
  2. @using MVCDataAccessByEntityFrame.Models;  
  3. @{  
  4.     ViewBag.Title = "Students List";  
  5. }  
  6.   
  7. <h2>Students List</h2>  
  8. <ol start="1">  
  9.     @foreach (Students students in @Model)  
  10.     {  
  11.         <li id="item">  
  12.             @Html.ActionLink(students.Name, "Details", new { id = students.ID })  
  13.         </li>  
  14.     }  
  15. </ol>  
  16. @Html.ActionLink("Back to Standard List", "Index","Standard")  
Students/Details.cshtml
  1. @model MVCDataAccessByEntityFrame.Models.Students  
  2.   
  3. @{  
  4.     ViewBag.Title = "Students Details";  
  5. }  
  6.   
  7. <table border="1">  
  8.     <tr>  
  9.         <td><b>ID:</b></td>  
  10.         <td>  
  11.             @Model.ID  
  12.         </td>  
  13.     </tr>  
  14.     <tr>  
  15.         <td><b>Name:</b></td>  
  16.         <td>@Model.Name</td>  
  17.     </tr>  
  18.     <tr>  
  19.         <td><b>Gender:</b></td>  
  20.         <td>@Model.Gender</td>  
  21.     </tr>  
  22.     <tr>  
  23.         <td><b>Fees:</b></td>  
  24.         <td>@Model.Fees</td>  
  25.     </tr>  
  26. </table>  
  27. <br />  
  28. @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.

code

Step 9

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

solution explorer

Step 10

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

output

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.

student list

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.

list

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.

 


Similar Articles