Here's an implementation in a fairly general method that compares two data tables and returns another DataTable with the differences shown.
- The dynamic column is the "key" (only one column, not several).
- Does not display rows with the same data.
- Handles NULL in data.
- Columns are not included in both tables.
- T = comparison of objects.
Populating DataTables ...
/// Build data and test the underlying method.
public void Main ()
{
Dictionary columns = new Dictionary ();
columns.Add ("emp_num", typeof (int));
columns.Add ("salary", typeof (int));
columns.Add ("ov", typeof (double));
DataTable left = new DataTable ();
foreach (KeyValuePair column in columns)
{
left.Columns.Add (column.Key, column.Value);
}
left.Rows.Add (455, 3000, 67.891);
left.Rows.Add (677, 5000, 89.112);
left.Rows.Add (778, 6000, 12.672);
left.Rows.Add (9001, 5500, 12.672);
left.Rows.Add (4, null, 9.2);
//left.Dump("Left ");
DataTable right = new DataTable ();
right.Columns.Add ("outlier", typeof (string));
foreach (KeyValuePair column in columns)
{
right.Columns.Add (column.Key, column.Value);
}
right.Columns.Add ("float", typeof (float));
right.Rows.Add (0, 455, 3000, 67.891, 5);
right.Rows.Add (1, 677, 5000, 50.113, 5);
right.Rows.Add (2, 778, 5500, 12.672, 6);
right.Rows.Add (2, 9000, 5500, 12.672, 6);
right.Rows.Add (3, 4, 10, 9.2, 7);
//right.Dump("Right ");
// Compare.
DataTable results = Compare (left, right, "emp_num");
//results.Dump("Results "); // Fancy table output via LINQPad.
// Get the comparison columns for display.
List comparedColumns = new List ();
foreach (DataColumn column in results.Columns)
{
comparedColumns.Add (column.ColumnName);
}
// Display the comparison rows.
Console.WriteLine (string.Join (",", comparedColumns));
foreach (DataRow row in results.Rows)
{
Console.WriteLine (string.Join (",", row.ItemArray));
}
}
General Method: Comparing DataTable (DataTable, DataTable)
/// Compares the values of each row in the provided DataTables and returns any rows that have a difference based on a provided 'key' column.
/// the 'pre' data.
/// the 'post' data.
/// Name of the column to use for matching rows.
/// New DataTable populated with difference rows only.
public DataTable Compare (DataTable left, DataTable right, string keyColumn)
{
const string Pre = "_Pre";
const string Post = "_Post";
DataColumn leftKey = left.Columns.Contains (keyColumn)? left.Columns [keyColumn]: null;
DataColumn rightKey = right.Columns.Contains (keyColumn)? right.Columns [keyColumn]: null;
if (leftKey == null || rightKey == null)
{
return null;
}
// Get the matching columns between the two tables for doing comparisons.
List comparisonColumns = new List ();
DataTable results = new DataTable ();
// Adding the key column to the front for sake of ease of viewing.
results.Columns.Add (new DataColumn (leftKey.ColumnName, leftKey.DataType));
foreach (DataColumn column in left.Columns)
{
if (column == leftKey)
{
continue;
}
// Remove any columns that are not present in the compare table.
foreach (DataColumn compareColumn in right.Columns)
{
if (column.ColumnName == compareColumn.ColumnName && column.DataType == compareColumn.DataType)
{
comparisonColumns.Add (column.ColumnName);
results.Columns.Add (new DataColumn (column.ColumnName + Pre, column.DataType));
results.Columns.Add (new DataColumn (column.ColumnName + Post, column.DataType));
break;
}
}
}
foreach (DataRow leftRow in left.Rows)
{
object key = leftRow.Field (leftKey);
string filterExpression = string.Format ("{0} = {1}", keyColumn, key);
DataRow rightRow = right.Select (filterExpression) .SingleOrDefault ();
// Need a row for a comparison to be valid.
if (rightRow == null)
{
continue;
}
List comparison = new List ();
comparison.Add (key);
bool isDiff = false;
foreach (string comparisonColumn in comparisonColumns)
{
object pre = leftRow.ItemArray [left.Columns.IndexOf (comparisonColumn)];
comparison.Add (pre);
object post = rightRow.ItemArray [right.Columns.IndexOf (comparisonColumn)];
comparison.Add (post);
// Only need the row if the values differ in at least one column.
isDiff | = (pre == null && post! = null) || (pre! = null && post == null) || (! pre.Equals (post));
}
if (isDiff)
{
results.Rows.Add (comparison.ToArray ());
}
}
return results;
}
Output:
emp_num, salary_Pre, salary_Post, ov_Pre, ov_Post
677, 5000, 5000, 89.112, 50.113
778, 6000, 5500, 12.672, 12.672
4,, 10, 9.2, 9.2