Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Part 10 - Working with multiple tables in mvc

Suggested Videos 
Part 7 - Models in MVC
Part 8 - Data access in MVC using entity framework
Part 9 - Generate hyperlinks using actionlink html helper




Please watch Part 9, before proceeding.

In this video we will discuss working with 2 related tables in MVC
1. tblDepartment
2. tblEmployee



Use the sql script to create and populate these 2 tables
Create table tblDepartment
(
Id int primary key identity,
Name nvarchar(50)
)

Insert into tblDepartment values('IT')
Insert into tblDepartment values('HR')
Insert into tblDepartment values('Payroll')

Create table tblEmployee
(
EmployeeId int Primary Key Identity(1,1),
Name nvarchar(50),
Gender nvarchar(10),
City nvarchar(50),
DepartmentId int
)

Alter table tblEmployee
add foreign key (DepartmentId)
references tblDepartment(Id)

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

This is what we want to achieve
1. Display all the departments from tblDepartments table. The Department names should be rendered as hyperlinks.
2. On clicking the department name link, all the employees in the department should be displayed. The employee names should be rendered as hyperlinks.
3. On clicking the employee name link, the full details of the employee should be displayed. 
4. A link should also be provided on the employee full details page to navigate back to Employee list page. Along the same lines, a link should also be provided on the employee list page to navigate back to Departments list page. 

The screen shots of the above workflow is shown below for your reference
Working with multiple tables

Implementing Departments List:
Step 1: Right click on the "Models" folder and add a class file with name=Department.cs. Copy and paste the following code.
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<Employee> Employees { get; set; }
    }
}

Step 2: Add "Departments" property to "EmployeeContext" class that is present in "EmployeeContext.cs" file in "Models" folder.
public class EmployeeContext : DbContext
{
    public DbSet<Department> Departments { get; set; }
    public DbSet<Employee> Employees { get; set; }
}

Step 3: Right click on the "Controllers" folder and add a Controller, with name=DepartmentController. Copy and paste the following code.
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()
        {
            EmployeeContext employeeContext = new EmployeeContext();
            List<Department> departments = employeeContext.Departments.ToList();
            return View(departments);
        }
    }
}

Step 4: Right click on the Index() action method in DepartmentController class and select "Add View" from the context menu. Set
View name = Index
View engine = Razor
Select "Create Strongly-typed view" checkbox
Select Department class, from "Model class" dropdownlist
Click "Add" button

Copy and paste the following code in Index.cshtml view file in Department folder
@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", "Employee", 
    new { departmentId = department.ID }, null)</li>
}
</ul>
</div>

Changes to Employee List and Detail pages
Add "DepartmentId" property to "Employee" model class that is present in Employee.cs file in "Models" folder.
[Table("tblEmployee")]
public class Employee
{
    public int EmployeeId { get; set; }
    public string Name { get; set; }
    public string Gender { get; set; }
    public string City { get; set; }
    public int DepartmentId { get; set; }
}

Add "departmentId" parameter to Index() action method in "EmployeeController" class that is present in "EmployeeController.cs" file in "Controllers" folder. Use the "departmentId" parameter to filter the list of employees as shown below.
public ActionResult Index(int departmentId)
{
    EmployeeContext employeeContext = new EmployeeContext();
    List<Employee> employees = employeeContext.Employees.Where(emp => emp.DepartmentId == departmentId).ToList();

    return View(employees);
}

Copy and paste the following line in "Index.cshtml" that is present in "Employee" folder in "Views" folder. With this change we are able to generate an action link to redirect the user to a different controller action method.
@Html.ActionLink("Back to Department List", "Index", "Department"

Change the following line in "Details.cshtml" that is present in "Employee" folder in "Views" folder. 
CHANGE THIS LINE @Html.ActionLink("Back to List", "Index")  
TO 
@Html.ActionLink("Back to Employee List", "Index", new { departmentId = @Model.DepartmentId }) 

24 comments:

  1. I have been waiting for your MVC series for a while. Thank You. You approach the subject from a whole different perspective.I enjoy you approach. Both educational and informative.
    Can I ask Venkat was there any reason not have created a BusinessContext class? Any special reason to add to the EmployeeContext.cs. Also you added in the Department class a List Employee property. Would that be a one to many relationship concept in like a database?

    ReplyDelete
  2. Sir i got an error Pleas Help me

    List of Department are shown when i click IT
    Then it goes to that point

    List prson = percons.person.Where(pr => pr.Departmentid == deprtid).ToList();

    and that exception is shown

    An error occurred while executing the command definition. See the inner exception for details.


    Thanks Sir Your study material Is Best

    ReplyDelete
    Replies
    1. you should not ADD the DepartmentId in Employee Database.

      check it out

      CREATE TABLE [dbo].[tblEmployee] (
      [EmployeeId] INT NOT NULL,
      [Name] NVARCHAR (50) NULL,
      [Gender] NVARCHAR (50) NULL,
      [City] NVARCHAR (50) NULL,
      [DepartmentId] INT NULL,
      PRIMARY KEY CLUSTERED ([EmployeeId] ASC)
      );

      Delete
    2. you should not ADD the DepartmentId in Employee Database.

      check it out

      CREATE TABLE [dbo].[tblEmployee] (
      [EmployeeId] INT NOT NULL,
      [Name] NVARCHAR (50) NULL,
      [Gender] NVARCHAR (50) NULL,
      [City] NVARCHAR (50) NULL,
      [DepartmentId] INT NULL,
      PRIMARY KEY CLUSTERED ([EmployeeId] ASC)
      );

      Delete
  3. Sir here if we have to pass an department object then how we will do it.
    I already try it but it gives me object rerefence is not set.

    ReplyDelete
    Replies
    1. Hi mahesh i also get the same error ..if you solve this problem please reply

      Delete
  4. gr8 work sir....i have no words to say....

    ReplyDelete
  5. I keep getting the following code at the top of the departments list when I try and call it. I followed each step in the code and am not sure why it is happening. Any help would be appreciated.

    System.Collections.Generic.List`1[MVCDemo1.Models.Department] IEnumerable

    ReplyDelete
    Replies
    1. check the type of the variable you are trying to store the results . It should of type employee not the List

      Delete
  6. Help me sir, i am getting this...
    The parameters dictionary contains a null entry for parameter 'departmentId' of non-nullable type 'System.Int32' for method 'System.Web.Mvc.ActionResult Index(Int32)' in 'DemoMVC.Controllers.EmployeeController'. An optional parameter must be a reference type, a nullable type, or be declared as an optional parameter.
    Parameter name: parameters

    ReplyDelete
    Replies
    1. Hi himanshu,

      The error is because the departmentid is null in query string or in querystring there is no feild department id so u get this error. To avoid this u can use string ? departmentid instead of using string departmentid

      Delete
    2. i got the solution from google and it worked.
      change the actionlink to this :
      Html.ActionLink(employee.Name, "Details", new { id = employee.EmployeeId })

      Delete
  7. Please make sure your connection string name and Datacontext class name are same to solve the error.

    ReplyDelete
  8. Hi sir,Most of them are getting an error when following this tutorial.Do we need to change any settings in the RouteConfig.cs file.You are passing DepartmentId as parameters in ActionMethods and ActionLinks but there is no reference for that in the RouteConfig class.How does route engine will identity this[DepartmentId] parameter?

    ReplyDelete
  9. Hi Venkat,
    You are doing a really great service to all .NET developers. May God bless you. I was wondering if you have recorded video series for WPF. If you have, please share the link.

    Thank You

    ReplyDelete
  10. Hi,
    Can someone help me on this error bellow, I have diferent tables and I created the connection as:

    [Table("colab")]
    public class Employee
    {
    public int ID { get; set; }
    public string Nome { get; set; }
    public string Sexo { get; set; }
    public string Cidade { get; set; }
    public int idDiretoria { get; set; } // this is the DepartmentID
    }
    When I run this:
    http://localhost/MVCDemo/Employee?idDepto=2

    I Get this error, where 'Department_ID' comes from ? i don´t have it in my code..

    Invalid column name 'Department_ID'.

    Descrição: Ocorreu uma exceção sem tratamento durante a execução da atual solicitação da Web. Examine o rastreamento de pilha para obter mais informações sobre o erro e onde foi originado no código.

    Detalhes da Exceção: System.Data.SqlClient.SqlException: Invalid column name 'Department_ID'.

    Erro de Origem:


    Linha 14: {
    Linha 15: EmployeeContext employeeContext = new EmployeeContext();
    Linha 16: List employees = employeeContext.Employees.Where(emp => emp.idDiretoria == idDepto).ToList();
    Linha 17:
    Linha 18:

    Arquivo de Origem: d:\AplicWEB-Projetos\testeMVC\MVCDemo\MVCDemo\Controllers\EmployeeController.cs Linha: 16

    ReplyDelete
  11. I´m getting this error
    Invalid column name 'Department_ID'.

    Descrição: Ocorreu uma exceção sem tratamento durante a execução da atual solicitação da Web. Examine o rastreamento de pilha para obter mais informações sobre o erro e onde foi originado no código.

    Detalhes da Exceção: System.Data.SqlClient.SqlException: Invalid column name 'Department_ID'.

    Erro de Origem:


    Linha 14: {
    Linha 15: EmployeeContext employeeContext = new EmployeeContext();
    Linha 16: List employees = employeeContext.Employees.Where(emp => emp.idDiretoria == departmentId).ToList();
    Linha 17:
    Linha 18:

    Arquivo de Origem: d:\AplicWEB-Projetos\testeMVC\MVCDemo\MVCDemo\Controllers\EmployeeController.cs Linha: 16

    Rastreamento de Pilha:


    [SqlException (0x80131904): Invalid column name 'Department_ID'.]

    the sql execution is
    exec sp_executesql N'SELECT
    [Extent1].[ID] AS [ID],
    [Extent1].[Nome] AS [Nome],
    [Extent1].[Sexo] AS [Sexo],
    [Extent1].[Cidade] AS [Cidade],
    [Extent1].[EMail] AS [EMail],
    [Extent1].[idDiretoria] AS [idDiretoria],
    [Extent1].[Department_ID] AS [Department_ID]
    FROM [dbo].[colab] AS [Extent1]
    WHERE [Extent1].[idDiretoria] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=2
    go
    the table emplyee is
    [Table("colab")]
    public class Employee
    {
    public int ID { get; set; }
    public string Nome { get; set; }
    public string Sexo { get; set; }
    public string Cidade { get; set; }
    public string EMail { get; set; }
    public int idDiretoria { get; set; }
    }
    what is wrong ?

    ReplyDelete
  12. hi sir you are my god for dotnet ,.....sir can u provide Bootstrap,jquery ajax live examples also u keep in this site very useful to us,...

    ReplyDelete
  13. Hi Sir,
    Thanks for your videos, All are very helpful to learning from very beginning level.

    Thanks

    ReplyDelete
  14. This is really a great collection and a very precised package to get comprehensive understanding of MVC and related technologies. Much appreciated!

    ReplyDelete
  15. Hi i have error sir in below code

    public ActionResult Index(int deptid = 0)
    {
    EmployeeContext employeeContext = new EmployeeContext();
    if (deptid == 0)
    {
    List employee = employeeContext.Employees.ToList();
    return View(employee);
    }
    else
    {
    IQueryable employee = employeeContext.Employees.Where(emp >= emp.deptid == deptid).ToList();
    return View(employee);
    }

    Error:

    }
    else
    {
    List employee = employeeContext.Employees.Find(deptid);

    return View(employee);
    }

    }


    Error 1 'System.Data.Entity.DbSet' does not contain a definition for 'Where' and the best extension method overload 'System.Linq.Queryable.Where(System.Linq.IQueryable, System.Linq.Expressions.Expression>)' has some invalid arguments C:\Users\IBM_ADMIN\Documents\Visual Studio 2013\Projects\MVCDemoDataBase\MVCDemoDataBase\Controllers\EmployeeHyperLinkController.cs 26 49 MVCDemoDataBase
    Error 2 Argument 2: cannot convert from 'bool' to 'System.Linq.Expressions.Expression>' C:\Users\IBM_ADMIN\Documents\Visual Studio 2013\Projects\MVCDemoDataBase\MVCDemoDataBase\Controllers\EmployeeHyperLinkController.cs 26 81 MVCDemoDataBase
    Error 3 The name 'emp' does not exist in the current context C:\Users\IBM_ADMIN\Documents\Visual Studio 2013\Projects\MVCDemoDataBase\MVCDemoDataBase\Controllers\EmployeeHyperLinkController.cs 26 81 MVCDemoDataBase
    Error 4 The name 'emp' does not exist in the current context C:\Users\IBM_ADMIN\Documents\Visual Studio 2013\Projects\MVCDemoDataBase\MVCDemoDataBase\Controllers\EmployeeHyperLinkController.cs 26 88 MVCDemoDataBase

    Please help me out..

    ReplyDelete
  16. I am getting an error. Cannot open database "MvcApplication4.Models.EmployeeContext" requested by the login. The login failed.
    Login failed for user 'WINDOWS-RK365BU\user'.

    ReplyDelete
  17. {SELECT
    [Extent1].[EmployeeId] AS [EmployeeId],
    [Extent1].[Name] AS [Name],
    [Extent1].[Gender] AS [Gender],
    [Extent1].[City] AS [City],
    [Extent1].[DepartmentId] AS [DepartmentId],
    [Extent1].[Departments_ID] AS [Departments_ID]
    FROM [dbo].[tblEmployee] AS [Extent1]}


    I am getting this error of "Department_ID" but i haven't use this in my project. I have mentioned the code which i got from debug mode. Kindly rectify this asap.

    ReplyDelete
  18. I am getting this error of "Department_ID" but i haven't use this in my project.Please reply


    Reply

    ReplyDelete

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.