Populating DataGrid control from a parent-child relationship DB table

• Download source files in VB.NET - 22.2 Kb
• Download source files in C# - 18.4 Kb

Introduction

This article is not discussing about a user control to manage hierarchical data using DataGrid but this explains how we can display hierarchical data in a grid. This is not a nested grid but if the parent and child got same data to display then this article will help you to modify DataGrid with a few lines of code.

Overview

One of the rich in-built Webserver control is the DataGrid. We can do a lot with the powerful DataGrid control. There are several ways, in which we can customize the DataGrid according to our requirements. This article describes how to populate a DataGrid control using parent – child relation. This code is useful where you have a parent child relationship contained within the same table.

In an application, the results of the DataGrid control would look like this:

Screen shot of the DataGrid control populated by a parent child relationship from a database.

Before you jump into the details of this article, you should have a working knowledge of ASP.NET’s DataGrid control. This article assumes working knowledge of ASP.NET’s DataGrid control. If you need to brush up on this control's specifics, be sure to read the following articles and tutorials:

• A Practical Guide to .NET DataTables, DataSets and DataGrids - Part 4
• http://www.datagridgirl.com/

I will demonstrate building a parent-child DataGrid using the table described below. The sample code downloads is also available in Visual Basic .NET.

First, let’s build the Web form user interface with a DataGrid having Bound Columns and Button Column. The first column is a Button Column with a text value of ‘+’ having button type set to Push Button, and this column is used as the tree node. This column is used for opening and closing of nodes in the DataGrid. More columns can be created based on your requirement. Two columns are needed with the ID of the node and its parent ID for the manipulation of tree nodes in the code behind page. If you don’t want to show that IDs in the output of the page, you can set the Visibility of the columns to false.

The sample uses SQL Server 2000 database and the connection string is put in the Web.Config file. The advantage is even if the database connection string in the .config file changes, the application can be executed without recompiling it.

Parent-child relationship DB table

Consider the employee table of a software firm that stores all the employee records. Each employee is linked to his/her boss by a Boss ID. This table can be represented using the following CREATE TABLE statement:

CREATE TABLE dbo.Employee
(
    EmpID    int     PRIMARY KEY,
    EmpName          varchar(30),
    BossID   int     FOREIGN KEY REFERENCES Employee (EmpID)
)
GO

In the above employee table, EmpID is declared as a primary key, and the BossID column is declared as a foreign key constraint that references the EmpID column of the same table, that is, a self referencing table. This is so, because all employees and boss are stored in the same table. Use the following query to insert some data into the employee table.

INSERT dbo.Employee SELECT 1, 'President', NULL
INSERT dbo.Employee SELECT 2, 'Vice President', 1
INSERT dbo.Employee SELECT 3, 'CEO', 2
INSERT dbo.Employee SELECT 4, 'CTO', 2
INSERT dbo.Employee SELECT 5, 'Group PM', 4
INSERT dbo.Employee SELECT 6, 'PM 1', 5
INSERT dbo.Employee SELECT 7, 'PM 2', 5
INSERT dbo.Employee SELECT 8, 'Team Leader 1', 6
INSERT dbo.Employee SELECT 9, 'Software Engineer 1', 8
INSERT dbo.Employee SELECT 10, 'Software Engineer 2', 8
INSERT dbo.Employee SELECT 11, 'Test Lead 1', 6
INSERT dbo.Employee SELECT 12, 'Tester 1', 11
INSERT dbo.Employee SELECT 13, 'Tester 2', 11
INSERT dbo.Employee SELECT 14, 'Team Leader 2', 7
INSERT dbo.Employee SELECT 15, 'Software Engineer 3', 14
INSERT dbo.Employee SELECT 16, 'Software Engineer 4', 14
INSERT dbo.Employee SELECT 17, 'Test Lead 2', 7
INSERT dbo.Employee SELECT 18, 'Tester 3', 17
INSERT dbo.Employee SELECT 19, 'Tester 4', 17
INSERT dbo.Employee SELECT 20, 'Tester 5', 17
GO

All the employees except the superboss are linked to their respective boss using BossID. Since nobody is managing the President, the BossID of President is set to NULL.

The above structure is very versatile so that you can have unlimited child records using this structure, thus allowing you to create as many nests or branches you wish.

How the code works?

You can create the above said table with its value either in a new database or in any existing database. On the Page_Load event, the database is connected using the connection string read from the web.config file. The select query is passed into the SqlDataAdapter object and this query can be modified based on our requirement. Here, we are calling a recursive function to iterate through the entire child of the root. Based on the level of the child node, a little space is added using three continuous “ ”.

After loading all the data into the DataGrid in the Page_Load, the visibility of all the rows is set to false except the first row since it is the root of the hierarchy. The text of the ButtonColumn in the first column is having the default value “+”. On clicking the ButtonColumn, DataGrid1_ItemCommand is invoked, and based on the Text on the command button, we are tracking the closing and opening of the tree view. If the Text is ‘+’ then we are setting the visibility of the DataGrid rows with ParentID same as that of the current node’s ID to true. If the Text is “-” then we are storing all the children of the current node in an ArrayList and putting the Visibility to false.

Conclusion

The DataGrid server control is pretty simple to display a lot of contents with only a handful of lines of code, and is one of the most powerful server controls in the ASP.NET arsenal. I hope this article will help you open a few more ideas with the DataGrid control.