如何将多个DataTable合并成一个DataTable？

Question

如何将多个DataTable合并成一个DataTable？

3

我正在使用Microsoft Access数据库（请不要建议我使用其他数据库，因为在这种特定情况下我没有选择）。由于Access数据库表不能超过255列，所以我使用了垂直数据库分区技术，将信息拆分成5个不同的表。每个表有100列，第一列是主键。（请不要在此处建议我进行数据库规范化）。现在我需要从这五个表中读取数据，以便将其导出到Excel文件中。

为了实现这一点，我已经完成了以下步骤，但无法想出如何合并多个表。

I am reading each of these Table and putting into separate DataTable into the same DataSet. (Since I am using Access database I cannot select all columns from all tables)
I have declared Relationship on these five tables

Now I need to combine these DataTables into one DataTable. This means I will have total of 496 columns. I am eliminating primary keys from 4 tables since it is the same information. How do I do this?

Dim conn As OleDbConnection = New OleDbConnection
Conn ="Connection string stuff goes"

Dim FinalDS As New DataSet
Dim DataSet1 As New DataSet
Dim DataSet2 As New DataSet

SQLstr1 = "SELECT * FROM Table1"
SQLstr2 = "SELECT * FROM Table2"
SQLstr3 = "SELECT * FROM Table3"
SQLstr4 = "SELECT * FROM Table4"
SQLstr5 = "SELECT * FROM Table5"

Dim DA1 As New OleDb.OleDbDataAdapter(SQLstr1, cn)
Dim DA2 As New OleDb.OleDbDataAdapter(SQLstr2, cn)
Dim DA3 As New OleDb.OleDbDataAdapter(SQLstr3, cn)
Dim DA4 As New OleDb.OleDbDataAdapter(SQLstr4, cn)
Dim DA5 As New OleDb.OleDbDataAdapter(SQLstr5, cn)

Try
   DA1.Fill(FinalDS, "Tl")
   DA2.Fill(FinalDS, "T2")
   DA3.Fill(FinalDS, "T3")
   DA4.Fill(FinalDS, "T4")
   DA5.Fill(FinalDS, "T5")

   Dim DataRelation1 As New DataRelation("R1", _
                    FinalDS.Tables("T1").Columns("ID"), _
                    FinalDS.Tables("T2").Columns("ID"))


   Dim DataRelation2 As New DataRelation("R2", _
                    FinalDS.Tables("T2").Columns("ID"), _
                    FinalDS.Tables("T3").Columns("ID"))

   Dim DataRelation3 As New DataRelation("R3", _
                    FinalDS.Tables("T3").Columns("ID"), _
                    FinalDS.Tables("T4").Columns("ID"))

   Dim DataRelation4 As New DataRelation("R4", _
                    FinalDS.Tables("T4").Columns("ID"), _
                    FinalDS.Tables("T5").Columns("ID"))


   FinalDS.Relations.Add(DataRelation1)
   FinalDS.Relations.Add(DataRelation2)
   FinalDS.Relations.Add(DataRelation3)
   FinalDS.Relations.Add(DataRelation4)
   FinalDS.Relations.Add(DataRelation5)


  'Here I need to combine all these five DataTables into one DataTable
  'So that I can use Export it to excel from that one Final DataTable instead of  reading data from five different DataTables.

   'My Exporting to excel logies goes here which simply supporse to reads data from final DataTable which has 496 columns.

Catch ex As Exception
    MsgBox(ex.Message)
End Try

- Shai

@HansUp，是的，你说得对，我很好奇，测试了300个字段的连接。出现了异常“定义的字段太多”- 我删除了我的答案。 - Steve

2个回答

0

使用datatable.columns.add函数-详情请参见http://msdn.microsoft.com/en-us/library/hfx3s9wd.aspx#Y166

下面的代码未经过测试，可能无法编译，但是思路是循环所有数据表并将列添加到一个大数据表中。

Dim HugeDT as new datatable

For dt as datatable in FinalDS.DataTables
    For col as datacolumn in dt.Columns
        HugeDT.columns.add (col)
    Next
    'Run a loop on rows of each datatable to copy over the rows.
    'Remember to correlate the recordpointer correctly for each iteration
Next

- Shrieks

一个微小的改进是使用HugeDT.Columns.AddRange(dt.Columns)，但真正的问题在于数据复制。我们不知道要复制多少条记录5次。 - Steve

如果所有表之间都是一对一的关系，那么它应该是线性的。我现在没有编辑器来尝试一些东西，因此无法发布任何相关代码。 - Shrieks

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- NoAlias · Accepted Answer

我不会费心去使用DataRelations，而是直接使用Merge方法。然后像这样修改剩余部分...

    'Make all of the DataTables have the same columns.
    For Each dtPrepForMerge As DataTable In FinalDS.Tables

        For Each dtColumnsToGrab As DataTable In FinalDS.Tables

            For Each dc As DataColumn In dtColumnsToGrab.Columns

                'Don't duplicate columns.
                If dtPrepForMerge.Columns(dc.ColumnName) Is Nothing Then

                    dtPrepForMerge.Columns.Add(New DataColumn(dc.ColumnName, dc.DataType))

                End If

            Next

        Next

    Next

    'Now you can merge all of the tables together.
    'The order of the columns don't have to be the same between the tables.
    For intTableMergeCursor As Integer = 1 To (FinalDS.Tables.Count - 1)

        FinalDS.Tables(0).Merge(FinalDS.Tables(intTableMergeCursor))

    Next

    'Clone a new table that will hold your final results.
    Dim dtFinalResult As DataTable = FinalDS.Tables(0).Clone()

    'Sort the rows of your merged table by ID.
    Dim arrSortedDataRows As DataRow() = FinalDS.Tables(0).Select("id > 0", "id")

    Dim intLastID As Integer = CInt(arrSortedDataRows(0).Item("id"))

    Dim drMergedRow As DataRow = dtFinalResult.NewRow()

    For Each dr As DataRow In arrSortedDataRows

        'New ID?
        If CInt(dr.Item("id")) <> intLastID Then

            intLastID = CInt(dr.Item("id"))

            dtFinalResult.Rows.Add(drMergedRow)

            drMergedRow = dtFinalResult.NewRow()

        End If

        For intColumnCursor As Integer = 0 To (dtFinalResult.Columns.Count - 1)

            'Don't overwrite values.
            If Not dr.Item(intColumnCursor) Is DBNull.Value Then

                drMergedRow.Item(intColumnCursor) = dr.Item(intColumnCursor)

            End If

        Next

    Next

    dtFinalResult.Rows.Add(drMergedRow)