public DbSynchro(DbInterpreter sourceInterpreter, DbInterpreter targetInterpreter)
{
this.sourceInterpreter = sourceInterpreter;
this.targetInterpreter = targetInterpreter;
this.tableManager = new TableManager(this.targetInterpreter);
this.targetScriptGenerator = DbScriptGeneratorHelper.GetDbScriptGenerator(targetInterpreter);
}
public async Task <ContentSaveResult> GenerateChangeScripts(SchemaDesignerInfo schemaDesignerInfo, bool isNew)
{
string validateMsg;
Table table = null;
try
{
bool isValid = this.ValidateModel(schemaDesignerInfo, out validateMsg);
if (!isValid)
{
return(this.GetFaultSaveResult(validateMsg));
}
TableDesignerGenerateScriptsData scriptsData = new TableDesignerGenerateScriptsData();
SchemaInfo schemaInfo = this.GetSchemaInfo(schemaDesignerInfo);
table = schemaInfo.Tables.First();
scriptsData.Table = table;
DbScriptGenerator scriptGenerator = DbScriptGeneratorHelper.GetDbScriptGenerator(this.dbInterpreter);
List <Script> scripts = new List <Script>();
if (isNew)
{
ScriptBuilder scriptBuilder = scriptGenerator.GenerateSchemaScripts(schemaInfo);
scripts = scriptBuilder.Scripts;
}
else
{
#region Alter Table
scripts = new List <Script>();
TableDesignerInfo tableDesignerInfo = schemaDesignerInfo.TableDesignerInfo;
SchemaInfoFilter filter = new SchemaInfoFilter()
{
Strict = true
};
filter.TableNames = new string[] { tableDesignerInfo.OldName };
filter.DatabaseObjectType = DatabaseObjectType.Table
| DatabaseObjectType.TableColumn
| DatabaseObjectType.TablePrimaryKey
| DatabaseObjectType.TableForeignKey
| DatabaseObjectType.TableIndex
| DatabaseObjectType.TableConstraint;
if (this.dbInterpreter.DatabaseType == DatabaseType.Oracle)
{
this.dbInterpreter.Option.IncludePrimaryKeyWhenGetTableIndex = true;
}
SchemaInfo oldSchemaInfo = await this.dbInterpreter.GetSchemaInfoAsync(filter);
Table oldTable = oldSchemaInfo.Tables.FirstOrDefault();
if (oldTable == null)
{
return(this.GetFaultSaveResult($"Table \"{tableDesignerInfo.OldName}\" is not existed"));
}
if (tableDesignerInfo.OldName != tableDesignerInfo.Name)
{
scripts.Add(scriptGenerator.RenameTable(new Table()
{
Owner = tableDesignerInfo.Owner, Name = tableDesignerInfo.OldName
}, tableDesignerInfo.Name));
}
if (!this.IsStringEquals(tableDesignerInfo.Comment, oldTable.Comment))
{
oldTable.Comment = tableDesignerInfo.Comment;
scripts.Add(scriptGenerator.SetTableComment(oldTable, string.IsNullOrEmpty(oldTable.Comment)));
}
#region Columns
List <TableColumnDesingerInfo> columnDesingerInfos = schemaDesignerInfo.TableColumnDesingerInfos;
List <TableColumn> oldColumns = oldSchemaInfo.TableColumns;
List <TableDefaultValueConstraint> defaultValueConstraints = null;
if (this.dbInterpreter.DatabaseType == DatabaseType.SqlServer)
{
defaultValueConstraints = await(this.dbInterpreter as SqlServerInterpreter).GetTableDefautValueConstraintsAsync(filter);
}
foreach (TableColumnDesingerInfo columnDesignerInfo in columnDesingerInfos)
{
TableColumn oldColumn = oldColumns.FirstOrDefault(item => item.Name.ToLower() == columnDesignerInfo.Name.ToLower());
TableColumn newColumn = schemaInfo.TableColumns.FirstOrDefault(item => item.Name == columnDesignerInfo.Name);
if (oldColumn == null)
{
scripts.Add(scriptGenerator.AddTableColumn(table, newColumn));
}
else
{
if (!this.IsValueEqualsIgnoreCase(columnDesignerInfo.OldName, columnDesignerInfo.Name))
{
scripts.Add(scriptGenerator.RenameTableColumn(table, oldColumn, newColumn.Name));
}
bool isDefaultValueEquals = this.IsStringEquals(ValueHelper.GetTrimedDefaultValue(oldColumn.DefaultValue), newColumn.DefaultValue);
if (!SchemaInfoHelper.IsTableColumnEquals(this.dbInterpreter.DatabaseType, oldColumn, newColumn) ||
!isDefaultValueEquals)
{
if (!isDefaultValueEquals)
{
if (this.dbInterpreter.DatabaseType == DatabaseType.SqlServer)
{
SqlServerScriptGenerator sqlServerScriptGenerator = scriptGenerator as SqlServerScriptGenerator;
TableDefaultValueConstraint defaultValueConstraint = defaultValueConstraints?.FirstOrDefault(item => item.Owner == oldTable.Owner && item.TableName == oldTable.Name && item.ColumnName == oldColumn.Name);
if (defaultValueConstraint != null)
{
scripts.Add(sqlServerScriptGenerator.DropDefaultValueConstraint(defaultValueConstraint));
}
scripts.Add(sqlServerScriptGenerator.AddDefaultValueConstraint(newColumn));
}
}
Script alterColumnScript = scriptGenerator.AlterTableColumn(table, newColumn);
if (this.dbInterpreter.DatabaseType == DatabaseType.Oracle)
{
if (!oldColumn.IsNullable && !newColumn.IsNullable)
{
alterColumnScript.Content = Regex.Replace(alterColumnScript.Content, "NOT NULL", "", RegexOptions.IgnoreCase);
}
else if (oldColumn.IsNullable && newColumn.IsNullable)
{
alterColumnScript.Content = Regex.Replace(alterColumnScript.Content, "NULL", "", RegexOptions.IgnoreCase);
}
}
scripts.Add(alterColumnScript);
}
else if (!this.IsStringEquals(columnDesignerInfo.Comment, oldColumn.Comment))
{
scripts.Add(scriptGenerator.SetTableColumnComment(table, newColumn, string.IsNullOrEmpty(oldColumn.Comment)));
}
}
}
foreach (TableColumn oldColumn in oldColumns)
{
if (!columnDesingerInfos.Any(item => item.Name == oldColumn.Name))
{
scripts.Add(scriptGenerator.DropTableColumn(oldColumn));
}
}
#endregion
#region Primary Key
TablePrimaryKey oldPrimaryKey = oldSchemaInfo.TablePrimaryKeys.FirstOrDefault();
TablePrimaryKey newPrimaryKey = schemaInfo.TablePrimaryKeys.FirstOrDefault();
bool primaryKeyChanged = !SchemaInfoHelper.IsPrimaryKeyEquals(oldPrimaryKey, newPrimaryKey, schemaDesignerInfo.IgnoreTableIndex, true);
Action alterPrimaryKey = () =>
{
if (oldPrimaryKey != null)
{
scripts.Add(scriptGenerator.DropPrimaryKey(oldPrimaryKey));
}
scripts.Add(scriptGenerator.AddPrimaryKey(newPrimaryKey));
if (!string.IsNullOrEmpty(newPrimaryKey.Comment))
{
this.SetTableChildComment(scripts, scriptGenerator, newPrimaryKey, true);
}
};
if (primaryKeyChanged)
{
alterPrimaryKey();
}
else if (!ValueHelper.IsStringEquals(oldPrimaryKey?.Comment, newPrimaryKey?.Comment))
{
if (this.dbInterpreter.DatabaseType == DatabaseType.SqlServer)
{
this.SetTableChildComment(scripts, scriptGenerator, newPrimaryKey, string.IsNullOrEmpty(oldPrimaryKey?.Comment));
}
else
{
alterPrimaryKey();
}
}
#endregion
#region Index
if (!schemaDesignerInfo.IgnoreTableIndex)
{
IEnumerable <TableIndex> oldIndexes = oldSchemaInfo.TableIndexes.Where(item => !item.IsPrimary);
IEnumerable <TableIndexDesignerInfo> indexDesignerInfos = schemaDesignerInfo.TableIndexDesingerInfos.Where(item => !item.IsPrimary);
foreach (TableIndexDesignerInfo indexDesignerInfo in indexDesignerInfos)
{
TableIndex newIndex = schemaInfo.TableIndexes.FirstOrDefault(item => item.Name == indexDesignerInfo.Name);
TableIndex oldIndex = oldIndexes.FirstOrDefault(item => item.Name == indexDesignerInfo.OldName);
if (this.IsValueEqualsIgnoreCase(indexDesignerInfo.OldName, indexDesignerInfo.Name) &&
this.IsValueEqualsIgnoreCase(indexDesignerInfo.OldType, indexDesignerInfo.Type)
)
{
if (oldIndex != null && this.IsStringEquals(oldIndex.Comment, newIndex.Comment) && SchemaInfoHelper.IsIndexColumnsEquals(oldIndex.Columns, newIndex.Columns))
{
continue;
}
}
if (oldIndex != null)
{
scripts.Add(scriptGenerator.DropIndex(oldIndex));
}
scripts.Add(scriptGenerator.AddIndex(newIndex));
if (!string.IsNullOrEmpty(newIndex.Comment))
{
this.SetTableChildComment(scripts, scriptGenerator, newIndex, true);
}
}
foreach (TableIndex oldIndex in oldIndexes)
{
if (!indexDesignerInfos.Any(item => item.Name == oldIndex.Name))
{
scripts.Add(scriptGenerator.DropIndex(oldIndex));
}
}
}
#endregion
#region Foreign Key
if (!schemaDesignerInfo.IgnoreTableForeignKey)
{
List <TableForeignKey> oldForeignKeys = oldSchemaInfo.TableForeignKeys;
IEnumerable <TableForeignKeyDesignerInfo> foreignKeyDesignerInfos = schemaDesignerInfo.TableForeignKeyDesignerInfos;
foreach (TableForeignKeyDesignerInfo foreignKeyDesignerInfo in foreignKeyDesignerInfos)
{
TableForeignKey newForeignKey = schemaInfo.TableForeignKeys.FirstOrDefault(item => item.Name == foreignKeyDesignerInfo.Name);
TableForeignKey oldForeignKey = oldForeignKeys.FirstOrDefault(item => item.Name == foreignKeyDesignerInfo.OldName);
if (this.IsValueEqualsIgnoreCase(foreignKeyDesignerInfo.OldName, foreignKeyDesignerInfo.Name) &&
foreignKeyDesignerInfo.UpdateCascade == oldForeignKey.UpdateCascade &&
foreignKeyDesignerInfo.DeleteCascade == oldForeignKey.DeleteCascade)
{
if (oldForeignKey != null && this.IsStringEquals(oldForeignKey.Comment, newForeignKey.Comment) &&
SchemaInfoHelper.IsForeignKeyColumnsEquals(oldForeignKey.Columns, newForeignKey.Columns))
{
continue;
}
}
if (oldForeignKey != null)
{
scripts.Add(scriptGenerator.DropForeignKey(oldForeignKey));
}
scripts.Add(scriptGenerator.AddForeignKey(newForeignKey));
if (!string.IsNullOrEmpty(newForeignKey.Comment))
{
this.SetTableChildComment(scripts, scriptGenerator, newForeignKey, true);
}
}
foreach (TableForeignKey oldForeignKey in oldForeignKeys)
{
if (!foreignKeyDesignerInfos.Any(item => item.Name == oldForeignKey.Name))
{
scripts.Add(scriptGenerator.DropForeignKey(oldForeignKey));
}
}
}
#endregion
#region Constraint
if (!schemaDesignerInfo.IgnoreTableConstraint)
{
List <TableConstraint> oldConstraints = oldSchemaInfo.TableConstraints;
IEnumerable <TableConstraintDesignerInfo> constraintDesignerInfos = schemaDesignerInfo.TableConstraintDesignerInfos;
foreach (TableConstraintDesignerInfo constraintDesignerInfo in constraintDesignerInfos)
{
TableConstraint newConstraint = schemaInfo.TableConstraints.FirstOrDefault(item => item.Name == constraintDesignerInfo.Name);
TableConstraint oldConstraint = oldConstraints.FirstOrDefault(item => item.Name == constraintDesignerInfo.OldName);
if (this.IsValueEqualsIgnoreCase(constraintDesignerInfo.OldName, constraintDesignerInfo.Name))
{
if (oldConstraint != null && this.IsStringEquals(oldConstraint.Comment, newConstraint.Comment))
{
continue;
}
}
if (oldConstraint != null)
{
scripts.Add(scriptGenerator.DropCheckConstraint(oldConstraint));
}
scripts.Add(scriptGenerator.AddCheckConstraint(newConstraint));
if (!string.IsNullOrEmpty(newConstraint.Comment))
{
this.SetTableChildComment(scripts, scriptGenerator, newConstraint, true);
}
}
foreach (TableConstraint oldConstraint in oldConstraints)
{
if (!constraintDesignerInfos.Any(item => item.Name == oldConstraint.Name))
{
scripts.Add(scriptGenerator.DropCheckConstraint(oldConstraint));
}
}
}
#endregion
#endregion
}
scriptsData.Scripts.AddRange(scripts);
return(new ContentSaveResult()
{
IsOK = true, ResultData = scriptsData
});
}
catch (Exception ex)
{
return(this.GetFaultSaveResult(ExceptionHelper.GetExceptionDetails(ex)));
}
}
public InterpreterDemo(DbInterpreter dbInterpreter)
{
this.interpreter = dbInterpreter;
this.dbScriptGenerator = DbScriptGeneratorHelper.GetDbScriptGenerator(this.interpreter);
}
private async void GenerateScripts()
{
SchemaInfo schemaInfo = this.tvDbObjects.GetSchemaInfo();
if (!this.Validate(schemaInfo))
{
return;
}
this.isBusy = true;
this.btnGenerate.Enabled = false;
DatabaseType dbType = this.useConnector ? this.dbConnectionProfile.DatabaseType : this.databaseType;
DbInterpreterOption option = new DbInterpreterOption()
{
ScriptOutputMode = GenerateScriptOutputMode.WriteToFile,
SortObjectsByReference = true,
GetTableAllObjects = true
};
if (this.chkTreatBytesAsNull.Checked)
{
option.TreatBytesAsNullForReading = true;
option.TreatBytesAsNullForExecuting = true;
}
else
{
if (dbType == DatabaseType.Oracle)
{
option.TreatBytesAsNullForReading = true;
option.TreatBytesAsNullForExecuting = true;
}
else
{
option.TreatBytesAsHexStringForFile = true;
}
}
this.SetGenerateScriptOption(option);
GenerateScriptMode scriptMode = this.GetGenerateScriptMode();
if (scriptMode == GenerateScriptMode.None)
{
MessageBox.Show("Please specify the script mode.");
return;
}
this.dbInterpreter = DbInterpreterHelper.GetDbInterpreter(dbType, this.connectionInfo, option);
SchemaInfoFilter filter = new SchemaInfoFilter();
SchemaInfoHelper.SetSchemaInfoFilterValues(filter, schemaInfo);
try
{
schemaInfo = await this.dbInterpreter.GetSchemaInfoAsync(filter);
this.dbInterpreter.Subscribe(this);
GenerateScriptMode mode = GenerateScriptMode.None;
DbScriptGenerator dbScriptGenerator = DbScriptGeneratorHelper.GetDbScriptGenerator(this.dbInterpreter);
if (scriptMode.HasFlag(GenerateScriptMode.Schema))
{
mode = GenerateScriptMode.Schema;
dbScriptGenerator.GenerateSchemaScripts(schemaInfo);
}
if (scriptMode.HasFlag(GenerateScriptMode.Data))
{
mode = GenerateScriptMode.Data;
await dbScriptGenerator.GenerateDataScriptsAsync(schemaInfo);
}
this.isBusy = false;
ManagerUtil.OpenInExplorer(dbScriptGenerator.GetScriptOutputFilePath(mode));
MessageBox.Show(DONE, "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
this.HandleException(ex);
}
this.btnGenerate.Enabled = true;
}
public async Task ClearData(List <Table> tables = null)
{
this.FeedbackInfo("Begin to clear data...");
if (tables == null)
{
tables = await this.dbInterpreter.GetTablesAsync();
}
bool failed = false;
DbTransaction transaction = null;
try
{
this.FeedbackInfo("Disable constrains.");
DbScriptGenerator scriptGenerator = DbScriptGeneratorHelper.GetDbScriptGenerator(this.dbInterpreter);
using (DbConnection dbConnection = this.dbInterpreter.CreateConnection())
{
dbConnection.Open();
await this.SetConstrainsEnabled(dbConnection, false);
transaction = dbConnection.BeginTransaction();
foreach (Table table in tables)
{
string sql = $"DELETE FROM {this.dbInterpreter.GetQuotedObjectName(table)}";
this.FeedbackInfo(sql);
CommandInfo commandInfo = new CommandInfo()
{
CommandType = CommandType.Text,
CommandText = sql,
Transaction = transaction
};
await this.dbInterpreter.ExecuteNonQueryAsync(dbConnection, commandInfo);
}
if (!this.dbInterpreter.HasError)
{
transaction.Commit();
}
await this.SetConstrainsEnabled(dbConnection, true);
}
}
catch (Exception ex)
{
failed = true;
this.FeedbackError(ExceptionHelper.GetExceptionDetails(ex));
if (transaction != null)
{
try
{
transaction.Rollback();
}
catch (Exception iex)
{
}
}
}
finally
{
if (failed)
{
this.FeedbackInfo("Enable constrains.");
await this.SetConstrainsEnabled(null, true);
}
}
this.FeedbackInfo("End clear data.");
}
public DbManager(DbInterpreter dbInterpreter)
{
this.dbInterpreter = dbInterpreter;
this.scriptGenerator = DbScriptGeneratorHelper.GetDbScriptGenerator(dbInterpreter);
}
private async Task InternalConvert(SchemaInfo schemaInfo = null)
{
DbInterpreter sourceInterpreter = this.Source.DbInterpreter;
sourceInterpreter.Option.BulkCopy = this.Option.BulkCopy;
sourceInterpreter.Subscribe(this.observer);
sourceInterpreter.Option.GetTableAllObjects = false;
sourceInterpreter.Option.ThrowExceptionWhenErrorOccurs = false;
this.Target.DbInterpreter.Option.ThrowExceptionWhenErrorOccurs = false;
if (string.IsNullOrEmpty(this.Target.DbOwner))
{
if (this.Target.DbInterpreter.DatabaseType == DatabaseType.Oracle)
{
this.Target.DbOwner = (this.Target.DbInterpreter as OracleInterpreter).GetDbOwner();
}
}
DatabaseObjectType databaseObjectType = (DatabaseObjectType)Enum.GetValues(typeof(DatabaseObjectType)).Cast <int>().Sum();
if (schemaInfo != null && !this.Source.DbInterpreter.Option.GetTableAllObjects &&
(schemaInfo.TableTriggers == null || schemaInfo.TableTriggers.Count == 0))
{
databaseObjectType = databaseObjectType ^ DatabaseObjectType.TableTrigger;
}
SchemaInfoFilter filter = new SchemaInfoFilter()
{
Strict = true, DatabaseObjectType = databaseObjectType
};
SchemaInfoHelper.SetSchemaInfoFilterValues(filter, schemaInfo);
SchemaInfo sourceSchemaInfo = await sourceInterpreter.GetSchemaInfoAsync(filter);
if (sourceInterpreter.HasError)
{
return;
}
sourceSchemaInfo.TableColumns = DbObjectHelper.ResortTableColumns(sourceSchemaInfo.Tables, sourceSchemaInfo.TableColumns);
if (SettingManager.Setting.NotCreateIfExists)
{
this.Target.DbInterpreter.Option.GetTableAllObjects = false;
SchemaInfo targetSchema = await this.Target.DbInterpreter.GetSchemaInfoAsync(filter);
SchemaInfoHelper.ExcludeExistingObjects(sourceSchemaInfo, targetSchema);
}
#region Set data type by user define type
List <UserDefinedType> utypes = new List <UserDefinedType>();
if (sourceInterpreter.DatabaseType != this.Target.DbInterpreter.DatabaseType)
{
utypes = await sourceInterpreter.GetUserDefinedTypesAsync();
if (utypes != null && utypes.Count > 0)
{
foreach (TableColumn column in sourceSchemaInfo.TableColumns)
{
UserDefinedType utype = utypes.FirstOrDefault(item => item.Name == column.DataType);
if (utype != null)
{
column.DataType = utype.Type;
column.MaxLength = utype.MaxLength;
}
}
}
}
#endregion
SchemaInfo targetSchemaInfo = SchemaInfoHelper.Clone(sourceSchemaInfo);
if (this.Source.TableNameMappings != null && this.Source.TableNameMappings.Count > 0)
{
SchemaInfoHelper.MapTableNames(targetSchemaInfo, this.Source.TableNameMappings);
}
if (this.Option.RenameTableChildren)
{
SchemaInfoHelper.RenameTableChildren(targetSchemaInfo);
}
if (this.Option.IgnoreNotSelfForeignKey)
{
targetSchemaInfo.TableForeignKeys = targetSchemaInfo.TableForeignKeys.Where(item => item.TableName == item.ReferencedTableName).ToList();
}
#region Translate
TranslateEngine translateEngine = new TranslateEngine(sourceSchemaInfo, targetSchemaInfo, sourceInterpreter, this.Target.DbInterpreter, this.Option, this.Target.DbOwner);
translateEngine.SkipError = this.Option.SkipScriptError || this.Option.OnlyForTranslate;
DatabaseObjectType translateDbObjectType = TranslateEngine.SupportDatabaseObjectType;
if (!this.Option.GenerateScriptMode.HasFlag(GenerateScriptMode.Schema) && this.Option.BulkCopy && this.Target.DbInterpreter.SupportBulkCopy)
{
translateDbObjectType = DatabaseObjectType.TableColumn;
}
translateEngine.UserDefinedTypes = utypes;
translateEngine.OnTranslated += this.Translated;
translateEngine.Subscribe(this.observer);
translateEngine.Translate(translateDbObjectType);
if (this.Option.OnlyForTranslate)
{
if (targetSchemaInfo.Tables.Count == 0 && targetSchemaInfo.UserDefinedTypes.Count == 0)
{
return;
}
}
#endregion
if (targetSchemaInfo.Tables.Any())
{
if (this.Option.EnsurePrimaryKeyNameUnique)
{
SchemaInfoHelper.EnsurePrimaryKeyNameUnique(targetSchemaInfo);
}
if (this.Option.EnsureIndexNameUnique)
{
SchemaInfoHelper.EnsureIndexNameUnique(targetSchemaInfo);
}
}
DbInterpreter targetInterpreter = this.Target.DbInterpreter;
bool generateIdentity = targetInterpreter.Option.TableScriptsGenerateOption.GenerateIdentity;
if (generateIdentity)
{
targetInterpreter.Option.InsertIdentityValue = true;
}
string script = "";
targetInterpreter.Subscribe(this.observer);
ScriptBuilder scriptBuilder = null;
DbScriptGenerator targetDbScriptGenerator = DbScriptGeneratorHelper.GetDbScriptGenerator(targetInterpreter);
if (this.Option.GenerateScriptMode.HasFlag(GenerateScriptMode.Schema))
{
scriptBuilder = targetDbScriptGenerator.GenerateSchemaScripts(targetSchemaInfo);
if (targetSchemaInfo.Tables.Any())
{
this.Translated(targetInterpreter.DatabaseType, targetSchemaInfo.Tables.First(), new TranslateResult()
{
Data = scriptBuilder.ToString()
});
}
}
if (this.Option.OnlyForTranslate)
{
return;
}
DataTransferErrorProfile dataErrorProfile = null;
using (DbConnection dbConnection = this.Option.ExecuteScriptOnTargetServer ? targetInterpreter.CreateConnection() : null)
{
this.isBusy = true;
if (this.Option.ExecuteScriptOnTargetServer && this.Option.UseTransaction)
{
dbConnection.Open();
this.transaction = dbConnection.BeginTransaction();
}
#region Schema sync
if (scriptBuilder != null && this.Option.ExecuteScriptOnTargetServer)
{
List <Script> scripts = scriptBuilder.Scripts;
if (scripts.Count == 0)
{
this.Feedback(targetInterpreter, $"The script to create schema is empty.", FeedbackInfoType.Info);
this.isBusy = false;
return;
}
targetInterpreter.Feedback(FeedbackInfoType.Info, "Begin to sync schema...");
try
{
if (!this.Option.SplitScriptsToExecute)
{
targetInterpreter.Feedback(FeedbackInfoType.Info, script);
await targetInterpreter.ExecuteNonQueryAsync(dbConnection, this.GetCommandInfo(script, null, this.transaction));
}
else
{
Func <Script, bool> isValidScript = (s) =>
{
return(!(s is NewLineSript || s is SpliterScript || string.IsNullOrEmpty(s.Content) || s.Content == targetInterpreter.ScriptsDelimiter));
};
int count = scripts.Where(item => isValidScript(item)).Count();
int i = 0;
foreach (Script s in scripts)
{
if (targetInterpreter.HasError)
{
break;
}
if (!isValidScript(s))
{
continue;
}
bool isCreateScript = s.ObjectType == nameof(Function) || s.ObjectType == nameof(Procedure) || s.ObjectType == nameof(TableTrigger);
bool skipError = this.Option.SkipScriptError && isCreateScript;
string sql = s.Content?.Trim();
if (!string.IsNullOrEmpty(sql) && sql != targetInterpreter.ScriptsDelimiter)
{
i++;
if (!isCreateScript && targetInterpreter.ScriptsDelimiter.Length == 1 && sql.EndsWith(targetInterpreter.ScriptsDelimiter))
{
sql = sql.TrimEnd(targetInterpreter.ScriptsDelimiter.ToArray());
}
if (!targetInterpreter.HasError)
{
targetInterpreter.Feedback(FeedbackInfoType.Info, $"({i}/{count}), executing:{Environment.NewLine} {sql}");
CommandInfo commandInfo = this.GetCommandInfo(sql, null, transaction);
commandInfo.SkipError = skipError;
await targetInterpreter.ExecuteNonQueryAsync(dbConnection, commandInfo);
}
}
}
}
}
catch (Exception ex)
{
targetInterpreter.CancelRequested = true;
this.Rollback();
ConnectionInfo sourceConnectionInfo = sourceInterpreter.ConnectionInfo;
ConnectionInfo targetConnectionInfo = targetInterpreter.ConnectionInfo;
SchemaTransferException schemaTransferException = new SchemaTransferException(ex)
{
SourceServer = sourceConnectionInfo.Server,
SourceDatabase = sourceConnectionInfo.Database,
TargetServer = targetConnectionInfo.Server,
TargetDatabase = targetConnectionInfo.Database
};
this.HandleError(schemaTransferException);
}
targetInterpreter.Feedback(FeedbackInfoType.Info, "End sync schema.");
}
#endregion
#region Data sync
if (!targetInterpreter.HasError && this.Option.GenerateScriptMode.HasFlag(GenerateScriptMode.Data) && sourceSchemaInfo.Tables.Count > 0)
{
List <TableColumn> identityTableColumns = new List <TableColumn>();
if (generateIdentity)
{
identityTableColumns = targetSchemaInfo.TableColumns.Where(item => item.IsIdentity).ToList();
}
if (this.Option.PickupTable)
{
dataErrorProfile = DataTransferErrorProfileManager.GetProfile(sourceInterpreter.ConnectionInfo, targetInterpreter.ConnectionInfo);
if (dataErrorProfile != null)
{
sourceSchemaInfo.PickupTable = new Table()
{
Owner = schemaInfo.Tables.FirstOrDefault()?.Owner, Name = dataErrorProfile.SourceTableName
};
}
}
await this.SetIdentityEnabled(identityTableColumns, targetInterpreter, targetDbScriptGenerator, dbConnection, transaction, false);
if (this.Option.ExecuteScriptOnTargetServer || targetInterpreter.Option.ScriptOutputMode.HasFlag(GenerateScriptOutputMode.WriteToFile))
{
Dictionary <Table, long> dictTableDataTransferredCount = new Dictionary <Table, long>();
sourceInterpreter.OnDataRead += async(TableDataReadInfo tableDataReadInfo) =>
{
if (!this.hasError)
{
Table table = tableDataReadInfo.Table;
List <TableColumn> columns = tableDataReadInfo.Columns;
try
{
(Table Table, List <TableColumn> Columns)targetTableAndColumns = this.GetTargetTableColumns(targetSchemaInfo, this.Target.DbOwner, table, columns);
if (targetTableAndColumns.Table == null || targetTableAndColumns.Columns == null)
{
return;
}
if (this.Option.ExecuteScriptOnTargetServer)
{
DataTable dataTable = tableDataReadInfo.DataTable;
List <Dictionary <string, object> > data = tableDataReadInfo.Data;
if (this.Option.BulkCopy && targetInterpreter.SupportBulkCopy)
{
BulkCopyInfo bulkCopyInfo = this.GetBulkCopyInfo(table, targetSchemaInfo, this.transaction);
if (targetInterpreter.DatabaseType == DatabaseType.Oracle)
{
if (columns.Any(item => item.DataType.ToLower().Contains("datetime2") || item.DataType.ToLower().Contains("timestamp")))
{
bulkCopyInfo.DetectDateTimeTypeByValues = true;
}
}
if (this.Option.ConvertComputeColumnExpression)
{
IEnumerable <DataColumn> dataColumns = dataTable.Columns.OfType <DataColumn>();
foreach (TableColumn column in bulkCopyInfo.Columns)
{
if (column.IsComputed && dataColumns.Any(item => item.ColumnName == column.Name))
{
dataTable.Columns.Remove(column.Name);
}
}
}
await targetInterpreter.BulkCopyAsync(dbConnection, dataTable, bulkCopyInfo);
}
else
{
StringBuilder sb = new StringBuilder();
Dictionary <string, object> paramters = targetDbScriptGenerator.AppendDataScripts(sb, targetTableAndColumns.Table, targetTableAndColumns.Columns, new Dictionary <long, List <Dictionary <string, object> > >()
{
{ 1, data }
});
script = sb.ToString().Trim().Trim(';');
await targetInterpreter.ExecuteNonQueryAsync(dbConnection, this.GetCommandInfo(script, paramters, this.transaction));
}
if (!dictTableDataTransferredCount.ContainsKey(table))
{
dictTableDataTransferredCount.Add(table, dataTable.Rows.Count);
}
else
{
dictTableDataTransferredCount[table] += dataTable.Rows.Count;
}
long transferredCount = dictTableDataTransferredCount[table];
double percent = (transferredCount * 1.0 / tableDataReadInfo.TotalCount) * 100;
string strPercent = (percent == (int)percent) ? (percent + "%") : (percent / 100).ToString("P2");
targetInterpreter.FeedbackInfo($"Table \"{table.Name}\":{dataTable.Rows.Count} records transferred.({transferredCount}/{tableDataReadInfo.TotalCount},{strPercent})");
}
}
catch (Exception ex)
{
sourceInterpreter.CancelRequested = true;
this.Rollback();
ConnectionInfo sourceConnectionInfo = sourceInterpreter.ConnectionInfo;
ConnectionInfo targetConnectionInfo = targetInterpreter.ConnectionInfo;
string mappedTableName = this.GetMappedTableName(table.Name);
DataTransferException dataTransferException = new DataTransferException(ex)
{
SourceServer = sourceConnectionInfo.Server,
SourceDatabase = sourceConnectionInfo.Database,
SourceObject = table.Name,
TargetServer = targetConnectionInfo.Server,
TargetDatabase = targetConnectionInfo.Database,
TargetObject = mappedTableName
};
this.HandleError(dataTransferException);
if (!this.Option.UseTransaction)
{
DataTransferErrorProfileManager.Save(new DataTransferErrorProfile
{
SourceServer = sourceConnectionInfo.Server,
SourceDatabase = sourceConnectionInfo.Database,
SourceTableName = table.Name,
TargetServer = targetConnectionInfo.Server,
TargetDatabase = targetConnectionInfo.Database,
TargetTableName = mappedTableName
});
}
}
}
};
}
DbScriptGenerator sourceDbScriptGenerator = DbScriptGeneratorHelper.GetDbScriptGenerator(sourceInterpreter);
await sourceDbScriptGenerator.GenerateDataScriptsAsync(sourceSchemaInfo);
await this.SetIdentityEnabled(identityTableColumns, targetInterpreter, targetDbScriptGenerator, dbConnection, transaction, true);
}
#endregion
if (this.transaction != null && this.transaction.Connection != null && !this.cancelRequested)
{
this.transaction.Commit();
}
this.isBusy = false;
}
if (dataErrorProfile != null && !this.hasError && !this.cancelRequested)
{
DataTransferErrorProfileManager.Remove(dataErrorProfile);
}
}
public async Task <string> Generate(DatabaseObject dbObject, ScriptAction scriptAction)
{
string typeName = dbObject.GetType().Name;
if (dbObject is Table)
{
dbInterpreter.Option.GetTableAllObjects = true;
}
DatabaseObjectType databaseObjectType = (DatabaseObjectType)Enum.Parse(typeof(DatabaseObjectType), typeName);
SchemaInfoFilter filter = new SchemaInfoFilter()
{
DatabaseObjectType = databaseObjectType
};
filter.GetType().GetProperty($"{typeName}Names").SetValue(filter, new string[] { dbObject.Name });
SchemaInfo schemaInfo = await dbInterpreter.GetSchemaInfoAsync(filter);
DbScriptGenerator dbScriptGenerator = DbScriptGeneratorHelper.GetDbScriptGenerator(dbInterpreter);
List <Script> scripts = dbScriptGenerator.GenerateSchemaScripts(schemaInfo).Scripts;
StringBuilder sbContent = new StringBuilder();
DatabaseType databaseType = this.dbInterpreter.DatabaseType;
foreach (Script script in scripts)
{
if (databaseType == DatabaseType.SqlServer && script is SpliterScript)
{
continue;
}
string content = script.Content;
if (scriptAction == ScriptAction.ALTER && typeName != nameof(Table))
{
string objType = typeName;
if (typeName == nameof(TableTrigger))
{
objType = "TRIGGER";
}
string createFlag = "CREATE ";
int createFlagIndex = this.GetCreateIndex(content, createFlag);
if (createFlagIndex >= 0)
{
switch (databaseType)
{
case DatabaseType.SqlServer:
content = content.Substring(0, createFlagIndex) + "ALTER " + content.Substring(createFlagIndex + createFlag.Length);
break;
case DatabaseType.MySql:
content = $"DROP {objType} IF EXISTS {this.dbInterpreter.GetQuotedString(dbObject.Name)};" + Environment.NewLine + content;
break;
case DatabaseType.Oracle:
if (!Regex.IsMatch(content, @"^(CREATE[\s]+OR[\s]+REPLACE[\s]+)", RegexOptions.IgnoreCase))
{
content = content.Substring(0, createFlagIndex) + "CREATE OR REPLACE " + content.Substring(createFlagIndex + createFlag.Length);
}
break;
}
}
}
sbContent.AppendLine(content);
}
return(sbContent.ToString());
}
private async void Compare(DatabaseObjectType databaseObjectType)
{
DatabaseType dbType;
if (this.useSourceConnector)
{
dbType = this.sourceDbProfile.DatabaseType;
if (!this.sourceDbProfile.IsDbTypeSelected())
{
MessageBox.Show("Please select a source database type.");
return;
}
if (!this.sourceDbProfile.IsProfileSelected())
{
MessageBox.Show("Please select a source database profile.");
return;
}
if (!this.sourceDbConnectionInfo.IntegratedSecurity && string.IsNullOrEmpty(this.sourceDbConnectionInfo.Password))
{
MessageBox.Show("Please specify password for the source database.");
this.sourceDbProfile.ConfigConnection(true);
return;
}
}
else
{
dbType = this.sourceDatabaseType;
}
if (this.sourceDbConnectionInfo == null)
{
MessageBox.Show("Source connection is null.");
return;
}
if (this.targetDbConnectionInfo == null)
{
MessageBox.Show("Target connection info is null.");
return;
}
if (dbType != this.targetDbProfile.DatabaseType)
{
MessageBox.Show("Target database type must be same as source database type.");
return;
}
if (this.sourceDbConnectionInfo.Server == this.targetDbConnectionInfo.Server && this.sourceDbConnectionInfo.Database == this.targetDbConnectionInfo.Database)
{
MessageBox.Show("Source database cannot be equal to the target database.");
return;
}
this.btnCompare.Text = "...";
this.btnCompare.Enabled = false;
try
{
DbInterpreterOption sourceOption = new DbInterpreterOption();
DbInterpreterOption targetOption = new DbInterpreterOption();
SchemaInfoFilter sourceFilter = new SchemaInfoFilter()
{
DatabaseObjectType = databaseObjectType
};
SchemaInfoFilter targetFilter = new SchemaInfoFilter()
{
DatabaseObjectType = databaseObjectType
};
if (databaseObjectType.HasFlag(DatabaseObjectType.Table))
{
sourceOption.GetTableAllObjects = true;
targetOption.GetTableAllObjects = true;
}
this.sourceInterpreter = DbInterpreterHelper.GetDbInterpreter(dbType, this.sourceDbConnectionInfo, sourceOption);
this.targetInterpreter = DbInterpreterHelper.GetDbInterpreter(this.targetDbProfile.DatabaseType, this.targetDbConnectionInfo, targetOption);
this.sourceScriptGenerator = DbScriptGeneratorHelper.GetDbScriptGenerator(this.sourceInterpreter);
this.targetScriptGenerator = DbScriptGeneratorHelper.GetDbScriptGenerator(this.targetInterpreter);
this.sourceInterpreter.Subscribe(this);
this.targetInterpreter.Subscribe(this);
this.sourceSchemaInfo = await this.sourceInterpreter.GetSchemaInfoAsync(sourceFilter);
this.targetSchemaInfo = await this.targetInterpreter.GetSchemaInfoAsync(targetFilter);
DbCompare dbCompare = new DbCompare(sourceSchemaInfo, targetSchemaInfo);
this.Feedback("Begin to compare...");
this.differences = dbCompare.Compare();
this.Feedback("End compare.");
this.LoadData();
}
catch (Exception ex)
{
string message = ExceptionHelper.GetExceptionDetails(ex);
LogHelper.LogError(message);
MessageBox.Show("Error:" + message);
}
finally
{
this.btnCompare.Text = "Compare";
this.btnCompare.Enabled = true;
}
}