public a7DbTableOnlyIn(string tableName, a7DbComparedDataBases onlyIn, a7DbStructureComparer comparer)
{
TableName = tableName;
AddToOtherDbName = comparer.GetOtherDb(onlyIn).Name;
AddToOtherCommand = new a7LambdaCommand((o) =>
{
comparer.IsBusy = true;
Task.Factory.StartNew(() =>
{
comparer.CopyTable(TableName, onlyIn);
Application.Current.Dispatcher.Invoke(() =>
{
if (onlyIn == a7DbComparedDataBases.A)
{
comparer.TablesOnlyInA.Remove(this);
}
else
{
comparer.TablesOnlyInB.Remove(this);
}
comparer.IsBusy = false;
});
}).ContinueWith((t) =>
{
if (t.Exception != null)
{
throw t.Exception;
}
});
}
);
RemoveFromThisDbName = comparer.GetDb(onlyIn).Name;
RemoveFromThisCommand = new a7LambdaCommand((o) =>
{
comparer.IsBusy = true;
Task.Factory.StartNew(() =>
{
var tbl = comparer.GetTable(TableName, onlyIn);
tbl.Drop();
Application.Current.Dispatcher.Invoke(() =>
{
if (onlyIn == a7DbComparedDataBases.A)
{
comparer.TablesOnlyInA.Remove(this);
}
else
{
comparer.TablesOnlyInB.Remove(this);
}
comparer.IsBusy = false;
});
});
}
);
}
public a7DbTableFieldCopyTo(string dbName, Column column, int colPos, Table tableExists, Table tableNotExists, Database isInDb, Database isNotIntDb, a7DbStructureComparer comparer, a7DbTableFieldDifferences tableFieldDifferencesExists)
{
Text = column.Name;
AddToOtherDbName = isNotIntDb.Name;
AddToOtherCommand = new a7LambdaCommand((o) =>
{
comparer.IsBusy = true;
Task.Factory.StartNew(() =>
{
a7DbTableUtils.CopyColumn(column, tableExists, tableNotExists, new Server(dbName), colPos);
tableNotExists.Alter();
Application.Current.Dispatcher.Invoke(() =>
{
tableFieldDifferencesExists.TableFieldsOnlyInA.Remove(this);
tableFieldDifferencesExists.TableFieldsOnlyInB.Remove(this);
comparer.IsBusy = false;
});
}).ContinueWith((t) =>
{
if (t.Exception != null)
{
throw t.Exception;
}
});
}
);
RemoveFromThisDbName = isInDb.Name;
RemoveFromThisCommand = new a7LambdaCommand((o) =>
{
comparer.IsBusy = true;
Task.Factory.StartNew(() =>
{
column.Drop();
Application.Current.Dispatcher.Invoke(() =>
{
tableFieldDifferencesExists.TableFieldsOnlyInA.Remove(this);
tableFieldDifferencesExists.TableFieldsOnlyInB.Remove(this);
comparer.IsBusy = false;
});
});
}
);
}
public a7DateTimePicker()
: base()
{
_opened = false;
_value2Changed = false;
_valueChanged = false;
this.Style = ResourcesManager.Instance.GetStyle("DateTimePickerStyle");
this.Format = DateTimeFormat.Custom;
this.FormatString = Thread.CurrentThread.CurrentCulture.DateTimeFormat.ShortDatePattern + " HH:mm";
ClearValuesCommand = new a7LambdaCommand((o) =>
{
Value = null;
Value2 = null;
this.IsOpen = false;
if (SelectedDateChanged != null)
SelectedDateChanged(this, null);
});
SelectValuesCommand = new a7LambdaCommand((o) =>
{
IsOpen = false;
if (SelectedDateChanged != null)
SelectedDateChanged(this, null);
});
}
public a7DbTableFieldDifferent(Column colA, Column colB, Table tableA, Table tableB, Database dbA, Database dbB, a7DbStructureComparer comparer, a7DbTableFieldDifferences differences)
{
DbAName = comparer.DbAName;
DbBName = comparer.DbBName;
TypeInA = colA.DataType.ToString();
if (colA.DataType.SqlDataType == SqlDataType.VarChar || colA.DataType.SqlDataType == SqlDataType.NVarChar ||
colA.DataType.SqlDataType == SqlDataType.Char || colA.DataType.SqlDataType == SqlDataType.NChar)
{
TypeInA += "(" + colA.DataType.MaximumLength + ")";
}
TypeInB = colB.DataType.ToString();
if (colB.DataType.SqlDataType == SqlDataType.VarChar || colB.DataType.SqlDataType == SqlDataType.NVarChar ||
colB.DataType.SqlDataType == SqlDataType.Char || colB.DataType.SqlDataType == SqlDataType.NChar)
{
TypeInB += "(" + colB.DataType.MaximumLength + ")";
}
Text = colA.Name;
CopyTypeToA = new a7LambdaCommand((o) =>
{
comparer.IsBusy = true;
Task.Factory.StartNew(() =>
{
if ((colA.DataType.SqlDataType == SqlDataType.VarChar &&
colB.DataType.SqlDataType == SqlDataType.VarChar) ||
(colA.DataType.SqlDataType == SqlDataType.NVarChar &&
colB.DataType.SqlDataType == SqlDataType.NVarChar))
{
colA.DataType.MaximumLength = colB.DataType.MaximumLength;
tableA.Alter();
Application.Current.Dispatcher.Invoke(() =>
{
differences.TableFieldsDifferentType.Remove(this);
});
}
else
{
Application.Current.Dispatcher.Invoke(() =>
{
MessageBox.Show("Not supported for non-varchar types");
});
}
Application.Current.Dispatcher.Invoke(() =>
{
comparer.IsBusy = false;
});
}).ContinueWith((t) =>
{
if (t.Exception != null)
{
throw t.Exception;
}
});
}
);
CopyTypeToB = new a7LambdaCommand((o) =>
{
comparer.IsBusy = true;
if ((colA.DataType.SqlDataType == SqlDataType.VarChar &&
colB.DataType.SqlDataType == SqlDataType.VarChar) ||
(colA.DataType.SqlDataType == SqlDataType.NVarChar &&
colB.DataType.SqlDataType == SqlDataType.NVarChar))
{
colB.DataType.MaximumLength = colA.DataType.MaximumLength;
tableB.Alter();
Application.Current.Dispatcher.Invoke(() =>
{
differences.TableFieldsDifferentType.Remove(this);
});
}
else
{
Application.Current.Dispatcher.Invoke(() =>
{
MessageBox.Show("Not supported for non-varchar types");
});
}
Application.Current.Dispatcher.Invoke(() =>
{
comparer.IsBusy = false;
});
}
);
}
public a7DbDataComparer(string dbName, string dbA, string dbB, Action <string> log)
{
_mergeWithDelete = false;
_log = log;
Srv = new Server(dbName);
DbA = Srv.Databases[dbA];
DbB = Srv.Databases[dbB];
MergeDirection = a7DbComparerDirection.None;
MergeAtoB = new a7LambdaCommand(async(o) =>
{
if (MergeDirection != a7DbComparerDirection.AtoB)
{
await SetMergeDirection(a7DbComparerDirection.AtoB);
}
else
{
await SetMergeDirection(a7DbComparerDirection.None);
}
}
);
MergeBtoA = new a7LambdaCommand(async(o) =>
{
if (MergeDirection != a7DbComparerDirection.BtoA)
{
await SetMergeDirection(a7DbComparerDirection.BtoA);
}
else
{
await SetMergeDirection(a7DbComparerDirection.None);
}
}
);
ShowGeneratedQuery = new a7LambdaCommand((o) =>
{
var sb = new StringBuilder();
foreach (var tbl in Tables)
{
if (tbl.Rows != null)
{
foreach (var rw in tbl.Rows)
{
if (rw.IsDifferent)
{
sb.Append(rw.SQL);
}
}
}
}
var str = sb.ToString();
if (string.IsNullOrWhiteSpace(str))
{
MessageBox.Show(
"Empty query, please select some at least one difference selector (like A>B or B>A) on a row, table or database level.");
return;
}
new a7GeneratedQueryWindow(str).Show();
}
);
var tableNamesA = new List <string>();
var tableNamesB = new List <string>();
var tableNamesAorB = new List <string>();
Log("Collecting table list");
foreach (var tbA in DbA.Tables)
{
var table = tbA as Table;
tableNamesA.Add(table.Name);
if (!tableNamesAorB.Contains(table.Name))
{
tableNamesAorB.Add(table.Name);
}
}
foreach (var tbB in DbB.Tables)
{
var table = tbB as Table;
tableNamesB.Add(table.Name);
if (!tableNamesAorB.Contains(table.Name))
{
tableNamesAorB.Add(table.Name);
}
}
TableNamesOnlyInA = new ObservableCollection <string>();
TableNamesOnlyInB = new ObservableCollection <string>();
TableNamesInBothDb = new ObservableCollection <string>();
Tables = new ObservableCollection <a7DbTableComparer>();
Log("Searching tables with differences tables");
foreach (var tblName in tableNamesAorB)
{
if (!tableNamesA.Contains(tblName) && tableNamesB.Contains(tblName))
{
TableNamesOnlyInB.Add(tblName);
continue;
}
if (!tableNamesB.Contains(tblName) && tableNamesA.Contains(tblName))
{
TableNamesOnlyInA.Add(tblName);
continue;
}
if (tableNamesA.Contains(tblName) && tableNamesB.Contains(tblName) && tblName != "adminQueriesLog" && tblName != "logExceptions" &&
tblName != "logQueries")
{
TableNamesInBothDb.Add(tblName);
var tblComp = new a7DbTableComparer(DbA.Tables[tblName], DbB.Tables[tblName], this);
if (tblComp.IsDifferentData)
{
Tables.Add(tblComp);
}
}
}
}
public a7DbTableComparer(Table tableA, Table tableB, a7DbDataComparer dataComparer)
{
IsSelected = true;
PrimaryKeyColumnsA = new List <string>();
PrimaryKeyColumnsB = new List <string>();
ColumnsA = new List <string>();
ColumnsB = new List <string>();
_mergeWithDelete = false;
MergeDirection = a7DbComparerDirection.None;
MergeAtoB = new a7LambdaCommand(async(o) =>
{
if (MergeDirection != a7DbComparerDirection.AtoB)
{
await SetMergeDirection(a7DbComparerDirection.AtoB);
}
else
{
await SetMergeDirection(a7DbComparerDirection.None);
}
}
);
MergeBtoA = new a7LambdaCommand(async(o) =>
{
if (MergeDirection != a7DbComparerDirection.BtoA)
{
await SetMergeDirection(a7DbComparerDirection.BtoA);
}
else
{
await SetMergeDirection(a7DbComparerDirection.None);
}
}
);
//AnalyzeTableCommand = new a7LambdaCommand((o) =>
// { AnalyzeTable().ContinueWith(t =>IsTableVisible = true); });
//HideTable = new a7LambdaCommand((o) => IsTableVisible = false);
//ShowTableCommand = new a7LambdaCommand((o) => IsTableVisible = true);
AnalyzeTableCommand = new a7LambdaCommand((o) =>
{ AnalyzeTable().ContinueWith(t => this.ShowTable()); });
ShowTableCommand = new a7LambdaCommand((o) => this.ShowTable());
dataComparer.Log("Testing for differences - '" + tableA.Name + "'");
_comparer = dataComparer;
_srv = dataComparer.Srv;
TableA = tableA;
TableB = tableB;
IsOK = true;
var sql = "Select * from {0}.dbo.{1}";
DataSetA = _srv.ConnectionContext.ExecuteWithResults(string.Format(sql, DbAName, tableA.Name));
DataTableA = DataSetA.Tables[0];
DataSetB = _srv.ConnectionContext.ExecuteWithResults(string.Format(sql, DbBName, tableB.Name));
DataTableB = DataSetB.Tables[0];
IsAnalyzedRows = false;
IsDifferentStructure = false;
var columnsInAorB = new List <string>();
//collect column and primary keys info
var pkColumnsA = new List <DataColumn>();
foreach (var clA in tableA.Columns)
{
var col = clA as Column;
if (col.InPrimaryKey)
{
pkColumnsA.Add(DataTableA.Columns[col.Name]);
PrimaryKeyColumnsA.Add(col.Name);
}
ColumnsA.Add(col.Name);
if (!columnsInAorB.Contains(col.Name))
{
columnsInAorB.Add(col.Name);
}
}
DataTableA.PrimaryKey = pkColumnsA.ToArray();
var pkColumnsB = new List <DataColumn>();
foreach (var clB in tableB.Columns)
{
var col = clB as Column;
if (col.InPrimaryKey)
{
pkColumnsB.Add(DataTableB.Columns[col.Name]);
PrimaryKeyColumnsB.Add(col.Name);
}
ColumnsB.Add(col.Name);
if (!columnsInAorB.Contains(col.Name))
{
columnsInAorB.Add(col.Name);
}
}
DataTableB.PrimaryKey = pkColumnsB.ToArray();
//compare primary keys if the same in both databases
if (pkColumnsA.Count == 0 && pkColumnsB.Count == 0)
{
IsOK = false;
IsDifferentData = true;
ErrorText = string.Format("{2}: Table in {0} and in {1} has no primary keys.", DbAName, DbBName, TableName);
}
else if (pkColumnsA.Count == 0)
{
IsOK = false;
IsDifferentData = true;
ErrorText = string.Format("{1}: Table in {0} has no primary keys.", DbAName, TableName);
}
else if (pkColumnsB.Count == 0)
{
IsOK = false;
IsDifferentData = true;
ErrorText = string.Format("{1}: Table in {0} has no primary keys.", DbBName, TableName);
}
else if (pkColumnsA.Count != pkColumnsB.Count)
{
IsOK = false;
IsDifferentData = true;
ErrorText = TableName + ": Different amount of primary key columns in both databases.";
}
else
{
var differentPk = false;
for (var i = 0; i < pkColumnsA.Count; i++)
{
if (pkColumnsA[i].ColumnName != pkColumnsB[i].ColumnName)
{
differentPk = true;
break;
}
}
if (differentPk)
{
IsOK = false;
IsDifferentData = true;
ErrorText = TableName + ": Primary key columns in both databases are different.";
}
}
if (IsOK)
{
//summary of column information
ColumnsOnlyInA = new ObservableCollection <string>();
ColumnsOnlyInB = new ObservableCollection <string>();
ColumnsInBothTables = new ObservableCollection <string>();
foreach (var cn in columnsInAorB)
{
if (!ColumnsA.Contains(cn) && ColumnsB.Contains(cn))
{
ColumnsOnlyInB.Add(cn);
continue;
}
if (!ColumnsB.Contains(cn) && ColumnsA.Contains(cn))
{
ColumnsOnlyInA.Add(cn);
continue;
}
if (ColumnsA.Contains(cn) && ColumnsB.Contains(cn))
{
ColumnsInBothTables.Add(cn);
}
}
var pkA = DataTableA.PrimaryKey;
var pkB = DataTableB.PrimaryKey;
IsDifferentData = false;
//compare the rows of the two tables
if (ColumnsOnlyInA.Count > 0 || ColumnsOnlyInB.Count > 0)
{//different number of columns
IsDifferentData = true;
IsDifferentStructure = true;
}
else if (DataTableA.Rows.Count != DataTableB.Rows.Count)
{//different number of rows
IsDifferentData = true;
}
else if (pkA.Length != pkB.Length)
{//different number of primary key columns
IsDifferentData = true;
}
else
{ //compare the row data
foreach (var rwA in DataTableA.Rows)
{ //foreach row in dtA
var rowA = rwA as DataRow;
var pkValues = new List <object>();
foreach (var pk in pkA)
{
pkValues.Add(rowA[pk.ColumnName]);
}
//get the row in dtB
var rowB = DataTableB.Rows.Find(pkValues.ToArray());
if (rowB == null)
{//no row in dtB for this row
IsDifferentData = true;
break;
}
else
{
//compare the field values
var isDataDifferentInRow = false;
foreach (var cn in ColumnsInBothTables)
{
if (cn != "dbVersion" && cn != "dbTimestamp" && cn != "dbUser")
{
if (rowA[cn]?.ToString() != rowB[cn]?.ToString())
{
isDataDifferentInRow = true;
break;
}
}
}
if (isDataDifferentInRow)
{
IsDifferentData = true;
break;
}
}
}
}
}
}
public a7ComparisonRow(DataRow rowA, DataRow rowB, a7DbTableComparer tableComparer, a7DbDataComparer comparer)
{
MergeDirection = a7DbComparerDirection.None;
MergeAtoB = new a7LambdaCommand((o) =>
{
if (MergeDirection != a7DbComparerDirection.AtoB)
{
SetMergeDirection(a7DbComparerDirection.AtoB, false);
}
else
{
SetMergeDirection(a7DbComparerDirection.None, false);
}
}
);
MergeBtoA = new a7LambdaCommand((o) =>
{
if (MergeDirection != a7DbComparerDirection.BtoA)
{
SetMergeDirection(a7DbComparerDirection.BtoA, false);
}
else
{
SetMergeDirection(a7DbComparerDirection.None, false);
}
}
);
_dataRowA = rowA;
_dataRowB = rowB;
_comparer = comparer;
_tblComparer = tableComparer;
if (_dataRowA == null && _dataRowB != null)
{
IsOnlyInB = true;
IsOnlyInA = false;
AtoBCaption = "B>X";
BtoACaption = "B>A";
}
else if (_dataRowB == null && _dataRowA != null)
{
IsOnlyInB = false;
IsOnlyInA = true;
AtoBCaption = "A>B";
BtoACaption = "A>X";
}
else
{
IsOnlyInA = false;
IsOnlyInB = false;
AtoBCaption = "A>B";
BtoACaption = "B>A";
}
Fields = new Dictionary <string, a7ComparisonField>();
IsDifferent = false;
for (var i = 0; i < tableComparer.ColumnsInBothTables.Count; i++)
{
var colName = tableComparer.ColumnsInBothTables[i];
object valA = null;
if (rowA != null)
{
valA = rowA[colName];
}
object valB = null;
if (rowB != null)
{
valB = rowB[colName];
}
Fields.Add(colName, new a7ComparisonField(valA, valB, rowA != null, rowB != null, colName, this._comparer));
if (valA?.ToString() != valB?.ToString() &&
colName != "dbVersion" && colName != "dbTimestamp" && colName != "dbUser")
{
IsDifferent = true;
}
}
for (var i = 0; i < tableComparer.ColumnsOnlyInA.Count; i++)
{
var colName = tableComparer.ColumnsOnlyInA[i];
object valA = null;
if (rowA != null)
{
valA = rowA[colName];
}
Fields.Add(colName, new a7ComparisonField(valA, null, true, false, colName, this._comparer));
IsDifferent = true;
}
for (var i = 0; i < tableComparer.ColumnsOnlyInB.Count; i++)
{
var colName = tableComparer.ColumnsOnlyInB[i];
object valB = null;
if (rowB != null)
{
valB = rowB[colName];
}
Fields.Add(colName, new a7ComparisonField(null, valB, false, true, colName, this._comparer));
IsDifferent = true;
}
}