Displaying GridView in a GridView - Part 32

Suggested Videos 
Part 29 - Enhancing gridview using jquery when deleting mulitple rows
Part 30 - Displaying images in asp.net gridview using templatefield
Part 31 - Displaying images in asp.net gridview using imagefield



In this video we will discuss about displaying GridView in another GridView. For this example we will be using tblDepartment and tblEmployee tables. Use the sql script below to create and populate these tables with sample data.



Create Table tblDepartment
(
DepartmentId int primary key identity,
Name nvarchar(50)
)

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

Create Table tblEmployee
(
EmployeeId int Primary key identity,
Name nvarchar(50),
DeptId int Foreign key references tblDepartment(DepartmentId)
)

Insert into tblEmployee values('Mark',1)
Insert into tblEmployee values('John',1)
Insert into tblEmployee values('Mary',3)
Insert into tblEmployee values('Mike',4)
Insert into tblEmployee values('Pam',3)
Insert into tblEmployee values('David',1)
Insert into tblEmployee values('Tom',2)
Insert into tblEmployee values('Jason',4)
Insert into tblEmployee values('Vicky',3)
Insert into tblEmployee values('Todd',1)
Insert into tblEmployee values('Ben',2)

We want to display Employees gridview, with in Departments GridView as shown below. 


Step1 : Create EmployeeDataAccessLayer
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Demo
{
    public class Employee
    {
        public int EmployeeId { get; set; }
        public string EmployeeName { get; set; }
    }

    public class EmployeeDataAccessLayer
    {
        public static List<Employee> GetAllEmployees(int DepartmentId)
        {
            List<Employee> listEmployees = new List<Employee>();

            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("Select * from tblEmployee where DeptId = @DepartmentId", con);
                SqlParameter parameter = new SqlParameter();
                parameter.ParameterName = "@DepartmentId";
                parameter.Value = DepartmentId;
                cmd.Parameters.Add(parameter);

                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Employee employee = new Employee();
                    employee.EmployeeId = Convert.ToInt32(rdr["EmployeeId"]);
                    employee.EmployeeName = rdr["Name"].ToString();

                    listEmployees.Add(employee);
                }
            }

            return listEmployees;
        }
    }
}

Step2 : Create DepartmentDataAccessLayer
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace Demo
{
    public class Department
    {
        public int DepartmentId { get; set; }
        public string DepartmentName { get; set; }
        public List<Employee> Employees
        {
            get
            {
                return EmployeeDataAccessLayer.GetAllEmployees(this.DepartmentId);
            }
        }
    }

    public class DepartmentDataAccessLayer
    {
        public static List<Department> GetAllDepartmentsandEmployees()
        {
            List<Department> listDepartments = new List<Department>();

            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                SqlCommand cmd = new SqlCommand("Select * from tblDepartment", con);
                con.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Department department = new Department();
                    department.DepartmentId = Convert.ToInt32(rdr["DepartmentId"]);
                    department.DepartmentName = rdr["Name"].ToString();

                    listDepartments.Add(department);
                }
            }

            return listDepartments;
        }
    }
}

Step 3: Drag and drop a gridview control on webform1.aspx. Create 2 bound fields and 1 template field. Use another GridView control, in the ItemTemplate of the TemplateField.
<asp:GridView ID="GridView1" runat="server" 
    AutoGenerateColumns="False">
    <Columns>
        <asp:BoundField DataField="DepartmentId" 
            HeaderText="Department Id" />
        <asp:BoundField DataField="DepartmentName" 
            HeaderText="Department Name" />
        <asp:TemplateField HeaderText="Employees">
            <ItemTemplate>
                <asp:GridView ID="GridView2" runat="server" 
                    DataSource='<%# Bind("Employees") %>'>
                </asp:GridView>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

Step 4: Copy and paste the following code in WebForm1.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
    GridView1.DataSource = 
        DepartmentDataAccessLayer.GetAllDepartmentsandEmployees();
    GridView1.DataBind();
}