In this session we will Discuss Working with Two tables in ASPMVC
Here i have Two tables in Database 1)- EmpCategory 2). EmpList
In EmpCat table we have List of Departments and EmpList table we have List of all the Employees .using this table I want to Display a MVC Application that should Display Department List and Employee List Page .
Department table Will Display all the Departments that are available in
as Hyperlink that should Redirect EmployeeList Page that display
Name of All Employees belonging to the Department As HyperLink that should
Redirect To Employee Details Page that should Display complete Details of particular Employee.
Flow of Program (This is What we want to Achieve) ——
Database Code-----
Create Database Demo
use Demo
Create table EmpCategory
(
EmpId int identity primary key,
EmpStream varchar(50)
)
Create table EmpList
(
EID int identity Primary key,
EName varchar(50),
EAge int,
EEmail varchar(50),
EmpCat int
)
insert into EmpCategory values(‘IT’)
insert into EmpCategory values(‘HR’)
insert into EmpCategory values(‘Payroll’)
insert into EmpList values(‘Manish’,19,’M@gmail.com’,1)
insert into EmpList values(‘Ravi’,22,’R@gmail.com’,3)
insert into EmpList values(‘Amit’,23,’A@gmail.com’,1)
insert into EmpList values(‘Riya’,23,’Riya@gmail.com’,2)
insert into EmpList values(‘Akanksha’,20,’Ak@gmail.com’,2)
Lets See how to implement this–
Follow these Steps to Implement the Program-
Step 1 – Open your Visual Studio and Create a New MVC4 Program “WorkWithTwoTable”.
Go to File -> New Project -> Web ->ASP.Net MVC 4 Web Application (Named “WorkWithTwoTable”) -> Ok ->Basic Template ->ok
Step 2 – Add Model Classes To map With Database Tables , first you have to Add “Department.cs”
Go to Solution Explorer -> Right Click on Models Folder ->Add ->Class (Department) ->
using System.ComponentModel.DataAnnotations.Schema;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;
namespace WorkWithTwoTables
{
[Table("EmpCategory")]
public class Department
{
[Key]
public int EmpId { get; set; }
public string EmpStream { get; set; }
}
}
Now Add Another Class Employee.cs to map with EmpList table-
Go to Solution Explorer -> Right Click on Models Folder ->Add ->Class (Employee) ->
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations.Schema;
using System.ComponentModel.DataAnnotations;
namespace WorkWithTwoTables.Models
{
[Table("EmpList")]
public class Employee
{
[Key]
public int EID { get; set; }
public string EName { get; set; }
public int EAge { get; set; }
public string EEmail { get; set; }
public int EmpCat { get; set; }
}
}
Step 3 – Add Context Classes to Models Folder , “EmployeeContext.cs” //To Query From Database
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
namespace WorkWithTwoTables.Models
{
public class EmployeeContext:DbContext
{
public DbSet<Employee> Employees { get; set; }
public DbSet<Department> Departments { get; set; }
}
}
Step 4 – Now Create a Controller Class To Render Department Regarding Data on View .
Go to Solution Explorer -> Right Click on Controllers Folder ->Add ->Controller (DepartmentController) ->
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using WorkWithTwoTables.Models;
namespace WorkWithTwoTables.Controllers
{
public class DepartmentController : Controller
{
EmployeeContext db = new EmployeeContext();
public ActionResult Index()
{
return View(db.Departments.ToList());
}
}
}
Step 5 – Now Create a a View Page of Index
Right Click in Index Action Method->Add View->Index-> Ok..
@model List<WorkWithTwoTables.Models.Department>
@{
ViewBag.Title = "Index";
}
<h1>Department List</h1>
@foreach (var p in Model)
{
@Html.ActionLink(p.EmpStream, "Index", "Employee", new {id=p.EmpId },null)
}
Step 6 – Now Create a Employee Controller ..
Go to Solution Explorer -> Right Click on Controllers Folder ->Add ->Controller (EmployeeController) ->
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using WorkWithTwoTables.Models;
namespace WorkWithTwoTables.Controllers
{
public class EmployeeController : Controller
{
EmployeeContext db = new EmployeeContext();
public ActionResult Index(int id)
{
return View(db.Employees.Where(x=>x.EmpCat==id).ToList());
}
}
}
Step 7 – Add View of This Action Method ..
Right Click in Action Method ->Add View->Index.cshtml-> Ok
@model List<workingWithTwoTables.Models.Employee>
@{
ViewBag.Title = "Index";
}
<h1>Employee List</h1>
@foreach (var p in Model)
{
@Html.ActionLink(p.EName, "Details", "Employee", new{ id=p.EmpCat},null)
}
Step-8) Now Create a Details Action Method on Employee Controller ..
public ActionResult Details(int id)
{
return View(db.Employees.Single(x=>x.EID==id));
}
Step -9). Add A View Of Details Method
@model WorkWithTwoTables.Models.Employee
@{
ViewBag.Title = "Details";
}
<h2>Details</h2>
<table>
<tr>
<th>Employee ID</th>
<td>@Model.EID</td>
</tr>
<tr>
<th>Employee Name</th>
<td>@Model.EName</td>
</tr>
<tr>
<th>Employee Age</th>
<td>@Model.EAge</td>
</tr>
<tr>
<th>Employee Email</th>
<td>@Model.EEmail</td>
</tr>
</table>
Step 10 - Add connection String in Web.Config File
<add name="EmployeeContext" connectionString="Data Source=DHANANJAY\SQLEXPRESS;Initial Catalog=Demo;User Id=sa;Password=1" providerName="System.Data.SqlClient"/>
Step 11 -Edit in Global.asax File
Use Namespace- using System.Data.Entity; Database.SetInitializer<WorkingWithTwoTable.Models.EmployeeContext>(null);
Step-12 RUN the Programm!!!!
Here I have Created 2 tables – 1) First table that you are watching in image is