Convert Datatable data to XML file for XMLDATASOURCE binding to Menu control

peter liles 516 Reputation points
2022-08-16T17:03:29.48+00:00

I currently populate my Menu from Datatable in code behind.
I want to take advantage of the datasource wizard controls like persist state. So i would like to convert the data that creates the menu nodes to a XML file that can be bound to menu same. The root node has Make and id value while the childnode has Model and id value respectively.

Data

            For Each parentItem As DataRow In DT.Rows  


                Dim categoryItem As New MenuItem(parentItem("MAKE").ToString, parentItem("MAKE_ID").ToString)  

                Menu1.Items.Add(categoryItem)  

                AddChildMenuItems(menuData, categoryItem)  
            Next  

Private Sub AddChildMenuItems(ByVal menuData As DataTable, ByVal parentMenuItem As MenuItem)
Dim view As New DataView(menuData, "MAKE_ID=" & Convert.ToString(parentMenuItem.Value), Nothing, DataViewRowState.CurrentRows)
If view.Count > 0 Then
For Each row As DataRowView In view
Dim newMenuItem As New MenuItem(row("MODEL").ToString(), row("MODEL_Id").ToString())
parentMenuItem.ChildItems.Add(newMenuItem)

            AddChildMenuItems(menuData, newMenuItem)  
        Next  
    End If  
End Sub
ASP.NET
ASP.NET
A set of technologies in the .NET Framework for building web applications and XML web services.
3,220 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Lan Huang-MSFT 24,461 Reputation points Microsoft Vendor
    2022-08-17T08:57:51.31+00:00

    Hi @peter liles ,
    The code to convert DataTable to xml file can be set as follows:

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)  
            Dim ConString As String = ConfigurationManager.ConnectionStrings("MenuTest").ConnectionString  
            Dim CmdString As String = "SE LECT * FROM MenuTest"  
            Dim con As SqlConnection  
            Dim cmd As SqlCommand  
            Dim sda As SqlDataAdapter  
            Dim dt As DataTable  
            Using CSharpImpl.__Assign(con, New SqlConnection(ConString))  
                cmd = New SqlCommand(CmdString, con)  
                con.Open()  
                dt = New DataTable("Menus")  
               sda = New SqlDataAdapter(cmd)  
                sda.Fill(dt)  
                dt.WriteXml("***")  
                con.Close()  
            End Using  
        End Sub  
    

    You can also add a data table to a dataset and call GetXml() on the dataset.
    In addition to this dataset, it is equipped with WriteXml() and ReadXml() for writing/reading XML directly into a file path or stream.

    Dim ds As DataSet = New DataSet()  
        ds.Tables.Add(dt1)  
        ds.Tables.Add(dt2)  
        Dim dsXml As String = ds.GetXml()  
      
        Using fs As StreamWriter = New StreamWriter(xmlFile) // XML File Path  
            ds.WriteXml(fs)  
        End Using  
    

    Best regards,
    Lan Huang


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  2. peter liles 516 Reputation points
    2022-08-19T23:20:29.913+00:00

    Thanks that's easier than the routine normally shown. Another associated problem is dont you need some sort of relationship between parent and child data? as in my case i have Vehicle MAKE AND MODELS. Examples often show a hierarchy database approach. whereas my database file is flat file format.
    MAKE MAKEID MODEL MODELID
    Ford 12 Fiesta 15
    Ford 12 Focus 16

    So how may i create a query string that can recreate XML file that displays this sort of relationship between Make and Model?
    I want to display correct layout when shown in listview.