Recursive fill of a tree from sql query

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 =3D New TreeNode
node.Text =3D "All Templates"
node.NodeData =3D "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 =3D dt.DefaultView
dv.RowFilter =3D "[parent] =3D '" & parent & "'"

Dim i As Integer, count As Integer
count =3D dv.Count - 1
For i =3D 0 To count
Dim newNode As TreeNode =3D New TreeNode
newNode.Text =3D CStr(dv.Item(i)("label"))
newNode.NodeData =3D 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
Fri May 14 02:09:57 CDT 2004

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/



"Raterus" <raterus@spam.org> wrote in message
news:ez1BrTQOEHA.2480@tk2msftngp13.phx.gbl...
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
Fri May 14 03:47:47 CDT 2004

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[i]["UserId"].ToString());
idToNode.Add(users.Rows[i]["UserId"], newNode);
if((int)users.Rows[i]["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[i]["UserId"]];
// find parent
if((int)users.Rows[i]["ParentId"]!=0)
{
TreeNode parentNode = (TreeNode)idToNode[users.Rows[i]["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

Raterus wrote:

> 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



--
Get LLBLGen Pro, the new O/R mapper for .NET: http://www.llblgen.com
My .NET Blog: http://weblogs.asp.net/fbouma
Microsoft C# MVP