public void MissingColumnEqualsDbNullTest()
{
var master = TestHelper.BasicDataTableWithNullInt1();
var replica = TestHelper.BasicDataTableMissingInt1Column();
var result = new DataTableComparer(master,replica).Compare(TableCompareOptions.AllowIncompatibleSchema | TableCompareOptions.CaptureValues);
Assert.IsFalse(result.Value.RowDiffs.Any());
}
public void DontCompareByDefaultTest()
{
var master = TestHelper.BasicDataTable();
var replica = TestHelper.BasicDataTableMissingInt1Column();
var result = new DataTableComparer(master,replica).Compare();
Assert.IsFalse(result.Success);
Assert.AreEqual("The schema for replica 'DataTable1' is not compatible with 'DataTable1' and the AllowIncompatibleSchema option is not set", result.Context);
}
public void MatchingFieldsTest()
{
var dtMaster = TestHelper.BobTableV1();
var dtReplica = dtMaster.Copy();
var dtc = new DataTableComparer(dtMaster,dtReplica);
var result = dtc.CompareSchema().Value;
Assert.IsTrue(result.IsCompatible);
Assert.IsFalse(result.HasDiffs);
}
public void ExtraFieldsTest()
{
var dtMaster = TestHelper.BobTableV1();
var dtReplica =TestHelper.BobTableAddField();
var dtc = new DataTableComparer(dtMaster, dtReplica);
var result = dtc.CompareSchema().Value;
Assert.IsTrue(result.IsCompatible);
Assert.IsTrue(result.HasDiffs);
Assert.AreEqual(DiffType.Extra, result.ColumnDiffs[0].DiffType);
Assert.AreEqual("field5", result.ColumnDiffs[0].Column.ColumnName);
}
public void ExtraRowsTest()
{
var master = TestHelper.BasicDataTable();
var replica = TestHelper.BasicDataTableExtraRow();
var dtc = new DataTableComparer(master, replica);
var diff = dtc.Compare().Value;
Assert.AreEqual(TableDiffType.Data, diff.DiffType);
var rowDiff = diff.RowDiffs.First();
Assert.AreEqual(99, rowDiff.Row["pk"]);
Assert.AreEqual(DiffType.Extra, rowDiff.DiffType);
}
public void FieldTypeChangeTest()
{
var dtMaster = TestHelper.BobTableV1();
var dtReplica = TestHelper.BobTableChangeDT();
var dtc = new DataTableComparer(dtMaster, dtReplica);
var result = dtc.CompareSchema().Value;
Assert.IsFalse(result.IsCompatible);
Assert.IsTrue(result.HasDiffs);
Assert.AreEqual(1, result.ColumnDiffs.Count());
Assert.AreEqual(DiffType.TypeMismatch, result.ColumnDiffs[0].DiffType);
Assert.AreEqual("field1", result.ColumnDiffs[0].Column.ColumnName);
}
public void MissingFieldsTest()
{
var dtMaster = TestHelper.BobTableV1();
var dtReplica =TestHelper.BobTableRemoveField();
var dtc = new DataTableComparer(dtMaster, dtReplica);
var result = dtc.CompareSchema().Value;
Assert.IsFalse(result.IsCompatible);
Assert.IsTrue(result.HasDiffs);
Assert.AreEqual(1, result.ColumnDiffs.Count());
Assert.AreEqual(DiffType.Missing, result.ColumnDiffs[0].DiffType);
Assert.AreEqual("field3", result.ColumnDiffs[0].Column.ColumnName);
}
public void FieldAddAndRemoveTest()
{
var dtMaster = TestHelper.BobTableV1();
var dtReplica = TestHelper.BobTableAddAndRemove();
var dtc = new DataTableComparer(dtMaster, dtReplica); ;
var result = dtc.CompareSchema().Value;
Assert.IsFalse(result.IsCompatible);
Assert.IsTrue(result.HasDiffs);
Assert.AreEqual(2, result.ColumnDiffs.Count());
var extraCol = result.ColumnDiffs.First(cd => cd.DiffType == DiffType.Extra).Column;
var missingCol = result.ColumnDiffs.First(cd => cd.DiffType == DiffType.Missing).Column;
Assert.AreEqual("field3", missingCol.ColumnName);
Assert.AreEqual("field5",extraCol.ColumnName);
}
public void CompareIdenticalTablesTest()
{
var master = TestHelper.BasicDataTable();
var replica = master.Copy();
var dtc = new DataTableComparer(master,replica);
var diff = dtc.Compare().Value;
Assert.IsTrue(diff.SchemaDiff.IsCompatible);
Assert.IsFalse(diff.SchemaDiff.HasDiffs);
Assert.IsEmpty(diff.SchemaDiff.ColumnDiffs);
Assert.IsFalse(diff.HasDiffs);
Assert.AreEqual(diff.DiffType, TableDiffType.None);
Assert.IsEmpty(diff.RowDiffs);
}
public void MissingColumnsTest()
{
var master = TestHelper.BasicDataTable();
var replica = TestHelper.BasicDataTableMissingInt1Column();
var result = new DataTableComparer(master,replica).Compare(TableCompareOptions.AllowIncompatibleSchema | TableCompareOptions.CaptureValues);
Assert.IsTrue(result.Success);
var missingRow = result.Value.RowDiffs.Single(rd => rd.DiffType == DiffType.Missing);
var extraRow = result.Value.RowDiffs.Single(rd => rd.DiffType == DiffType.Extra);
var moddedRows = result.Value.RowDiffs.Where(rd => rd.DiffType == DiffType.DataMismatch);
CollectionAssert.AreEquivalent(new[] { 1, 3 }, moddedRows.Select(r => r.Row["pk"]).ToArray());
var rowWithMissingColumnAndOtherMod = moddedRows.Single(r => r.Row["pk"].Equals(3));
var diffCells = rowWithMissingColumnAndOtherMod.ColumnDiffs;
Assert.AreEqual(2, diffCells.Count);
var missingCell = diffCells.Single(cd => cd.DiffType == DiffType.Missing);
Assert.AreEqual("int1", missingCell.Column.ColumnName);
var moddedCell = diffCells.Single(cd => cd.DiffType == DiffType.DataMismatch);
Assert.AreEqual("dateTime1", moddedCell.Column.ColumnName);
}
public void ModdedRowTest()
{
var master = TestHelper.BasicDataTable();
var replica = TestHelper.BasicDataTableModdedRow();
var dtc = new DataTableComparer(master, replica);
var diff = dtc.Compare().Value;
Assert.AreEqual(TableDiffType.Data, diff.DiffType);
var rowDiff = diff.RowDiffs.First();
Assert.AreEqual(3, rowDiff.Row["pk"]);
Assert.AreEqual(DiffType.DataMismatch, rowDiff.DiffType);
Assert.AreEqual(2, rowDiff.ColumnDiffs.Count());
var colInt1Diff = rowDiff.ColumnDiffs.First(cd => cd.Column.ColumnName == "int1");
Assert.AreEqual(DiffType.DataMismatch, colInt1Diff.DiffType);
Assert.AreEqual(67, rowDiff.Row[colInt1Diff.Column] );
var colDateTime1Diff = rowDiff.ColumnDiffs.First(cd => cd.Column.ColumnName == "dateTime1");
Assert.AreEqual(DiffType.DataMismatch, colDateTime1Diff.DiffType);
Assert.AreEqual(new DateTime(2005, 1, 3), rowDiff.Row [colDateTime1Diff.Column]);
}
public void AddAndDeleteTest()
{
var master = TestHelper.BasicDataTable();
var replica = TestHelper.BasicDataTableAddAndDelete();
var dtc = new DataTableComparer(master, replica);
var diff = dtc.Compare().Value;
Assert.AreEqual(TableDiffType.Data, diff.DiffType);
Assert.AreEqual(2, diff.RowDiffs.Count);
var missingRow = diff.RowDiffs.First(rd => rd.DiffType == DiffType.Missing);
Assert.AreEqual(3, missingRow.Row["pk"]);
var extraRow = diff.RowDiffs.First(rd => rd.DiffType == DiffType.Extra);
Assert.AreEqual(4, extraRow.Row["pk"]);
}
private void cmdCompare_Click(object sender, EventArgs e)
{
var dtImported = (DataTable) dgvImported.DataSource;
// var dtActualTable = TableListView.ExecQuery(new SqlConnection(txtConnStr.Text), string.Format("select * from {0}", txtCompareToTable.Text) );
DataTable dtActualTable = new SqlConnection(txtConnStr.Text).GetTable(dtImported.TableName);
//var delta = CompareDataTables(dtImported, dtActualTable);
var dtc = new DataTableComparer(dtImported, dtActualTable);
//!dtImported.PrimaryKey = dtActualTable.PrimaryKey;
ReturnValue<TableDiff> deltaResult = dtc.Compare();
if (!deltaResult.Success)
{
MessageBox.Show(deltaResult.ToString(), "Error Comparing table");
}
else
{
TableDiff delta = deltaResult.Value;
if (delta.DiffType == TableDiffType.IncompatibleSchema)
{
MessageBox.Show(delta.SchemaDiff.ToString(), "Incompatible schema");
}
else
{
HighlightDifferences(dgvImported, delta);
}
}
}
/// <summary>
/// compares each table in the master collection with the corresponding replica table.
/// This version will only compare tables that appear in the master collection table list.
/// </summary>
/// <returns></returns>
public static ReturnValue<TableSetDiff> CompareSets(DataTableSet masterDts, DataTableSet replicaDts,
TableCompareOptions options = TableCompareOptions.None)
{
var tdDict = new TableSetDiff(masterDts, replicaDts);
try
{
foreach (DataTable masterTable in masterDts)
{
DataTable replicaTable = replicaDts[masterTable.TableName];
ReturnValue<TableDiff> result;
if (replicaTable == null)
{
result = ReturnValue<TableDiff>.FailResult(string.Format(
"Table '{0}' was not found in the replica collection [{1}]", masterTable.TableName,
replicaDts.Id));
}
else
{
var dtc = new DataTableComparer(masterTable, replicaTable);
result = dtc.Compare(options);
}
tdDict.Add(masterTable.TableName, result);
}
return ReturnValue<TableSetDiff>.SuccessResult(tdDict);
}
catch (Exception ex)
{
return ReturnValue<TableSetDiff>.FailResult("Error comparing table ", ex);
}
}
