none
2. How to load grid from multiple xml files using ADO.NET? RRS feed

  • Question

  • Hi, 

    I have a Link file (xml) containing the ID nodes of several xml files, how do I load tdbgrid with the description node of the xml files based on the ID node of the link file matching the ID node of the xml files?  I am using VB.NET (VS2008)

     

    File 1

    <Root>

    <Row><Receiver_ID>1</Receiver_ID>

    <Description>FRA</Description>

    </Row>

    <Row><Receiver_ID>2</Receiver_ID>

    <Description>GBR</Description>

    </Row>

    <Row>

    <Receiver_ID>3</Receiver_ID>

    <Description>NLD</Description>

    </Row>

    <Row>

    <Receiver_ID>4</Receiver_ID>

    <Description>DEU</Description>

    </Row>

    <Row><Receiver_ID>5</Receiver_ID>

    <Description>USA</Description>

    </Row>

    </Root>

     

    File 2

    <Root>

    <Row>

    <Prop_NSN_ID>1</Prop_NSN_ID>

    <Description>1305</Description>

    </Row>

    <Row>

    <Prop_NSN_ID>2</Prop_NSN_ID>

    <Description>1307</Description>

    </Row>

    <Row>

    <Prop_NSN_ID>3</Prop_NSN_ID>

    <Description>1309</Description>

    </Row>

    </Root>

     

    File 3 - If the Link file contains the following:

    <Root>

    <Row>

    <Link_ID>1</Link_ID>

    <Receiver_ID>2</Receiver_ID>

    <Prop_NSN_ID>1</Prop_NSN_ID>

    </Row>

    <Row>

    <Link_ID>2</Link_ID>

    <Receiver_ID>3</Receiver_ID>

    <Prop_NSN_ID>2</Prop_NSN_ID>

    </Row>

    </Root>

     

    The grid should load with the following data:

     

    Link_ID   Receiver      Prop_NSN

    1              GBR               1305

    2              NLD               1307

    Thanks,

     

    Victor

     

     

    Sunday, December 27, 2009 11:00 PM

Answers

  • Hello again Victor, J

     

    I have answered one of your related threads here, http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/c6732c48-3e78-4ff7-91aa-b210d4dd63ee.  In that thread, I recommend you to use LINQ to XML to parse the XML document.  For the current question, I think LINQ to XML is more appropriate for such scenario due to LINQ’s powerful query functionality. 

     

    Here are some sample codes for your references: 

     

    The Where query: (I used VB.NET Lambda Expression inside the query)

    ===================================================================================================
            Dim doc1 = XDocument.Load("File1.xml")

            Dim doc2 = XDocument.Load("File2.xml")

            Dim doc3 = XDocument.Load("File3.xml")

     

            Dim query = From link In doc3.Root.Elements("Row") _

                        Select Link_ID = Integer.Parse(link.Element("Link_ID").Value), _

                        Receiver = doc1.Root.Elements("Row").Where(Function(row) row.Element("Receiver_ID").Value = link.Element("Receiver_ID").Value).Single().Element("Description").Value, _

                        Prop_NSN = doc2.Root.Elements("Row").Where(Function(row) row.Element("Prop_NSN_ID").Value = link.Element("Prop_NSN_ID").Value).Single().Element("Description").Value

     

            DataGridView1.DataSource = query.ToList()
    ===================================================================================================

     

    The Join query:

    ===================================================================================================
            Dim doc1 = XDocument.Load("File1.xml")

            Dim doc2 = XDocument.Load("File2.xml")

            Dim doc3 = XDocument.Load("File3.xml")

     

            Dim query = From link In doc3.Root.Elements("Row") _

                        Join receiver In doc1.Root.Elements("Row") On link.Element("Receiver_ID").Value Equals receiver.Element("Receiver_ID").Value _

                        Join prop In doc2.Root.Elements("Row") On link.Element("Prop_NSN_ID").Value Equals prop.Element("Prop_NSN_ID").Value _

                        Select Link_ID = Integer.Parse(link.Element("Link_ID").Value), _

                        Receiver = receiver.Element("Description").Value, _

                        Prop_NSN = prop.Element("Description").Value

     

     

            DataGridView1.DataSource = query.ToList()
    ===================================================================================================

     

    If you have any questions on this case, please be free to tell me. 

     

    Happy New Year!

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, December 28, 2009 4:41 AM
    Moderator
  • HI Lingzhi,

    I was able to solve this issue with the following syntax, below is an example using files linked with the Link file.

    Thanks,

    Victor

     

    Dim dt As New DataTable()

    dt.Columns.Add(

    "Link_ID")

    dt.Columns.Add(

    "Receiver")

    dt.Columns.Add(

    "Donor") 'Add (1)

     

    Dim linker As XElement = XElement.Load(Application.StartupPath + "\Link.xml")

     

    Dim receiver As XElement = XElement.Load(Application.StartupPath + "\Receiver.xml")

     

    Dim Donor As XElement = XElement.Load(Application.StartupPath + "\Donor.xml") 'Add (2)

     

    For Each item As XElement In linker.Elements("Row")

     

    Dim linkID As String = item.Element("Link_ID").Value

     

    Dim receiverId As String = item.Element("Receiver_ID").Value

     

    Dim DonorID As String = item.Element("Donor_ID").Value 'Add (3)

     

    Dim receiverVal As String = String.Empty

    Dim xe As XElement = receiver.Elements(

    "Row").Cast(Of XElement)().Where(Function(n) n.Element("Receiver_ID").Value = receiverId).FirstOrDefault()

     

    If xe IsNot Nothing Then

    receiverVal = xe.Element(

    "Receiver").Value

     

    End If

     

    Dim DonorVal As String = String.Empty 'Add (4)

    xe = Donor.Elements(

    "Row").Cast(Of XElement)().Where(Function(n) n.Element("Donor_ID").Value = DonorID).FirstOrDefault()

     

    If xe IsNot Nothing Then

    DonorVal = xe.Element(

    "Donor").Value

     

    End If

     

     

    Dim dr As DataRow = dt.NewRow()

    dr(

    "Link_ID") = linkID

    dr(

    "Caliber") = CaliberVal

    dr(

    "Receiver") = receiverVal

    dr(

    "Donor") = DonorVal 'Add (5)

    dt.Rows.Add(dr)

     

    Next

     

    Dim bs As New BindingSource()

    bs.DataSource = dt

    C1TrueDBGrid17.DataSource = bs

    Monday, January 4, 2010 1:30 AM

