private async Task <bool> IsNameExisted()
{
DatabaseType databaseType = this.rbAnotherDatabase.Checked ? this.ucConnection.DatabaseType : this.DatabaseType;
ConnectionInfo connectionInfo = this.rbAnotherDatabase.Checked ? this.targetDbConnectionInfo : this.ConnectionInfo;
DbInterpreterOption option = new DbInterpreterOption()
{
ObjectFetchMode = DatabaseObjectFetchMode.Simple
};
DbInterpreter dbInterpreter = DbInterpreterHelper.GetDbInterpreter(databaseType, connectionInfo, option);
SchemaInfoFilter filter = new SchemaInfoFilter()
{
TableNames = new string[] { this.txtName.Text.Trim() }
};
var tables = await dbInterpreter.GetTablesAsync(filter);
if (tables.Count > 0)
{
return(true);
}
return(false);
}
public DbScriptGenerator(DbInterpreter dbInterpreter)
{
this.dbInterpreter = dbInterpreter;
this.option = dbInterpreter.Option;
this.databaseType = dbInterpreter.DatabaseType;
this.scriptsDelimiter = dbInterpreter.ScriptsDelimiter;
}
private async void SetupIntellisence()
{
if (this.CheckConnection())
{
DbInterpreterOption option = new DbInterpreterOption()
{
ObjectFetchMode = DatabaseObjectFetchMode.Simple
};
DbInterpreter dbInterpreter = DbInterpreterHelper.GetDbInterpreter(this.displayInfo.DatabaseType, this.displayInfo.ConnectionInfo, option);
this.queryEditor.DbInterpreter = dbInterpreter;
SchemaInfoFilter filter = new SchemaInfoFilter()
{
DatabaseObjectType = DatabaseObjectType.Table
| DatabaseObjectType.Function
| DatabaseObjectType.View
| DatabaseObjectType.TableColumn
};
SchemaInfo schemaInfo = await dbInterpreter.GetSchemaInfoAsync(filter);
DataStore.SetSchemaInfo(this.displayInfo.DatabaseType, schemaInfo);
this.queryEditor.SetupIntellisence();
}
}
public virtual async Task <DiagnoseResult> DiagnoseNotNullWithEmpty()
{
this.Feedback("Begin to diagnose not null fields with empty value...");
DiagnoseResult result = new DiagnoseResult();
DbInterpreterOption option = new DbInterpreterOption()
{
ObjectFetchMode = DatabaseObjectFetchMode.Simple
};
DbInterpreter interpreter = DbInterpreterHelper.GetDbInterpreter(this.DatabaseType, this.connectionInfo, option);
this.Feedback("Begin to get table columns...");
List <TableColumn> columns = await interpreter.GetTableColumnsAsync();
this.Feedback("End get table columns.");
var groups = columns.Where(item => DataTypeHelper.IsCharType(item.DataType) && !item.IsNullable)
.GroupBy(item => new { item.Owner, item.TableName });
using (DbConnection dbConnection = interpreter.CreateConnection())
{
foreach (var group in groups)
{
foreach (TableColumn column in group)
{
string countSql = this.GetTableColumnEmptySql(interpreter, column, true);
this.Feedback($@"Begin to get invalid record count for column ""{column.Name}"" of table ""{column.TableName}""...");
int count = Convert.ToInt32(await interpreter.GetScalarAsync(dbConnection, countSql));
this.Feedback($@"End get invalid record count for column ""{column.Name}"" of table ""{column.TableName}"", the count is {count}.");
if (count > 0)
{
result.Details.Add(new DiagnoseResultItem()
{
DatabaseObject = column,
RecordCount = count,
Sql = this.GetTableColumnEmptySql(interpreter, column, false)
});
}
}
}
}
this.Feedback("End diagnose not null fields with empty value.");
return(result);
}
public virtual async Task <DiagnoseResult> DiagnoseSelfReferenceSame()
{
this.Feedback("Begin to diagnose self reference with same value...");
DiagnoseResult result = new DiagnoseResult();
DbInterpreterOption option = new DbInterpreterOption()
{
ObjectFetchMode = DatabaseObjectFetchMode.Details
};
DbInterpreter interpreter = DbInterpreterHelper.GetDbInterpreter(this.DatabaseType, this.connectionInfo, option);
this.Feedback("Begin to get foreign keys...");
List <TableForeignKey> foreignKeys = await interpreter.GetTableForeignKeysAsync();
this.Feedback("End get foreign keys.");
var groups = foreignKeys.Where(item => item.ReferencedTableName == item.TableName)
.GroupBy(item => new { item.Owner, item.TableName });
using (DbConnection dbConnection = interpreter.CreateConnection())
{
foreach (var group in groups)
{
foreach (TableForeignKey foreignKey in group)
{
string countSql = this.GetTableColumnReferenceSql(interpreter, foreignKey, true);
this.Feedback($@"Begin to get invalid record count for foreign key ""{foreignKey.Name}"" of table ""{foreignKey.TableName}""...");
int count = Convert.ToInt32(await interpreter.GetScalarAsync(dbConnection, countSql));
this.Feedback($@"End get invalid record count for column ""{foreignKey.Name}"" of table ""{foreignKey.TableName}"", the count is {count}.");
if (count > 0)
{
result.Details.Add(new DiagnoseResultItem()
{
DatabaseObject = foreignKey,
RecordCount = count,
Sql = this.GetTableColumnReferenceSql(interpreter, foreignKey, false)
});
}
}
}
}
this.Feedback("End diagnose self reference with same value.");
return(result);
}
private DbInterpreter GetDbInterpreter(string database, bool isSimpleMode = true)
{
ConnectionInfo connectionInfo = this.GetConnectionInfo(database);
DbInterpreterOption option = isSimpleMode ? simpleInterpreterOption : new DbInterpreterOption()
{
ObjectFetchMode = DatabaseObjectFetchMode.Details
};
option.ThrowExceptionWhenErrorOccurs = false;
DbInterpreter dbInterpreter = DbInterpreterHelper.GetDbInterpreter(this.databaseType, connectionInfo, option);
return(dbInterpreter);
}
public async Task LoadTree(DatabaseType dbType, ConnectionInfo connectionInfo, DatabaseObjectType excludeObjType = DatabaseObjectType.None)
{
this.tvDbObjects.Nodes.Clear();
DbInterpreterOption option = new DbInterpreterOption()
{
ObjectFetchMode = DatabaseObjectFetchMode.Simple
};
DatabaseObjectType databaseObjectType = DatabaseObjectType.None;
databaseObjectType = DbObjectsTreeHelper.DefaultObjectType;
if (excludeObjType != DatabaseObjectType.None)
{
databaseObjectType = databaseObjectType ^ DatabaseObjectType.UserDefinedType;
}
DbInterpreter dbInterpreter = DbInterpreterHelper.GetDbInterpreter(dbType, connectionInfo, option);
SchemaInfoFilter filter = new SchemaInfoFilter()
{
DatabaseObjectType = databaseObjectType
};
SchemaInfo schemaInfo = await dbInterpreter.GetSchemaInfoAsync(filter);
this.tvDbObjects.Nodes.AddDbObjectFolderNode(nameof(UserDefinedType), "User Defined Types", schemaInfo.UserDefinedTypes);
this.tvDbObjects.Nodes.AddDbObjectFolderNode(nameof(Table), "Tables", schemaInfo.Tables);
this.tvDbObjects.Nodes.AddDbObjectFolderNode(nameof(DatabaseInterpreter.Model.View), "Views", schemaInfo.Views);
this.tvDbObjects.Nodes.AddDbObjectFolderNode(nameof(Function), "Functions", schemaInfo.Functions);
this.tvDbObjects.Nodes.AddDbObjectFolderNode(nameof(Procedure), "Procedures", schemaInfo.Procedures);
this.tvDbObjects.Nodes.AddDbObjectFolderNode(nameof(TableTrigger), "Triggers", schemaInfo.TableTriggers);
if (this.tvDbObjects.Nodes.Count == 1)
{
this.tvDbObjects.ExpandAll();
}
}
private async void LoadSourceDbSchemaInfo()
{
this.tvSource.Nodes.Clear();
DatabaseType dbType = this.GetDatabaseType(this.cboSourceDB.Text);
DbInterpreterOption option = new DbInterpreterOption()
{
ObjectFetchMode = DatabaseObjectFetchMode.Simple
};
DbInterpreter dbInterpreter = DbInterpreterHelper.GetDbInterpreter(dbType, this.sourceDbConnectionInfo, option);
if (dbInterpreter is SqlServerInterpreter)
{
List <UserDefinedType> userDefinedTypes = await dbInterpreter.GetUserDefinedTypesAsync();
if (userDefinedTypes.Count > 0)
{
TreeNode userDefinedRootNode = new TreeNode("User Defined Types");
userDefinedRootNode.Name = nameof(UserDefinedType);
this.tvSource.Nodes.Add(userDefinedRootNode);
foreach (UserDefinedType userDefinedType in userDefinedTypes)
{
TreeNode node = new TreeNode();
node.Tag = userDefinedType;
node.Text = $"{userDefinedType.Owner}.{userDefinedType.Name}";
userDefinedRootNode.Nodes.Add(node);
}
}
}
TreeNode tableRootNode = new TreeNode("Tables");
tableRootNode.Name = nameof(Table);
this.tvSource.Nodes.Add(tableRootNode);
List <Table> tables = await dbInterpreter.GetTablesAsync();
foreach (Table table in tables)
{
TreeNode tableNode = new TreeNode();
tableNode.Tag = table;
tableNode.Text = dbInterpreter.GetObjectDisplayName(table, false);
tableRootNode.Nodes.Add(tableNode);
}
TreeNode viewRootNode = new TreeNode("Views");
viewRootNode.Name = nameof(DatabaseInterpreter.Model.View);
this.tvSource.Nodes.Add(viewRootNode);
List <DatabaseInterpreter.Model.View> views = ViewHelper.ResortViews(await dbInterpreter.GetViewsAsync());
foreach (var view in views)
{
TreeNode viewNode = new TreeNode();
viewNode.Tag = view;
viewNode.Text = dbInterpreter.GetObjectDisplayName(view, false);
viewRootNode.Nodes.Add(viewNode);
}
}
public DbInterpreter(ConnectionInfo connectionInfo, DbInterpreterOption option)
{
this.ConnectionInfo = connectionInfo;
this.Option = option;
}
private async Task CopyTable()
{
string name = this.txtName.Text.Trim();
if (!this.ValidateInputs())
{
return;
}
try
{
GenerateScriptMode scriptMode = this.GetGenerateScriptMode();
bool isTableExisted = false;
if (scriptMode.HasFlag(GenerateScriptMode.Schema))
{
isTableExisted = await this.IsNameExisted();
}
if (isTableExisted)
{
name = name + "_copy";
}
SchemaInfo schemaInfo = new SchemaInfo();
schemaInfo.Tables.Add(this.Table);
DatabaseType targetDatabaseType = this.rbAnotherDatabase.Checked ? this.ucConnection.DatabaseType : this.DatabaseType;
ConnectionInfo targetConnectionInfo = this.rbAnotherDatabase.Checked ? this.targetDbConnectionInfo : this.ConnectionInfo;
DbInterpreterOption sourceOption = new DbInterpreterOption();
DbInterpreterOption targetOption = new DbInterpreterOption();
targetOption.TableScriptsGenerateOption.GenerateIdentity = this.chkGenerateIdentity.Checked;
DbConveterInfo source = new DbConveterInfo()
{
DbInterpreter = DbInterpreterHelper.GetDbInterpreter(this.DatabaseType, this.ConnectionInfo, sourceOption)
};
DbConveterInfo target = new DbConveterInfo()
{
DbInterpreter = DbInterpreterHelper.GetDbInterpreter(targetDatabaseType, targetConnectionInfo, targetOption)
};
source.TableNameMappings.Add(this.Table.Name, name);
this.btnExecute.Enabled = false;
using (this.dbConverter = new DbConverter(source, target))
{
this.dbConverter.Option.RenameTableChildren = isTableExisted || this.rbSameDatabase.Checked;
this.dbConverter.Option.GenerateScriptMode = scriptMode;
this.dbConverter.Option.BulkCopy = true;
this.dbConverter.Option.UseTransaction = true;
this.dbConverter.Option.ConvertComputeColumnExpression = true;
this.dbConverter.Option.IgnoreNotSelfForeignKey = true;
this.dbConverter.Subscribe(this);
if (this.DatabaseType == DatabaseType.MySql)
{
source.DbInterpreter.Option.InQueryItemLimitCount = 2000;
}
if (this.DatabaseType != targetDatabaseType)
{
if (targetDatabaseType == DatabaseType.SqlServer)
{
target.DbOwner = "dbo";
}
else if (targetDatabaseType == DatabaseType.MySql)
{
target.DbInterpreter.Option.RemoveEmoji = true;
}
}
else
{
target.DbOwner = this.Table.Owner;
}
this.dbConverter.Option.SplitScriptsToExecute = true;
await this.dbConverter.Convert(schemaInfo);
if (!this.hasError && !this.dbConverter.HasError && !source.DbInterpreter.HasError && !target.DbInterpreter.HasError)
{
if (!this.dbConverter.CancelRequested)
{
MessageBox.Show("Copy finished", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Task has been canceled.");
}
}
}
}
catch (Exception ex)
{
this.hasError = true;
this.HandleException(ex);
}
finally
{
this.btnExecute.Enabled = true;
}
}
public async Task <QueryResult> Run(DatabaseType dbType, ConnectionInfo connectionInfo, string script)
{
this.cancelRequested = false;
this.isBusy = false;
QueryResult result = new QueryResult();
DbInterpreterOption option = new DbInterpreterOption()
{
RequireInfoMessage = true
};
DbInterpreter dbInterpreter = DbInterpreterHelper.GetDbInterpreter(dbType, connectionInfo, option);
dbInterpreter.Subscribe(this.observer);
try
{
ScriptParser scriptParser = new ScriptParser(dbInterpreter, script);
string cleanScript = scriptParser.CleanScript;
if (string.IsNullOrEmpty(cleanScript))
{
result.DoNothing = true;
return(result);
}
using (DbConnection dbConnection = dbInterpreter.CreateConnection())
{
if (scriptParser.IsSelect())
{
this.isBusy = true;
result.ResultType = QueryResultType.Grid;
if (!scriptParser.IsCreateOrAlterScript() && dbInterpreter.ScriptsDelimiter.Length == 1)
{
cleanScript = script.TrimEnd(dbInterpreter.ScriptsDelimiter[0]);
}
DataTable dataTable = await dbInterpreter.GetDataTableAsync(dbConnection, script, this.LimitCount);
result.Result = dataTable;
}
else
{
this.isBusy = true;
result.ResultType = QueryResultType.Text;
dbConnection.Open();
this.transaction = dbConnection.BeginTransaction();
IEnumerable <string> commands = Enumerable.Empty <string>();
if (scriptParser.IsCreateOrAlterScript())
{
commands = new string[] { script };
}
else
{
string delimiter = dbInterpreter.ScriptsDelimiter;
commands = script.Split(new string[] { delimiter, delimiter.Replace("\r", "\n") }, StringSplitOptions.RemoveEmptyEntries);
}
int affectedRows = 0;
foreach (string command in commands)
{
if (string.IsNullOrEmpty(command.Trim()))
{
continue;
}
CommandInfo commandInfo = new CommandInfo()
{
CommandType = CommandType.Text,
CommandText = command,
Transaction = transaction,
CancellationToken = this.CancellationTokenSource.Token
};
int res = await dbInterpreter.ExecuteNonQueryAsync(dbConnection, commandInfo);
affectedRows += (res == -1 ? 0 : res);
}
result.Result = affectedRows;
if (!dbInterpreter.HasError && !this.cancelRequested)
{
this.transaction.Commit();
}
}
this.isBusy = false;
}
}
catch (Exception ex)
{
this.Rollback();
result.ResultType = QueryResultType.Text;
result.HasError = true;
result.Result = ex.Message;
this.HandleError(ex);
}
return(result);
}
public OracleInterpreter(ConnectionInfo connectionInfo, DbInterpreterOption option) : base(connectionInfo, option)
{
this.dbOwner = connectionInfo.UserId;
this.dbConnector = this.GetDbConnector();
}
public static DbInterpreter GetDbInterpreter(DatabaseType dbType, ConnectionInfo connectionInfo, DbInterpreterOption option)
{
DbInterpreter dbInterpreter = null;
var assembly = Assembly.GetExecutingAssembly();
var typeArray = assembly.ExportedTypes;
var types = (from type in typeArray
where type.IsSubclassOf(typeof(DbInterpreter))
select type).ToList();
foreach (var type in types)
{
dbInterpreter = (DbInterpreter)Activator.CreateInstance(type, connectionInfo, option);
if (dbInterpreter.DatabaseType == dbType)
{
return(dbInterpreter);
}
}
return(dbInterpreter);
}
public MySqlInterpreter(ConnectionInfo connectionInfo, DbInterpreterOption options) : base(connectionInfo, options)
{
this.dbConnector = this.GetDbConnector();
}
public async Task Convert()
{
DatabaseType sourceDbType = this.sourceInterpreter.DatabaseType;
DatabaseType targetDbType = this.targetInterpreter.DatabaseType;
int dataBatchSize = 500;
DbInterpreterOption sourceScriptOption = new DbInterpreterOption()
{
ScriptOutputMode = GenerateScriptOutputMode.WriteToString
};
DbInterpreterOption targetScriptOption = new DbInterpreterOption()
{
ScriptOutputMode = (GenerateScriptOutputMode.WriteToFile | GenerateScriptOutputMode.WriteToString)
};
this.sourceInterpreter.Option = sourceScriptOption;
this.targetInterpreter.Option = targetScriptOption;
GenerateScriptMode scriptMode = GenerateScriptMode.Schema | GenerateScriptMode.Data;
DbConveterInfo source = new DbConveterInfo()
{
DbInterpreter = sourceInterpreter
};
DbConveterInfo target = new DbConveterInfo()
{
DbInterpreter = targetInterpreter
};
try
{
using (DbConverter dbConverter = new DbConverter(source, target))
{
dbConverter.Option.GenerateScriptMode = scriptMode;
dbConverter.Subscribe(this);
if (sourceDbType == DatabaseType.MySql)
{
source.DbInterpreter.Option.InQueryItemLimitCount = 2000;
}
if (targetDbType == DatabaseType.SqlServer)
{
target.DbOwner = "dbo";
}
else if (targetDbType == DatabaseType.MySql)
{
target.DbInterpreter.Option.RemoveEmoji = true;
}
dbConverter.Option.SplitScriptsToExecute = true;
FeedbackHelper.EnableLog = true;
await dbConverter.Convert();
}
}
catch (Exception ex)
{
string msg = ExceptionHelper.GetExceptionDetails(ex);
this.Feedback(new FeedbackInfo()
{
InfoType = FeedbackInfoType.Error, Message = msg
});
}
}
private async Task Convert()
{
SchemaInfo schemaInfo = this.GetSourceTreeSchemaInfo();
if (!this.ValidateSource(schemaInfo))
{
return;
}
if (this.targetDbConnectionInfo == null)
{
MessageBox.Show("Target connection info is null.");
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;
}
DatabaseType sourceDbType = this.GetDatabaseType(this.cboSourceDB.Text);
DatabaseType targetDbType = this.GetDatabaseType(this.cboTargetDB.Text);
int dataBatchSize = SettingManager.Setting.DataBatchSize;
DbInterpreterOption sourceScriptOption = new DbInterpreterOption()
{
ScriptOutputMode = GenerateScriptOutputMode.None, DataBatchSize = dataBatchSize
};
DbInterpreterOption targetScriptOption = new DbInterpreterOption()
{
ScriptOutputMode = (GenerateScriptOutputMode.WriteToString), DataBatchSize = dataBatchSize
};
this.SetGenerateScriptOption(sourceScriptOption, targetScriptOption);
if (this.chkGenerateSourceScripts.Checked)
{
sourceScriptOption.ScriptOutputMode = sourceScriptOption.ScriptOutputMode | GenerateScriptOutputMode.WriteToFile;
}
if (this.chkOutputScripts.Checked)
{
targetScriptOption.ScriptOutputMode = targetScriptOption.ScriptOutputMode | GenerateScriptOutputMode.WriteToFile;
}
targetScriptOption.GenerateIdentity = this.chkGenerateIdentity.Checked;
GenerateScriptMode scriptMode = this.GetGenerateScriptMode();
if (scriptMode == GenerateScriptMode.None)
{
MessageBox.Show("Please specify the script mode.");
return;
}
DbConvetorInfo source = new DbConvetorInfo()
{
DbInterpreter = DbInterpreterHelper.GetDbInterpreter(sourceDbType, this.sourceDbConnectionInfo, sourceScriptOption)
};
DbConvetorInfo target = new DbConvetorInfo()
{
DbInterpreter = DbInterpreterHelper.GetDbInterpreter(targetDbType, this.targetDbConnectionInfo, targetScriptOption)
};
try
{
using (dbConvertor = new DbConvertor(source, target))
{
dbConvertor.Option.GenerateScriptMode = scriptMode;
dbConvertor.Option.BulkCopy = this.chkBulkCopy.Checked;
dbConvertor.Option.ExecuteScriptOnTargetServer = this.chkExecuteOnTarget.Checked;
dbConvertor.Option.UseTransaction = this.chkUseTransaction.Checked;
dbConvertor.Subscribe(this);
if (sourceDbType == DatabaseType.MySql)
{
source.DbInterpreter.Option.InQueryItemLimitCount = 2000;
}
if (targetDbType == DatabaseType.SqlServer)
{
target.DbOwner = this.txtTargetDbOwner.Text ?? "dbo";
}
else if (targetDbType == DatabaseType.MySql)
{
target.DbInterpreter.Option.RemoveEmoji = true;
}
dbConvertor.Option.SplitScriptsToExecute = true;
this.btnExecute.Enabled = false;
this.btnCancel.Enabled = true;
await dbConvertor.Convert(schemaInfo);
}
}
catch (Exception ex)
{
this.hasError = true;
this.HandleException(ex);
}
if (!this.hasError)
{
this.btnExecute.Enabled = true;
this.btnCancel.Enabled = false;
if (!this.dbConvertor.CancelRequested)
{
this.txtMessage.AppendText(Environment.NewLine + DONE);
MessageBox.Show(DONE);
}
else
{
MessageBox.Show("Task has been canceled.");
}
}
}
public async Task Translate(DatabaseType sourceDbType, DatabaseType targetDbType, DatabaseObject dbObject, ConnectionInfo connectionInfo, TranslateHandler translateHandler = null)
{
DbInterpreterOption sourceScriptOption = new DbInterpreterOption()
{
ScriptOutputMode = GenerateScriptOutputMode.None
};
DbInterpreterOption targetScriptOption = new DbInterpreterOption()
{
ScriptOutputMode = GenerateScriptOutputMode.WriteToString
};
DbConveterInfo source = new DbConveterInfo()
{
DbInterpreter = DbInterpreterHelper.GetDbInterpreter(sourceDbType, connectionInfo, sourceScriptOption)
};
DbConveterInfo target = new DbConveterInfo()
{
DbInterpreter = DbInterpreterHelper.GetDbInterpreter(targetDbType, new ConnectionInfo(), sourceScriptOption)
};
using (DbConverter dbConverter = new DbConverter(source, target))
{
dbConverter.Option.OnlyForTranslate = true;
dbConverter.Option.GenerateScriptMode = GenerateScriptMode.Schema;
dbConverter.Option.ExecuteScriptOnTargetServer = false;
dbConverter.Option.ConvertComputeColumnExpression = true;
dbConverter.Subscribe(this.observer);
if (translateHandler != null)
{
dbConverter.OnTranslated += translateHandler;
}
if (targetDbType == DatabaseType.SqlServer)
{
target.DbOwner = "dbo";
}
SchemaInfo schemaInfo = new SchemaInfo();
if (dbObject is Table)
{
schemaInfo.Tables.Add(dbObject as Table);
}
else if (dbObject is View)
{
schemaInfo.Views.Add(dbObject as DatabaseInterpreter.Model.View);
}
else if (dbObject is Function)
{
schemaInfo.Functions.Add(dbObject as Function);
}
else if (dbObject is Procedure)
{
schemaInfo.Procedures.Add(dbObject as Procedure);
}
else if (dbObject is TableTrigger)
{
schemaInfo.TableTriggers.Add(dbObject as TableTrigger);
}
await dbConverter.Convert(schemaInfo);
}
}
public static DbInterpreter GetDbInterpreter(DatabaseType dbType, ConnectionInfo connectionInfo, DbInterpreterOption option)
{
DbInterpreter dbInterpreter = null;
if(dbType == DatabaseType.SqlServer)
{
dbInterpreter = new SqlServerInterpreter(connectionInfo, option);
}
else if(dbType == DatabaseType.MySql)
{
dbInterpreter = new MySqlInterpreter(connectionInfo, option);
}
else if(dbType == DatabaseType.Oracle)
{
dbInterpreter = new OracleInterpreter(connectionInfo, option);
}
return dbInterpreter;
}
private async Task Convert()
{
SchemaInfo schemaInfo = this.tvDbObjects.GetSchemaInfo();
if (!this.ValidateSource(schemaInfo))
{
return;
}
if (this.targetDbConnectionInfo == null)
{
MessageBox.Show("Target connection info is null.");
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;
}
DatabaseType sourceDbType = this.useSourceConnector ? this.sourceDbProfile.DatabaseType : this.sourceDatabaseType;
DatabaseType targetDbType = this.targetDbProfile.DatabaseType;
DbInterpreterOption sourceScriptOption = new DbInterpreterOption()
{
ScriptOutputMode = GenerateScriptOutputMode.None, SortObjectsByReference = true, GetTableAllObjects = true
};
DbInterpreterOption targetScriptOption = new DbInterpreterOption()
{
ScriptOutputMode = (GenerateScriptOutputMode.WriteToString)
};
this.SetGenerateScriptOption(sourceScriptOption, targetScriptOption);
if (this.chkGenerateSourceScripts.Checked)
{
sourceScriptOption.ScriptOutputMode = sourceScriptOption.ScriptOutputMode | GenerateScriptOutputMode.WriteToFile;
}
if (this.chkOutputScripts.Checked)
{
targetScriptOption.ScriptOutputMode = targetScriptOption.ScriptOutputMode | GenerateScriptOutputMode.WriteToFile;
}
if (this.chkTreatBytesAsNull.Checked)
{
sourceScriptOption.TreatBytesAsNullForReading = true;
targetScriptOption.TreatBytesAsNullForExecuting = true;
}
targetScriptOption.TableScriptsGenerateOption.GenerateIdentity = this.chkGenerateIdentity.Checked;
GenerateScriptMode scriptMode = this.GetGenerateScriptMode();
if (scriptMode == GenerateScriptMode.None)
{
MessageBox.Show("Please specify the script mode.");
return;
}
DbConveterInfo source = new DbConveterInfo()
{
DbInterpreter = DbInterpreterHelper.GetDbInterpreter(sourceDbType, this.sourceDbConnectionInfo, sourceScriptOption)
};
DbConveterInfo target = new DbConveterInfo()
{
DbInterpreter = DbInterpreterHelper.GetDbInterpreter(targetDbType, this.targetDbConnectionInfo, targetScriptOption)
};
try
{
using (this.dbConverter = new DbConverter(source, target))
{
this.dbConverter.Option.GenerateScriptMode = scriptMode;
this.dbConverter.Option.BulkCopy = this.chkBulkCopy.Checked;
this.dbConverter.Option.ExecuteScriptOnTargetServer = this.chkExecuteOnTarget.Checked;
this.dbConverter.Option.UseTransaction = this.chkUseTransaction.Checked;
this.dbConverter.Option.SkipScriptError = this.chkSkipScriptError.Checked;
this.dbConverter.Option.PickupTable = this.chkPickup.Checked;
this.dbConverter.Option.ConvertComputeColumnExpression = this.chkComputeColumn.Checked;
this.dbConverter.Option.OnlyCommentComputeColumnExpressionInScript = this.chkOnlyCommentComputeExpression.Checked;
this.dbConverter.Subscribe(this);
if (sourceDbType == DatabaseType.MySql)
{
source.DbInterpreter.Option.InQueryItemLimitCount = 2000;
}
if (targetDbType == DatabaseType.SqlServer)
{
target.DbOwner = this.txtTargetDbOwner.Text ?? "dbo";
}
else if (targetDbType == DatabaseType.MySql)
{
target.DbInterpreter.Option.RemoveEmoji = true;
}
this.dbConverter.Option.SplitScriptsToExecute = true;
this.btnExecute.Enabled = false;
this.btnCancel.Enabled = true;
await this.dbConverter.Convert(schemaInfo);
if (!this.hasError && !this.dbConverter.HasError && !source.DbInterpreter.HasError && !target.DbInterpreter.HasError)
{
this.btnExecute.Enabled = true;
this.btnCancel.Enabled = false;
if (!this.dbConverter.CancelRequested)
{
this.txtMessage.AppendText(Environment.NewLine + DONE);
MessageBox.Show(DONE, "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
MessageBox.Show("Task has been canceled.");
}
}
}
}
catch (Exception ex)
{
this.hasError = true;
this.HandleException(ex);
}
}
private async void GenerateScourceDbScripts()
{
SchemaInfo schemaInfo = this.GetSourceTreeSchemaInfo();
if (!this.ValidateSource(schemaInfo))
{
return;
}
this.btnGenerateSourceScripts.Enabled = false;
DatabaseType sourceDbType = this.GetDatabaseType(this.cboSourceDB.Text);
int dataBatchSize = SettingManager.Setting.DataBatchSize;
DbInterpreterOption sourceScriptOption = new DbInterpreterOption()
{
ScriptOutputMode = GenerateScriptOutputMode.WriteToFile, DataBatchSize = dataBatchSize
};
this.SetGenerateScriptOption(sourceScriptOption);
GenerateScriptMode scriptMode = this.GetGenerateScriptMode();
if (scriptMode == GenerateScriptMode.None)
{
MessageBox.Show("Please specify the script mode.");
return;
}
DbInterpreter dbInterpreter = DbInterpreterHelper.GetDbInterpreter(sourceDbType, this.sourceDbConnectionInfo, sourceScriptOption);
SelectionInfo selectionInfo = new SelectionInfo()
{
UserDefinedTypeNames = schemaInfo.UserDefinedTypes.Select(item => item.Name).ToArray(),
TableNames = schemaInfo.Tables.Select(item => item.Name).ToArray(),
ViewNames = schemaInfo.Views.Select(item => item.Name).ToArray()
};
try
{
schemaInfo = await dbInterpreter.GetSchemaInfoAsync(selectionInfo);
dbInterpreter.Subscribe(this);
GenerateScriptMode mode = GenerateScriptMode.None;
if (scriptMode.HasFlag(GenerateScriptMode.Schema))
{
mode = GenerateScriptMode.Schema;
dbInterpreter.GenerateSchemaScripts(schemaInfo);
}
if (scriptMode.HasFlag(GenerateScriptMode.Data))
{
mode = GenerateScriptMode.Data;
await dbInterpreter.GenerateDataScriptsAsync(schemaInfo);
}
this.OpenInExplorer(dbInterpreter.GetScriptOutputFilePath(mode));
MessageBox.Show(DONE);
}
catch (Exception ex)
{
this.HandleException(ex);
}
this.btnGenerateSourceScripts.Enabled = true;
}
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 SqlServerInterpreter(ConnectionInfo connectionInfo, DbInterpreterOption option) : base(connectionInfo, option)
{
this.dbConnector = this.GetDbConnector();
}
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;
}
}