How to combine two datatables (from diff servers) in code, not with SQL join

Question

These two datatables were queried from DIFFERENT database servers. SQL Join is not an option.

They are filled separately and I need them merged. They're actually quite large, column-wise, but for ease of my question, I simplified them.

Table A (dtA): Definitely some duplicate order numbers...

+-------+-----+
| Order | Lne |
+-------+-----+
| 101   |  1  |
| 123   |  1  |
| 123   |  2  |
| 215   |  1  |
| 333   |  1  |
| 333   |  2  |
| 333   |  3  |
| 333   |  4  |
| 405   |  1  |
| 405   |  2  |
| 405   |  3  |
| 500   |  1  |
+-------+-----+

Table B (dtB): Unique order numbers, but extra data I need

+-------+------------+------------+
| Order | ReqDate    | DelivDate  |
+-------+------------+------------+
| 215   | 03/15/2014 | 03/15/2014 | 
| 333   | 04/02/2014 | 04/15/2014 |
| 405   |            | 02/01/2014 |
+-------+------------+------------+

Combined Table (dtCombined): Hopeful goal

+-------+-----+------------+------------+
| Order | Typ | ReqDate    | DelivDate  |
+-------+-----+------------+------------+
| 101   |  1  |            |            |
| 101   |  2  |            |            |
| 101   |  3  |            |            |
| 215   |  1  | 03/15/2014 | 03/15/2014 |
| 333   |  1  | 04/02/2014 | 04/15/2014 |
| 333   |  2  | 04/02/2014 | 04/15/2014 |
| 333   |  3  | 04/02/2014 | 04/15/2014 |
| 333   |  4  | 04/02/2014 | 04/15/2014 |
| 405   |  1  |            | 02/01/2014 |
| 405   |  2  |            | 02/01/2014 |
| 405   |  3  |            | 02/01/2014 |
| 500   |  1  |            |            |
+-------+-----+------------+------------+

After combining, I will be displaying in a DataGrid.

Code attempt...

Current attempt at merging the tables:

Dim colsA() As String = {"HeadOrderNo", "Line"}
Dim colsB() As String = {"OrderNo", "RequestDate", "DeliveryDate"}

'Add the columns from the first datatable (dtA)
For nCount1 As Integer = 0 To dtA.Columns.Count - 1
    dtCombined.Columns.Add(New DataColumn(dtA.Columns(nCount1).ColumnName, dtA.Columns(nCount1).DataType))
Next

'Add the columns from the second datatable (dtB)
For nCount2 As Integer = 0 To dtB.Columns.Count - 1
    dtCombined.Columns.Add(New DataColumn(dtB.Columns(nCount2).ColumnName, dtB.Columns(nCount2).DataType))
Next

'Dim bFirstRunDone As Boolean = False
Dim sColumnName As String
Dim drNewRow As DataRow
Dim nOuter As Integer = 0

'Go through each row in dtA and copy it to dtCombined
For Each drRowETI As DataRow In dtA.Rows
    drNewRow = dtCombined.NewRow()

    'Copy all column data from to drRow
    For Each sColumnName In colsA
        drNewRow(sColumnName) = drRowETI(sColumnName)
    Next

    'Go through each row in dtB and copy it to drNewRow
    For Each drRowLion As DataRow In dtB.Rows

        'Copy all column data from to drRow -- not working right
        For Each sColumnName In colsB
            'If dtA.Columns(nOuter).ColumnName = dtB.Columns(sColumnName).ColumnName Then
                drNewRow(sColumnName) = drRowLion(sColumnName)
            'End If
        Next
    Next

    'If dtA.Columns.Item(sColumnName).ToString() = dtB.Columns.Item(nCurrentRowNumETI).ToString() Then
    'End If
    dtCombined.Rows.Add(drNewRow)
    nOuter = nOuter + 1
Next

It's showing the first two columns correctly, but just repeating either the first or last row from the second table in their respective columns. I'm used to 2D arrays and iterating through them, but these DataRows are confusing for me. I'm not sure how to properly iterate through them to perform this type of task. It really shouldn't be hard I don't think.

