TreeView Part 1 - Populating the New TreeView Control from a Hierarchical Database Table

Meet the TreeView – A Brief Introduction

Asp.Net is all about speed and ease-of-use. Microsoft has taken a look at the most common problems that face Web Programmers, and has given us pre-packaged classes to meet those problems with a quick, drag-and-drop solution. The TreeView control is a perfect example of this.

For many years Web Programmers have had to come up with complex programmatic solutions to create a simple hierarchical interface. This involved retrieving data from a database or XML file, and then wrapping our brains around complicated recursive functions that determine the structure, UI design, and event handling for what looks like a simple ‘tree’ structure. Needless to say, it’s a pain, and this led to many 3rd party components being created that you can ‘painlessly’ integrate into your application for a fee.

Thankfully, Microsoft has decided that this is a common enough requirement, that it should in fact be part of the .Net framework. At first, a similar object was available in the unsupported ‘Advanced IE Controls’ package. Of course, it was still flaky, and only worked with Internet Explorer. Now however, it has received a full induction into the .Net class library.

I have found this control to be quite powerful and sufficiently flexible to meet most web programming needs. I say "most" because there are a few limitations, which I’ll address the in Properties configuration section.

This article will explain how to get parent/child styled data into the TreeView Control from a database. Some common examples of this would be in a Content Management System or site map, to display the site structure. Another common use is creating an Organizational Chart, or employee list, which is the approach I’ll take. Alright, let’s do it!

Step 1 – Drag ‘n Drop

If you have the ability to left-click with a mouse and drag, you can install the TreeView Control. Not that you’d be able to do anything with it, but it would be installed…

Actually, to use the control, you will need to download and install the .Net 2.0 framework. At this point it’s in Beta 2. Although it’s optional, I fully recommend that you also download the Web Developer Express Edition, it is free right now as it’s in Beta 2 as well. You can download both here: http://lab.msdn.microsoft.com/express/vwd/default.aspx

So go ahead, install what you need, fire up the IDE, and create your blank web application. Now, just open the toolbox, from which you can simply drag the TreeView object onto your blank (or otherwise) web page. At this point, if you were to save and run the web application, you would see absolutely nothing, as we haven’t configured any options. So let’s do that now.

Switch to HTML/code view. You should see the following:

<asp:TreeView ID="TreeView1" Runat="Server"></asp:TreeView>

The first thing you want to do is to give it a meaningful name. Let’s call it "EmployeeList," which will aptly describe its function and purpose.

Some obvious functionality we want the TreeView to be able to perform is expanding and collapsing of child branches. To implement this is in our object, we need to do nothing at all; this functionality is already provided. By default these actions only occur when you click on the ‘+’ or ‘-’ buttons beside each node. Microsoft is doing their best to not constrain us to their design alone, so you have the option to override the default behavior. You would do this by modifying the SelectAction property of each node (item within the tree), set it to “Expand.” Then the behavior will be similar to Windows Explorer in Windows XP; you’ll see all child nodes when you select a single node.

Another fundamental piece of information that we’ll need to include for each TreeView is what exactly it needs to do when you click on a node. We do this at the object level, by modifying the OnSelectedNodeChanged property, which raises a SelectedNodeChanged event. You would want to point it to a method that will take the selected node value and do something with it. In this case, we’ll have a method called "GetEmployeeInfo," which will coincidentally enough get the information for the employee you select.

So now our TreeView is looking like this:

<asp:TreeView ID="EmployeeList" Runat="Server" OnSelectedNodeChanged="GetEmployeeInfo"></asp:TreeView>

Now if you need to, you can begin modifying the visual aspects of the TreeView. It would be helpful to make the selected node stand out from the rest, and here’s one way to do that:

<SelectedNodeStyle BackColor="#CCC" />

You also have the option to change the default image set. This will replace the standard plus and minus symbols beside each node. You can use any one of a pre-defined, very ‘Microsoft-esque’ set of icons (from MSN to Windows Explorer to Outlook), or you can define your own custom set of icons. You can also implement checkboxes with each node to enable the selection of multiple items. This would be handy for product lists and such. For the sake of simplicity in this article, I’ll stick with the default images, and no textboxes. So you should have the following code on your aspx page:

<asp:TreeView ID="EmployeeList" Runat="Server" OnSelectedNodeChanged="SelectEmployee">
          <SelectedNodeStyle BackColor="#CCC" />
</asp:TreeView>

Also, make sure you have created an empty method called SelectEmployee in your code-behind file, to prevent a compile error. It will look like this:

void SelectEmployee(object s, EventArgs e)
{
          // do stuff
}

