将多个数据集列组合到一个数据集中
问题描述:
我有多个我想合并为一个的数据集。有一个公共ID字段可以关联到每一行。在数据集上调用合并将为数据集添加额外的行,但我想组合其他列。在一个查询中有太多的字段需要这样做,因此会使其无法管理。每个单独的查询将能够处理排序以确保数据放置在正确的行中。将多个数据集列组合到一个数据集中
对于实例可以说我有导致两个数据集的两个查询:
SELECT ID, colA, colB
SELECT colC, colD
得到的数据集看起来像
ID colA colB colC colD
1 a b c d
2 e f g h
就如何做到这一点任何想法?
答
这里是一个如何使用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
这可能有所帮助:http://*.com/questions/1259788/join-between-two-tables-with-linq-to-datasets – 2010-05-19 18:51:37