How to Merge Multiple DataTables in C# with a Custom Merge Function

Merging multiple DataTables is a common requirement in data processing, especially when consolidating data from multiple sources. In this article, we’ll walk through a C# function that merges an array of DataTables, ensuring each unique row and column is included only once.

Below is the code we’ll be exploring in detail:

/// <summary>
/// This will merge all data in an array of DataTables
/// </summary>
/// <param name="dt">Array of DataTables.</param>
/// <returns>Merged DataTable.</returns>
public static DataTable MergeDataTable(DataTable[] dt)
{
    DataTable dt2 = new DataTable();

    for (int i = 0; i < dt.Length; i++)
    {
        // Step 1: Add unique columns from each DataTable
        foreach (DataColumn column in dt[i].Columns)
        {
            if (!dt2.Columns.Contains(column.ColumnName))
            {
                dt2.Columns.Add(column.ColumnName, column.DataType);
            }
        }

        // Step 2: Add each row while avoiding duplicates
        foreach (DataRow item in dt[i].Rows)
        {
            if (!dt2.Rows.Equals(item))
            {
                dt2.ImportRow(item);
            }
        }
    }
    return dt2;
}

How the Merge Works

This function, MergeDataTable, accepts an array of DataTables (DataTable[] dt) and outputs a single merged DataTable containing all unique rows and columns. Here’s a step-by-step breakdown:

Step 1: Initialize the Merged DataTable

DataTable dt2 = new DataTable();

The function begins by creating a new, empty DataTable dt2, which will store the merged result.

Step 2: Adding Unique Columns

foreach (DataColumn column in dt[i].Columns)
{
    if (!dt2.Columns.Contains(column.ColumnName))
    {
        dt2.Columns.Add(column.ColumnName, column.DataType);
    }
}

For each DataTable in the input array (dt), the function loops through its columns. If a column name doesn’t already exist in dt2, it adds that column. This ensures each column from the input DataTables appears only once in the merged DataTable.

Step 3: Importing Unique Rows

foreach (DataRow item in dt[i].Rows)
{
    if (!dt2.Rows.Equals(item))
    {
        dt2.ImportRow(item);
    }
}

Next, the function iterates through each row of the current DataTable and checks if the row already exists in dt2. If it doesn’t, the function imports the row into dt2. Using ImportRow preserves the original row structure, including column data types.

Note: The current approach to checking duplicates with dt2.Rows.Equals(item) may not fully capture row uniqueness. This check is often too general and can lead to unexpected results, so we’ll address a better approach for avoiding duplicate rows below.