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

Though the title is a mouthful it succinctly describes what will prove to be the most common use of the new TreeView control shipping with ASP.Net 2.0. The control will be perfect for implementing hierarchical structures in a User Interface, and this tutorial will show you how to actually get the data into the control from a ‘parent-child’ 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!

{mospagebreak title=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.

{mospagebreak title=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.

{mospagebreak title=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!

14 thoughts on “TreeView Part 1 – Populating the New TreeView Control from a Hierarchical Database Table

  1. It says in the article “you will have no trouble converting it into VB”.

    Wrong. Please dont assume that everyone knows C. There are a lot of people out there using VB without knowledge of C. It would be nice if, like some sites, snippets using both languages were listed. If not, please list at the beginning of the tutorial which language is used so we dont waste our time.

    Therefore, this tutorial was a waste of my time.

  2. A waste of time??

    The aritcle describes the process and principles behind the code. If you are unable to understand that, or unable to comprehend recursion, then you are wasting your time as a programmer, period.

  3. It is a good article but very high level cause was hoping to see more complex treetable with loading rules & updating rules.
    But was not waste of time at ALL!

  4. Was a wonderful article and it really helped me.
    Thank you. Was wondering if we could show the tree with the expand depth as 1 at load of the page

  5. 1: I never knew C-sharp had a project much more complex than this and converted it NO PROBLEM. Conversion has nothing to do with the Language as others have pointed out – it has to do with knowing WHAT the code is trying to do.. and the author explained – A FOR EACH statement and IF then statement – ALL exist in VB .. the only difference is mostly syntax of the languages.

    Besides there are many C-VB and VB-C converters that will do most of the work for you..
    I agree with the Author…

  6. I get an error that specifies that it can’t find EmployeeList

    in the Page_Load method … BuildEmployeeList(EmployeeList.Nodes,0);

  7. Best tut on the TreeView control to “n” I have seen so far. After hours of hopeless searching, I found this article and within 5 minutes had it up and running to my specifications. Thank you very much!!

  8. Hi,

    I love this tutorial because it’s simple but powerful.
    However,when I ran the default.aspx page I received this error message. Can someone please help?

    CS1061: ‘ASP.default_aspx’ does not contain a definition for ‘SelectEmployee’ and no extension method ‘SelectEmployee’ accepting a first argument of type ‘ASP.default_aspx’ could be found (are you missing a using directive or an assembly reference?)

  9. The author appreciates your comments and that’s why he/she provides space for the audience to provide comments. However, the author doesn’t appreciate comments such as “this is a waste of time” or things in that nature. You have the right to disagree with the author about the way he/she presents his/her code but saying “this is a waste of time” is not nice. The author spends time to share his code so that you or other people can learn from his tutorials and that’s very nice of the author already.

    I personally like his code because I have been searching for days for a tutorial like this and couldn’t find anywhere else but this website. Even though the code is not working for me yet but I am still happy because I can see his logic behind.

  10. Hi All,

    Thanks to the author for sharing a pretty good article. I tried to follow the tutorials but I didn’t know what I was doing wrong. When I ran the .aspx page, it displayed nothing. There was no error though, the page was just empty. I appreciate any help anyone can provide.

    I created a table called Employees with 3 columns: ID, name, mgr_id.
    Here is some data samples in this table

    1, lisa, 1
    2, jane, 2
    3, george, 2
    4, bill, 2

    Here is the code for default.aspx page


    Here is the code behind:
    public partial class _Default : System.Web.UI.Page
    {
    DataSet EmployeeDS = new DataSet();
    protected void SelectEmployee(object s, EventArgs e)
    {
    // do stuff
    }
    protected void Page_Load(object sender, EventArgs e)
    {
    if (!Page.IsPostBack)
    {
    // first load DataSet
    LoadDataSet();

    // now build list
    BuildEmployeeList(EmployeeList.Nodes, 0);
    }
    }
    void LoadDataSet()
    {
    string conStr = WebConfigurationManager.ConnectionStrings["NorthWind"].ConnectionString;
    SqlConnection conn = new SqlConnection(conStr);
    //String conn =ConfigurationSettings.AppSettings["Northwind"];
    string strSQL = “SELECT EmployeeID, FirstName, mgr_id FROM [EmployeesTable]“;
    SqlDataAdapter myAdapter = new SqlDataAdapter(strSQL, conn);
    myAdapter.Fill(EmployeeDS, “EmployeesTable”);
    }
    private void BuildEmployeeList(TreeNodeCollection nodes, Int32 IntParent)
    {
    Int32 ThisID;
    string ThisName;
    DataRow[] children = EmployeeDS.Tables["EmployeesTable"].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);
    }
    }
    }

  11. Thank you. Thank you. Worked like a charm! Love the fact that your article doesn’t use an XML file.

    Added imageset=’Contacts’ and it looks even better.

    Keep up the good work.

    Brian

[gp-comments width="770" linklove="off" ]