Sunday, 16 April 2017

Working With Multiple tables

In this session we will  Discuss Working with Two tables in ASPMVC

Here i have Two tables in Database  1)- EmpCategory   2). EmpList
joinedImage
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) ——

DEmo

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!!!!

Recent Post









No comments:

Post a Comment