Introduction
Binding hierarchical data for menus and trees is a very common task these days. Binding sub nodes, when needed, makes a server round trip, or binding data with recursive binding degrades performance. We can achieve these in an easier way with this builder class.
Using the code
The HierarchicalXMLDataBuilder class builds the required XML data. Consider the scenario of ghe Employee table, which has a ManagerId that refers back to the EmployeeId of the same table. This builder will help in such scenarios. The method BuildMap returns the XML string as output. The parameters for the method are:
DataSet- Identity column
- Referring column
- Key value
The data has to be fetched once from the database and it has to be passed as a parameter for the method. Identity column and referring column are the columns which have the relations. Key value is the initial value which is used to generate the XML string.
Imports Microsoft.VisualBasic
Imports System.Xml
Imports System.Data
Public Class HierarchicalXMLDataBuilder
Private ParentColumn As String
Private ChildColumn As String
Private NoOfColumns As Integer
Private Data As DataSet
Private vuData As DataView
Public Function BuildMap(ByVal data As DataSet, _
ByVal IdentityColumn As String, ByVal ReferenceColumn As String, _
ByVal KeyValue As String) As String
Me.ParentColumn = IdentityColumn
Me.ChildColumn = ReferenceColumn
Me.Data = data
vuData = New DataView(data.Tables(0))
NoOfColumns = data.Tables(0).Columns.Count
Dim xml As String
xml = BuildChildNodes(vuData, ParentColumn & " = " & KeyValue)
Return xml
End Function
Private Function BuildChildNodes(ByVal vu As DataView, _
ByVal filter As String) As String
Dim nodeBuilder As New StringBuilder(String.Empty)
vu.RowFilter = filter
Dim RowCounter As Integer = 0
For RowCounter = 0 To vu.Count - 1
nodeBuilder.Append("<Node")
Dim ColCounter As Integer = 0
For ColCounter = 0 To NoOfColumns - 1
nodeBuilder.Append(" ")
nodeBuilder.Append(Data.Tables(0).Columns(ColCounter).ColumnName)
nodeBuilder.Append(" = """)
nodeBuilder.Append(vu.Item(RowCounter)(ColCounter).ToString())
nodeBuilder.Append(""" ")
Next
nodeBuilder.Append(" >")
Dim vuData2 As New DataView(data.Tables(0))
If vu.Count > RowCounter Then
nodeBuilder.Append(BuildChildNodes(vuData2, _
ChildColumn & " = " & _
vu.Item(RowCounter)(ParentColumn)))
End If
nodeBuilder.Append("</Node>")
Next
Return nodeBuilder.ToString()
End Function
End Class
We can use the builder as shown below. This creates the XML file and saves it in the application directory. We can then bind the XML to a treeview or a menu.
Dim XmlData As New HierarchicalXMLDataBuilder
Dim XmlString As String = XmlData.BuildMap(ds, "RowId", "ParentID", 1)
Dim doc As New System.Xml.XmlDocument()
doc.LoadXml(XmlString)
doc.Save(Server.MapPath("xmlfile.xml"))
The HTML:
<asp:treeview id="tv" runat="server" DataSourceID="XmlDataSource1" >
<DataBindings>
<asp:TreeNodeBinding DataMember="Node"
TextField="Data" ToolTipField="Data" />
</DataBindings>
</asp:treeview>
<asp:XmlDataSource ID="XmlDataSource1" runat="server"
DataFile="xmlfile.xml"></asp:XmlDataSource>
The output:
ROWID DATA PARENTID
----------- ---------------------------------
1 MainMenuItem NULL
2 MenuItem1 1
3 MenuItem2 1
4 MenuItem3 1
5 MenuItem4 1
6 MenuItem11 2
7 MenuItem12 2
8 MenuItem13 2
9 MenuItem21 3
10 MenuItem22 3
11 MenuItem41 5
12 MenuItem42 5
13 MenuItem43 5
14 MenuItem121 7
15 MenuItem122 7
16 MenuItem123 7
17 MenuItem131 8
18 MenuItem132 8
19 MenuItem221 10
20 MenuItem222 10
21 MenuItem2221 20
22 MenuItem2222 20
23 MenuItem22221 22
24 MenuItem222211 23
25 MenuItem222212 23
If the data is looks like this, the XML file generated will be:
<Node RowId="1" Data="MainMenuItem" ParentId="">
<Node RowId="2" Data="MenuItem1" ParentId="1">
<Node RowId="6" Data="MenuItem11" ParentId="2">
</Node>
<Node RowId="7" Data="MenuItem12" ParentId="2">
<Node RowId="14" Data="MenuItem121" ParentId="7">
</Node>
<Node RowId="15" Data="MenuItem122" ParentId="7">
</Node>
<Node RowId="16" Data="MenuItem123" ParentId="7">
</Node>
</Node>
<Node RowId="8" Data="MenuItem13" ParentId="2">
<Node RowId="17" Data="MenuItem131" ParentId="8">
</Node>
<Node RowId="18" Data="MenuItem132" ParentId="8">
</Node>
</Node>
</Node>
<Node RowId="3" Data="MenuItem2" ParentId="1">
<Node RowId="9" Data="MenuItem21" ParentId="3">
</Node>
<Node RowId="10" Data="MenuItem22" ParentId="3">
<Node RowId="19" Data="MenuItem221" ParentId="10">
</Node>
<Node RowId="20" Data="MenuItem222" ParentId="10">
<Node RowId="21" Data="MenuItem2221" ParentId="20">
</Node>
<Node RowId="22" Data="MenuItem2222" ParentId="20">
<Node RowId="23" Data="MenuItem22221" ParentId="22">
<Node RowId="24" Data="MenuItem222211" ParentId="23">
</Node>
<Node RowId="25" Data="MenuItem222212" ParentId="23">
</Node>
</Node>
</Node>
</Node>
</Node>
</Node>
<Node RowId="4" Data="MenuItem3" ParentId="1">
</Node>
<Node RowId="5" Data="MenuItem4" ParentId="1">
<Node RowId="11" Data="MenuItem41" ParentId="5">
</Node>
<Node RowId="12" Data="MenuItem42" ParentId="5">
</Node>
<Node RowId="13" Data="MenuItem43" ParentId="5">
</Node>
</Node>
</Node>
