将多个数据集列一个数据集 [英] Combine multiple dataset columns to one dataset

问题描述

我有多个数据集，我想结合成一体。存在能够被关联到每行的公共ID字段。调用合并数据集上会增加额外行的数据集，但我想结合其他列。有太多场为此在一个查询，因此，会使它难以管理。每个单独的查询将能够处理排序，以确保数据被放置在正确的行

I have multiple datasets that I would like to combine into one. There is a common ID field that can be associated to each row. Calling Merge on the dataset will add additional rows to the dataset, but I would like to combine the additional columns. There are too many fields to do this in one query and therefore would make it unmanageable. Each individual query would be able to handle ordering to ensure the data is placed in the correct row.

举例来说可以说我有导致两个数据集两个查询：

For Example lets say I have two queries resulting in two datasets:

SELECT ID, colA, colB
SELECT colC, colD

由此产生的数据集看起来像

The resulting dataset would look like

ID colA colB colC colD
1  a    b    c    d
2  e    f    g    h

就如何做到这一点任何想法？

Any ideas on ways to accomplish this?

推荐答案

下面是你想怎么使用的是什么ASP.NET和VB.NET实现的一个例子。我创建了一个MergeColumns和MergeData分给你。

Here is an example of how to accomplish what you want using ASP.NET and VB.NET. I created a "MergeColumns" and "MergeData" sub for you.

   Private Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'Create the dataset and put in the data.  Normally you will just get this from a database query'

        Dim ds1 As New Data.DataSet
        Dim ds2 As New Data.DataSet

        Dim dt1 = ds1.Tables.Add()
        dt1.Columns.Add("ID", GetType(Int32))
        dt1.Columns.Add("ColA", GetType(String))
        dt1.Columns.Add("ColB", GetType(String))

        Dim dt2 = ds2.Tables.Add()
        dt2.Columns.Add("ColC", GetType(String))
        dt2.Columns.Add("ColD", GetType(String))

        dt1.Rows.Add(1, "a", "b")
        dt2.Rows.Add("c", "d")
        dt1.Rows.Add(2, "e", "f")
        dt2.Rows.Add("g", "h")

        'Sample data created, now to merge the results like you want'
        Dim dsNew As New Data.DataSet
        Dim dtNew = dsNew.Tables.Add(0)

        MergeColumns(dtNew, dt1, dt2)
        MergeData(dtNew, dt1, dt2)

        'Display the results'
        dsNew.AcceptChanges()
        Response.Write(dsNew.GetXml)

    End Sub


    Private Sub MergeColumns(ByVal TargetTable As Data.DataTable, ByVal ParamArray SourceTables() As Data.DataTable)
        For Each dtSource In SourceTables
            'Make a clone of the table, then steal the columns from the clone'
            Dim dtClone = dtSource.Clone

            While dtClone.Columns.Count > 0
                Dim dc = dtClone.Columns(0)

                dtClone.Columns.Remove(dc)

                TargetTable.Columns.Add(dc)
            End While
        Next
    End Sub


    Private Sub MergeData(ByVal TargetTable As Data.DataTable, ByVal ParamArray SourceTables() As Data.DataTable)
        'Determine the number of rows the final table will have'
        Dim nMaxRowCount = 0
        For Each dt In SourceTables
            If dt.Rows.Count > nMaxRowCount Then
                nMaxRowCount = dt.Rows.Count
            End If
        Next

        For i = 0 To nMaxRowCount - 1
            'Create a new row using column data from each table.  Assumes the name is unique across tables.'
            Dim drTarget = TargetTable.NewRow
            For Each dcTarget As Data.DataColumn In TargetTable.Columns
                For Each dt In SourceTables
                    If i < dt.Rows.Count AndAlso dt.Columns.Contains(dcTarget.ColumnName) Then
                        drTarget(dcTarget) = dt.Rows(i)(dcTarget.ColumnName)
                    End If
                Next
            Next
            TargetTable.Rows.Add(drTarget)
        Next

    End Sub

这篇关于将多个数据集列一个数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！