How to merge (join) datatables in .net where rows span multiple datatables

Question

I have multiple datatables that I want to join together. Rows span the tables not columns.

This means datatable1 could have columns 1, 2, 3 and datatable2 could have columns 4, 5, 6.

I want to merge the two so that datatable1 has columns 1, 2, 3, 4, 5, 6.

I feel like I have read plenty of examples but something continues not to work.

Private Sub mergeDataTables()
    Dim primaryKeyValue As Integer
    Dim tableIndex As Integer = 0
    For Each t As System.Data.DataTable In dataSet.Tables
        Dim pkColumn As New System.Data.DataColumn
        primaryKeyValue = 0

        'add column to house primary key and move it to the far left of the table
        pkColumn = t.Columns.Add()
        pkColumn.SetOrdinal(0)

        'populate the primary key column with unique values
        For Each r As System.Data.DataRow In t.Rows
            r(0) = primaryKeyValue
            primaryKeyValue += 1
        Next

        'set the primary key column as a primary key
        t.PrimaryKey = {t.Columns(0)}


        If tableIndex > 0 Then
            For col = 1 To t.Columns.Count - 1
                'assign unique column names to all columns but primary key column
                t.Columns(col).ColumnName = String.Format("{0}{1}", GetExcelColumnName(tableIndex), col) 'using getexcelcolumname for no other reason that to get a unique column name 1 = a 2 = b etc
            Next
            dataSet.Tables(0).Merge(t)
        End If

        tableIndex += 1
    Next
End Sub

@rheitzman I have, that is how I got the code above. However it doesn't produce the desired result, so I figured I was doing something wrong that someone could help me discover. — donL
– donL, Commented Apr 11, 2015 at 0:16
@rheitzman Do you see something that I would have missed from a Google search? — donL
– donL, Commented Apr 11, 2015 at 0:27
I think what you are trying to do is a JOIN not a merge. You may be able to retrieve entire copies of both tables, create local DataTables, and use logic or LINQ to join the records together based on key values from the tables that JOIN defines the relationship between the records. If this is a one time/small project you may consider using MS Access which can easily access data in different databases and perform queries on the links to the external tables. — rheitzman
– rheitzman, Commented Apr 12, 2015 at 14:28

hynsey · Accepted Answer · 2015-04-11 15:21:48Z

Try this - you need to pass in the DataSet containing your source tables as a parameter, and columns cannot have the same name across the tables:

    Private Sub mergeDataTables(ds As DataSet)

    Dim mergedTable As New DataTable("MergedData")

    mergedTable.Columns.Add("ID", System.Type.GetType(System.Int32))

    'Create the combined fields for the table
    For Each t As System.Data.DataTable In ds.Tables
        For Each col As DataColumn In t.Columns
            Dim newCol As New DataColumn(col.ColumnName, col.DataType)
            mergedTable.Columns.Add(newCol)
        Next
    Next

    Dim rowId As Integer = 0

    For Each row As DataRow In ds.Tables(0).Rows

        Dim newRow As DataRow = mergedTable.NewRow

        For Each col As DataColumn In mergedTable.Columns

            If col.ColumnName = "ID" Then
                newRow(col.ColumnName) = rowId
            Else
                For Each t As DataTable In ds.Tables

                    'This table has the column we need here
                    If t.Columns.Contains(col.ColumnName) Then
                        'Retrieve the corresponding data from the same row in the source table
                        newRow(col.ColumnName) = t.Rows(rowId)(col.ColumnName)
                        Exit For
                    End If
                Next
            End If

        Next

        mergedTable.Rows.Add(newRow)
        rowId += 1
    Next

End Sub

Collectives™ on Stack Overflow

How to merge (join) datatables in .net where rows span multiple datatables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related