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.
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:
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
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,
BossID int FOREIGN KEY REFERENCES Employee (EmpID)
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
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
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
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
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