C# DataTable Compare Rows

Use SequenceEqual to compare DataTable rows. Get values from a row with a helper method.

DataTable, compare rows. DataTables sometimes have equal rows. How can you compare these rows between tables? A transformation can be applied to each cell.

With the C# code in this example, we loop over both DataTables in nested foreach-loops. We call SequenceEqual to compare the data in each row.DataTable

First example. DataTables have no built-in methods for complicated comparison operations. Here, we introduce the CompareRows method.

CompareRows: It receives 2 DataTable instances. Then it loops over all the rows in both DataTables with nested loops.

And: In the inner block, we access the ItemArray property on both DataRow instances at the present position.

SequenceEqual: We use SequenceEqual from System.Linq to determine if the arrays are exactly equal in length and element composition.

SequenceEqual

Note: If your comparison requirements are different, you can analyze each element in a for-loop.

For

C# program that compares DataTable rows using System; using System.Data; using System.Linq; class Program { static void CompareRows(DataTable table1, DataTable table2) { foreach (DataRow row1 in table1.Rows) { foreach (DataRow row2 in table2.Rows) { var array1 = row1.ItemArray; var array2 = row2.ItemArray; if (array1.SequenceEqual(array2)) { Console.WriteLine("Equal: {0} {1}", row1["Drug"], row2["Drug"]); } else { Console.WriteLine("Not equal: {0} {1}", row1["Drug"], row2["Drug"]); } } } } static DataTable GetTable1() { DataTable table = new DataTable(); table.Columns.Add("Dosage", typeof(int)); table.Columns.Add("Drug", typeof(string)); table.Columns.Add("Patient", typeof(string)); table.Rows.Add(25, "Indocin", "David"); table.Rows.Add(50, "Enebrel", "Cecil"); return table; } static DataTable GetTable2() { DataTable table = new DataTable(); table.Columns.Add("Dosage", typeof(int)); table.Columns.Add("Drug", typeof(string)); table.Columns.Add("Patient", typeof(string)); table.Rows.Add(21, "Combivent", "Janet"); table.Rows.Add(50, "Enebrel", "Cecil"); table.Rows.Add(10, "Hydralazine", "Christoff"); return table; } static void Main() { CompareRows(GetTable1(), GetTable2()); } } Output Not equal: Indocin Combivent Not equal: Indocin Enebrel Not equal: Indocin Hydralazine Not equal: Enebrel Combivent Equal: Enebrel Enebrel Not equal: Enebrel Hydralazine

Notes, above program. The program's result is correct. It determines that each DataTable has a DataRow with the medication name "Enebrel" and a dosage of 50 mg.

And: Looking at the data construction logic, we see that is the only equal row in both DataTables.

DataRow

Tip: To further test the logic, try changing the data GetTable1() and GetTable2() to add more or fewer equal rows.

Note: In this algorithm, all fields must be equal for rows to be considered equal. This requirement could be modified.

Transformation example. Sometimes we wish to transform the data in rows before comparing. For example, we may need to see that each decimal in a row has a certain relation to another cell.

Here: We introduce GetDecimals(), which extracts all the decimal data from a row.

And: We multiply each value in the row by 1.5 or 1.0. Each cell in table 2 must be 1.5 times greater than each cell in table 1.

Result: With GetDecimals(), we can validate that the decimals in table 2 are 1.5 times greater.

