Display Multiple Tables In A Single View In ASP.NET MVC

This article will explain how you can display multiple tables' records in a single view using ViewModel in ASP.Net MVC.

Introduction

 
This article will explain to you how you can display multiple tables' record in a single view using ViewModel in ASP.Net MVC. For this article, you should have basic understanding of the following,
  • Bootstrap 4
  • MVC
  • Entity Framework
  • View Model
  • SQL Server
Step 1
 
Open your favourite SQL server database any version. It doesn’t really matter. Create tables as shown below.
  1. CREATE TABLE [dbo].[Employee](    
  2.     [Id] [int] IDENTITY(1,1) NOT NULL,    
  3.     [Name] [nvarchar](50) NULL,    
  4.     [Gender] [char](10) NULL,    
  5.     [Age] [intNULL,    
  6.     [Position] [nvarchar](50) NULL,    
  7.     [Office] [nvarchar](50) NULL,    
  8.     [Salary] [intNULL,    
  9.  CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED     
  10. (    
  11.     [Id] ASC    
  12. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]    
  13. ON [PRIMARY]    
  14.     
  15. GO    
  16.     
  17. CREATE TABLE [dbo].[Department](    
  18.     [Id] [int] IDENTITY(1,1) NOT NULL,    
  19.     [DepartmentName] [nvarchar](50) NULL,    
  20.  CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED     
  21. (    
  22.     [Id] ASC    
  23. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]    
  24. ON [PRIMARY]    
  25.     
  26. GO    
  27.     
  28. CREATE TABLE [dbo].[Incentive](    
  29.     [Id] [int] IDENTITY(1,1) NOT NULL,    
  30.     [IncentiveAmount] [intNULL,    
  31.  CONSTRAINT [PK_Incentive] PRIMARY KEY CLUSTERED     
  32. (    
  33.     [Id] ASC    
  34. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]    
  35. ON [PRIMARY]    
  36.     
  37. GO    
Step 2
 
Now open your favorite Visual Studio 2017 or any version you wish to.
 
Display Multiple Tables In A Single View In ASP.NET MVC
 
Step 3
 
Create an empty project in Visual Studio, and give an appropriate name. Check MVC checkbox and click on OK.
 
Display Multiple Tables In A Single View In ASP.NET MVC
 
Step 4
 
Right-click on the Models folder and add a database model. Add Entity Framework now. For that, right-click on Models folder, select Add, then select New Item.
 
Display Multiple Tables In A Single View In ASP.NET MVC
 
You will get a window; from there, select Data from the left panel and choose ADO.NET Entity Data Model, give it the name MyModel (this name is not mandatory, you can give any name) and click "Add"
 
Display Multiple Tables In A Single View In ASP.NET MVC
 
After you click on "Add a window", the wizard will open. Choose EF Designer from the database and click "Next".
 
Display Multiple Tables In A Single View In ASP.NET MVC
 
After clicking on "Next", a window will appear. Choose New Connection. Another window will appear. Add your server name - if it is local, then enter a dot (.). Choose your database and click "OK".
 
Display Multiple Tables In A Single View In ASP.NET MVC
 
The connection will be added. If you wish, save the connection name as you want. You can change the name of your connection below. It will save the connection in the web config. Now, click "Next".
 
Display Multiple Tables In A Single View In ASP.NET MVC
 
After clicking on NEXT, another window will appear. Choose the database table name as shown in the below screenshot and click "Finish".
 
Display Multiple Tables In A Single View In ASP.NET MVC
 
Entity Framework gets added and the respective class gets generated under the Models folder.
 
Display Multiple Tables In A Single View In ASP.NET MVC
 
Step 5
 
Right-click on Controllers folder and add a controller.
 
Display Multiple Tables In A Single View In ASP.NET MVC
 
A window will appear. Choose MVC5 Controller-Empty and click "Add".
 
Display Multiple Tables In A Single View In ASP.NET MVC
 
After clicking on "Add", another window will appear with DefaultController. Change the name to HomeController and click "Add". The HomeController will be added under the Controllers folder. Don’t change the Controller suffix for all controllers, change only the highlight, and instead of Default, just change Home.
 
Display Multiple Tables In A Single View In ASP.NET MVC
 
Step 6
 
Right click on project “Add Folder” name it ViewModel. Now right click on this folder and “Add Class” with name EmployeeViewModel.
  1. using MvcMultipleTable_Demo.Models;  
  2. using System;  
  3. using System.Collections.Generic;  
  4. using System.Linq;  
  5. using System.Web;  
  6.   
  7. namespace MvcMultipleTable_Demo.ViewModel  
  8. {  
  9.     public class EmployeeViewModel  
  10.     {  
  11.         public IEnumerable<Employee> Employees { get; set; }  
  12.         public IEnumerable<Department> Departments { get; set; }  
  13.         public IEnumerable<Incentive> Incentives { get; set; }  
  14.     }  
  15. }  
Controller Class Code
  1. using MvcMultipleTable_Demo.Models;  
  2. using MvcMultipleTable_Demo.ViewModel;  
  3. using System;  
  4. using System.Collections.Generic;  
  5. using System.Linq;  
  6. using System.Web;  
  7. using System.Web.Mvc;  
  8.   
  9. namespace MvcMultipleTable_Demo.Controllers  
  10. {  
  11.     public class HomeController : Controller  
  12.     {  
  13.         private readonly EmployeeContext db = new EmployeeContext();  
  14.   
  15.         public ActionResult Index()  
  16.         {  
  17.             var tables = new EmployeeViewModel  
  18.             {  
  19.                 Employees=db.Employees.ToList(),  
  20.                 Departments=db.Departments.ToList(),  
  21.                 Incentives=db.Incentives.ToList()  
  22.             };  
  23.             return View(tables);  
  24.         }  
  25.   
  26.     }  
  27. }  
Step 7
 
Right click on Index action method in controller class “Add View”
 
Display Multiple Tables In A Single View In ASP.NET MVC
 
Display Multiple Tables In A Single View In ASP.NET MVC
 
Index View Code
  1. @model MvcMultipleTable_Demo.ViewModel.EmployeeViewModel  
  2.   
  3. @{  
  4.     ViewBag.Title = "Index";  
  5. }  
  6.   
  7. <ul class="nav nav-pills">  
  8.   <li class="nav-item ">  
  9.     <a class="nav-link active rounded-0" data-toggle="pill" href="#home">Employee</a>  
  10.   </li>  
  11.   <li class="nav-item">  
  12.     <a class="nav-link rounded-0" data-toggle="pill" href="#menu1">Department</a>  
  13.   </li>  
  14.   <li class="nav-item">  
  15.     <a class="nav-link rounded-0" data-toggle="pill" href="#menu2">Incentive</a>  
  16.   </li>  
  17. </ul>  
  18. <div class="tab-content">  
  19.     <div class="tab-pane container active p-0" id="home">  
  20.         <h5 class="text-uppercase p-2 text-center">List of Employee</h5>  
  21.         <table class="table table-bordered table-striped">  
  22.             <thead class="thead-dark text-white">  
  23.                 <tr>  
  24.                     <th>@Html.DisplayNameFor(m => Model.Employees.FirstOrDefault().Name)</th>  
  25.                     <th>@Html.DisplayNameFor(m => Model.Employees.FirstOrDefault().Gender)</th>  
  26.                     <th>@Html.DisplayNameFor(m => Model.Employees.FirstOrDefault().Age)</th>  
  27.                     <th>@Html.DisplayNameFor(m => Model.Employees.FirstOrDefault().Position)</th>  
  28.                     <th>@Html.DisplayNameFor(m => Model.Employees.FirstOrDefault().Office)</th>  
  29.                     <th>@Html.DisplayNameFor(m => Model.Employees.FirstOrDefault().Salary)</th>  
  30.                 </tr>  
  31.             </thead>  
  32.             <tbody>  
  33.                 @foreach (var employee in Model.Employees)  
  34.                 {  
  35.                     <tr>  
  36.                         <td>@employee.Name</td>  
  37.                         <td>@employee.Gender</td>  
  38.                         <td>@employee.Age</td>  
  39.                         <td>@employee.Position</td>  
  40.                         <td>@employee.Office</td>  
  41.                         <td>@employee.Salary</td>  
  42.                     </tr>  
  43.                 }  
  44.             </tbody>  
  45.         </table>  
  46.     </div>  
  47.     <div class="tab-pane container fade p-0" id="menu1">  
  48.         <h5 class="text-uppercase p-2 text-center">List of Department</h5>  
  49.         <table class="table table-bordered table-striped">  
  50.             <thead class="thead-dark text-white">  
  51.                 <tr>  
  52.                     <th>@Html.DisplayNameFor(m => Model.Departments.FirstOrDefault().Id)</th>  
  53.                     <th>@Html.DisplayNameFor(m => Model.Departments.FirstOrDefault().DepartmentName)</th>  
  54.                 </tr>  
  55.             </thead>  
  56.             <tbody>  
  57.                 @foreach (var employee in Model.Departments)  
  58.                 {  
  59.                     <tr>  
  60.                         <td>@employee.Id</td>  
  61.                         <td>@employee.DepartmentName</td>  
  62.                     </tr>  
  63.                 }  
  64.             </tbody>  
  65.         </table>  
  66.     </div>  
  67.     <div class="tab-pane container fade p-0" id="menu2">  
  68.         <h5 class="text-uppercase p-2 text-center">List of Incentive</h5>  
  69.         <table class="table table-bordered table-striped">  
  70.             <thead class="thead-dark text-white">  
  71.                 <tr>  
  72.                     <th>@Html.DisplayNameFor(m => Model.Incentives.FirstOrDefault().Id)</th>  
  73.                     <th>@Html.DisplayNameFor(m => Model.Incentives.FirstOrDefault().IncentiveAmount)</th>  
  74.                 </tr>  
  75.             </thead>  
  76.             <tbody>  
  77.                 @foreach (var employee in Model.Incentives)  
  78.                 {  
  79.                     <tr>  
  80.                         <td>@employee.Id</td>  
  81.                         <td>@employee.IncentiveAmount</td>  
  82.                     </tr>  
  83.                 }  
  84.             </tbody>  
  85.         </table>  
  86.     </div>  
  87. </div>  
Step 8
 
Install latest version of bootstrap and JQuery from NuGet package manager under tools in Visual Studio.
 
Step 9
 
Add the following styles and script link in layout view,
  1. <!DOCTYPE html>  
  2. <html>  
  3. <head>  
  4.     <meta charset="utf-8" />  
  5.     <meta name="viewport" content="width=device-width, initial-scale=1.0">  
  6.     <title>@ViewBag.Title - My ASP.NET Application</title>  
  7.     <link href="~/Content/Site.css" rel="stylesheet" type="text/css" />  
  8.     <link href="~/Content/bootstrap.min.css" rel="stylesheet" type="text/css" />  
  9.     <script src="~/Scripts/modernizr-2.6.2.js"></script>  
  10. </head>  
  11. <body>  
  12.     <div class="container body-content">  
  13.         @RenderBody()  
  14.     </div>  
  15.     <script src="~/Scripts/jquery-3.4.1.min.js"></script>  
  16.     <script src="~/Scripts/bootstrap.min.js"></script>  
  17.     <script src="~/Scripts/popper.min.js"></script>  
  18. </body>  
  19. </html>  
Step 10 
 
Build your project and run by pressing Ctrl+F5
 
Display Multiple Tables In A Single View In ASP.NET MVC
Display Multiple Tables In A Single View In ASP.NET MVC
Display Multiple Tables In A Single View In ASP.NET MVC