I've tried countless ways like this: Joining two DataTables in C# with non matching data but to no avail. I might be implementing it incorrectly, but even the syntax is messing with me (e.g.: "Show New With { }).

Thank you

score 1 · Accepted Answer · answered May 21 '14 at 18:38

You don't appear to be filtering the dtB rows to find the ones matching the current dtA row. You could add your two source DataTables to a DataSet and define a DataRelation for the join. This will allow you to easily find the matching rows in dtB to combine the data.

Here is an example command line program, based on your pictures (not the code). Hopefully you can adapt this to your actual code!

Module Module1

    Sub Main()

        Dim row As DataRow = Nothing

        Dim dtA = New DataTable()
        dtA.Columns.Add("Order", GetType(Integer))
        dtA.Columns.Add("Lne", GetType(Integer))

        For i = 1 To 10
            row = dtA.NewRow()
            row("Order") = i
            row("Lne") = 1
            dtA.Rows.Add(row)
            row = dtA.NewRow()
            row("Order") = i
            row("Lne") = 2
            dtA.Rows.Add(row)
        Next

        Dim dtB = New DataTable()
        dtB.Columns.Add("Order", GetType(Integer))
        dtB.Columns.Add("ReqDate", GetType(Date))
        dtB.Columns.Add("DelivDate", GetType(Date))

        For i = 1 To 10
            row = dtB.NewRow()
            row("Order") = i
            row("ReqDate") = New Date(2014, 1, i)
            row("DelivDate") = New Date(2014, 2, i)
            dtB.Rows.Add(row)
        Next

        Dim dtCombined = New DataTable()
        dtCombined.Columns.Add("Order", GetType(Integer))
        dtCombined.Columns.Add("Typ", GetType(Integer))
        dtCombined.Columns.Add("ReqDate", GetType(Date))
        dtCombined.Columns.Add("DelivDate", GetType(Date))

        ' Add source tables to a dataset
        Dim ds = New DataSet()
        ds.Tables.Add(dtA)
        ds.Tables.Add(dtB)
        ' Define the relationship between the two tables
        ds.Relations.Add("order", dtA.Columns("Order"), dtB.Columns("Order"), False)

        For Each r1 As DataRow In dtA.Rows
            ' For each row in dtA, get the related rows in dtB
            For Each r2 As DataRow In r1.GetChildRows("order")
                row = dtCombined.NewRow()
                row("Order") = r1("Order")
                row("Typ") = r1("Lne")
                row("ReqDate") = r2("ReqDate")
                row("DelivDate") = r2("DelivDate")
                dtCombined.Rows.Add(row)
            Next
        Next

        For Each r As DataRow In dtCombined.Rows
            Console.WriteLine("{0,2} {1,2} {2:yyyy-MM-dd} {3:yyyy-MM-dd}", r("Order"), r("Typ"), r("ReqDate"), r("DelivDate"))
        Next

        Console.ReadLine()

    End Sub

End Module

And here is the output:

 1  1 2014-01-01 2014-02-01
 1  2 2014-01-01 2014-02-01
 2  1 2014-01-02 2014-02-02
 2  2 2014-01-02 2014-02-02
 3  1 2014-01-03 2014-02-03
 3  2 2014-01-03 2014-02-03
 4  1 2014-01-04 2014-02-04
 4  2 2014-01-04 2014-02-04
 5  1 2014-01-05 2014-02-05
 5  2 2014-01-05 2014-02-05
 6  1 2014-01-06 2014-02-06
 6  2 2014-01-06 2014-02-06
 7  1 2014-01-07 2014-02-07
 7  2 2014-01-07 2014-02-07
 8  1 2014-01-08 2014-02-08
 8  2 2014-01-08 2014-02-08
 9  1 2014-01-09 2014-02-09
 9  2 2014-01-09 2014-02-09
10  1 2014-01-10 2014-02-10
10  2 2014-01-10 2014-02-10

You're a life saver. I can't believe how simple that nested for loop was and I was failing... lol. Thank you very, very much! — Keith, May 22 '14 at 12:49

How to combine two datatables (from diff servers) in code, not with SQL join

Code attempt...

1 Answers1