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

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

 

 

Answer 1

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 [e-mail]

---

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.

Answer 2

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 [e-mail]

---

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.

Answer 3

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

Answer 4

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 [e-mail]

---

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.