private bool ValuesMatch(object masterValue, object repValue, TableCompareOptions options) { if (options.HasFlag(TableCompareOptions.TreatDefaultsAsNull)) { // this code will compare the value to the default based on it's datatype, and set it to null if it is the same. } if (masterValue == null) { return (repValue == null || repValue == DBNull.Value); } if (masterValue.GetType().Name == "Byte[]") { return SlowButSureByteArrayCompare(masterValue as byte[], repValue as byte[]); } if (masterValue.GetType().Name == "String" && options.HasFlag(TableCompareOptions.IgnoreWhitespace)) { if ((repValue as string) == null) { return false; } return (((string)masterValue).Trim().Equals(((string)repValue).Trim())); } return masterValue.Equals(repValue); }
/// <summary> /// returns a list of rows with differences. will early exit with empty list if schema is not compatible. /// </summary> /// <returns></returns> public ReturnValue<List<RowDiff>> GetRowDiffs(TableCompareOptions options = TableCompareOptions.None) { var rowDiffs = new List<RowDiff>(); //use a Dataset to make use of a DataRelation object using (var ds = new DataSet()) { bool hasPk = (_master.PrimaryKey.Any()); if (!hasPk && !options.HasFlag(TableCompareOptions.KeysOptional)) { return ReturnValue<List<RowDiff>>.FailResult( "Master table has no Primary Key, and the KeysOptional flag was not set."); } //Add tables DataTable masterCopy = _master.Copy(); masterCopy.TableName = "master"; DataTable repCopy = _replica.Copy(); repCopy.TableName = "replica"; ds.Tables.AddRange(new[] { masterCopy, repCopy }); //Get Columns for DataRelation List<DataColumn> keyCols; if (hasPk) { keyCols = ds.Tables[0].PrimaryKey.ToList(); } else { List<string> fic = FieldsInCommon.ToList(); keyCols = ds.Tables[0].Columns.Cast<DataColumn>().Where(c => fic.Contains(c.ColumnName)).ToList(); } int numKeyCols = keyCols.Count(); var masterKeyCols = new DataColumn[numKeyCols]; var repKeyCols = new DataColumn[numKeyCols]; for (int i = 0; i < masterKeyCols.Length; i++) { DataColumn keyCol = keyCols[i]; masterKeyCols[i] = keyCol; repKeyCols[i] = ds.Tables[1].Columns.Cast<DataColumn>() .First(k => k.ColumnName == keyCol.ColumnName); } //Create DataRelation var findMissingRelat = new DataRelation(string.Empty, masterKeyCols, repKeyCols, false); ds.Relations.Add(findMissingRelat); var findExtraRelat = new DataRelation(string.Empty, repKeyCols, masterKeyCols, false); ds.Relations.Add(findExtraRelat); // check for missing rows and mismatched rows foreach (DataRow parentrow in ds.Tables[0].Rows) { DataRow[] childrows = parentrow.GetChildRows(findMissingRelat); if (childrows.Length == 0) { rowDiffs.Add(new RowDiff { DiffType = DiffType.Missing, Row = FindRow(_master,parentrow) }); } else if (childrows.Count() > 1) { rowDiffs.Add(new RowDiff { DiffType = DiffType.TypeMismatch, Row = FindRow(_master,parentrow) }); } else { DataRow masterRow = FindRow(_master,parentrow); RowDiff matchRows = CompareRowData(masterRow, childrows[0], options); if (matchRows.DiffType != DiffType.None) { rowDiffs.Add(matchRows); } } } // check for extra rows foreach (DataRow parentrow in ds.Tables[1].Rows) { DataRow[] childrows = parentrow.GetChildRows(findExtraRelat); if (childrows.Length == 0) rowDiffs.Add(new RowDiff { DiffType = DiffType.Extra, Row = FindRow(_replica, parentrow) }); } } return ReturnValue<List<RowDiff>>.SuccessResult(rowDiffs); }
/// <summary> /// compares the values of the cells in each row. /// Does not do a schema check. /// </summary> private RowDiff CompareRowData(DataRow masterRow, DataRow repRow, TableCompareOptions options) { var colDiffs = new List<ColumnDiff>(); var rowDiff = new RowDiff { DiffType = DiffType.None, ColumnDiffs = colDiffs, Row = masterRow }; //TODO: needs general clean up & optimization. foreach (string fieldName in FieldsInCommon) { object masterValue = masterRow[fieldName]; object repValue = repRow[fieldName]; if (!ValuesMatch(masterValue, repValue, options)) { rowDiff.DiffType = DiffType.DataMismatch; var colDiff = new ColumnDiff { Column = _master.Columns[fieldName], DiffType = DiffType.DataMismatch, }; if (options.HasFlag(TableCompareOptions.CaptureValues)) { colDiff.ReplicaValue = repValue; colDiff.MasterValue = masterValue; } colDiffs.Add(colDiff); } } if ((FieldsInCommon.Count() == _master.Columns.Count) && (FieldsInCommon.Count() == _replica.Columns.Count)) { return rowDiff; } string[] missingFieldNames = _masterFieldNames.Except(FieldsInCommon.ToArray()).ToArray(); if (missingFieldNames.Any()) { // this means the table is not schema comptabile. //(question - if the all the values in the master column are missing, could this be considered "compatible" even if it's not // schema compatible? This might be part of a tighter compatibility check, but for now that will be punted.) Contract.Assert(options.HasFlag(TableCompareOptions.AllowIncompatibleSchema), "Missing column in replica during compare, and AllowIncompatibleSchema is not set."); foreach (string fieldName in missingFieldNames) { var masterValue = masterRow[fieldName]; bool valuesMatch = ValuesMatch(masterValue, DBNull.Value, options); if (!valuesMatch) { rowDiff.DiffType = DiffType.DataMismatch; var colDiff = new ColumnDiff { Column = _master.Columns[fieldName], DiffType = DiffType.Missing, }; if (options.HasFlag(TableCompareOptions.CaptureValues)) { colDiff.ReplicaValue = DBNull.Value; colDiff.MasterValue = masterValue; } colDiffs.Add(colDiff); } } } if (ExtraFieldNames.Any()) { foreach (string fieldName in ExtraFieldNames) { rowDiff.DiffType = DiffType.DataMismatch; var repValue = repRow[fieldName]; bool valuesMatch = ValuesMatch(DBNull.Value, repValue, options); if (!valuesMatch) { var colDiff = new ColumnDiff { Column = _replica.Columns[fieldName], DiffType = DiffType.Extra, }; if (options.HasFlag(TableCompareOptions.CaptureValues)) { colDiff.ReplicaValue = repValue; colDiff.MasterValue = DBNull.Value; } colDiffs.Add(colDiff); } } } return rowDiff; }
public ReturnValue<TableDiff> Compare(TableCompareOptions options = TableCompareOptions.None) { try { ReturnValue<SchemaDiff> schemaDiffResult = CompareSchema(); if (!schemaDiffResult.Success) return ReturnValue<TableDiff>.Cascade(schemaDiffResult); SchemaDiff schemaDiff = schemaDiffResult.Value; var tableDiff = new TableDiff(_master, _replica) { SchemaDiff = schemaDiff, DiffType = TableDiffType.None }; if (!schemaDiff.IsCompatible) { tableDiff.DiffType = TableDiffType.IncompatibleSchema; if (!options.HasFlag(TableCompareOptions.AllowIncompatibleSchema)) { return ReturnValue<TableDiff>.FailResult( string.Format( "The schema for replica '{0}' is not compatible with '{1}' and the AllowIncompatibleSchema option is not set", _replica.TableName, _master.TableName)); } } else if (schemaDiff.HasDiffs) { tableDiff.DiffType = TableDiffType.CompatibleSchema; } ReturnValue<List<RowDiff>> dataDiffsResult = GetRowDiffs(options); if (!dataDiffsResult.Success) { ReturnValue<TableDiff>.Cascade(dataDiffsResult, string.Format("Unable to compare rows for {0} and {1}.", _master.TableName, _replica.TableName)); } tableDiff.RowDiffs = dataDiffsResult.Value; if (tableDiff.RowDiffs.Any()) { tableDiff.DiffType = TableDiffType.Data; } return ReturnValue<TableDiff>.SuccessResult(tableDiff); } catch (Exception ex) { return ReturnValue<TableDiff>.FailResult( string.Format("Unhandled error comparing tables {0}.", _master.TableName), ex); } }