Recursive fill of a tree from sql query

[Raterus]

Hello,

Has anyone here ever used recursion to take the results of ONE sql query, and fill a tree object accordingly. Here is my situation. I'm storing data on "templates", and certain templates can fall under other templates. From a data standpoint in sql server, I have a [parent] column, that contains the id of its parent (the primary key of the table), if this parent is "0", I'm assuming its parent will be my root node.

My strategy for doing this was to have one datatable (the results of the query), and then set dataviews in the recursive function to look at the desired [parent], if one exists, add that node to the tree, then call the same function again with the new parent. Here is my code I have:

'dt is my datatable, it is filled before this
Dim node As TreeNode = New TreeNode
node.Text = "All Templates"
node.NodeData = "0"
FillTemplates(dt, "0", node)
tv.Nodes.Add(node)
...
Private Sub FillTemplates(ByRef dt As DataTable, ByVal parent As String, ByRef node As TreeNode)
Dim dv As DataView = dt.DefaultView
dv.RowFilter = "[parent] = '" & parent & "'"

Dim i As Integer, count As Integer
count = dv.Count - 1
For i = 0 To count
Dim newNode As TreeNode = New TreeNode
newNode.Text = CStr(dv.Item(i)("label"))
newNode.NodeData = CStr(dv.Item(i)("tmpID"))
FillTemplates(dt, newNode.NodeData, newNode)
node.Nodes.Add(newNode)
Next

End Sub
....

This works..., until I have a nested depth of 3, then it breaks (the dataviews are getting messed up from prior recursive calls). I'm not really after fixing this function just yet, I'm throwing this question out in hopes someone may offer an entirely different way of going about this.

Anyone ever know of an easier way?
--Michael

 

[Sahil Malik]

I am not sure if recursion is the easiest way to do it, though it is
certainly possible.

Try this instead -

I am assuming that your One query is similar to say a table with a self
referential integrity constraint (ok for argument's sake, lets assume,
unless you could post your dataset structure).

All you have to do is, create a datarelation that maps to that, set that
relation.nested=true.
Do a DataSet.GetXML
Use that DOMTree to fill your treeview - then thru recursion.

I have used the above technique in a project, if you really insist, I'll go
to the pain of finding that code for you .. hehe !!

- Sahil Malik
Independent Consultant
You can reach me thru my blog at -
http://www.dotnetjunkies.com/weblog/sahilmalik/



Hello,

Has anyone here ever used recursion to take the results of ONE sql query,
and fill a tree object accordingly. Here is my situation. I'm storing data
on "templates", and certain templates can fall under other templates. From
a data standpoint in sql server, I have a [parent] column, that contains the
id of its parent (the primary key of the table), if this parent is "0", I'm
assuming its parent will be my root node.

My strategy for doing this was to have one datatable (the results of the
query), and then set dataviews in the recursive function to look at the
desired [parent], if one exists, add that node to the tree, then call the
same function again with the new parent. Here is my code I have:

'dt is my datatable, it is filled before this
Dim node As TreeNode = New TreeNode
node.Text = "All Templates"
node.NodeData = "0"
FillTemplates(dt, "0", node)
tv.Nodes.Add(node)
...
Private Sub FillTemplates(ByRef dt As DataTable, ByVal parent As String,
ByRef node As TreeNode)
Dim dv As DataView = dt.DefaultView
dv.RowFilter = "[parent] = '" & parent & "'"

Dim i As Integer, count As Integer
count = dv.Count - 1
For i = 0 To count
Dim newNode As TreeNode = New TreeNode
newNode.Text = CStr(dv.Item(i)("label"))
newNode.NodeData = CStr(dv.Item(i)("tmpID"))
FillTemplates(dt, newNode.NodeData, newNode)
node.Nodes.Add(newNode)
Next

End Sub
....

This works..., until I have a nested depth of 3, then it breaks (the
dataviews are getting messed up from prior recursive calls). I'm not really
after fixing this function just yet, I'm throwing this question out in hopes
someone may offer an entirely different way of going about this.

Anyone ever know of an easier way?
--Michael

 

[Frans Bouma [C# MVP]]

See an example I posted some time ago, answering the same question of another
user.

I hope you can read C# so you can get the idea and port it to your particular
situation.

---
It's pretty simple: (I assume your data is in a datatable)

TreeNode rootNode = null;
Hashtable idToNode = new Hashtable(users.Rows.Count);

// first walk the list of users and create Treeview Nodes.
// we need 2 loops, as we can encounter a user with a parent
// and the parent isn't processed yet.
for(int i=0;i<users.Rows.Count;i++)
{
TreeNode newNode = new TreeNode(users.Rows["UserId"].ToString());
idToNode.Add(users.Rows["UserId"], newNode);
if((int)users.Rows["ParentId"]==0)
{
// found the root
rootNode = newNode;
}
}

// now we're going to build the tree!
for(int i=0;i<users.Rows.Count;i++)
{
TreeNode nodeToAdd = (TreeNode)idToNode[users.Rows["UserId"]];
// find parent
if((int)users.Rows["ParentId"]!=0)
{
TreeNode parentNode = (TreeNode)idToNode[users.Rows["ParentId"]];
parentNode.Nodes.Add(nodeToAdd);
}
}

// add the complete node structure to the treeview control
myTreeView.Nodes.Add(rootNode);

voila, an O(2) algo. This is from my bare head, so I might have made a syntax
error here and there, but you get the idea.
---

FB

Hello,

Has anyone here ever used recursion to take the results of ONE sql query,
and fill a tree object accordingly. Here is my situation. I'm storing
data on "templates", and certain templates can fall under other templates.
From a data standpoint in sql server, I have a [parent] column, that
contains the id of its parent (the primary key of the table), if this
parent is "0", I'm assuming its parent will be my root node.

My strategy for doing this was to have one datatable (the results of the
query), and then set dataviews in the recursive function to look at the
desired [parent], if one exists, add that node to the tree, then call the
same function again with the new parent. Here is my code I have:

'dt is my datatable, it is filled before this
Dim node As TreeNode = New TreeNode
node.Text = "All Templates"
node.NodeData = "0"
FillTemplates(dt, "0", node)
tv.Nodes.Add(node)
...
Private Sub FillTemplates(ByRef dt As DataTable, ByVal parent As
String, ByRef node As TreeNode) Dim dv As DataView = dt.DefaultView
dv.RowFilter = "[parent] = '" & parent & "'"

Dim i As Integer, count As Integer
count = dv.Count - 1
For i = 0 To count
Dim newNode As TreeNode = New TreeNode
newNode.Text = CStr(dv.Item(i)("label"))
newNode.NodeData = CStr(dv.Item(i)("tmpID"))
FillTemplates(dt, newNode.NodeData, newNode)
node.Nodes.Add(newNode)
Next

End Sub
....

This works..., until I have a nested depth of 3, then it breaks (the
dataviews are getting messed up from prior recursive calls). I'm not
really after fixing this function just yet, I'm throwing this question out
in hopes someone may offer an entirely different way of going about this.

Anyone ever know of an easier way?
--Michael