private void btnTest_Click(object sender, EventArgs e) { ConnectionInfo connectionInfo = this.GetConnectionInfo(); DbInterpreter dbInterpreter = DbInterpreterHelper.GetDbInterpreter(this.DatabaseType, connectionInfo, new GenerateScriptOption()); try { using (DbConnection dbConnection = dbInterpreter.GetDbConnector().CreateConnection()) { dbConnection.Open(); MessageBox.Show("Success."); if (string.IsNullOrEmpty(this.cboDatabase.Text.Trim())) { this.cboDatabase.Items.Clear(); List <Database> databaseses = dbInterpreter.GetDatabases(); databaseses.ForEach(item => { this.cboDatabase.Items.Add(item.Name); }); } } } catch (Exception ex) { MessageBox.Show("Failed:" + ex.Message); } }
public async Task <bool> TestConnect() { if (!this.ValidateInfo()) { return(false); } ConnectionInfo connectionInfo = this.GetConnectionInfo(); DbInterpreter dbInterpreter = DbInterpreterHelper.GetDbInterpreter(this.DatabaseType, connectionInfo, new DbInterpreterOption()); try { using (DbConnection dbConnection = dbInterpreter.CreateConnection()) { await dbConnection.OpenAsync(); MessageBox.Show("Success."); if (this.OnTestConnect != null) { this.OnTestConnect(); } return(true); } } catch (Exception ex) { MessageBox.Show("Failed:" + ex.Message); return(false); } }
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(); } }
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); }
private async void btnTest_Click(object sender, EventArgs e) { ConnectionInfo connectionInfo = this.GetConnectionInfo(); DbInterpreter dbInterpreter = DbInterpreterHelper.GetDbInterpreter(this.DatabaseType, connectionInfo, new DbInterpreterOption()); string oldDatabase = this.cboDatabase.Text; try { using (DbConnection dbConnection = dbInterpreter.GetDbConnector().CreateConnection()) { dbConnection.Open(); MessageBox.Show("Success."); this.cboDatabase.Items.Clear(); List <Database> databaseses = await dbInterpreter.GetDatabasesAsync(); databaseses.ForEach(item => { this.cboDatabase.Items.Add(item.Name); }); this.cboDatabase.Text = oldDatabase; } } catch (Exception ex) { MessageBox.Show("Failed:" + ex.Message); } }
private async void TestConnect() { ConnectionInfo connectionInfo = this.GetConnectionInfo(); DbInterpreter dbInterpreter = DbInterpreterHelper.GetDbInterpreter(this.DatabaseType, connectionInfo, new DbInterpreterOption()); string oldDatabase = this.cboDatabase.Text; try { this.cboDatabase.Items.Clear(); List <Database> databaseses = await dbInterpreter.GetDatabasesAsync(); databaseses.ForEach(item => { this.cboDatabase.Items.Add(item.Name); }); this.cboDatabase.Text = oldDatabase; } catch (Exception ex) { MessageBox.Show("Failed:" + ex.Message); } }
private async void LoadData(DatabaseObjectDisplayInfo displayInfo, long pageNum = 1, bool isSort = false) { this.displayInfo = displayInfo; this.pagination.PageNum = pageNum; Table table = displayInfo.DatabaseObject as Table; int pageSize = this.pagination.PageSize; DbInterpreter dbInterpreter = DbInterpreterHelper.GetDbInterpreter(displayInfo.DatabaseType, displayInfo.ConnectionInfo, new DbInterpreterOption()); string orderColumns = ""; if (this.dgvData.SortedColumn != null) { string sortOrder = (this.sortOrder == SortOrder.Descending ? "DESC" : "ASC"); orderColumns = $"{dbInterpreter.GetQuotedString(this.dgvData.SortedColumn.Name)} {sortOrder}"; } string conditionClause = ""; if (this.conditionBuilder != null && this.conditionBuilder.Conditions.Count > 0) { this.conditionBuilder.QuotationLeftChar = dbInterpreter.QuotationLeftChar; this.conditionBuilder.QuotationRightChar = dbInterpreter.QuotationRightChar; conditionClause = "WHERE " + this.conditionBuilder.ToString(); } (long Total, DataTable Data)result = await dbInterpreter.GetPagedDataTableAsync(table, orderColumns, pageSize, pageNum, conditionClause); this.pagination.TotalCount = result.Total; this.dgvData.DataSource = DataGridViewHelper.ConvertDataTable(result.Data); foreach (DataGridViewColumn column in this.dgvData.Columns) { Type valueType = column.ValueType; if (valueType == typeof(byte[]) || valueType.Name == "SqlGeography") { column.SortMode = DataGridViewColumnSortMode.NotSortable; } } if (this.sortedColumnIndex != -1) { DataGridViewColumn column = this.dgvData.Columns[this.sortedColumnIndex]; this.isSorting = true; ListSortDirection sortDirection = this.GetSortDirection(this.sortOrder); this.dgvData.Sort(column, sortDirection); this.isSorting = false; } }
public void LoadDbTypes() { var databaseTypes = DbInterpreterHelper.GetDisplayDatabaseTypes(); foreach (var value in databaseTypes) { this.cboDbType.Items.Add(value.ToString()); } }
private void LoadSourceDbSchemaInfo() { this.tvSource.Nodes.Clear(); DatabaseType dbType = this.GetDatabaseType(this.cboSourceDB.Text); DbInterpreter dbInterpreter = DbInterpreterHelper.GetDbInterpreter(dbType, this.sourceDbConnectionInfo, new GenerateScriptOption()); if (dbInterpreter is SqlServerInterpreter) { List <UserDefinedType> userDefinedTypes = dbInterpreter.GetUserDefinedTypes(); 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 = dbInterpreter.GetTables(); 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(DatabaseMigration.Core.View); this.tvSource.Nodes.Add(viewRootNode); List <DatabaseMigration.Core.View> views = ViewHelper.ResortViews(dbInterpreter.GetViews()); foreach (var view in views) { TreeNode viewNode = new TreeNode(); viewNode.Tag = view; viewNode.Text = dbInterpreter.GetObjectDisplayName(view, false); viewRootNode.Nodes.Add(viewNode); } }
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 void SourceScriptBackgroundWorker_DoWork(object sender, DoWorkEventArgs e) { if (this.sourceScriptBackgroundWorker.CancellationPending) { e.Cancel = true; return; } SchemaInfo schemaInfo = this.GetSourceTreeSchemaInfo(); if (!this.ValidateSource(schemaInfo)) { return; } DatabaseType sourceDbType = this.GetDatabaseType(this.cboSourceDB.Text); int dataBatchSize = SettingManager.Setting.DataBatchSize; GenerateScriptOption sourceScriptOption = new GenerateScriptOption() { ScriptOutputMode = GenerateScriptOutputMode.None, 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); string[] tableNames = schemaInfo.Tables.Select(item => item.Name).ToArray(); schemaInfo = dbInterpreter.GetSchemaInfo(tableNames); dbInterpreter.Subscribe(this); if (scriptMode.HasFlag(GenerateScriptMode.Schema)) { dbInterpreter.GenerateSchemaScripts(schemaInfo); } if (scriptMode.HasFlag(GenerateScriptMode.Data)) { dbInterpreter.GenerateDataScripts(schemaInfo); } MessageBox.Show(DONE); }
private void DoScript(DatabaseObjectType databaseObjectType, ScriptAction scriptAction) { DbInterpreter dbInterpreter = DbInterpreterHelper.GetDbInterpreter(this.databaseType, new ConnectionInfo(), new DbInterpreterOption()); ScriptTemplate scriptTemplate = new ScriptTemplate(dbInterpreter); DatabaseObjectDisplayInfo displayInfo = this.GetDisplayInfo(); displayInfo.IsNew = true; displayInfo.Content = scriptTemplate.GetTemplateContent(databaseObjectType, scriptAction); displayInfo.ScriptAction = scriptAction; this.ShowContent(displayInfo); }
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); }
private void tsmiTranslate_MouseEnter(object sender, EventArgs e) { this.tsmiTranslate.DropDownItems.Clear(); var dbTypes = DbInterpreterHelper.GetDisplayDatabaseTypes(); foreach (var dbType in dbTypes) { if ((int)dbType != (int)this.databaseType) { ToolStripMenuItem item = new ToolStripMenuItem(dbType.ToString()); item.Click += TranslateItem_Click; this.tsmiTranslate.DropDownItems.Add(item); } } }
public void LoadDbTypes() { var databaseTypes = DbInterpreterHelper.GetDisplayDatabaseTypes(); foreach (var value in databaseTypes) { this.cboDbType.Items.Add(value.ToString()); } if (this.cboDbType.Items.Count > 0) { if (!this.IsForSelecting) { this.cboDbType.SelectedIndex = 0; } else { this.cboDbType.Text = this.DatabaseType.ToString(); } } }
public void LoadDbTypes() { var databaseTypes = DbInterpreterHelper.GetDisplayDatabaseTypes(); foreach (var value in databaseTypes) { this.cboDbType.Items.Add(value.ToString()); } if (this.cboDbType.Items.Count > 0) { this.cboDbType.Text = SettingManager.Setting.PreferredDatabase.ToString(); if (string.IsNullOrEmpty(this.cboDbType.Text)) { this.cboDbType.SelectedIndex = 0; } } this.btnConnect.Focus(); }
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 void LoadSourceDbSchemaInfo() { this.tvSource.Nodes.Clear(); DatabaseType dbType = this.GetDatabaseType(this.cboSourceDB.Text); DbInterpreter dbInterpreter = DbInterpreterHelper.GetDbInterpreter(dbType, this.sourceDbConnectionInfo, new GenerateScriptOption()); if (dbInterpreter is SqlServerInterpreter) { TreeNode userDefinedRootNode = new TreeNode("User Defined Types"); userDefinedRootNode.Name = nameof(UserDefinedType); this.tvSource.Nodes.Add(userDefinedRootNode); List <UserDefinedType> userDefinedTypes = dbInterpreter.GetUserDefinedTypes(); 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 = dbInterpreter.GetTables(); foreach (Table table in tables) { TreeNode tableNode = new TreeNode(); tableNode.Tag = table; tableNode.Text = dbInterpreter.GetDisplayTableName(table, false); tableRootNode.Nodes.Add(tableNode); } }
public async Task LoadTree(DatabaseType dbType, ConnectionInfo connectionInfo) { this.databaseType = dbType; this.connectionInfo = connectionInfo; this.tvDbObjects.Nodes.Clear(); DbInterpreter dbInterpreter = DbInterpreterHelper.GetDbInterpreter(dbType, connectionInfo, simpleInterpreterOption); List <Database> databases = await dbInterpreter.GetDatabasesAsync(); foreach (Database database in databases) { TreeNode node = DbObjectsTreeHelper.CreateTreeNode(database, true); this.tvDbObjects.Nodes.Add(node); } if (this.tvDbObjects.Nodes.Count == 1) { this.tvDbObjects.SelectedNode = this.tvDbObjects.Nodes[0]; this.tvDbObjects.Nodes[0].Expand(); } }
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 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."); } } }
private DbInterpreter GetDbInterpreter() { DbInterpreter dbInterpreter = DbInterpreterHelper.GetDbInterpreter(this.displayInfo.DatabaseType, this.displayInfo.ConnectionInfo, new DbInterpreterOption()); return(dbInterpreter); }
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); }
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; }
private async Task ConvertorBackgroundWorker_DoWork(object sender, DoWorkEventArgs e) { if (this.convertorBackgroundWorker.CancellationPending) { e.Cancel = true; return; } 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; GenerateScriptOption sourceScriptOption = new GenerateScriptOption() { ScriptOutputMode = GenerateScriptOutputMode.None, DataBatchSize = dataBatchSize }; GenerateScriptOption targetScriptOption = new GenerateScriptOption() { ScriptOutputMode = (GenerateScriptOutputMode.WriteToString), DataBatchSize = dataBatchSize }; this.SetGenerateScriptOption(sourceScriptOption, targetScriptOption); 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) }; DbConvertor dbConvertor = new DbConvertor(source, target, null); dbConvertor.Option.GenerateScriptMode = scriptMode; dbConvertor.OnFeedback += Feedback; 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; } else if (targetDbType == DatabaseType.Oracle) { dbConvertor.Option.SplitScriptsToExecute = true; dbConvertor.Option.ScriptSplitChar = ';'; } DataTransferErrorProfile dataErrorProfile = null; if (this.chkPickup.Checked && scriptMode.HasFlag(GenerateScriptMode.Data)) { dataErrorProfile = DataTransferErrorProfileManager.GetProfile(this.sourceDbConnectionInfo, this.targetDbConnectionInfo); if (dataErrorProfile != null) { dbConvertor.Option.PickupTable = new Table() { Owner = schemaInfo.Tables.FirstOrDefault()?.Owner, Name = dataErrorProfile.SourceTableName }; } } this.btnExecute.Enabled = false; this.btnCancel.Enabled = true; bool success = false; try { await dbConvertor.ConvertAsync(schemaInfo, false); success = true; if (dataErrorProfile != null) { DataTransferErrorProfileManager.Remove(dataErrorProfile); } } catch (Exception ex) { string errMsg = ex.Message; sbFeedback.AppendLine("Error:" + ex.Message); if (ex.InnerException != null) { sbFeedback.AppendLine("Innser Exception:" + ex.InnerException.Message); } if (!string.IsNullOrEmpty(ex.StackTrace)) { sbFeedback.AppendLine(ex.StackTrace); } this.AppendErrorMessage(errMsg); this.txtMessage.SelectionStart = this.txtMessage.TextLength; this.txtMessage.ScrollToCaret(); this.btnExecute.Enabled = true; this.btnCancel.Enabled = false; if (ex is TableDataTransferException dataException) { DataTransferErrorProfileManager.Save(new DataTransferErrorProfile { SourceServer = dataException.SourceServer, SourceDatabase = dataException.SourceDatabase, SourceTableName = dataException.SourceTableName, TargetServer = dataException.TargetServer, TargetDatabase = dataException.TargetDatabase, TargetTableName = dataException.TargetTableName }); } MessageBox.Show(ex.Message); } LogHelper.Log(sbFeedback.ToString()); sbFeedback.Clear(); if (success) { this.btnExecute.Enabled = true; this.btnCancel.Enabled = false; this.txtMessage.AppendText(Environment.NewLine + DONE); MessageBox.Show(DONE); } }
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); } }
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 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; } }