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

 

[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.Add("InstanceId", GetType(Integer))
table.Columns.Add("LevelId", GetType(Integer))
table.Columns.Add("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(ByVal table As DataTable)
Dim ds As New DataSet("ds")
table.PrimaryKey = New DataColumn() {table.Columns("InstanceId")}

ds.Tables.Add(table)

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

ds.Relations.Add(relation)

ds.WriteXml("rjn.xml")
End Sub

Private Shared Sub WriteXml(ByVal table As DataTable)
Dim writer As New Xml.XmlTextWriter("rjn.xml",
System.Text.Encoding.UTF8)
writer.WriteStartDocument()
WriteXmlTree(writer, table, Nothing)

writer.WriteEndDocument()

writer.Close()
End Sub

Private Shared Sub WriteXmlTree(ByVal writer As Xml.XmlTextWriter, ByVal
table As DataTable, ByVal instanceId As Object)
Dim children As New DataView(table)
If instanceId Is Nothing Then
children.RowFilter = "ParentId Is Null"
Else
children.RowFilter = "ParentId = " & DirectCast(instanceId,
Integer)
End If
For Each row As DataRowView In children
writer.WriteStartElement("row")
writer.WriteElementString("InstanceId",
row.Row!InstanceId.ToString())
writer.WriteElementString("LevelId", row.Row!LevelId.ToString())
writer.WriteElementString("ParentId",
row.Row!ParentId.ToString())
WriteXmlTree(writer, table, row.Row!InstanceId)
writer.WriteEndElement()
Next
End Sub

Public Shared Sub Main()
Dim table As DataTable = CreateTable()
WriteDataSet(table)
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

| 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
|
|
|
|

 

[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