Click here to Skip to main content
Click here to Skip to main content

Populating DataGrid control from a parent-child relationship DB table

, 7 Aug 2004
Rate this:
Please Sign up or sign in to vote.
This article explains how to populate a DataGrid with records from a database table containing a parent-child relationship.

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:

Sample Image - HierDataGrid.jpg

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

Table screenshot

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Jimmy M Joy
Web Developer
United States United States
Jimmy M Joy - Microsoft Application Developer

Comments and Discussions

 
Questioncan i use this in webapplication? Pinmembernicklibee30-Nov-14 5:15 
QuestionHow to do the same idea in Gridview PinmemberAlex Budiman16-Mar-10 9:00 
GeneralBuilding the tree with one query PinmemberJanusz Sas17-Jun-09 23:43 
Questionasp.net datagrid Pinmembergovindi unal18-May-09 1:44 
GeneralSample does not load Pinmembermexx_edm25-Nov-08 5:26 
GeneralSample project does not load PinmemberAhtam15-Jul-08 3:46 
Generaln number of parent id is null Pinmemberjeevan shanmukham20-Jun-07 22:09 
QuestionHow to make this work with paging? Pinmemberewds24-Mar-07 9:03 
AnswerRe: How to make this work with paging? PinmemberRajib Ahmed26-Sep-07 18:17 
GeneralAdding Checkbox in this treeview datagrid Pinmemberbyock19-Dec-06 23:48 
Generalquestion on Datagrid(very URGENT) Pinmemberprameelapydi13-Nov-06 18:41 
GeneralRe: question on Datagrid(very URGENT) PinmemberMuammar©5-Jun-07 21:15 
Generaldatagrid Pinmemberrégina_phalange26-Jul-06 10:14 
Generalvery useful codeing Pinmembercinni9-Jan-06 19:33 
GeneralWinForms PinmemberArankun3-Jan-06 9:04 
GeneralRe: WinForms PinmemberAYasser21-Feb-07 12:14 
GeneralGreat! Pinmembertakcrazy6-Nov-05 23:57 
GeneralOLNY WORKS WITH SMALL TABLES PinmemberCodeProjectJim5-Oct-05 13:14 
GeneralRe: OLNY WORKS WITH SMALL TABLES Pinmemberenjoycrack5-Oct-05 16:11 
GeneralRe: OLNY WORKS WITH SMALL TABLES Pinmemberpickedaname4-Jan-06 3:02 
AnswerRe: OLNY WORKS WITH SMALL TABLES PinmemberBalaNet26-Apr-06 0:14 
GeneralDo you have a link? Pinmemberewds24-Mar-07 8:52 
GeneralThank you very much! Pinmembermaharsoft4-Aug-05 20:23 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150301.1 | Last Updated 8 Aug 2004
Article Copyright 2004 by Jimmy M Joy
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid