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.
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.
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.
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
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
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)
SqlHierarchyId iID = new SqlHierarchyId();
EnumerableRowCollection<DataRow> query =
from TNodes in oTable.AsEnumerable()
DataView oDV = query.AsDataView();
if (oDV.Count == 1)
oNode = new TreeNode(oDV[sTextField].ToString());
oNode.Tag = oDV.Row;
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
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