Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / web / ASP.NET

Hierarchical XML Data Builder

3.86/5 (4 votes)
17 Oct 2007CPOL1 min read 1  
Data builder that builds XML from a SQL query in hierarchical format.

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:

  1. DataSet
  2. Identity column
  3. Referring column
  4. 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.

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

VB
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.NET
<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:

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)