Join multiple DataTables

I'm reading an XML file and storing the results into a DataSet. This dataset contains 10 DataTables. I would like to join all DataTables into one datatable and use this table to set as a datasource for a datagridview so I can later export the gridview into csv or excel. Currently I'm displaying each datatable in a separate gridview. I have tried datatable.merge() but that didn't work.

Any suggestions on how I can display this 10 datatables in a single datagridview?

Thanks

Imports System.Xml

Public Class Form1

Public tbl0 As New DataTable
Public tbl1 As New DataTable
Public tbl2 As New DataTable
Public tbl3 As New DataTable
Public tbl4 As New DataTable
Public tbl5 As New DataTable
Public tbl6 As New DataTable
Public tbl7 As New DataTable
Public tbl8 As New DataTable
Public tbl9 As New DataTable

Public result As New DataTable


Private Sub btnreadxml_Click(sender As Object, e As EventArgs) Handles btnreadxml.Click
    Try
        Dim filePath As String
        filePath = "C:/Win/XMLReader/822396000069521.xml"

        AuthorsDataSet.ReadXml(filePath)

        tbl0 = AuthorsDataSet.Tables(0)
        tbl1 = AuthorsDataSet.Tables(1)
        tbl2 = AuthorsDataSet.Tables(2)
        tbl3 = AuthorsDataSet.Tables(3)
        tbl4 = AuthorsDataSet.Tables(4)
        tbl5 = AuthorsDataSet.Tables(5)
        tbl6 = AuthorsDataSet.Tables(6)
        tbl7 = AuthorsDataSet.Tables(7)
        tbl8 = AuthorsDataSet.Tables(8)
        tbl9 = AuthorsDataSet.Tables(9)

        DataGridView1.DataSource = tbl1
        DataGridView2.DataSource = tbl2
        DataGridView3.DataSource = tbl3
        DataGridView4.DataSource = tbl4
        DataGridView5.DataSource = tbl5
        DataGridView6.DataSource = tbl6
        DataGridView7.DataSource = tbl7
        DataGridView8.DataSource = tbl8
        DataGridView9.DataSource = tbl9


    Catch ex As Exception

    End Try
End Sub

Solution

There isn't one simple answer to this question but there are things you can do to make this more trivial. The following code are based on the following data schema.

Data schema

Our goal is to solve this task using LINQ.

The first thing we should do is to create custom classes reflecting the data schema.

Public Class Author
    Public Property pk_author As Integer
    Public Property firstname As String
    Public Property lastname As String
End Class

Public Class Book
    Public Property pk_book As Integer
    Public Property fk_author As Integer
    Public Property title As String
End Class

Public Class Review
    Public Property pk_review As Integer
    Public Property fk_book As Integer
    Public Property [text] As String
End Class

Next we'll need to create two extension/helper method. (You'll find the full source code at the bottom of this post.)

ToDataTable

This function will turn a List(Of T) into a DataTable based on the properties defined in T.

<Extension()>
Public Function ToDataTable(Of T)(source As List(Of T)) As DataTable

ToList

This function will turn a DataTable into a List(Of T) based on the properties defined in T and columns contained by the source table.

<Extension()>
Public Function ToList(Of T As {Class, New})(source As DataTable) As List(Of T)

Usage

Read the XML file into a DataTable

Dim [set] As DataSet = ReadXmlIntoDataSet()

"Convert" the tables into lists.

Dim authors As List(Of Author) = [set].Tables("authors").ToList(Of Author)()
Dim books As List(Of Book) = [set].Tables("books").ToList(Of Book)()
Dim reviews As List(Of Review) = [set].Tables("reviews").ToList(Of Review)()

Use LINQ to join the lists and then "convert" the result back to a DataTable.

Dim results As DataTable = (
    From a In authors
    From b In books.Where(Function(item) ((Not a Is Nothing) AndAlso item.fk_author = a.pk_author)).DefaultIfEmpty()
    From r In reviews.Where(Function(item) ((Not b Is Nothing) AndAlso item.fk_book = b.pk_book)).DefaultIfEmpty()
    Select New With {
        .pk_author = If((Not a Is Nothing), New Integer?(a.pk_author), Nothing),
        .pk_book = If((Not b Is Nothing), New Integer?(b.pk_book), Nothing),
        .pk_review = If((Not r Is Nothing), New Integer?(r.pk_review), Nothing),
        .firstname = If((Not a Is Nothing), a.firstname, Nothing),
        .lastname = If((Not a Is Nothing), a.lastname, Nothing),
        .title = If((Not b Is Nothing), b.title, Nothing),
        .[text] = If((Not r Is Nothing), r.[text], Nothing)
    }
).ToList().ToDataTable()

