A canonical hierarchy.
page 1 of 1
Published: 22 Sep 2003
Unedited - Community Contributed
In this article I hope to highlight a few of the important facts about building and rendering hierarchies in your pages. I'll work through a simple, theoretical excercise then leave you with a working demo.
by Darren Neimke
Average Rating: 
Views (Total / Last 10 Days): 17715/ 21

Everyday Hierarchies

In this article I hope to highlight a few of the important facts about building and rendering hierarchies in your pages. I'll work through a simple, theoretical excercise then leave you with a working demo. Lets get started...

First Steps

From a high-level there's 3 steps to painting a hierarchy on a page:

  • Assemble all the data that you need
  • Arrange it in the right order
  • Print each item underneath it's parent with a symbol or space to indent it

The first task is getting the data into a useable state. Typically members in the hierarchy will be linked via a Foreign Key; in the case of the "Boss/MiddleManager/Employee" example, the Foreign Key is likely to be a self-referencing join because all of the items will exist within the same table - "Employee", this type of hierarchy also tends to have an unlimited number of levels, i.e.:

    - Chairman
        - CEO
            - Manager
                - Middle-Manager
                    - Employee
            - Manager
                - Middle-Manager
                    - Employee

Each of these items will have a unique id (Primary Key) and a non-unique id that points to the unique id of their manager (Foreign Key).

Some Golden Rules

Before I move on, there are a couple of scalability issues that tend to arise when dealing with hierarchical data. I try to stick to the following 4 rules to help ensure that my apps don't crash too many servers ;-):

  • Limit calls to the database
  • Get the raw data as close to what I need from within the database
  • Do any looping - i.e. recursion - in the middle-tier
  • Build the simplest thing that will work!

The second point is particularly important to me. Making the most of sql joins and calculated columns means that often you will find that you can get away with a single call to the database and avoid doing a stack of rework in OnItemDataBound.

Step 1 : Getting the data

To continue on with my unlimited 'Employee' example, I'll grab a simple resultset. A query something like this should do:

    SELECT [Id], [ReportsTo], [Title], FirstName, LastName 
    FROM Employee

Resultset returned from database query

Id ReportsTo Title FirstName LastName
1 0 Chairman Bob Smith
2 3 Clerk Jim Jones
3 4 Accts Receivable Janet Anderson
4 5 Accountant Wendy Lewinski
5 1 General Manager Mike Donaldson

