/// <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; }