Merging 2 data tables in vb.net

Question

I have 2 DataTables in vb.net. Each is populated from it's own stored procedure. Table A contains a project number in the first column. Table B also contains the project number in the first column. Table A could have many records that have the same project number, but Table B will always have just one record in it per project number. I would like to append the data from Table B to every matching record in Table A. How would I do this?

Table A could look like this:

PROJECT#, QUANTITY

12345, 100

12345, 200

12345, 300

Table B could look like this:

PROJECT#, CUSTOMER

12345, ABC Inc.

I would like to merge the two to create something like this:

PROJECT#, QUANTITY, CUSTOMER

12345, 100, ABC Inc.

12345, 200, ABC Inc.

12345, 300, ABC Inc.

Please help!

Answer 1

This may help you, and may be semi-generic enough to be applied to other situations.

It's a function that will merge the data (as per your example) by passing in the two tables, two arrays containing the column names you require from each table, and the key used to join the tables.

There is an assumption that tblA is the driving table, with a lookup into tblB.

   Sub Main()

      Dim tbl As DataTable

      Dim colsA() As String = {"ProjectNo", "Quantity"}
      Dim colsB() As String = {"Customer"}
      Dim sKey As String = "ProjectNo"

      tbl = MergeData(tblA, tblB, colsA, colsB, sKey)

   End Sub

   Private Function MergeData(ByVal tblA As DataTable, ByVal tblB As DataTable, _
                              ByVal colsA() As String, ByVal colsB() As String, _
                              ByVal sKey As String) As DataTable

      Dim tbl As DataTable
      Dim col As DataColumn
      Dim sColumnName As String
      Dim row As DataRow
      Dim newRow As DataRow
      Dim dv As DataView

      tbl = New DataTable
      dv = tblB.DefaultView

      For Each sColumnName In colsA
         col = tblA.Columns(sColumnName)
         tbl.Columns.Add(New DataColumn(col.ColumnName, col.DataType))
      Next
      For Each sColumnName In colsB
         col = tblB.Columns(sColumnName)
         tbl.Columns.Add(New DataColumn(col.ColumnName, col.DataType))
      Next

      For Each row In tblA.Rows
         newRow = tbl.NewRow
         For Each sColumnName In colsA
            newRow(sColumnName) = row(sColumnName)
         Next

         dv.RowFilter = (sKey & " = " & row(sKey).ToString)
         If dv.Count = 1 Then
            For Each sColumnName In colsB
               newRow(sColumnName) = dv(0).Item(sColumnName)
            Next
         End If
         tbl.Rows.Add(newRow)
      Next

      Return tbl

   End Function

Answer 2

I'd advise using the LINQ methods, LINQ has a join operator that can handle this.

From q In quantities _
Join c In customers On q.project Equals c.project _
Select Quantity = q, Customer = c

Answer 3

anything AsEnumerable can be used in LINQ

var quantities = DataTable1().AsEnumerable(); var customers = DataTable2().AsEnumerable();

var result = from dtquanRow in quantities
        join dtcustomers in customers
        on dtquanRow.Field<string>("project") equals dtcustomers.Field<string>("project")

        select new
        {
            project = dtquanRow.Field<string>("project"),
            Quantity = dtquanRow.Field<string>("Quantity"),
           Customer = dtquanRow.Field<string>("Customer")
        };
moredetailed query and table to linq conversion 

Its further explanation of Chris Chilvers
From q In quantities _ Join c In customers On q.project Equals c.project _ Select Quantity = q, Customer = c