在 vb.net 中合并 2 个数据表 [英] Merging 2 data tables in vb.net

问题描述

我在 vb.net 中有 2 个数据表.每个都是从它自己的存储过程填充的.表 A 的第一列包含项目编号.表 B 还包含第一列中的项目编号.表 A 可以有许多具有相同项目编号的记录，但表 B 中的每个项目编号始终只有一个记录.我想将表 B 中的数据附加到表 A 中的每个匹配记录.我该怎么做?

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?

表 A 可能如下所示:

项目#，数量

12345、100

12345、200

12345、300

表 B 可能如下所示:

项目#，客户

12345，ABC 公司

12345, ABC Inc.

我想将两者合并以创建如下内容:

项目#、数量、客户

12345、100、ABC 公司

12345, 100, ABC Inc.

12345、200、ABC 公司

12345, 200, ABC Inc.

12345、300、ABC 公司

12345, 300, ABC Inc.

请帮忙！

推荐答案

这可能对您有所帮助，并且可能是半通用的，足以应用于其他情况.

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.

假设 tblA 是驱动表，需要查找 tblB.

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

这篇关于在 vb.net 中合并 2 个数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！