Steve Wellens

Programming in the .Net environment

Sponsors

Links

From Table to TreeView…Displaying Hierarchies

Who remembers Object Databases? At one time they were going to dominate the programming world. But, as with a lot of over-hyped technology (Lisp, AI, ADA, Virtual Reality) they were destined to a small niche segment of the market.

Relational Databases remain king. Why? Because Relational Databases are easy to understand and they work good…real good.

Still, many times hierarchical data is stored in relational databases. A common example is an organizational chart. This is usually stored in a single Employee table. Each table row represents an employee with an ID field and a ParentID (Boss) field.

This code shows how to go from a hierarchical table to a TreeView. There is nothing sophisticated here, but a few tricks are needed to make it work.

The first trick is to sort the records by the ParentID. We cannot insert a node into the tree until its parent node is in the tree.  This implies a special case where the root node of the tree must be inserted first since it has no parent.

Here is sample data:

// Create the DataTable and columns
DataTable ItemTable = new DataTable("MyTable");
ItemTable.Columns.Add("ID"      , typeof(int   ));
ItemTable.Columns.Add("ParentID", typeof(int   ));
ItemTable.Columns.Add("Name"    , typeof(String));
 
// add some test data
ItemTable.Rows.Add(new object[] { 0,-1, "Bill Gates"    });
ItemTable.Rows.Add(new object[] { 1, 0, "Steve Ballmer" });
ItemTable.Rows.Add(new object[] { 3, 1, "Mary Smith"    });
ItemTable.Rows.Add(new object[] { 2, 0, "Paul Allen"    });
ItemTable.Rows.Add(new object[] { 4, 2, "Ahmed Jones"   });
ItemTable.Rows.Add(new object[] { 5, 2, "Wing Lee"      });
 
// Use the Select method to sort the rows by ParentID
DataRow[] SortedRows;
SortedRows = ItemTable.Select("", "ParentID");

The second trick is the use of XmlDocument. Initially, I tried to go directly to a TreeView without the intermediate data object. But the TreeView control does not have functionality to find an arbitrary node given a node ID. Writing a recursive find function seemed like a lot of work so we take advantage of the XPath search capabilities of XmlDocument to find an arbitrary Parent node.

// create an XmlDocument (with an XML declaration)
XmlDocument XDoc = new XmlDocument();
XmlDeclaration XDec = XDoc.CreateXmlDeclaration("1.0", null, null);
XDoc.AppendChild(XDec);
 
// iterate through the sorted data
// and build the XML document
foreach (DataRow Row in SortedRows)
{
    // create an element node to insert
    // note: Element names may not have spaces so use ID
    // note: Element names may not start with a digit so add underscore
    XmlElement NewNode = XDoc.CreateElement("_" + Row["ID"].ToString());
    NewNode.SetAttribute("ID"      , Row["ID"].ToString());
    NewNode.SetAttribute("ParentID", Row["ParentID"].ToString());
    NewNode.SetAttribute("FullName", Row["Name"].ToString());
 
    // special case for top level node
    if ((int)Row["ParentID"] == -1)
        XDoc.AppendChild(NewNode);  // root node
    else
    {
        // use XPath to find the parent node in the tree
        String SearchString;
        SearchString = String.Format("//*[@ID=\"{0}\"] ", Row["ParentID"].ToString());
        XmlNode Parent = XDoc.SelectSingleNode(SearchString);
 
        if (Parent != null)
            Parent.AppendChild(NewNode);
        else
            ; // Handle Error: Employee with no boss
    }
}

The third trick deals with an error in the documentation. A TreeView (or a Menu) cannot be data bound to an XmlDocument. Doing so produces this error: HierarchicalDataBoundControl only accepts data sources that implement IHierarchicalDataSource or IHierarchicalEnumerable. The workaround is to create an XmlDataSource control dynamically and assign the XML string to it.

// we cannot bind the TreeView directly to an XmlDocument
// so we must create an XmlDataSource and assign the XML text
XmlDataSource XDdataSource = new XmlDataSource();
XDdataSource.ID = DateTime.Now.Ticks.ToString();  // unique ID is required
XDdataSource.Data = XDoc.OuterXml;
 
// we want the full name displayed in the tree so 
// do custom databindings
TreeNodeBinding Binding = new TreeNodeBinding();
Binding.TextField = "FullName";
Binding.ValueField = "ID";
TreeView1.DataBindings.Add(Binding);
 
// Finally! Hook that bad boy up!       
TreeView1.DataSource = XDdataSource;
TreeView1.DataBind();

After all that work, the results:

I hope you find this useful,

Steve Wellens.

PS: Happy 2008++;

Comments

rsaggar said:

Can this be nested any level deep?

Can you post a vb code ?

# March 23, 2009 4:58 PM

SGWellens said:

>> Can this be nested any level deep?

Yes, until you run out of memory.

>> Can you post a vb code ?

There are many C# to VB converters online.

# March 23, 2009 5:26 PM

Eugene said:

God bless you mate

# July 28, 2009 11:32 PM

Kalle said:

Super, a much easier way then i am working with today.

# August 4, 2009 9:03 AM

saber said:

Thanks :)

It really helped me.

I was going to write a recursive method and add the items one by one!

# September 28, 2009 6:05 PM

Joseph said:

This seemed to work very well except if the parent id is not in order. Our table has an employee id and a supervisor id. Any ideas on how to make this same code work based on those two id's?

# January 6, 2010 12:33 PM

SGWellens said:

Re-read the paragraph that starts with:

"The first trick is to sort the records by the ParentID."

You can do this in the SQL statement.

# January 6, 2010 1:11 PM

David M. said:

Just read your approach... that's great!  Fantastic way to implement a treeview without a lot of verbose coding... thanks!

# April 23, 2010 2:04 PM

Jezbobaggins said:

Wow! many thanks - excellent stuff.

# April 27, 2010 12:51 PM

elizabeth said:

thank you thank you thank you

# October 4, 2010 11:21 AM

MKar said:

Perfect!... I tried so many different solution, for my ASP.NET page, but this one worked like a butter... Thanks very much

# December 5, 2011 4:00 AM

vinoth said:

really great work dude... thanks a lot...

# February 29, 2012 1:47 AM