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

From Table to TreeView…Displaying Hierarchies

, 11 Feb 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
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

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Steve Wellens
EndWell Software, Inc.
United States United States
I am an independent contractor/consultant working in the Twin Cities area in Minnesota. I work in .Net, Asp.Net, C#, C++, XML, SQL, Windows Forms, HTML, CSS, etc., etc., etc.

Comments and Discussions

 
GeneralAlternative to first trick Pinmembermsmits7-Apr-09 20:19 
GeneralRe: Alternative to first trick PinmemberSteve Wellens8-Apr-09 3:22 
GeneralRe: Alternative to first trick Pinmembermsmits8-Apr-09 4:12 

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 | Mobile
Web01 | 2.8.141022.2 | Last Updated 11 Feb 2013
Article Copyright 2009 by Steve Wellens
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid