Click here to Skip to main content
15,867,885 members
Articles / Desktop Programming / Windows Forms

Loading a TreeView using HierarchyID

Rate me:
Please Sign up or sign in to vote.
4.55/5 (9 votes)
5 Jun 2009CPOL3 min read 117.4K   5.1K   64   17
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. 

C#
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.

C#
//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.

C#
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)


Written By
Retired None
Australia Australia
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.

And now I'm retired, no deadlines, no meetings, no managers (except the ONE) and no users!

Comments and Discussions

 
Questionhow to update tree view and save in data base Pin
D@rk Knight19-Apr-20 22:43
D@rk Knight19-Apr-20 22:43 
QuestionCast Error Pin
Roozbeh Amiressami23-May-18 1:46
Roozbeh Amiressami23-May-18 1:46 
QuestionAdding ID of every node Pin
pawellrus6-Feb-17 1:29
pawellrus6-Feb-17 1:29 
QuestionHierarchy id with treeview Pin
balaguru28-Jan-13 0:50
balaguru28-Jan-13 0:50 
Questionwebform Pin
alaaomer12310-Dec-10 2:29
alaaomer12310-Dec-10 2:29 
AnswerRe: webform Pin
Mycroft Holmes10-Dec-10 12:08
professionalMycroft Holmes10-Dec-10 12:08 
QuestionLoad on Demand Pin
AGM201019-Nov-10 0:35
AGM201019-Nov-10 0:35 
Is it possible to do a Load on Demand?
i.e. Child nodes get populated only on parent node click. This will avoid some intial heavy lifting and improve performance?
I'm using the same technique in ASP.net.

Thanks
AnswerRe: Load on Demand Pin
Mycroft Holmes19-Nov-10 10:28
professionalMycroft Holmes19-Nov-10 10:28 
GeneralTreeNodeGetTree Pin
davea3328-Jul-09 17:17
davea3328-Jul-09 17:17 
GeneralRe: TreeNodeGetTree Pin
Mycroft Holmes28-Jul-09 17:57
professionalMycroft Holmes28-Jul-09 17:57 
GeneralRe: TreeNodeGetTree Pin
pawellrus31-Jan-17 2:45
pawellrus31-Jan-17 2:45 
GeneralRe: TreeNodeGetTree Pin
Mycroft Holmes31-Jan-17 9:55
professionalMycroft Holmes31-Jan-17 9:55 
GeneralSome improvements Pin
nikola.morena15-Jul-09 3:33
nikola.morena15-Jul-09 3:33 
GeneralRe: Some improvements Pin
Mycroft Holmes15-Jul-09 14:12
professionalMycroft Holmes15-Jul-09 14:12 
GeneralMy vote of 5! Pin
jonasgranlund12-Jun-09 3:48
jonasgranlund12-Jun-09 3:48 
GeneralRe: My vote of 5! Pin
Mycroft Holmes12-Jun-09 14:10
professionalMycroft Holmes12-Jun-09 14:10 
GeneralRe: My vote of 5! Pin
davea3328-Jul-09 17:18
davea3328-Jul-09 17:18 

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

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