Datatable multiple rows per one record VB.NET

Question

0.00/5 (No votes)

See more:

C#

Public Class TableA
        Public Field1 As Integer
        Public Field2 As String
        Public Field3 As String
        Public Field4 As TableB()
    End Class

    Public Class TableB
        Public Fld1 As Integer
        Public Fld2 As Integer
        Public Fld3 As Decimal
    End Class

What I have tried:

Hi, can anyone help me creating a DataTable consisting of 2 tables defined as below:

Posted 27-Dec-17 1:41am

ATeDe

Updated 27-Dec-17 8:03am

Add a Solution

2 solutions

Solution 1

There's no way to create a DataTable from what the code you've posted.

You would have to craft the columns in the DataTables yourself, possibly even adding a foreign key relationship between them.

C#

DataTable table1 = new DataTable();
table1.Columns.Add("Id", typeof(int));
table1.Columns.Add("Field1", typeof(int));
table1.Columns.Add("Field2", typeof(string));
table1.Columns.Add("Field3", typeof(string));

DataTable table2 = new DataTable();
table2.Columns.Add("Id", typeof(int));
table2.Columns.Add("table1FK", typeof(int));
table2.Columns.Add("Fld1", typeof(int));
table2.Columns.Add("Fld2", typeof(int));
table2.Columns.Add("Fld3", typeof(decimal));

DataSet ds = new DataSet();
ds.Tables.Add(table1);
ds.Tables.Add(table2);
ds.Relations.Add("MasterDetail", table1.Columns["Id"], table2.Columns["Id"]);

Posted 27-Dec-17 5:19am

Dave Kreskowiak

Comments

ATeDe 27-Dec-17 14:06pm

Hi Dave! Thanks for reply, but I should have mentioned in 1st post, that the source of data is in form of XML file, which looks like one attached below. I don't have problem reading XML file using

dataSet.ReadXml(fileXML)

or

XDocument.Load(fileXML).Root.Elements("Details")

and creating <tablea>, but can't create composite DataTable including <tableb> detail data!!!

Dave Kreskowiak 27-Dec-17 14:25pm

Well, that changes everything...

I have no idea what you mean by "can't create composite DataTable including tableb detail data".

What is the end result supposed to be? What is this being used for?

The DataSet will read the XML, but I don't think it's going to make any sense when you look at the tables in the DataSet. This is either really bad example data or your real data that's just about unusable in any kind of meaningful way.

ATeDe 27-Dec-17 15:35pm

Hi Dave!

...after this piece of code:

Dim dataSet As DataSet = New DataSet
dataSet.ReadXml("details.XML")
Dim dataTable As New DataTable
For Each dataTable In dataSet.Tables
_ShowTable(dataTable)
Next
_NewLine(Message:="Reading using ReadXml finished, OK! ")

... the printout looks like this:

->Unformatted table: tableA
Field1,Field2,Field3,tableA_Id
11,12,13,0
21,22,23,1
31,32,33,2

->Unformatted table: detail
detail_Id,tableA_Id
0,0
1,1
2,2

->Unformatted table: tableB
Fld1,Fld2,Fld3,detail_Id
1.1,1.2,1.3,0
1.4,1.5,1.6,0
2.1,2.2,2.3,1
2.4,2.5,2.6,1
3.1,3.2,3.3,2
3.4,3.5,3.6,2
3.7,3.8,3.9,2

->Reading using ReadXml finished, OK! Press any key to continue...

...as you see everything is going right way, except fact tableA & tableB are apart instead of look like that or similar:

11,12,13,1.1,1.2,1.3
1.4,1.5,1.6
21,22,23,2.1,2.2,2.3
2.4,2.5,2.6
31,32,33,3.1,3.2,3.3
3.4,3.5,3.6
3.7,3.8,3.9

...do you see what's the problem now?

Dave Kreskowiak 27-Dec-17 15:49pm

Yep, and ReadXml s not going to fix this. It will not combine two different tables into one. You're going to have to write the code to parse the XML file yourself and place the data into an appropriately built table yourself. The schema of the XML file you have is NOT setup to do what you want.

ATeDe 28-Dec-17 16:41pm

Hi Dave!

...the code using XDocument and LINQ query looks as follow:

Dim listTableA = XDocument.Load("details.XML").Root.Elements("tableA")
Dim tableAList As List(Of TableA)
tableAList = (From e In listTableA
Select New TableA With {
.Field1 = CInt(e.<field1>.Value),
.Field2 = CStr(e.<field2>.Value),
.Field3 = CStr(e.<field3>.Value),
.Field4 = (
From o In e...<field4>...<tableb>
Select New TableB With {
.Fld1 = CInt(o.<fld1>.Value),
.Fld2 = CInt(o.<fld2>.Value),
.Fld3 = CDec(o.<fld3>.Value)
}).ToArray()
}).ToList()

Dim tableA = New DataTable("tableA")
tableA.Columns.Add("Field1", GetType(Integer))
tableA.Columns.Add("Field2", GetType(String))
tableA.Columns.Add("Field3", GetType(String))

Dim tableB = New DataTable("tableB")
tableB.Columns.Add("Fld1", GetType(Integer))
tableB.Columns.Add("Fld2", GetType(Integer))
tableB.Columns.Add("Fld3", GetType(Decimal))

For Each a In tableAList
Dim rowA As New List(Of Object) From {a.Field1, a.Field2, a.Field3}
tableA.Rows.Add(rowA.ToArray())
For Each b In a.Field4
Dim rowB As New List(Of Object) From {b.Fld1, b.Fld2, b.Fld3}
tableB.Rows.Add(rowB.ToArray())
Next
Next
_ShowTable(tableA)
_ShowTable(tableB)

...and results as below:

->Unformatted table: tableA
Field1,Field2,Field3
11,12,13
21,22,23
31,32,33

->Unformatted table: tableB
Fld1,Fld2,Fld3
1,1,1.3
1,2,1.6
2,2,2.3
2,2,2.6
3,3,3.3
3,4,3.6
4,4,3.9

...Re: What is the end result supposed to be? What is this being used for?

Actually, I hope to develop some sort of most simplistic way, perhaps maybe even generating code to read automatically my legacy scientific data. At the moment, I can't see any chances to simplify the code. The next step would be to use a Dictionary as it is easy to use and effective. It has many functions (like ContainsKey and TryGetValue) that do lookups. And I hope that way my code will recognise the structure and get correct type of T for my data records

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

ATeDe · Accepted Answer · 2017-12-27T08:03:00

<?xml version="1.0" encoding="utf-8"?>

<tablea>
<field1>11
<field2>12
<field3>13
<detail>
<tableb>
<fld1>1.1
<fld2>1.2
<fld3>1.3

<tableb>
<fld1>1.4
<fld2>1.5
<fld3>1.6

<tablea>
<field1>21
<field2>22
<field3>23
<detail>
<tableb>
<fld1>2.1
<fld2>2.2
<fld3>2.3

<tableb>
<fld1>2.4
<fld2>2.5
<fld3>2.6

<tablea>
<field1>31
<field2>32
<field3>33
<detail>
<tableb>
<fld1>3.1
<fld2>3.2
<fld3>3.3

<tableb>
<fld1>3.4
<fld2>3.5
<fld3>3.6

<tableb>
<fld1>3.7
<fld2>3.8
<fld3>3.9