Answered by:
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
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 SunMSDN 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.- Proposed as answer by Michael Sun [MSFT]Microsoft employee Monday, January 4, 2010 12:56 AM
- Marked as answer by Michael Sun [MSFT]Microsoft employee Monday, January 4, 2010 9:49 AM
Monday, December 28, 2009 4:41 AM -
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
- Marked as answer by Michael Sun [MSFT]Microsoft employee Monday, January 4, 2010 9:49 AM
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 SunMSDN 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.- Proposed as answer by Michael Sun [MSFT]Microsoft employee Monday, January 4, 2010 12:56 AM
- Marked as answer by Michael Sun [MSFT]Microsoft employee Monday, January 4, 2010 9:49 AM
Monday, December 28, 2009 4:41 AM -
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 SunMSDN 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 -
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
- Marked as answer by Michael Sun [MSFT]Microsoft employee Monday, January 4, 2010 9:49 AM
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 SunMSDN 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