C# program that uses CompareRows, GetDecimals using System; using System.Collections.Generic; using System.Data; using System.Linq; class Program { static List<decimal> GetDecimals(object[] array, decimal multiplier) { // Add all decimals in the row to a List of decimals. // ... And return the list. var list = new List<decimal>(); foreach (object value in array) { try { decimal result = (decimal)value; result *= multiplier; list.Add(result); } catch { // Error. } } return list; } static void CompareRows(DataTable table1, DataTable table2) { foreach (DataRow row1 in table1.Rows) { foreach (DataRow row2 in table2.Rows) { // Get decimals in each row. // ... Multiply items in row 1 by 1.5. var decimals1 = GetDecimals(row1.ItemArray, (decimal)1.5); var decimals2 = GetDecimals(row2.ItemArray, (decimal)1.0); // See if the row is valid. if (decimals1.SequenceEqual(decimals2)) { Console.WriteLine("OK: " + decimals1[0]); } else { Console.WriteLine("NOT OK: " + decimals1[0]); } } } } static DataTable GetTable1() { var table = new DataTable { TableName = "Prices in Sterling" }; table.Columns.Add("Product", typeof(string)); table.Columns.Add("Variety1", typeof(decimal)); table.Columns.Add("Variety2", typeof(decimal)); table.Columns.Add("Variety3", typeof(decimal)); table.Columns.Add("Variety4", typeof(decimal)); table.Rows.Add("Product 1", 10, 12, 14, 45); table.Rows.Add("Product 2", 20, 15, 24, null); table.Rows.Add("Product 3", 22, 60, null, null); table.Rows.Add("Product 4", 28, null, null, null); var sum1 = (decimal)table.Compute("SUM(Variety1)", string.Empty); var sum2 = (decimal)table.Compute("SUM(Variety2)", string.Empty); var sum3 = (decimal)table.Compute("SUM(Variety3)", string.Empty); var sum4 = (decimal)table.Compute("SUM(Variety4)", string.Empty); table.Rows.Add("Total", sum1, sum2, sum3, sum4); return table; } static DataTable GetTable2() { var table = new DataTable { TableName = "Prices in Euro" }; table.Columns.Add("Product", typeof(string)); table.Columns.Add("Variety1", typeof(decimal)); table.Columns.Add("Variety2", typeof(decimal)); table.Columns.Add("Variety3", typeof(decimal)); table.Columns.Add("Variety4", typeof(decimal)); table.Rows.Add("Product 1", 15, 18, 21, 67.5); table.Rows.Add("Product 2", 30, 22.5, 36, null); table.Rows.Add("Product 3", 33, 90, null, null); table.Rows.Add("Product 4", 42, null, 24, null); decimal sum1 = (decimal)table.Compute("SUM(Variety1)", string.Empty); decimal sum2 = (decimal)table.Compute("SUM(Variety2)", string.Empty); decimal sum3 = (decimal)table.Compute("SUM(Variety3)", string.Empty); decimal sum4 = (decimal)table.Compute("SUM(Variety4)", string.Empty); table.Rows.Add("Total", sum1, sum2, sum3, sum4); return table; } static void Main() { CompareRows(GetTable1(), GetTable2()); } } Output OK: 15.0 NOT OK: 15.0 NOT OK: 15.0 NOT OK: 15.0 NOT OK: 15.0 NOT OK: 30.0 OK: 30.0 NOT OK: 30.0 NOT OK: 30.0 NOT OK: 30.0 NOT OK: 33.0 NOT OK: 33.0 OK: 33.0 NOT OK: 33.0 NOT OK: 33.0 NOT OK: 42.0 NOT OK: 42.0 NOT OK: 42.0 NOT OK: 42.0 NOT OK: 42.0 NOT OK: 120.0 NOT OK: 120.0 NOT OK: 120.0 NOT OK: 120.0 NOT OK: 120.0

Notes, row comparisons. Often converting DataTable rows into Lists and then working on the Lists is easier. In C#, using Lists tends to be a good approach to problems.List

Notes, nested loops. For comparing the rows in 2 DataTables, you do not need to have nested loops. We can just do a linear scan through 2 DataTables to compare them.

A summary. The rows in DataTables can be compared using SequenceEqual. We can determine the equality or near-equality of 2 DataRows with the elements in the ItemArray property.

The Dev Codes

.Net

.NET Array Dictionary List String 2D Async DataTable Dates DateTime Enum File For Foreach Format IEnumerable If IndexOf Lambda LINQ Parse Path Process Property Regex Replace Sort Split Static StringBuilder Substring Switch Tuple

Java

Core Array ArrayList HashMap String 2D Cast Character Console Deque Duplicates File For Format HashSet If IndexOf Lambda Math ParseInt Process Random Regex Replace Sort Split StringBuilder Substring Switch Vector While

TheDeveloperBlog.com

C# DataTable Compare Rows

Related Links:

.Net

Java

Related Links