Click here to Skip to main content
Click here to Skip to main content

Loading a TreeView using HierarchyID

, 5 Jun 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
Using SQL Server 2008 Hierarchy ID data type to populate a TreeView
Tree.jpg

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.  

Grid.jpg

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 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Mycroft Holmes
Software Developer (Senior) Contractor
Singapore Singapore
Started my programming life writing Excel 1.0 macros, God what a long time ago.
 
Now I'm a dotnet developer, I get to influence direction, play with new toys, build stuff, life is wonderful.
 
Greatest buzz you can get, walk past a row of desks and see your application running on all of them (and getting paid).
 
Greatest irritant, pouring 12 months of knowledge and experience into an empty head only to have it leave.

Comments and Discussions

 
QuestionHierarchy id with treeview Pinmemberbalaguru28-Jan-13 0:50 
Anybody have worked with hierarchyid and treeview in asp.net?
Questionwebform Pinmemberalaaomer12310-Dec-10 2:29 
AnswerRe: webform PinmvpMycroft Holmes10-Dec-10 12:08 
QuestionLoad on Demand PinmemberAGM201019-Nov-10 0:35 
AnswerRe: Load on Demand PinmvpMycroft Holmes19-Nov-10 10:28 
GeneralTreeNodeGetTree Pinmemberdavea3328-Jul-09 17:17 
GeneralRe: TreeNodeGetTree PinmemberMycroft Holmes28-Jul-09 17:57 
GeneralSome improvements Pinmembernikola.morena15-Jul-09 3:33 
GeneralRe: Some improvements PinmemberMycroft Holmes15-Jul-09 14:12 
GeneralMy vote of 5! Pinmemberjonasgranlund12-Jun-09 3:48 
GeneralRe: My vote of 5! PinmemberMycroft Holmes12-Jun-09 14:10 
GeneralRe: My vote of 5! Pinmemberdavea3328-Jul-09 17:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.141022.2 | Last Updated 5 Jun 2009
Article Copyright 2009 by Mycroft Holmes
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid