Recursive function to get parent child relationship

[RJN]

Hi

I need help in writing a recursive function. My table structure is as
below.

InstanceId LevelId ParentId
100 1 null
101 2 100
102 3 101
103 4 102
104 5 103
105 5 104
106 5 104

From the above example, 100 is the root node which has a child 101, 101
has a child 102 and so on. Given a instanceid I want to get all the
child below that having levelid = 5. So if I give InstanceId=100, then I
should get a list of 104, 105, 106. If I pass the instance id 104, then
I should get 105 and 106. How I can I write a recursive function for
this?

Also I want to construct the xml tree structure. So passing in Instance
Id, I should get the immediate child below that and so on. If instance
id is 100, then there should be one leaf node 101 which will have
another leaf node 102 and so on. Can anyone help me?

Thanks

rjn

*** Sent via Developersdex http://www.developersdex.com ***

[Jay B. Harlow [MVP - Outlook]]

RJN,
Assuming your data is already in a DataTable or DataSet, there are a couple
of ways to do this, the "easiest" is to let the DataSet itself write the
Tree.

Here is an example of both methods:

Private Shared Function CreateTable() As DataTable
Dim table As New DataTable("RJN" )
table.Columns.A dd("InstanceId" , GetType(Integer ))
table.Columns.A dd("LevelId", GetType(Integer ))
table.Columns.A dd("ParentId", GetType(Integer ))

table.Rows.Add( New Object() {100, 1, Nothing})
table.Rows.Add( New Object() {101, 2, 100})
table.Rows.Add( New Object() {102, 3, 101})
table.Rows.Add( New Object() {103, 4, 102})
table.Rows.Add( New Object() {104, 5, 103})
table.Rows.Add( New Object() {105, 5, 104})
table.Rows.Add( New Object() {106, 5, 104})
Return table
End Function

Private Shared Sub WriteDataSet(By Val table As DataTable)
Dim ds As New DataSet("ds")
table.PrimaryKe y = New DataColumn() {table.Columns( "InstanceId ")}

ds.Tables.Add(t able)

Dim relation As New DataRelation("C hildren",
table.Columns(" InstanceId"), table.Columns(" ParentId"))
relation.Nested = True

ds.Relations.Ad d(relation)

ds.WriteXml("rj n.xml")
End Sub

Private Shared Sub WriteXml(ByVal table As DataTable)
Dim writer As New Xml.XmlTextWrit er("rjn.xml",
System.Text.Enc oding.UTF8)
writer.WriteSta rtDocument()
WriteXmlTree(wr iter, table, Nothing)

writer.WriteEnd Document()

writer.Close()
End Sub

Private Shared Sub WriteXmlTree(By Val writer As Xml.XmlTextWrit er, ByVal
table As DataTable, ByVal instanceId As Object)
Dim children As New DataView(table)
If instanceId Is Nothing Then
children.RowFil ter = "ParentId Is Null"
Else
children.RowFil ter = "ParentId = " & DirectCast(inst anceId,
Integer)
End If
For Each row As DataRowView In children
writer.WriteSta rtElement("row" )
writer.WriteEle mentString("Ins tanceId",
row.Row!Instanc eId.ToString())
writer.WriteEle mentString("Lev elId", row.Row!LevelId .ToString())
writer.WriteEle mentString("Par entId",
row.Row!ParentI d.ToString())
WriteXmlTree(wr iter, table, row.Row!Instanc eId)
writer.WriteEnd Element()
Next
End Sub

Public Shared Sub Main()
Dim table As DataTable = CreateTable()
WriteDataSet(ta ble)
WriteXml(table)
End Sub

Generally I would use the WriteDataSet method rather then "rolling my own"
(WriteXml & WriteXmlTree). However "rolling my own" does provide extra
flexibilty...

Hope this helps
Jay

"RJN" <rj*@yahoo.co m> wrote in message
news:uJ******** ******@TK2MSFTN GP15.phx.gbl...
| Hi
|
| I need help in writing a recursive function. My table structure is as
| below.
|
| InstanceId LevelId ParentId
| 100 1 null
| 101 2 100
| 102 3 101
| 103 4 102
| 104 5 103
| 105 5 104
| 106 5 104
|
| From the above example, 100 is the root node which has a child 101, 101
| has a child 102 and so on. Given a instanceid I want to get all the
| child below that having levelid = 5. So if I give InstanceId=100, then I
| should get a list of 104, 105, 106. If I pass the instance id 104, then
| I should get 105 and 106. How I can I write a recursive function for
| this?
|
| Also I want to construct the xml tree structure. So passing in Instance
| Id, I should get the immediate child below that and so on. If instance
| id is 100, then there should be one leaf node 101 which will have
| another leaf node 102 and so on. Can anyone help me?
|
| Thanks
|
| rjn
|
|
|
| *** Sent via Developersdex http://www.developersdex.com ***

[RJN]

Hi Jay

Thanks for your reply. That was a great piece of code. But I've one
issue here. I need to fetch the data from database first. In the example
you have assumed that you already have the complete data in the data
table including all the child. But I need to fetch the complete data
from database given a parentid and drill down till the leaf nodes. Once
I have the complete data in my data table, then I can go ahead with your
suggestion.

Regards

rjn

*** Sent via Developersdex http://www.developersdex.com ***