加入具有公共领域的两个数据表 [英] Join two data tables having common fields

问题描述

我有2 数据表对象（ DT1 ， DT2 ），其中有一个共同的申请名为FILE_NAME。我想创建一个数据表（ dtFinal ），结合两个DataTable在asp.net的GridView控件使用

I have 2 datatable objects (dt1,dt2), which have a common filed called "File_Name". I want to create a datatable (dtFinal), combining the two datatables to be used in gridview of asp.net

每个表的（​​ DT1 ， DT2 ），可以有重叠的列标题名称，这意味着如果DT1有头为plant_ code DT2 也可以有标题为plant_ code，而不是强制性的。

Each of the tables (dt1,dt2) can have overlapping column header names, meaning if dt1 has a header as "plant_code" dt2 can also have the header as "plant_Code" but not mandatory.

我的目标是有一个共同的数据表（ dtFinal ）与所有的单个数据表的所有头。

My objective is to have a common datatable (dtFinal) with all the headers from all the individual datatables.

我想是这样的。

  Dt1.PrimaryKey = New DataColumn() {Dt1.Columns(System.AppDomain.CurrentDomain.BaseDirectory & "\resources\files\" & "GRN" & ".csv")}
  Dt2.PrimaryKey = New DataColumn() {Dt1.Columns(System.AppDomain.CurrentDomain.BaseDirectory & "\resources\files\" & "Payment Data" & ".csv")}
  Dt1 = CsvToDataTable(System.AppDomain.CurrentDomain.BaseDirectory & "\resources\files\" & "GRN" & ".csv")
  Dt2 = CsvToDataTable(System.AppDomain.CurrentDomain.BaseDirectory & "\resources\files\" & "Payment Data" & ".csv")


  ds.Tables.Add(Dt1)
  ds.Tables.Add(Dt2)

  Dim drel As New DataRelation("EquiJoin", Dt2.Columns("File_Name"), Dt1.Columns("File_Name"))

  ds.Relations.Add(drel)

  Dim jt As New DataTable("Joinedtable")
  ds.Tables.Add(jt)
  For Each dr As DataRow In ds.Tables("Table1").Rows

      Dim parent As DataRow = dr.GetParentRow("EquiJoin")
      Dim current As DataRow = jt.NewRow()
      ' Just add all the columns' data in "dr" to the New table.

      For i As Integer = 0 To ds.Tables("Table1").Columns.Count - 1
          current(i) = dr(i)
      Next
      ' Add the column that is not present in the child, which is present in the parent.
      current("Dname") = parent("Dname")
      jt.Rows.Add(current)
  Next

  DtFinal = ds.Tables("Joinedtable")

创建集 DT1和DT2和之间的关系......但是，这似乎并没有工作，让我在第1行使用新声明对象的实例。

Creating a dataset and a relation between dt1 and dt2 ...But this doesn't seem to work and giving me an error in line 1 instance of object to be declared using New.

有有人试图在VB中这样的事情？或者，我可以code进行编辑，以使其发挥作用。

Has someone tried something like this in Vb ? Or can my code be edited to make it work.

进一步信息：每个DataTable TB1和TB2都在FILE_NAME列这是第一列的唯一值。因此可以选择作为主键

Further info: each datatable tb1 and tb2 have unique values in the File_Name column which is the first column. and hence can be selected as the primary key.

推荐答案

当你说，合并这可能意味着一件事或其他。例如，我可能会认为这种方法的。

When you say, "Merge" it may mean one thing or another. For example, I may think of this method.

这是工作code：

Public Sub  MergeTables()
    Dim t1 As New DataTable("T1")
    t1.Columns.Add("C1", GetType(String))
    t1.Columns.Add("C2", GetType(String))
    t1.Columns.Add("C3", GetType(String))

    Dim t2 As New DataTable("T2")
    t2.Columns.Add("C1", GetType(String)) 'same column
    t2.Columns.Add("C2", GetType(Integer)) ' same column, different data type
    t2.Columns.Add("C4", GetType(String)) ' different column

    Dim map As New Dictionary(Of String, String)

    Dim t3 As New DataTable("T4")
    MergeColumns(t3, t1, map)
    MergeColumns(t3, t2, map)

    Debug.WriteLine("Should be 5 columns and reality is: " & t3.Columns.Count)

    ' Add some data
    t1.Rows.Add({"data from t1 c1", "data from t1 c2", "data from t1 c3"})
    t2.Rows.Add({"data from t2 c1", 55, "data from t2 c3"})

    MergeRows(t3, t1, map)
    MergeRows(t3, t2, map)
    t3.AcceptChanges()


    For Each row As DataRow In t3.Rows
        Debug.WriteLine(String.Join(";", row.ItemArray.Select(Function(o) o.ToString()).ToArray()))
    Next

End Sub


Private Sub MergeColumns(totbl As DataTable, fromtbl As DataTable, map As Dictionary(Of String, String))
    For Each c As DataColumn In fromtbl.Columns
        If Not totbl.Columns.Contains(c.ColumnName) Then
            totbl.Columns.Add(c.ColumnName, c.DataType)
            map.Add(c.Table.TableName & "_" & c.ColumnName, c.ColumnName)
        ElseIf Not totbl.Columns(c.ColumnName).DataType.Equals(c.DataType) Then
            totbl.Columns.Add(c.Table.TableName & "_" & c.ColumnName, c.DataType)
            map.Add(c.Table.TableName & "_" & c.ColumnName, c.Table.TableName & "_" & c.ColumnName)
        Else
            map.Add(c.Table.TableName & "_" & c.ColumnName, c.ColumnName)
        End If
    Next
End Sub

Private Sub MergeRows(totbl As DataTable, fromtbl As DataTable, map As Dictionary(Of String, String))
    For Each row As DataRow In fromtbl.Rows
        Dim newRow As DataRow = totbl.NewRow()
        For Each c As DataColumn In fromtbl.Columns
            newRow(map(fromtbl.TableName & "_" & c.ColumnName)) = row(c.ColumnName)
        Next
        totbl.Rows.Add (newRow) 
    Next

End Sub

结果：

从T1 C1的数据;从T1 C2的数据;从T1 C3的数据; ;
 从T2 C1的数据; ; ; 55;从T2 C3数据

但可能是，你需要别的东西。然而，这是很好的例子，如何做到这样的事情

But may be, you need something else. However, this is good example how to do thing like this

这篇关于加入具有公共领域的两个数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！