There is a client that has several statements that are rendered as Crystal Reports; we bind the Crystal Reposts to ADO.NET DataSets. The DataSets are loaded from stored procedures in the usual way. Right now we are printing several of these statements and some poor clerk has to compare those values with an earlier correct one.
My idea is to store the DataSets of known good statements and then I can automatically compare them with a new DataSet loaded from the stored procedure. So, over the weekend I wrote these classes to do the comparisons:
The Code
using System; using System.Collections.Generic; using System.Data; using System.Linq; namespace CompareDS { /// <summary> /// Compare the contents of DataSets or DataTables /// </summary> public class CompareDataSet { /// <summary> /// Compare the content of two DataSets /// </summary> /// <param name="fromDS">The DataSet considered to be correct</param> /// <param name="toDS">The DataSet to be tested</param> /// <returns>A list of Inconsistencies</returns> public static List<CompareDifference> DoCompareDataSet( DataSet fromDS, DataSet toDS) { var rVal = new List<CompareDifference>(); // Make sure that the table count match if (fromDS.Tables.Count != toDS.Tables.Count) { rVal.Add(new CompareDifference { Message = string.Format("Non maching # of tables: {0} != {1}", fromDS.Tables.Count, toDS.Tables.Count) }); } // Process the smaller # of tables int minColCount = Math.Min(fromDS.Tables.Count, toDS.Tables.Count); for (int i = 0; i < minColCount; ++i) { DataTable fromTable = fromDS.Tables[i]; DataTable toTable = toDS.Tables[i]; string tableName = fromTable.TableName; rVal.AddRange(DoCompareTable(fromTable, toTable, tableName, i)); } return rVal; } /// <summary> /// Compare the content of two DataTables /// </summary> /// <param name="fromTable">The DataTable considered to be correct</param> /// <param name="toTable">The DataTable to be tested</param> /// <param name="tableName">The Name of the table to be compared</param> /// <param name="tableNumber">The zero based table number</param> /// <returns>A list of Inconsistencies</returns> public static List<CompareDifference> DoCompareTable(DataTable fromTable, DataTable toTable, string tableName = "Table", int tableNumber = 0) { var rVal = new List<CompareDifference>(); // Number of Rows should match if (fromTable.Rows.Count != toTable.Rows.Count) { rVal.Add(new CompareDifference { TableName = tableName, TableNumber = tableNumber, Message = string.Format("Non maching # of rows: {0} != {1}", fromTable.Rows.Count, toTable.Rows.Count) }); } // Create new CompareDataTable for this table var compTables = new CompareDataTable (fromTable, toTable, tableName, tableNumber); int minColCount = Math.Min(fromTable.Rows.Count, toTable.Rows.Count); for (int i = 0; i < minColCount; ++i) { rVal.AddRange(compTables.CompareRow( fromTable.Rows[i], toTable.Rows[i], i)); } return rVal; } } /// <summary> /// Compares a DateTable row by row. /// I use a seperate non-static class so I don't have to re-aquire /// the table's Columns (Overkill, I know) /// </summary> public class CompareDataTable { private DataTable _fromTable; private DataTable _toTable; private DataColumnCollection _fromColumns; private DataColumnCollection _toColumns; private string _tableName; public int _tableNumber; /// <summary> /// Constructor: I use this class to avoid re-aquiring Columns /// for each row. /// </summary> /// <param name="fromTable">The DataTable considered to be correct</param> /// <param name="toTable">The DataTable to be tested</param> /// <param name="tableName">The Name of the table to be compared</param> /// <param name="tableNumber">The zero based table number</param> public CompareDataTable(DataTable fromTable, DataTable toTable, string tableName, int tableNumber) { _fromTable = fromTable; _toTable = toTable; _tableName = tableName; _tableNumber = tableNumber; // Remember the Table Columns _fromColumns = _fromTable.Columns; _toColumns = _toTable.Columns; } /// <summary> /// Compare the content of two DataRows /// </summary> /// <param name="fromRow">The DataRow considered to be correct</param> /// <param name="toRow">The DataRow to be tested</param> /// <param name="rowNumber">The zero based number of the row</param> /// <returns>A list of Inconsistencies</returns> public List<CompareDifference> CompareRow(DataRow fromRow, DataRow toRow, int rowNumber) { var rVal = new List<CompareDifference>(); int minColCount = Math.Min(fromRow.ItemArray.Count(), toRow.ItemArray.Count()); for (int i = 0; i < minColCount; ++i) { object fromCell = fromRow.ItemArray[i]; object toCell = toRow.ItemArray[i]; string fromColName = _fromColumns[i].ColumnName; string toColName = _toColumns[i].ColumnName; // Column names must match if (fromColName != toColName) { rVal.Add(new CompareDifference { TableName = _tableName, TableNumber = _tableNumber, RowNumber = rowNumber, ColumnName = fromColName, ColumnNumber = i, Message = string.Format( "Non matching column names \"{0}\" != \"{1}\"", fromColName, toColName) }); } // Type must match if (fromCell.GetType() != toCell.GetType()) { rVal.Add(new CompareDifference { TableName = _tableName, TableNumber = _tableNumber, RowNumber = rowNumber, ColumnName = fromColName, ColumnNumber = i, ExpectedValue = fromCell.ToString(), ActualValue = toCell.ToString(), Message = string.Format( "No matching types \"{0}\" != \"{1}\"", fromCell.GetType(), toCell.GetType()) }); } // And values (string compare for simplicity) else if (fromCell.ToString() != toCell.ToString()) { rVal.Add(new CompareDifference { TableName = _tableName, TableNumber = _tableNumber, RowNumber = rowNumber, ColumnName = fromColName, ColumnNumber = i, ExpectedValue = fromCell.ToString(), ActualValue = toCell.ToString(), Message = string.Format( "No matching values \"{0}\" != \"{1}\"", fromCell, toCell) }); } } return rVal; } } /// <summary> /// Simple class to store error messages /// </summary> public class CompareDifference { /// <summary> /// The Name of the Table /// where the inconsistancy exists /// </summary> public int TableNumber { get; set; } /// <summary> /// The zero based number of the Table /// where the inconsistancy exists /// </summary> public string TableName { get; set; } /// <summary> /// The zero based number of the column /// where the inconsistancy exists /// </summary> public int? ColumnNumber { get; set; } /// <summary> /// The name of the column /// where the inconsistancy exists /// </summary> public string ColumnName { get; set; } /// <summary> /// The number of the row /// where the inconsistancy exists /// </summary> public int? RowNumber { get; set; } /// <summary> /// The expected value /// </summary> public string ExpectedValue { get; set; } /// <summary> /// The actual value /// </summary> public string ActualValue { get; set; } /// <summary> /// An error message /// </summary> public string Message { get; set; } } }