All replies

  • Hello again Victor, J

     

    I have answered one of your related threads here, http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/c6732c48-3e78-4ff7-91aa-b210d4dd63ee.  In that thread, I recommend you to use LINQ to XML to parse the XML document.  For the current question, I think LINQ to XML is more appropriate for such scenario due to LINQ’s powerful query functionality. 

     

    Here are some sample codes for your references: 

     

    The Where query: (I used VB.NET Lambda Expression inside the query)

    ===================================================================================================
            Dim doc1 = XDocument.Load("File1.xml")

            Dim doc2 = XDocument.Load("File2.xml")

            Dim doc3 = XDocument.Load("File3.xml")

     

            Dim query = From link In doc3.Root.Elements("Row") _

                        Select Link_ID = Integer.Parse(link.Element("Link_ID").Value), _

                        Receiver = doc1.Root.Elements("Row").Where(Function(row) row.Element("Receiver_ID").Value = link.Element("Receiver_ID").Value).Single().Element("Description").Value, _

                        Prop_NSN = doc2.Root.Elements("Row").Where(Function(row) row.Element("Prop_NSN_ID").Value = link.Element("Prop_NSN_ID").Value).Single().Element("Description").Value

     

            DataGridView1.DataSource = query.ToList()
    ===================================================================================================

     

    The Join query:

    ===================================================================================================
            Dim doc1 = XDocument.Load("File1.xml")

            Dim doc2 = XDocument.Load("File2.xml")

            Dim doc3 = XDocument.Load("File3.xml")

     

            Dim query = From link In doc3.Root.Elements("Row") _

                        Join receiver In doc1.Root.Elements("Row") On link.Element("Receiver_ID").Value Equals receiver.Element("Receiver_ID").Value _

                        Join prop In doc2.Root.Elements("Row") On link.Element("Prop_NSN_ID").Value Equals prop.Element("Prop_NSN_ID").Value _

                        Select Link_ID = Integer.Parse(link.Element("Link_ID").Value), _

                        Receiver = receiver.Element("Description").Value, _

                        Prop_NSN = prop.Element("Description").Value

     

     

            DataGridView1.DataSource = query.ToList()
    ===================================================================================================

     

    If you have any questions on this case, please be free to tell me. 

     

    Happy New Year!

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, December 28, 2009 4:41 AM
    Moderator
  • Hi Victor,

     

    I am writing to check the status of the issue on your side.  Would you mind letting me know the result of the suggestions? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Wish you all the best in the new year!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, December 31, 2009 3:46 AM
    Moderator
  • HI Lingzhi,

    I was able to solve this issue with the following syntax, below is an example using files linked with the Link file.

    Thanks,

    Victor

     

    Dim dt As New DataTable()

    dt.Columns.Add(

    "Link_ID")

    dt.Columns.Add(

    "Receiver")

    dt.Columns.Add(

    "Donor") 'Add (1)

     

    Dim linker As XElement = XElement.Load(Application.StartupPath + "\Link.xml")

     

    Dim receiver As XElement = XElement.Load(Application.StartupPath + "\Receiver.xml")

     

    Dim Donor As XElement = XElement.Load(Application.StartupPath + "\Donor.xml") 'Add (2)

     

    For Each item As XElement In linker.Elements("Row")

     

    Dim linkID As String = item.Element("Link_ID").Value

     

    Dim receiverId As String = item.Element("Receiver_ID").Value

     

    Dim DonorID As String = item.Element("Donor_ID").Value 'Add (3)

     

    Dim receiverVal As String = String.Empty

    Dim xe As XElement = receiver.Elements(

    "Row").Cast(Of XElement)().Where(Function(n) n.Element("Receiver_ID").Value = receiverId).FirstOrDefault()

     

    If xe IsNot Nothing Then

    receiverVal = xe.Element(

    "Receiver").Value

     

    End If

     

    Dim DonorVal As String = String.Empty 'Add (4)

    xe = Donor.Elements(

    "Row").Cast(Of XElement)().Where(Function(n) n.Element("Donor_ID").Value = DonorID).FirstOrDefault()

     

    If xe IsNot Nothing Then

    DonorVal = xe.Element(

    "Donor").Value

     

    End If

     

     

    Dim dr As DataRow = dt.NewRow()

    dr(

    "Link_ID") = linkID

    dr(

    "Caliber") = CaliberVal

    dr(

    "Receiver") = receiverVal

    dr(

    "Donor") = DonorVal 'Add (5)

    dt.Rows.Add(dr)

     

    Next

     

    Dim bs As New BindingSource()

    bs.DataSource = dt

    C1TrueDBGrid17.DataSource = bs

    Monday, January 4, 2010 1:30 AM
  • Hello Victor,

     

    I am very glad that the problem has been resolved.  Thank you so much for sharing your resolution here, it will be definitely benefitial to other community members. 
     

    Have a great day!

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, January 4, 2010 9:47 AM
    Moderator