如何在 C# 中“联合”2 个或更多数据表？

How to 'union' 2 or more DataTables in C#?

提问by Dhana

How to 'union' 2 or more DataTables in C#?

如何在 C# 中“联合”2 个或更多数据表？

Both table has same structure.

两个表具有相同的结构。

Is there any build-in function or should we do manually?

是否有任何内置功能或我们应该手动执行？

采纳答案by TheTXI

You are looking most likely for the DataTable.Mergemethod.

您很可能正在寻找DataTable.Merge方法。

Example:

示例：

private static void DemonstrateMergeTable()
{
    DataTable table1 = new DataTable("Items");

    // Add columns
    DataColumn idColumn = new DataColumn("id", typeof(System.Int32));
    DataColumn itemColumn = new DataColumn("item", typeof(System.Int32));
    table1.Columns.Add(idColumn);
    table1.Columns.Add(itemColumn);

    // Set the primary key column.
    table1.PrimaryKey = new DataColumn[] { idColumn };

    // Add RowChanged event handler for the table.
    table1.RowChanged += new 
        System.Data.DataRowChangeEventHandler(Row_Changed);

    // Add ten rows.
    DataRow row;
    for (int i = 0; i <= 9; i++)
    {
        row = table1.NewRow();
        row["id"] = i;
        row["item"] = i;
        table1.Rows.Add(row);
    }

    // Accept changes.
    table1.AcceptChanges();
    PrintValues(table1, "Original values");

    // Create a second DataTable identical to the first.
    DataTable table2 = table1.Clone();

    // Add column to the second column, so that the 
    // schemas no longer match.
    table2.Columns.Add("newColumn", typeof(System.String));

    // Add three rows. Note that the id column can't be the 
    // same as existing rows in the original table.
    row = table2.NewRow();
    row["id"] = 14;
    row["item"] = 774;
    row["newColumn"] = "new column 1";
    table2.Rows.Add(row);

    row = table2.NewRow();
    row["id"] = 12;
    row["item"] = 555;
    row["newColumn"] = "new column 2";
    table2.Rows.Add(row);

    row = table2.NewRow();
    row["id"] = 13;
    row["item"] = 665;
    row["newColumn"] = "new column 3";
    table2.Rows.Add(row);

    // Merge table2 into the table1.
    Console.WriteLine("Merging");
    table1.Merge(table2, false, MissingSchemaAction.Add);
    PrintValues(table1, "Merged With table1, schema added");

}

private static void Row_Changed(object sender, 
    DataRowChangeEventArgs e)
{
    Console.WriteLine("Row changed {0}\t{1}", e.Action, 
        e.Row.ItemArray[0]);
}

private static void PrintValues(DataTable table, string label)
{
    // Display the values in the supplied DataTable:
    Console.WriteLine(label);
    foreach (DataRow row in table.Rows)
    {
        foreach (DataColumn col in table.Columns)
        {
            Console.Write("\t " + row[col].ToString());
        }
        Console.WriteLine();
    }
}

回答by George Stocker

You could try this:

你可以试试这个：

public static DataTable Union (DataTable First, DataTable Second)
{

      //Result table
      DataTable table = new DataTable("Union");

      //Build new columns
      DataColumn[] newcolumns = new DataColumn[First.Columns.Count];

      for(int i=0; i < First.Columns.Count; i++)
      {
          newcolumns[i] = new DataColumn(
          First.Columns[i].ColumnName, First.Columns[i].DataType);
      }

      table.Columns.AddRange(newcolumns);
      table.BeginLoadData();

      foreach(DataRow row in First.Rows)
      {
           table.LoadDataRow(row.ItemArray,true);
      }

      foreach(DataRow row in Second.Rows)
      {
          table.LoadDataRow(row.ItemArray,true);
      }

      table.EndLoadData();
      return table;
}

From here(not tested).

从这里（未测试）。

回答by Ruben Bartelink

