Fast Updating of Treeview Control With a Database in VB.NET

Introduction

There is not a lot of documentation dealing with the use of the .NET Treeview control except on CodeProject. What I have found is either old code dealing with the COM Treeview control, or articles written using recursive algorithms (which are cool, BTW). What I needed was a fast, lightweight method to fill the nodes in a tree dynamically as the user clicks on them (also known as load or populate on demand). This is the behavior one encounters when you are using Windows explorer's tree to browse network shares. You do not get an indication of possible node expansion (+ symbol) until you actually click on it. I deemed this behavior acceptable for the application I was writing, since the source database contains thousands of records. Selective recursion on an expanded node might be an option or deciding to recurse based on the number of records returned by a query, and I welcome any comments on that topic.

I have made changes to the code to load one level ahead--the algorithms now load the grandchildren of the node being selected. This duplicates the behavior of the Windows Explorer tree while browsing local drives.

Since this my first submission, I will not attempt to include working code or a project sample, just code snippets to demonstrate the method.

It is beyond the scope of the article to teach the reader how to use ADO.NET to do what is presented. There are plenty of good articles on the net to learn this.

Background

One can search the internet for code snippets dealing with recursive methods that fill out the entire tree, one is here. Note that the author is using ASP.NET, and is using the table adapter scheme in ADO.NET, which I consider overkill for simply filling out nodes, unless the developer intends to allow the user to edit and save the node labels. What is instructive is the database table scheme to represent folder relationships--a methodology I have been using for some time, and one that is demonstrated in this article. It's powerful, for example, one could allow the user to drag/drop folders as in Windows explorer and all that would be required is a single record update in the database to effect the operation. With transaction processing, undos are available to the user, and strongly recommended.

This is the database table scheme I often use to represent hierarchical relationships.

Using the Code

These code snippets will not compile as is. They are used to demonstrate a method.

Some assumptions will be made here. One is that you are familiar with opening Access or SQL Server databases using ADO.NET. We will be using the IEnumerator object to iterate through the records, filling the Treeview nodes. DataReader and IEnumerator are very fast, read-only, forward-only recordset-like objects perfect for just this sort of thing.

Assume we have opened a query against the database that either reads the records to initialize the treeview with the root nodes or one that uses a parameter or stored procedure argument to fill the children of a selected node. Having done that, we want to create a SQLDbDataReader or OleDbDataReader object and return an IEnumerator by calling GetEnumerator on it. Here we get a data enumerator to fill out the root nodes:

Public dR As SQL(or Ole)DbDataReader

Public Function GetRootFolders() As IEnumerator

Dim dbCommandObject As SQL(or Ole)DbCommand

' specify command text and parameters, open the connection, etc.

dR = dbCommandObject.ExecuteReader()

If dR IsNot Nothing And Not dR.IsClosed Then
  Return dR.GetEnumerator()
End If
Return Nothing
End Function

We can fill the root nodes like this. One could create a single node and wait for the user to click on it--strictly a design decision.

Private Sub RefreshTree()
    treeView.Nodes.Clear()
    Dim iE As IEnumerator = GetRootFolders()
    Do While iE.MoveNext()
        If iE.Current IsNot Nothing Then
            Dim r As DbDataRecord = CType(iE.Current, DbDataRecord)
            treeView.Nodes.Add(r("Folder_ID").ToString, _
		r("DiplayText").ToString, "folderclose", "folderopen")
        End If
    Loop
    dR.Close()
    '// add children of root nodes
    If treeMain.Nodes.Count > 0 Then
         For Each nd As TreeNode In treeMain.Nodes
             iE = pDB.GetChildFolders(CInt(nd.Name))
             Do While iE.MoveNext()
                 If iE.Current IsNot Nothing Then
                     Dim r As DbDataRecord = CType(iE.Current, DbDataRecord)
                     If Not nd.Nodes.ContainsKey(r("Folder_ID").ToString) Then
                         nd.Nodes.Add(r("Folder_ID").ToString, _
			r("DisplayText").ToString, "folderclose", "folderopen")
                     End If
                 End If
             Loop
             dR.Close()
         Next
    End If
End Sub

Note the ugly cast to DbDataRecord. Be sure to test the current record for nothingness or an exception can be thrown if it is. The Nodes.Add method we are using will stuff the primary key (Folder_ID) of the record into the node's Name member (retrieved later as Key or Name), the node's Text member from the database field called "DisplayText", and specify some icons from an ImageList attached to the TreeView in the form designer.

Notice that I have added a for loop to add the children of the root nodes. The collection the for loop is working are the root nodes just added.

So, if a user clicks on one of the root nodes just created, we handle it with this:

 Private Sub treeView_NodeMouseClick(ByVal sender As Object, _
	ByVal e As TreeNodeMouseClickEventArgs) Handles treeView.NodeMouseClick
     Dim iE As IEnumerator
     '// add the children of the children just added
     If e.Node.Nodes.Count > 0 Then
         For Each nd As TreeNode In e.Node.Nodes
             iE = GetChildFolders(CInt(nd.Name))
             Do While iE.MoveNext()
                 If iE.Current IsNot Nothing Then
                     Dim r As DbDataRecord = CType(iE.Current, DbDataRecord)
                     If Not nd.Nodes.ContainsKey(r("Folder_ID").ToString) Then
                         nd.Nodes.Add(r("Folder_ID").ToString, _
			r("DisplayText").ToString, "folderclose", "folderopen")
                     End If
                 End If
             Loop
             dR.Close()
         Next
     End If
End Sub

We get the clicked Node's Name member from the event and use it as a parameter for the database query. In the .NET treeview control, a node, in a sense, is a fully-fledged tree and we can query its children easily. Using the ContainsKey method, we look to see if the particular node we want to add has, in fact, already been added. If so, we skip, otherwise we use the selected node's Nodes.Add method as in the root folder code.

Note here too that I have changed the for loop to add the grandchildren of the selected node, instead of just the children as in the prior snippet posted here before 4/6/2010.

Points of Interest

It was annoying to find so little information and/or lack of understanding about the .NET Treeview control. As was the case with the COM Treeview, MSDN was appalling. During all this, I learned some things about the .NET Treeview control. Treeview is indispensable when you need to present a hierarchical scheme to the user. I posted this article to give back, in some small measure, to the community from whom I have learned so much about other things.

History

When I have the time, I would like to create a demo project using a simple Access database, an OleDbclass or SqlDbClass wrapper class, and its handler class that inherits from the BackgroundWorker thread object.

With the addition of the code (4/6/2010), we have demonstrated the ability to get fast updates to the tree in a user-friendly manner without resorting to recursion of the entire tree.