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

Question

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

Both table has same structure.

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

Answer 1

You are looking most likely for the DataTable.Merge method.

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();
    }
}

Answer 2

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).

Answer 3

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)

Answer 4

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 :)

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