You could use Concat from Linq to datasets (get the free chapter of LINQ in Action) to join them and then .AsDataTable to create the table (assuming you actually want them as a DataTable)

您可以使用从 Linq 到数据集的 Concat（获取 LINQ in Action 的免费章节）加入它们，然后使用 .AsDataTable 创建表（假设您确实希望它们作为 DataTable）

回答by mattmc3

Stumbled across this question, and Ruben Bartelink gave a great answer, but with no code. So I had to look it up elsewhere, which defeats the point of StackOverflow. Now that it's 2010, the other answers given aren't quite as viable. For reference, here's code demonstrating the CopyToDataTable() extension method. It's in VB so as to not steal the credit from Ruben if he wants to revisit the past and post a more complete answer :)

偶然发现了这个问题，Ruben Bartelink 给出了很好的答案，但没有代码。所以我不得不在别处查找它，这违背了 StackOverflow 的观点。现在是 2010 年，给出的其他答案不太可行。作为参考，这里的代码演示了 CopyToDataTable() 扩展方法。如果他想重温过去并发布更完整的答案，它在 VB 中是为了不从鲁本那里窃取功劳:)

Public Function GetSchema(ByVal dbNames As IEnumerable(Of String)) As DataTable
   Dim schemaTables As New List(Of DataTable)()
   For Each dbName As String In dbNames
      Dim cnnStr = GetConnectionString(dbName)
      Dim cnn As New SqlConnection(cnnStr)
      cnn.Open()
      Dim dt = cnn.GetSchema("Columns")
      cnn.Close()
      schemaTables.Add(dt)
   Next

   Dim dtResult As DataTable = Nothing
   For Each dt As DataTable In schemaTables
      If dtResult Is Nothing Then
         dtResult = dt
      Else
         dt.AsEnumerable().CopyToDataTable(dtResult, LoadOption.PreserveChanges)
      End If
   Next

   Return dtResult
End Function

回答by antonio

Try this using Linq to DataSet, must add the reference for System.Data.DataSetExtensions.dll, another approach, alternative for DataTable.Merge method).

尝试使用 Linq to DataSet 进行此操作，必须添加对 System.Data.DataSetExtensions.dll 的引用，这是另一种方法，DataTable.Merge 方法的替代方法）。

static void Main(string[] args)
{
    DoUnion();
}

private static void DoUnion()
{
    DataTable table1 = GetProducts();
    DataTable table2 = NewProducts();
    var tbUnion = table1.AsEnumerable()
        .Union(table2.AsEnumerable());
    DataTable unionTable = table1.Clone();
    foreach (DataRow fruit in tbUnion)
    {
        var fruitValue = fruit.Field<string>(0);
        Console.WriteLine("{0}->{1}", fruit.Table, fruitValue);
        DataRow row = unionTable.NewRow();
        row.SetField<string>(0, fruitValue);
        unionTable.Rows.Add(row);
    }
}

private static DataTable NewProducts()
{
    DataTable table = new DataTable("CitricusTable");
    DataColumn col = new DataColumn("product", typeof(string));
    table.Columns.Add(col);
    string[] citricusFruits = { "Orange", "Grapefruit", "Lemon", "Lime", "Tangerine" };
    foreach (string fruit in citricusFruits)
    {
        DataRow row = table.NewRow();
        row.SetField<string>(col, fruit);
        table.Rows.Add(row);
    }
    return table;
}

private static DataTable GetProducts()
{
    DataTable table = new DataTable("MultipleFruitsTable");
    DataColumn col = new DataColumn("product", typeof(string));
    table.Columns.Add(col);
    string[] multipleFruits = { "Breadfruit", "Custardfruit", "Hymanfruit", "Osage-orange", "Pineapple" };
    foreach (string fruit in multipleFruits)
    {
        DataRow row = table.NewRow();
        row.SetField<string>(col, fruit);
        table.Rows.Add(row);
    }
    return table;
}

antonio

安东尼奥