Loading a TreeView using HierarchyID

• Download source - 29.54 KB 

Introduction

This article demonstrates how to load a TreeView based on the new hierarchyID data type in SQL Server 2008. My previous methods of loading a TreeView were either with a dataset using the data relations or a table with a parent/child hierarchy.  

I have found that you need to put a limit to the number of nodes in a TreeView to retain loading performance. With the hierarchyid, that limit has been raised. I was pleasantly surprised by the speed of the load.

This article assumes you have constructed a result set using the hierarchyID in SQL 2008. The sample table, stored as XML, is of a realistic size and complexity as it is based on actual data that has been sanitised. Constructing the result set is a challenge all unto itself and is too individualistic to be covered here. 

Background 

MSDN has some surprisingly useful samples and tutorials.    

Using the Code

Before anything else, you need to reference the Microsoft types as the hierarchyid is not a SQLDBType as you would expect. 

using Microsoft.SqlServer.Types; 

Getting the Data 

Once again I have split out the data class and used an XML sample data file for this demo. Note that when loading the data from a text system, the hierarchyID needs to be converted from text before the ID is useful.

//convert the string back into a hierarchyid
oRow["NodeKey"] = SqlHierarchyId.Parse((string)oRow["NodeString"]);

I load this data into the datagridview so we can inspect the contents before loading the TreeView. Note the structure of the hierarchyID forward slash separated id values.  

There is a note here about constructing the hierarchyID in SQL Server, all the Microsoft tutorials use the id number to designate the order of the nodes within the level of the hierarchy. This presented a problem as I need to add nodes from the UI and duplicate nodes are not allowed naturally so I would have to do a bit of dancing around the IDs in the construction of nodes in the UI.  

My answer was to use the IDs from the database. I used the order in the select statement to order the display and I have no requirement to order the data within a level in a hierarchy. This could also have been achieved in the LINQ filter but I am most comfortable with TSQL.

Loading the TreeView 

Naturally the hierachyID functions cannot be used in a dataview filter or a datatable select, functions have limited support. So I needed to find another way to identify the parent/children for loading into the TreeView. I had a look at using the NodeString field with string.contains and counting the number of delimiters (would have worked too).  

It did not seem right that Microsoft would have implemented the hierarchyid in SQL server and not have it in the CLR, after all you are supposed to be able to integrate the two. So a few more hours of hunting produced the reference to the SQLTypes and from there the step to LINQ was obvious. 

I have constructed the LoadTreeSQLHierarchy and LoadNodeSQLHierarchy in such a way that they can be moved to a utilities class.

private void LoadTreeSQLHierarchy(TreeView oTV, DataTable oTable, 
		string sKeyField, string sTextField)
{
	oTV.Nodes.Clear();

	TreeNode oNode;

	//get an empty id to get the top node
	SqlHierarchyId iID = new SqlHierarchyId();

	//filter the table using linq. See blog for equals()/== issue
	EnumerableRowCollection<DataRow> query = 
		from TNodes in oTable.AsEnumerable()
		where TNodes.Field<SqlHierarchyId>
			(sKeyField).GetAncestor(1).Equals(iID)
		select TNodes;

	//convert to a dataview because I am comfortable with a dataview.
	DataView oDV = query.AsDataView();
	if (oDV.Count == 1)
	{
		//load up a node
		oNode = new TreeNode(oDV[0][sTextField].ToString());

		//put the datarow into the tag property
		oNode.Tag = oDV[0].Row;

		//load up the children
		LoadNodeSQLHierarchy(oNode, oTable);

		//add the node hierarchy to the tree
		oTV.Nodes.Add(oNode);
	}
} 

By using an empty hierarchyID and the GetAncestor(1) in the initial LINQ query, we retrieve the top node. This would allow us to use one method to load the tree and the nodes but we would need to deal with the different types TreeView and TreeNode. I find it simpler to support using two methods. 

I picked up the .Equals() requirement from here.

And because I am more familiar with the data structures, I use a DataView to populate the top node of the treeview. Once I have the top node, I can then recursively call the LoadNodeSQLHierarchy to populate the entire structure. 

Another benefit to using the dataview is that I can put the datarow into the tag property of the node and have it available when processing the tree in the UI. 

Points of Interest 

What I really dislike about the hierarchyID is that it is not a SQLDBType and therefore breaks my ORM layer which is based on stored procedures and the SQLDBTypes. 

History 

• First version