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++;