Retrieve Data From Multiple Tables Using Asp.Net MVC

-
Today, in this article, we will see the step-by-step process of joining multiple tables using LINQ Join and displaying records in a View. So, let's start.
Step 1
Open SQL server and create a database and 3 tables. I have created three tables as - Employee, Department, and Incentive respectively.

Employee Table

CREATE TABLE [dbo].[Employee](    
[EmployeeId] [int] IDENTITY(1,1) NOT NULL,    
[Name] [nvarchar](50) NULL,    
[Gender] [char](10) NULL,    
[Age] [int] NULL,    
[Position] [nvarchar](50) NULL,    
[Salary] [int] NULL,    
[HireDate] [datetime] NULL,    
[Department_Id] [int] NULL,    
[Incentive_Id] [int] NULL,    
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED     
(    
    [EmployeeId] ASC    
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]    
) ON [PRIMARY]    
    
GO   

Department Table

CREATE TABLE [dbo].[Department](    
    [DepartmentId] [int] IDENTITY(1,1) NOT NULL,    
    [DepartmentName] [nvarchar](50) NULL,    
PRIMARY KEY CLUSTERED     
(    
    [DepartmentId] ASC    
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]    
) ON [PRIMARY]    
    
GO



Incentive Table

CREATE TABLE [dbo].[Incentive](    
    [IncentiveId] [int] IDENTITY(1,1) NOT NULL,    
    [IncentiveAmount] [int] NULL,    
PRIMARY KEY CLUSTERED     
(    
    [IncentiveId] ASC    
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]    
) ON [PRIMARY]    
    
GO

Step 2

Open Visual Studio  click on "New Project", and create an Mvc web application project. 


Now add Model Entity click on Models folder And Add New Entity 



You will get a window; from there, select Data from the left panel and choose ADO.NET Entity Data Model, give it the name DBModels (this name is not mandatory, you can give any name) and click "Add".

After clicking on NEXT, another window will appear. Choose the database table name as shown in the below screenshot and click "Finish".



Entity Framework gets added and the respective class gets generated under the Models folder.



Step 4
Right-click the Controllers folder, select Add Empty Controller  give name to controller

Complete code for Demo Controller


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

namespace MultipleTableDemo.Controllers
{
    public class DemoController : Controller
    {
        
        // GET: Demo
        public ActionResult Index()
        {
            using (ShanelEntities Db = new ShanelEntities())
            {
                List<Employee> emp = Db.Employees.ToList();
                List<Department> Dept = Db.Departments.ToList();
                List<Incentive> Inc = Db.Incentives.ToList();

                var Result = from e in emp
                             join d in Dept on e.Department_Id equals d.DepartmentId into table1
                             from d in table1.ToList()
                             join i in Inc on e.Incentive_Id equals i.IncentiveId into table2
                             from i in table2.ToList()
                             select new ViewModel
                             {
                                 employee = e,
                                 department = d,
                                 incentive = i
                             };

                return View(Result);
            }
        }
    }
}

Step 5
Create a ViewModel Class.

using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Web;  
  
namespace WorkingWithMultipleDataTable_Demo.Models  
{  
    public class ViewModel  
    {  
        public Employee employee { get; set; }  
        public Department department { get; set; }  
        public Incentive incentive { get; set; }  
    }  
} 

Step 6
Right-click on the index action method in Controller. The "Add View" window will appear with default index name unchecked (use a Layout page), and click on "Add", as shown in the below screenshot. The View will be added in the Views folder under Demo folder with the name index.

Step 7
Design the View  HTML, .cshtml, and bootstrap 4 classes.
Complete Index View code
@model IEnumerable<MultipleTableDemo.Models.ViewModel>
@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
    <link href="~/Content/bootstrap.min.css" rel="stylesheet" />
    <script type="text/javascript" src="@Url.Action("~/scripts/jquery-3.3.1.min.js")"></script>
    <script type="text/javascript" src="@Url.Action("~/scripts/bootstrap.min.js")"></script>
</head>
<body>
    <div class="container-fluid py-4">
        <h4 class="text-center text-uppercase">Employee List</h4>
        <table class="table table-striped table-bordered">
            <thead class="bg-dark text-white">
                <tr>
                    <th>Name</th>
                    <th>Position</th>
                    <th>Salary</th>
                    <th>Hire Date</th>
                    <th>Department</th>
                    <th>Incentive</th>
                </tr>
            </thead>
            <tbody>
                @foreach (var item in Model)
                {
                    <tr>
                        <td>@item.employee.Name</td>
                        <td>@item.employee.Position</td>
                        <td>@item.employee.Salary</td>
                        <td>@item.employee.HireDate</td>
                        <td>@item.department.DepartmentName</td>
                        <td>@item.incentive.IncentiveAmount</td>
                    </tr>

                }
            </tbody>
        </table>
    </div>
</body>
</html>  

Output












  

Comments

Post a Comment

Popular posts from this blog

How to Integrate SB Admin 2 Template in Asp.net Mvc Application

-
Image
Template URL    https://startbootstrap.com/themes/sb-admin-2/ Step1 : Download Template form above URL open template copy files /folder from  CSS , JS,Vendor Folder from template  Step 2 : Create Asp.net Mvc Application Open content Folder and paste above Item into folder   Step 3: Open Index.html from template and copy content from Index.html after that open Home controller and Index .cshtml file and paste code inside Index. cshtml replace reference of file as below screen (~/Content) that we have copied Inside content folder   Step 4 : Layout should be null for this Action Method   Step 5 : Run the Application ...
Read more

Send mail when Rss news is published using Logic Apps - Azure portal

-
Image
Basically Logic App is use to Automate the process  Today we are going to demonstrate How to Send Mail(gmail) using Logic App when new Item or news feed is published step 1 :- Create Logic App using Azure Portal , give any meaningful name to the logic App  step 2 :- step 3 :- click on create and after that when app get published click on Go To Resources and go inside newly created Logic App and click on Blank Logic App step 4:- click on RSS  and Search for action like when a feed item is published after that provide  url  :-  http://feeds.reuters.com/reuters/topNews  and click on next step step 5 :- Add Gmail account add necessary details and allow access to use logic app  and select action send mail  step 6 :-  click on the save Button and Run the Logic App step 7 :- Go to gmail and check after sometime  step 8 :- Go to Azure portal inside logic app check s...
Read more
-
Image
COOKIE IN ASP.NET CORE Below sample C# code demonstrated on how to create and set cookie in ASP.NET  Core A cookie is a client-side state management technique which use to store a small amount of data on client  browser There are 2 types of Cookies. Persistence :- Persistence Cookies are stored according to the time span mentioned by user Non-Persistence :- Non-Persistence Cookies get expires when you close your browser. We are going to see a short demo of how to create a cookie and read the view from the cookie in ASP.NET Core. I have added Home Controller with name and Create ActionMethod in it. In creating action method, we are going to create a cookie with name “ DemoCookie ” and we are going to set an expiration for a week and to add a cookie to the browser we are going to call  Response.Cookies.Append().  public IActionResult Create()         {             string key = "DemoCookie";         ...
Read more