Application photo

Source code

References

Imports System.Runtime.CompilerServices
Imports System.ComponentModel
Imports System.Reflection
Imports <your namespace goes here>.Extensions

Author

Public Class Author
    Public Property pk_author As Integer
    Public Property firstname As String
    Public Property lastname As String
End Class

Book

Public Class Book
    Public Property pk_book As Integer
    Public Property fk_author As Integer
    Public Property title As String
End Class

Review

Public Class Review
    Public Property pk_review As Integer
    Public Property fk_book As Integer
    Public Property [text] As String
End Class

Extensions

<Extension()>
Public Module Extensions

    <Extension()>
    Public Function ToDataTable(Of T)(source As List(Of T)) As DataTable

        Dim properties As PropertyDescriptorCollection = TypeDescriptor.GetProperties(GetType(T))
        Dim table As New DataTable()
        Dim descriptor As PropertyDescriptor = Nothing
        Dim column As DataColumn = Nothing
        Dim values As Object() = Nothing
        Dim length As Integer = Nothing
        Dim index As Integer = Nothing
        Dim item As T = Nothing
        Dim type As Type = Nothing

        table.BeginInit()

        For Each descriptor In properties
            type = Nullable.GetUnderlyingType(descriptor.PropertyType)
            column = New DataColumn()
            column.ColumnName = descriptor.Name
            column.Caption = descriptor.DisplayName
            column.DataType = If((type Is Nothing), descriptor.PropertyType, type)
            column.ReadOnly = descriptor.IsReadOnly
            table.Columns.Add(column)
        Next

        table.BeginLoadData()
        length = (properties.Count - 1)
        values = New Object(length) {}

        For Each item In source
            For index = 0 To length
                values(index) = properties(index).GetValue(item)
            Next
            table.Rows.Add(values)
        Next

        table.EndLoadData()
        table.EndInit()

        Return table

    End Function

    <Extension()>
    Public Function ToList(Of T As {Class, New})(source As DataTable) As List(Of T)

        If (source Is Nothing) Then
            Throw New ArgumentNullException("source")
        End If

        Dim list As New List(Of T)
        Dim properties As PropertyDescriptorCollection = TypeDescriptor.GetProperties(GetType(T))
        Dim descriptor As PropertyDescriptor = Nothing
        Dim index As Integer = Nothing
        Dim row As DataRow = Nothing
        Dim item As T = Nothing

        For index = (properties.Count - 1) To 0 Step -1
            If (Not source.Columns.Contains(properties(index).Name)) Then
                properties.RemoveAt(index)
            End If
        Next

        For Each row In source.Rows
            item = New T()
            For Each descriptor In properties
                descriptor.SetValue(item, row.Item(descriptor.Name))
            Next
            list.Add(item)
        Next

        Return list

    End Function

End Module

Sample application

