How to Implement Multiple Tables In ASP.NET MVC Application

Introduction

In this article, we’ll discuss how to implement multiple tables in ASP.NET MVC Application. In the previous article, we have done Entity Framework in ASP.NET MVC Application.

Create Table

In the SQL Server database, we have two tables. First, we have a TblDepartment table which has got Departments data and another is A Tblstudent table that has got Student data.

Create table TblDepartment
(
 Id int primary key identity,
 Name nvarchar(50)
)
Insert into TblDepartment values('Computer')
Insert into TblDepartment values('Electronics')
Insert into TblDepartment values('Civil')
Create table TblStudent
(
 StudentId int Primary Key Identity(1,1),
 Name nvarchar(50),
 Gender nvarchar(10),
 City nvarchar(50),
 DepartmentId int
)

Then using These two tables we build an ASP.NET MVC application in which the Departments list page should display The list of all Departments that are available in the TblDepartment table.

Alter table TblStudent
add foreign key (DepartmentId)
references TblDepartment(Id)

Insert into TblStudent values('Mark','Male','London',1)
Insert into TblStudent values('John','Male','Chennai',3)
Insert into TblStudent values('Mary','Female','New York',3)
Insert into TblStudent values('Mike','Male','Sydeny',2)
Insert into TblStudent values('Scott','Male','London',1)
Insert into TblStudent values('Pam','Female','Falls Church',2)
Insert into TblStudent values('Todd','Male','Sydney',1)
Insert into TblStudent values('Ben','Male','New Delhi',2)
Insert into TblStudent values('Sara','Female','London',1)

Department names must be rendered as Hyperlinks, once we click on a Department name Then we should redirect The user to The Student List a page which shows all The Students belonging to The Department upon which we have clicked and Then once we click on a Student name Then we should redirect The user to The Student details page.

Firstly we implement The Department list page. we have These two tables TB department and TblStudent.

Create model class

In visual studio first, let’s go ahead and create ASP.NET project and add a model class in model folder Department.cs

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

namespace MVCDemo.Models
{
    [Table("TblDepartment")]
    public class Department
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public List<Student> Students { get; set; }
    }
}

The Department table has two columns ID and name, Then create two public properties ID and name let’s have The get and set accessors.

The Department can have multiple Students, Then we have another property here, a public list of students, using this property we are going to retrieve The list of Students.

Table mapping

The Department class should be mapped to TblDepartment table using Table attribute and it is present in System.ComponentModel.DataAnnotations.Schema
namespace.

Then add Student Context class in models folder, The important thing is if you see The StudentContext.cs class at The moment it’s only retrieving The DbSet of Students and DbSet of Departments. The Base class Entity Framework is DbContext.

public class StudentContext : DbContext
{
    public DbSet<Department> Departments { get; set; }
    public DbSet<Student> Students { get; set; }
}

Add Controller

The implement Department Controller class and Then right-click on Controller folder add Department Controller.

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

namespace MVCDemo.Controllers
{
  public class DepartmentController : Controller
  {
    public ActionResult Index()
    {
      StudentContext StudentContext = new StudentContext();
      List<Department> Departments = StudentContext.Departments.ToList();
            return View(Departments);
        }
    }
}

The Department Controller class has a default index action method. Then we need to create an instance of StudentContext.cs class Then add The model’s namespace.

The StudentContext.cs class which is going to establish The connection to The database and Then let’s use The Departments property of The StudentContext class to retrieve The list of Departments that are present in TblDepartments table. Then The Department’s list renders to the View.

Add View

Then add a view which can render The list of Departments and after The right click on index action method from The Context menu Then select add view.

The use of razor view engine is we want to create a strongly typed of view and we want to select Department as our model class.

After adding view generates Index.CSHTML Then within The Department’s list and title of The page should be Departments in a list.

@using MVCDemo.Models;

@model IEnumerable<Department>

<div style="font-family:Arial">
@{
    ViewBag.Title = "Departments List";
}

<h2>Departments List</h2>
<ul>
@foreach (Department Department in @Model)
{
    <li>@Html.ActionLink(Department.Name, "Index", "Student", 
    new { DepartmentId = Department.ID }, null)</li>
}
</ul>
</div>

The view we are actually handing over a list of Departments look at this is actually a collection of Departments but if you see The index.cshtml at The moment we are only getting one IEnumerable of Department object.

we need to loop through this list of Departments that this view is going to receive and display The Department names. using HTML you know action link helper to generate The Hyperlinks for Department names

Navigation

following action link you need to specify The link and action name and Then any root values.

after clicking on Hyperlink, The Hyperlink we actually want to invoke redirect The user to the Student list page. In Student Controller we want to redirect The user to Index action.

<li>@Html.ActionLink(Department.Name, "Index", "Student", 
    new { DepartmentId = Department.ID }, null)</li>

here, Department name Hyperlink we want to redirect The user to The index action of The Student Controller. It displays the list of Students belonging to The Department on which we have clicked.

The index action within The Student Controller receives The DepartmentID parameter.

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

namespace MVCDemo.Controllers
{
    public class DepartmentController : Controller
    {
        public ActionResult Index(int DepartmentId)
        {
          StudentContext StudentContext = new StudentContext();
          List<Student> Students = StudentContext.Students.Where(emp => emp.DepartmentId == DepartmentId).ToList();

          return View(Students);
        }
    }
}

Student Context object is going to retrieve all Students but we don’t want all Students, we want to filter The Students based on The DepartmentID using LINQ Query syntax.

Add The Student model class and Then add The properties. It has default get and set accessors with Table attribute.

[Table("TblStudent")]
public class Student
{
    public int StudentId { get; set; }
    public string Name { get; set; }
    public string Gender { get; set; }
    public string City { get; set; }
    public int DepartmentId { get; set; }
}

The Student Controller index action method we want to filter The Students based on The Department to which They belong. After running this and see it works as expected.

Then let’s navigate The Department Controller index action method click on The Computer Department. we get all The Students that belong to The Computer Department going to The index action of The Student Controller.

@Html.ActionLink("Back to Student List", "Index", new { DepartmentId = @Model.DepartmentId })  

After click on Student name Hyperlink, it redirects The details action in The Student Controller and Then display The Student information.

public ActionResult Details(int DepartmentId)
{
    StudentContext StudentContext = new StudentContext();
    Student Students = StudentContext.Students.Single(stud => 
    stud.StudentId == Id);

    return View(Students);
}

You can see the following output.

Thanks for the Reading article! I hope this get’s you started application using ASP.NET MVC. Take care.