代码之家 › 专栏 › 技术社区 › Matt

将多个数据集列组合为一个数据集

dataset datatable visual-studio vb.net .net

Matt · 技术社区 · 15 年前

我希望将多个数据集合并为一个。有一个公共ID字段可以与每一行关联。对数据集调用merge将向数据集添加其他行,但我希望合并其他列。在一个查询中有太多的字段要执行此操作,因此会使其无法管理。每个单独的查询都能够处理排序,以确保将数据放置在正确的行中。

例如,假设我有两个查询产生两个数据集:

SELECT ID, colA, colB
SELECT colC, colD

生成的数据集看起来像

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

关于如何实现这一目标有什么想法吗?

1 回复 | 直到 15 年前

Carter Medlin 15 年前

下面是一个如何使用ASP.NET和VB.NET实现所需功能的示例。我为您创建了一个“mergecolumns”和“mergedata”子级。

   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