I return the results to a DataSet in the middle-tier and add a self-referencing DataRelation to represent the hierarchy in memory. I also add a column to the DataTable to keep track of depth, this can save me having to do messy operations during the actual binding operation and allows me to use that figure to calculate the width or type of image to display when presenting the data.


    ' return the above dataset from database
    Dim tbl As DataTable = GetData() 
    Dim ds As New DataSet()

    ' append a column to keep track of depth
    tbl.Columns.Add(New DataColumn("Depth", GetType(Int32)))

    Dim rel As New DataRelation( _
            "ParentChild", _
            tbl.Columns("Id"), _
            tbl.Columns("ReportsTo"), _
            False _
    rel.Nested = True

I think that it's worth noting that, up until now I've done nothing terribly special. I've returned a dirt simple rowset from my datastore and used a DataRelation object to relate my data. I'll discuss the special benefits gained from using this object in a moment!

Step 2 : Arranging the data

We now have a data resultset that is hierarchically represented in memory. What I want to do is to actually compute the nesting depth of each member and get the rows into the correct order before I do my databind. To do that I'll create a temporary table which is schematically identical to my current table and use it to append correctly ordered rows:


    ' configure our global table
    Dim mTable As DataTable
    mTable = tbl.Clone()

Now I can use recursion to loop through the data in my original table and append items to my cloned table. Notice that I use a Select() method to ensure that I pass in the top-level rows to start with. Also pay attention to the ImportRow() and the GetChildRows() method calls (see sidebar below):


    ' pass in the top-level rows
    ComputeHierarchy(tbl.Select("ReportsTo=0"), 0)
    Sub ComputeHierarchy(ByVal members() As DataRow, ByVal depth As Int32)
        Dim member As DataRow
        For Each member In members
            member.Item("Depth") = depth
            ComputeHierarchy(member.GetChildRows("ParentChild"), depth + 1)
    End Sub

Temp DataTable (mTable) after recursion

Id ReportsTo Title FirstName LastName Depth
1 0 Chairman Bob Smith 0
5 1 General Manager Mike Donaldson 1
4 5 Accountant Wendy Lewinski 2
3 4 Accts Receivable Janet Anderson 3
2 3 Clerk Jim Jones 4

As you can see, the data is now correctly ordered and ready for display; as mentioned before, I can use the Depth value to determine my indent width when drawing my hierarchical structure.

Step 3 : Painting the data

Finally, now that I have my data arranged and in the correct order all that remains is to create a WebControl and bind to it!


    EmployeeTable.DataSource = mTable.DefaultView


    <asp:Repeater id="Repeater1" runat="server">
                Int32.Parse(DataBinder.Eval(Container.DataItem, "Depth")) * 20 
                height="1" />
            <%# DataBinder.Eval(Container.DataItem, "Name") %>
            <br />

Packaging for reuse

Having done all that hard work, all that remains is to package the code that does the ordering of the hierarchy into its own class so that we can use it over and over again. The following class exposes a Type named OrderedHierarchy that you can use for this very purpose.

To create the OrderedHierarchy Type

  1. save the following code into a file named OrderedHierarchy.vb.
  2. Create and compile the module by typing the following command at the VS.NET command prompt:

    vbc /t:library /r:System.dll,System.Data.dll OrderedHierarchy.vb

You can now save the OrderedHierarchy assembly into the \bin folder of your projects and re-use it.

Option Strict On

Imports System
Imports System.Data

Namespace Samples

    Public Class OrderedHierarchy

        ' Receives a Table and orders the rows based on a specific relationship and
        ' a parent column.
        Public Shared Function GetOrderedTable( _
                        ByVal baseTable As DataTable, _
                        ByVal relationshipName As String, _
                        ByVal parentColumnName As String _
                    ) As DataTable

            ' Configure the ordered table that will be passed out to the client
            Dim tbl As DataTable = baseTable.Clone()
            tbl.Columns.Add(New DataColumn("Depth", GetType(Int32)))
            ComputeHierarchy(tbl, baseTable.Select(parentColumnName & "=0"), 0)
            Return tbl
        End Function

        ' Recursive routine that appends rows to the Private 
        ' table in an ordered manner
        Private Shared Sub ComputeHierarchy( _
                        ByRef orderedTable As DataTable, _
                        ByVal members() As DataRow, _
                        ByVal depth As Int32 _

            Dim member As DataRow
            For Each member In members
                orderedTable.Rows(orderedTable.Rows.Count - 1).Item("Depth") = depth
                ComputeHierarchy(orderedTable, member.GetChildRows("ParentChild"), depth + 1)
        End Sub

    End Class

End Namespace

Click here to view a working sample

There's a couple of excellent articles and ideas that follow on from where this article ends. For an advanced sample that deals with unlimited levels, Teemu Keiski's example is excellent and extends this example by showing how to declaratively wire the data to a TreeView. If you are dealing with just a couple of levels, Thomas Johansen's "Master/Detail" example is second to none!

User Comments

Title: Thanks GOD.   
Name: Buaban
Date: 2006-07-05 10:44:23 AM
Excelent topic, excelent sample.
Title: Very Helpful   
Name: Rick Tilelli
Date: 2006-06-30 12:12:37 PM
This is a great example on datarelations and recursion that relates directly to my current project. Thanks for the help and keep up the good work!!
Title: A canonical hierarchy.   
Name: Sankar
Date: 2006-05-24 8:11:24 AM
canonical hierarchy Recursive Condition is Missing

The Loop Goes Infinte

So Please Help

Mail Me at sankarakps@gmail.com
Title: using listbox?   
Name: Chris
Date: 2005-09-28 8:56:45 AM
I attempted to use this article to create a tree like view
using a drop down list box. but it is blank.

anyone try this?
Title: Student   
Name: Brooks
Date: 2005-02-23 9:22:35 PM
Would you be able to easily append this data to a treeview control, or would it be a little more difficult than that? I saw Teemu Keiski's article, and it focused on C# and ASP.NET, I need to write in VB, so I'm not sure if I can accomplish what they did with that.
Title: Great : 2 remarks   
Name: Bubelah
Date: 2004-10-21 5:15:38 AM
Your example is great thank you !
a) If you use the string parameter "relationshipName" in your GetOrderedTable function, of course you meant to use it in ComputeHierarchy as well.
b) The code does not work for me with idparent=0 but well when it is null. I tried with 0 and i got the error that the relationship cannot be activated because the constraint is not met (i.e not all items have a parent)
The line :
ComputeHierarchy(tbl, baseTable.Select(parentColumnName & "=0"), 0)
ComputeHierarchy(tbl, baseTable.Select(parentColumnName & " is null"), 0)

THEN all is fine, I've been struggling with this for a long time, many many thanks

ps: i'm not an English speaker and did my best:)
Title: There is :)   
Name: Darren Neimke
Date: 2004-08-24 8:25:06 AM
...if only you'd clicked on the link which said: "Working Demo":


Good Luck, and thanks for the positive feedback!
Title: Student   
Name: Esam Ahmed
Date: 2004-08-24 5:30:45 AM
This is an excelent topic that relates to my project problem. I am new at ASP .NET and C#. Currently I am trying to develope a web application part of which deals with Glossary terms of VUI (Voice User Interface). I need to apply hierarchies of the glossary terms before I actually disply the terms and their associated information. It would have been terrific had I have the codes in C#. :):). Peace.

Product Spotlight
Product Spotlight 

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-05-18 9:57:52 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search