Now run your web page. You should get no errors, but you’ll also see absolutely nothing. This is because we haven’t populated the tree yet. Let’s do that now.

Step 2 - Populating the Data

Open your code behind file. I will be using C# in this tutorial, but you should have no trouble converting the code to VB.Net if you need to. First let’s reference the proper namespaces, and create a DataSet to hold the data.

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Caching;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

DataSet EmployeeDS = new DataSet();

Now, we need to configure the page to pull the data on first load, dump it into the DataSet, then into the TreeView. The best way to accomplish this is by separating out the data pull and tree population into two separate methods. The two primary reasons for this are:

The method for populating the TreeView is recursive. We only need to hit the database once, so we would have to build expensive conditional logic into the recursive method for that to happen. It’s easiest to create a separate function to pull the data and only call it once.

If there is an error in either step, it’s far easier to isolate if we have separate, distinct functions to debug.

So here’s the code to do this on the first page load:

void page_load(object sender, EventArgs e)
{
        if (!Page.IsPostBack)
        {
               // first load DataSet
               LoadDataSet();

               // now build list
               BuildEmployeeList(EmployeeList.Nodes, 0);
        }
}

Now here’s the code to load the DataSet. It’s pretty basic stuff, so I won’t go into any deep explanation. Just to explain, I’m assuming that you’re storing your connection string in the web.config file as is the best practice, and that’s where the ‘conn’ variable is reading from.

void LoadDataSet()
{
    String conn =
        ConfigurationSettings.AppSettings["connStr"];

    String strSQL =
        "SELECT id, name, mgr_id FROM [employees]";

    SqlDataAdapter myAdapter =
        new SqlDataAdapter(strSQL, conn);

    myAdapter.Fill(EmployeeDS, "employees");
}

So now we have all the necessary employee data in the DataSet. You can feel free to modify that step all you need to, build in whatever error handling logic and connection closing steps you’d like.  Now we’ll get into the recursive function to populate the tree.

Step 3 - Building the Tree

To explain first of all, we need to build the tree node by node. We specify to start with the Tree’s root node set, and start with a parent id of ‘0’. This would be the president of the company, the home directory of a site, the root of a product list, etc. Then the DataSet is searched for any items with that parent id (in this case, the ‘mgr_id’). Any found are placed into the tree as a node. Then the function calls itself with that employee’s id as the parent id. Any children (reporting employees, sub-directories) are populated, and the recursion takes place for each. Each time we get to a node with no children (a leaf node) the function exits, and continues populating children nodes for the previous parent, all the way back to the root. Now here’s all that in code:

private void BuildEmployeeList(TreeNodeCollection nodes,
Int32 IntParent)
{

    Int32 ThisID;

    String ThisName;

    DataRow[] children =
        EmployeeDS.Tables["employees"].Select
        ("mgr_id='"+IntParent+"'");

    //no child nodes, exit function
    if (children.Length == 0) return;

    foreach (DataRow child in children)
    {
        // step 1
        ThisID = Convert.ToInt32(child.ItemArray[0]);

        // step 2
        ThisName = Convert.ToString(child.ItemArray[1]);

        // step 3
        TreeNode NewNode =
            new TreeNode(ThisName, ThisID.ToString());

        // step 4
        nodes.Add(NewNode);
        NewNode.ToggleExpandState();

        // step 5
        BuildEmployeeList(NewNode.ChildNodes, ThisID);
    }
}

Rather than break up the code with explanation, here’s each step explained so that you can understand what’s happening where, in case you need to modify or debug the code for you own purposes.

Step #

1. Here we retrieve the id of the current employee for use in creating the node and feeding back in the recursive call to find any subordinates.
2. The name is retrieved, more for display purposes than anything functional.
3. Here a new TreeNode is created on the fly, with the name as the display text, and the id as the string value. This is necessary for the SelectEmployee method we created earlier. Within that method, you would parse that unique id value, and use it to retrieve the information on that specific employee from the database.
4. The node is added to the Tree. Then we toggle the expanded state, which both sets it to collapsed, and actually gives it an expanded property value. This will save you many headaches later.
5. Now we recursively call the same method, now using the current employee’s id as the parent id, and the current node as the parent node. If no children are found, the method will simply move on to the next ‘sibling’ node.

Conclusion

There you have it, a fully functioning TreeView. Well, functioning in that it will actually display the employee data in a hierarchical manner when you load the page.

There is still much functionality we could add. In my next article, I’ll explain how to select a specific employee from the list, perhaps by a variable passed in the query string, and retrieve data for that employee. Until then, have fun!