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();
dt2
, which will store the merged result.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.