/// <summary>
/// Compare all the columns information between two databases of one same table
/// </summary>
/// <param name="conn1"></param>
/// <param name="conn2"></param>
/// <param name="tableName"></param>
/// <returns></returns>
private List <ColumnDiff> compareColumns(string dbname1, string dbname2, List <DBColumns> columns1, List <DBColumns> columns2)
{
// Logger
DateTime start = DateTime.Now;
logger.Info("[ compareColumns ] - start time : " + start.ToString());
List <ColumnDiff> columns = new List <ColumnDiff>();
List <Column> cols1 = readColumns(dbname1, columns1);
List <Column> cols2 = readColumns(dbname2, columns2);
// get and delete not coexit columns
foreach (ColumnDiff item in getAndDeleteNoCoexitColumns(cols1, cols2, dbname1, dbname2))
{
columns.Add(item);
}
// compare the coeixt columns
cols1.Sort((a, b) => a.colname.CompareTo(b.colname));
cols2.Sort((a, b) => a.colname.CompareTo(b.colname));
for (int i = 0; i < cols1.Count(); i++)
{
if (!cols1[i].Compare(cols2[i]))
{
ColumnDiff colDiff = new ColumnDiff(cols1[i].colname);
colDiff.different.Add(cols1[i]);
colDiff.different.Add(cols2[i]);
columns.Add(colDiff);
}
}
// Logger
DateTime end = DateTime.Now;
logger.Info("[ compareColumns ] - end time : " + end.ToString() + " ; spend time : " + (end - start).ToString() + "\n");
return(columns);
}
/// <summary>
/// Get the Column that only in database1 or only in database2
/// The unique columns will be deleted from the list
/// Time complex: 2n
/// </summary>
/// <param name="cols1"></param>
/// <param name="cols2"></param>
/// <param name="dbname1"></param>
/// <param name="dbname2"></param>
/// <returns></returns>
private List <ColumnDiff> getAndDeleteNoCoexitColumns(List <Column> cols1, List <Column> cols2, string dbname1, string dbname2)
{
// Logger
DateTime start = DateTime.Now;
logger.Info("[ getAndDeleteNoCoexitColumns ] - start time : " + start.ToString());
List <ColumnDiff> rtn = new List <ColumnDiff>();
List <Column> toBeDelete1 = new List <Column>();
List <Column> toBeDelete2 = new List <Column>();
// Sort the columns information by column name
cols1.Sort((a, b) => a.colname.CompareTo(b.colname));
cols2.Sort((a, b) => a.colname.CompareTo(b.colname));
// Get all the unique columns
int pos1 = 0, pos2 = 0;
while (pos1 < cols1.Count() && pos2 < cols2.Count())
{
if (cols1[pos1].colname == cols2[pos2].colname)
{
pos1++;
pos2++;
continue;
}
else if (cols1[pos1].colname.CompareTo(cols2[pos2].colname) < 0)
{
toBeDelete1.Add(cols1[pos1]);
pos1++;
}
else
{
toBeDelete2.Add(cols2[pos2]);
pos2++;
}
}
while (pos1 < cols1.Count())
{
toBeDelete1.Add(cols1[pos1]);
pos1++;
}
while (pos2 < cols2.Count())
{
toBeDelete2.Add(cols2[pos2]);
pos2++;
}
foreach (Column item in toBeDelete1)
{
ColumnDiff colDiff = new ColumnDiff(item.colname);
item.exist = true;
Column noCol = new Column(dbname2, item.colname, false);
colDiff.different.Add(item);
colDiff.different.Add(noCol);
cols1.Remove(item);
rtn.Add(colDiff);
}
foreach (Column item in toBeDelete2)
{
ColumnDiff colDiff = new ColumnDiff(item.colname);
item.exist = true;
Column noCol = new Column(dbname1, item.colname, false);
colDiff.different.Add(item);
colDiff.different.Add(noCol);
cols2.Remove(item);
rtn.Add(colDiff);
}
// Logger
DateTime end = DateTime.Now;
logger.Info("[ getAndDeleteNoCoexitColumns ] - end time : " + end.ToString() + " ; spend time : " + (end - start).ToString() + "\n");
return(rtn);
}
/// <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;
}