Public Class Form1

    Public Sub New()
        Me.InitializeControls()
        Try

            Dim [set] As DataSet = Form1.ReadXmlIntoDataSet()
            Dim authors As List(Of Author) = [set].Tables("authors").ToList(Of Author)()
            Dim books As List(Of Book) = [set].Tables("books").ToList(Of Book)()
            Dim reviews As List(Of Review) = [set].Tables("reviews").ToList(Of Review)()

            Dim results As DataTable = (
                From a In authors
                From b In books.Where(Function(item) ((Not a Is Nothing) AndAlso item.fk_author = a.pk_author)).DefaultIfEmpty()
                From r In reviews.Where(Function(item) ((Not b Is Nothing) AndAlso item.fk_book = b.pk_book)).DefaultIfEmpty()
                Select New With {
                    .pk_author = If((Not a Is Nothing), New Integer?(a.pk_author), Nothing),
                    .pk_book = If((Not b Is Nothing), New Integer?(b.pk_book), Nothing),
                    .pk_review = If((Not r Is Nothing), New Integer?(r.pk_review), Nothing),
                    .firstname = If((Not a Is Nothing), a.firstname, Nothing),
                    .lastname = If((Not a Is Nothing), a.lastname, Nothing),
                    .title = If((Not b Is Nothing), b.title, Nothing),
                    .[text] = If((Not r Is Nothing), r.[text], Nothing)
                }
            ).ToList().ToDataTable()

            Me.resultsGrid.DataSource = results
            Me.authorsGrid.DataSource = authors
            Me.booksGrid.DataSource = books
            Me.reviewsGrid.DataSource = reviews

        Catch ex As Exception
            MessageBox.Show(ex.Message, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Private Sub InitializeControls()
        Me.Label1 = New System.Windows.Forms.Label() With {.AutoSize = True, .Dock = System.Windows.Forms.DockStyle.Top, .Location = New System.Drawing.Point(0, 213), .Name = "Label1", .Size = New System.Drawing.Size(60, 18), .TabIndex = 0, .Text = "Reviews"}
        Me.Label2 = New System.Windows.Forms.Label() With {.AutoSize = True, .Dock = System.Windows.Forms.DockStyle.Top, .Location = New System.Drawing.Point(0, 142), .Name = "Label2", .Size = New System.Drawing.Size(45, 18), .TabIndex = 4, .Text = "Books"}
        Me.Label3 = New System.Windows.Forms.Label() With {.AutoSize = True, .Dock = System.Windows.Forms.DockStyle.Top, .Location = New System.Drawing.Point(0, 71), .Name = "Label3", .Size = New System.Drawing.Size(57, 18), .TabIndex = 7, .Text = "Authors"}
        Me.Label4 = New System.Windows.Forms.Label() With {.AutoSize = True, .Dock = System.Windows.Forms.DockStyle.Top, .Font = New System.Drawing.Font("Calibri", 9.0!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte)), .Location = New System.Drawing.Point(0, 0), .Name = "Label4", .Size = New System.Drawing.Size(51, 18), .TabIndex = 10, .Text = "Result:"}
        Me.Splitter1 = New System.Windows.Forms.Splitter() With {.BackColor = System.Drawing.Color.Gray, .Dock = System.Windows.Forms.DockStyle.Top, .Location = New System.Drawing.Point(0, 203), .Name = "Splitter1", .Size = New System.Drawing.Size(613, 10), .TabIndex = 2, .TabStop = False}
        Me.Splitter2 = New System.Windows.Forms.Splitter() With {.BackColor = System.Drawing.Color.Gray, .Dock = System.Windows.Forms.DockStyle.Top, .Location = New System.Drawing.Point(0, 132), .Name = "Splitter2", .Size = New System.Drawing.Size(613, 10), .TabIndex = 5, .TabStop = False}
        Me.Splitter3 = New System.Windows.Forms.Splitter() With {.BackColor = System.Drawing.Color.Gray, .Dock = System.Windows.Forms.DockStyle.Top, .Location = New System.Drawing.Point(0, 61), .Name = "Splitter3", .Size = New System.Drawing.Size(613, 10), .TabIndex = 8, .TabStop = False}
        Me.resultsGrid = New System.Windows.Forms.DataGridView() With {.AllowUserToAddRows = False, .AllowUserToDeleteRows = False, .BackgroundColor = System.Drawing.Color.White, .ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize, .Dock = System.Windows.Forms.DockStyle.Top, .Location = New System.Drawing.Point(0, 18), .Name = "resultsGrid", .ReadOnly = True, .Size = New System.Drawing.Size(613, 280), .TabIndex = 9}
        Me.authorsGrid = New System.Windows.Forms.DataGridView() With {.AllowUserToAddRows = False, .AllowUserToDeleteRows = False, .BackgroundColor = System.Drawing.Color.White, .ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize, .Dock = System.Windows.Forms.DockStyle.Top, .Location = New System.Drawing.Point(0, 89), .Name = "authorsGrid", .ReadOnly = True, .Size = New System.Drawing.Size(613, 115), .TabIndex = 6}
        Me.booksGrid = New System.Windows.Forms.DataGridView() With {.AllowUserToAddRows = False, .AllowUserToDeleteRows = False, .BackgroundColor = System.Drawing.Color.White, .ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize, .Dock = System.Windows.Forms.DockStyle.Top, .Location = New System.Drawing.Point(0, 160), .Name = "booksGrid", .ReadOnly = True, .Size = New System.Drawing.Size(613, 200), .TabIndex = 3}
        Me.reviewsGrid = New System.Windows.Forms.DataGridView() With {.AllowUserToAddRows = False, .AllowUserToDeleteRows = False, .BackgroundColor = System.Drawing.Color.White, .ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize, .Dock = System.Windows.Forms.DockStyle.Fill, .Location = New System.Drawing.Point(0, 231), .Name = "reviewsGrid", .ReadOnly = True, .Size = New System.Drawing.Size(613, 415), .TabIndex = 1}
        Me.SuspendLayout()
        Me.AutoScaleDimensions = New System.Drawing.SizeF(8.0!, 18.0!)
        Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
        Me.ClientSize = New System.Drawing.Size(613, 900)
        Me.Controls.AddRange({Me.reviewsGrid, Me.Label1, Me.Splitter1, Me.booksGrid, Me.Label2, Me.Splitter2, Me.authorsGrid, Me.Label3, Me.Splitter3, Me.resultsGrid, Me.Label4})
        Me.Font = New System.Drawing.Font("Calibri", 9.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Margin = New System.Windows.Forms.Padding(3, 4, 3, 4)
        Me.Name = "Form1"
        Me.Text = "Form1"
        Me.ResumeLayout(False)
        Me.PerformLayout()
    End Sub

    Private Shared Function ReadXmlIntoDataSet() As DataSet

        Dim [set] As New DataSet()

        Dim authors As DataTable = [set].Tables.Add("authors")

        With authors
            .Columns.Add("pk_author", GetType(Integer))
            .Columns.Add("firstname", GetType(String))
            .Columns.Add("lastname", GetType(String))
            .BeginLoadData()
            .Rows.Add(1, "William", "Shakespeare")
            .Rows.Add(2, "Henrik", "Ibsen")
            .Rows.Add(3, "Ernest", "Hemingway")
            .EndLoadData()
            .AcceptChanges()
        End With

        Dim books As DataTable = [set].Tables.Add("books")

        With books
            .Columns.Add("pk_book", GetType(Integer))
            .Columns.Add("fk_author", GetType(Integer))
            .Columns.Add("title", GetType(String))
            .BeginLoadData()
            .Rows.Add(1, 1, "Timon of Athens")
            .Rows.Add(2, 1, "Titus Othello")
            .Rows.Add(3, 1, "The Comedy of Errors")
            .Rows.Add(4, 2, "Peer Gynt")
            .Rows.Add(5, 2, "A Doll's House")
            .Rows.Add(6, 2, "Emperor and Galilean")
            .EndLoadData()
            .AcceptChanges()
        End With

        Dim reviews As DataTable = [set].Tables.Add("reviews")

        With reviews
            .Columns.Add("pk_review", GetType(Integer))
            .Columns.Add("fk_book", GetType(Integer))
            .Columns.Add("text", GetType(String))
            .BeginLoadData()
            .Rows.Add(1, 1, "The book 'Timon of Athens' blabla ...")
            .Rows.Add(2, 2, "The book 'Titus Othello' blabla ...")
            .Rows.Add(3, 4, "The book 'Peer Gynt' blabla 1...")
            .Rows.Add(4, 4, "The book 'Peer Gynt' blabla 2...")
            .Rows.Add(5, 4, "The book 'Peer Gynt' blabla 3...")
            .Rows.Add(6, 5, "The book 'A Doll's House' blabla ...")
            .EndLoadData()
            .AcceptChanges()
        End With

        [set].Relations.Add(New DataRelation("books_author", authors.Columns("pk_author"), books.Columns("fk_author")))
        [set].Relations.Add(New DataRelation("reviews_books", books.Columns("pk_book"), reviews.Columns("fk_book")))

        Return [set]

    End Function

    Friend WithEvents Label1 As System.Windows.Forms.Label
    Friend WithEvents Label2 As System.Windows.Forms.Label
    Friend WithEvents Label3 As System.Windows.Forms.Label
    Friend WithEvents Label4 As System.Windows.Forms.Label
    Friend WithEvents Splitter1 As System.Windows.Forms.Splitter
    Friend WithEvents Splitter2 As System.Windows.Forms.Splitter
    Friend WithEvents Splitter3 As System.Windows.Forms.Splitter
    Friend WithEvents resultsGrid As System.Windows.Forms.DataGridView
    Friend WithEvents authorsGrid As System.Windows.Forms.DataGridView
    Friend WithEvents booksGrid As System.Windows.Forms.DataGridView
    Friend WithEvents reviewsGrid As System.Windows.Forms.DataGridView

End Class