Using Nested Sets with  the Treeview

If you’re like me, one day Joe Celko exposed you to Michael J. Kamfonas’ Nested Sets model for trees in SQL on the Usenet, and it changed your life. So you have an easy way of expressing hierarchical data in SQL, with ordered sibling nodes, no less.

A decade or so later, we find ourselves trying to integrate our database design patterns with the .NET controls. Data-binding handles a lot of the functional requirements for you, but you have to do it pretty much the way that Microsoft expects you to, which you’ll understand if you try to set the DataSource on a FormView control. We have to give up middle layer logic and tie our user interface directly to the database objects, which is not generally a good idea, but not always worth avoiding.

The .NET control for hierarchical data is the TreeView, and the TreeView requires an IHierarchicalDataSource object. However, if you try to do this through the GUI, you’ll see there is no way that is provided to add a connection to a SQL Server database.

Figure 1 TreeView control in Toolbox under ‘Navigation’

 

Figure 2: Options for a TreeView data source

It seems nested sets aren’t so pervasive that .NET provides a UI component set. The point of this article, though, is to fill that gap. So, let’s back up a little.

Populating a Treeview with Nested sets

The Database bit.

In the nested set model, every node has two integer values which (after Celko) I call lft and rgt . rgt is always greater than lft , but only one greater for leaf nodes. If one node’s lft is less than another node’s lft , and the first node’s rgt is greater than the next node’s rgt , the first node is an ancestor of the second node. There is a lot more to nested sets, and many wonderful reasons to use them. However, this article is about showing them in a .NET TreeView, so I’ll refer you to Celko’s “SQL for Smarties” or “Trees and Hierarchies in SQL For Smarties” for a discussion. Now, we’ll just make one, with data enough to populate a TreeView.

That code contains a few different sections

1. Making the data tables. We make one table to contain the data and another to locate it in the tree. This can be one table for simple implementations. Note that for a production implementation, you might want security, auditing and historical state functions, which are not included here
2. This section starts with the GetParentPath function. It provides the ‘select’ functionality for the TreeView control, for which we’ll shortly get to providing a client.
3. The third section adds some data, leaning on some free icons I found on the web. Any practical implementation is going to provide Create/Review/Update/Delete features, but since the TreeView doesn’t provide a way to use them, they’re out of scope here.

The Middle Bit

So, now, all that’s left to do is write some helper functions for the IHierarchicalDataSource , the IHierarchicalDataSource itself, and tie it to a TreeView. Really, we’ve defined the table, so the heavy lifting is done. I don’t know what those application developers go on about.

What’s in an IHierarchicalDataSource ? The documentation says ” The interface defines a single method, GetHierarchicalView, which retrieves a strongly typed HierarchicalDataSourceView object.” Our HierarchicalDataSourceView implementation, in turn, must override HierarchicalDataSourceView.Select and return a collection implementing IHierarchicalEnumerable. So, let’s define our IHierarchicalEnumerable.

I added the name of the select stored procedure to the object as a static property. The public fields consumed by the .NET infrastructure, including those mapped to column names, are all properties, because you otherwise risk “Could not find a property named…” errors.

We’re now ready to code a HierarchicalDataSourceView.

The Select method builds the hierarchy and loads the root node into the return value. It also loads every other node into that node’s parent’s Children collection, which we defined as part of the SQLTreeNodeData implementation of IHierachyData. That Select method is the most coding that gets done; most of this implementation is just getting the objects in the right places.

Unlike with identifiers like the NodeID, there’s no philosophical objection to exposing lft and rgt to the data consumer. They’re meaningful numbers describing the node position in any context. We certainly could have retrieved the data as lft , rgt , Text, et cetera, instead of building the path enumeration in the database, but the logic for matching parents to children in C# would have been somewhat hairier. Any node can get its immediate children from the TreeNode table with

 

where @lft is the node’s lft value and @rgt is the node’s rgt value, and @LEVEL is the number of the node’s ancestors, or

but you can see that that’s starting to become a large number of database round-trips. This is why we’re getting all the nodes at once.

Or, nearly. There’s a hidden feature in the code as written that lets you start your tree from any node. Wiring that in to the front-end code is left as an exercise to the reader.

With a principle of reducing impact on data resources in mind, you might prefer to load the data into a detached data set and close the connection before doing anything else at all. However, in this case, all that’s being done is the building of a number of ArrayList objects — there’s little faster. Building the hierarchy of SQLTreeNodeData objects as you navigate the open cursor is not even necessarily more demanding that the construction of an offline DataSet would be.

Our only remaining component to provide a data source for a TreeView is the DataSource itself. So…

We now have a HierarchicalDataSourceControl object that can be used declaratively, so let’s take advantage of that.

The Front End Bit

With a registered and included SQLTreeNodeDataSource control, my aspx page now looks like

All that’s left, really, is to bind the SQLTreeNodeData fields to TreeNode properties, which we do through a property page on the TreeView called DataBindings.

Figure  3… Property Page for the TreeView Control

Click on the ellipses in the Property Page for the TreeView to launch the TreeViewDataBindingsEditor.

Figure 4…TreeViewDataBindings Editor

Click the ‘Add’ button to create a TreeViewDataBindings object.

Figure  5… TreeViewBindings Editor with bindings defined

Populate the fields of the new TreeViewDataBindings. The DataMember field should be the return value of the ‘Type’ property of the SQLTreeNodeData objects. This can be useful as a filter, but as I’ve implemented it each object just returns the class name, so this field could just as well be left blank. The ImageUrlField, TextField and ValueField have the obvious mappings, and the ValueField is set to text so as to enable the Find method for nodes. This is also why it was so important that we use a declarative data source with DataSourceID instead of programmatically setting the DataSource property of the TreeView — Find does not work otherwise, and we are going to need it to.  Not to foreshadow.

You now have a working TreeView based on hierarchical data stored as nested sets in the database.

We should take a moment to savor this before addressing that nagging feeling that we’ve defined a column and returned it in our stored procedure without displaying it on the page.

Adding Checkboxes

There comes a time in every .NET implementation — or at least every one I have done to date — where patiently sorting out how to connect all the plumbing to meet your specified requirements suddenly blows up into a red-eyed tragicomedy. One day you’re tending sheep for Laban, then suddenly you’re taking blows to your hip socket in a tent at Peniel.

And so it is with checkboxes. You can show them easily enough by setting ‘ShowCheckBoxes’ to Leaf, All or whatever you please.

The difficulties — or ‘opportunities for excellence’ — start to come when you try to do things like handle OnCheck events, or even set them to checked. I will show you how to do the latter  here. The former is still beyond my reach.

OnDataBound

.NET data-bound controls provide ‘OnDataBinding’ and ‘OnDataBound’ events, to modify states before or after a control updates itself with retrieved data respectively. We can implement any event handler by clicking through the event part of the property page.

Figure  8… Event handlers for the TreeView control

Triple click in the method name field for the DataBound event handler, and Visual Studio 2005 will create the handler and give it focus in the code editor.

 

This justifies our LastReturned read-only HierarchicalEnumerable-valued property procedures for the SQLTreeNodeView and SQLTreeNodeDataSource objects. If we hadn’t get a copy of those objects when creating them for the TreeView’s consumption, we would have to return to the database to get the data again. Our other option would be to store the ‘Checked’ value in some otherwise irrelevant property, perhaps binding it with TargetField, but we have to realize that the days of storing obscure XML snippets in the tag property of controls are gone.

In any case, we have now succeeded in our task as set.

Thanks for reading, and I hope you enjoy your many .NET TreeView controls populated from nested sets!