ASP.Net Treeview Filling using SQL SERVER Recursion

I was desperately searching for a single Sql recursive query and i got an idea to accomplish it using CTE(Common Table expressions - SqlServer 2005 feature).It can return the hierarchical dataset of a particular parent/parents with depth/Level . In my project recursion is needed in many scenarios, like getting the complete set of reference ids from a self reference table, for filling a treeview .

To avoid recursion in .Net code I tried in a different way and the outcome is like this;

This is an example of an organization data which contains employees and their reporting officers in the same self referenced table.

Table structure

With a normal query, hierarchical data fetching is not possible if we are specifying a particular parent id. Sample Sql Query is given below for fetching data hierarchically with hierarchical depth level. We can use this for fetching the records of a particular parent also.

SQL Query

WITH SimpleRecursive AS(SELECT Employee_Nm,Employee_Id,Parent,0 AS depth FROM Recursion WHERE (Employee_Id IN(SELECT Employee_Id FROM Recursion AS Recursion_1 WHERE (Parent=1))) UNION ALL
SELECT P.Employee_Nm,P.Employee_Id,P.Parent,A.depth + 1 AS depth FROM Recursion AS P INNER JOIN SimpleRecursive AS A ON A.Employee_Id=P.Parent) SELECT Employee_Id,Employee_Nm,depth,CONVERT(int,Parent) AS Parent
FROM SimpleRecursive AS SimpleRecursive_1 ORDER BY depth

Sql query pane with result set :

Then i was trying to populate this result set in to a tree view. I could find this is done using Recursive methods for adding the child nodes. I thought of doing it without using recursive methods and finally i got solution for that.

For that first we need to fill the dataset with this query result.
Pass this dataset and parentid into the below procedure to fill the tree view.Pass parent id to get only that particular id’s hierarchical member set or leave parentid as blank to get the complete result set.

VB.Net code window: - Treeview

'//This procedure accepts dataset and an optional parentid as parameters.
'/if we pass a parentid then it fills treeview with complete hierarichial structure under that particular parent.
'/If not supplying a parentid then procedure fills treeview with complete hierarichial tree structure.

Private Sub TreeViewPopulation(ByVal ds As DataSet, Optional ByVal intParent As Integer = 0)
Dim TvwParent As New TreeNode
Dim TvwNode As New TreeNode
Dim CollItems As New Collection

Try
tvw.Nodes.Clear()
For Each dr As DataRow In ds.Tables(0).Rows ''''assigning dataset values to a collection as node
TvwNode = New TreeNode(dr("Employee_Nm").ToString)
TvwNode.Tag = dr("Parent")
CollItems.Add(TvwNode, dr("Employee_Id"))
Next

For Each TvwNode In CollItems
TvwParent = Nothing
'find parent of a node
If Val(TvwNode.Tag) <> intParent Then TvwParent = CType(CollItems.Item(TvwNode.Tag.ToString), TreeNode)
If Not TvwParent Is Nothing Then
'add child node to its parent node
CType(CollItems.Item(TvwNode.Tag.ToString), TreeNode).Nodes.Add(TvwNode.Clone)
Else
tvw.Nodes.Add(TvwNode) 'add node to Treeview
End If
Next

Catch ex As Exception
MsgBox("TreeView Population Error", MsgBoxStyle.Critical)
End Try
End Sub

 

As per my observation this method is relatively fast and easy to implement (with few lines of code).

Author:	Aneesh Kuzhikattil
	He is an accomplished IT professional with over 4 years of extensive experience in Microsoft technologies.