Ejemplo n.º 1
0
        private async Task <SchemaInfo> InternalGetSchemalInfo(SelectionInfo selectionInfo, bool getAllIfNotSpecified = true, bool async = false)
        {
            List <UserDefinedType> userDefinedTypes = new List <UserDefinedType>();

            string[] userDefinedTypeNames = selectionInfo.UserDefinedTypeNames;
            string[] tableNames           = selectionInfo.TableNames;
            string[] viewNames            = selectionInfo.ViewNames;

            List <Table>       tables  = new List <Table>();
            List <TableColumn> columns = new List <TableColumn>();
            List <View>        views   = new List <View>();

            if ((userDefinedTypeNames != null && userDefinedTypeNames.Length > 0) || getAllIfNotSpecified)
            {
                userDefinedTypes = async ? await this.GetUserDefinedTypesAsync(userDefinedTypeNames) : this.GetUserDefinedTypes(userDefinedTypeNames);
            }

            List <TablePrimaryKey> tablePrimaryKeys = new List <TablePrimaryKey>();
            List <TableForeignKey> tableForeignKeys = new List <TableForeignKey>();
            List <TableIndex>      tableIndices     = new List <TableIndex>();

            if ((tableNames != null && tableNames.Length > 0) || getAllIfNotSpecified)
            {
                tables = async ? await this.GetTablesAsync(tableNames) : this.GetTables(tableNames);

                columns = this.GetTableColumns(tableNames);

                if (Option.GenerateKey)
                {
                    tablePrimaryKeys = this.GetTablePrimaryKeys(tableNames);
                    tableForeignKeys = this.GetTableForeignKeys(tableNames);
                }

                if (Option.GenerateIndex)
                {
                    tableIndices = this.GetTableIndexes(tableNames);
                }
            }

            if (Option.SortTablesByKeyReference && Option.GenerateKey)
            {
                List <string> sortedTableNames = TableReferenceHelper.ResortTableNames(tableNames, tableForeignKeys);

                int i = 1;
                foreach (string tableName in sortedTableNames)
                {
                    Table table = tables.FirstOrDefault(item => item.Name == tableName);
                    if (table != null)
                    {
                        table.Order = i++;
                    }
                }

                tables = tables.OrderBy(item => item.Order).ToList();
            }

            if ((viewNames != null && viewNames.Length > 0) || getAllIfNotSpecified)
            {
                views = async ? await this.GetViewsAsync(viewNames) : this.GetViews(viewNames);

                views = ViewHelper.ResortViews(views);
            }

            return(new SchemaInfo
            {
                UserDefinedTypes = userDefinedTypes,
                Tables = tables,
                Columns = columns,
                TablePrimaryKeys = tablePrimaryKeys,
                TableForeignKeys = tableForeignKeys,
                TableIndices = tableIndices,
                Views = views
            });
        }
Ejemplo n.º 2
0
 public virtual Task <SchemaInfo> GetSchemaInfoAsync(SelectionInfo selectionInfo, bool getAllIfNotSpecified = true)
 {
     return(this.InternalGetSchemalInfo(selectionInfo, getAllIfNotSpecified, true));
 }
Ejemplo n.º 3
0
        private async Task InternalConvert(SchemaInfo schemaInfo = null, bool getAllIfNotSpecified = true, bool async = false)
        {
            DbInterpreter sourceInterpreter = this.Source.DbInterpreter;

            sourceInterpreter.Option.TreatBytesAsNullForScript = true;

            string[] tableNames           = null;
            string[] userDefinedTypeNames = null;
            string[] viewNames            = null;

            if (schemaInfo == null || getAllIfNotSpecified)
            {
                tableNames           = sourceInterpreter.GetTables().Select(item => item.Name).ToArray();
                userDefinedTypeNames = sourceInterpreter.GetUserDefinedTypes().Select(item => item.Name).ToArray();
                viewNames            = sourceInterpreter.GetViews().Select(item => item.Name).ToArray();
            }
            else
            {
                tableNames           = schemaInfo.Tables.Select(t => t.Name).ToArray();
                userDefinedTypeNames = schemaInfo.UserDefinedTypes.Select(item => item.Name).ToArray();
                viewNames            = schemaInfo.Views.Select(item => item.Name).ToArray();
            }

            SelectionInfo selectionInfo = new SelectionInfo()
            {
                UserDefinedTypeNames = userDefinedTypeNames,
                TableNames           = tableNames,
                ViewNames            = viewNames
            };

            SchemaInfo sourceSchemaInfo = async? await sourceInterpreter.GetSchemaInfoAsync(selectionInfo, getAllIfNotSpecified):
                                          sourceInterpreter.GetSchemaInfo(selectionInfo, getAllIfNotSpecified);

            SchemaInfo targetSchemaInfo = SchemaInfoHelper.Clone(sourceSchemaInfo);

            if (!string.IsNullOrEmpty(this.Target.DbOwner))
            {
                SchemaInfoHelper.TransformOwner(targetSchemaInfo, this.Target.DbOwner);
            }

            targetSchemaInfo.Columns = ColumnTranslator.Translate(targetSchemaInfo.Columns, this.Source.DbInterpreter.DatabaseType, this.Target.DbInterpreter.DatabaseType);
            targetSchemaInfo.Views   = ViewTranslator.Translate(targetSchemaInfo.Views, sourceInterpreter, this.Target.DbInterpreter, this.Target.DbOwner);

            if (this.Option.EnsurePrimaryKeyNameUnique)
            {
                SchemaInfoHelper.EnsurePrimaryKeyNameUnique(targetSchemaInfo);
            }

            if (this.Option.EnsureIndexNameUnique)
            {
                SchemaInfoHelper.EnsureIndexNameUnique(targetSchemaInfo);
            }

            DbInterpreter targetInterpreter = this.Target.DbInterpreter;
            bool          generateIdentity  = targetInterpreter.Option.GenerateIdentity;

            if (generateIdentity)
            {
                targetInterpreter.Option.InsertIdentityValue = true;
            }

            string script = "";

            sourceInterpreter.Subscribe(this);
            targetInterpreter.Subscribe(this);

            if (this.Option.GenerateScriptMode.HasFlag(GenerateScriptMode.Schema))
            {
                script = targetInterpreter.GenerateSchemaScripts(targetSchemaInfo);

                if (string.IsNullOrEmpty(script))
                {
                    throw new Exception($"The script to create schema is null.");
                }

                targetInterpreter.Feedback(FeedbackInfoType.Info, "Begin to sync schema...");
                if (!this.Option.SplitScriptsToExecute)
                {
                    if (targetInterpreter is SqlServerInterpreter)
                    {
                        string[] scriptItems = script.Split(new string[] { "GO" + Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);

                        if (async)
                        {
                            scriptItems.ToList().ForEach(async item =>
                            {
                                await targetInterpreter.ExecuteNonQueryAsync(item);
                            });
                        }
                        else
                        {
                            scriptItems.ToList().ForEach(item =>
                            {
                                targetInterpreter.ExecuteNonQuery(item);
                            });
                        }
                    }
                    else
                    {
                        if (async)
                        {
                            await targetInterpreter.ExecuteNonQueryAsync(script);
                        }
                        else
                        {
                            targetInterpreter.ExecuteNonQuery(script);
                        }
                    }
                }
                else
                {
                    string[] sqls  = script.Split(new char[] { this.Option.ScriptSplitChar }, StringSplitOptions.RemoveEmptyEntries);
                    int      count = sqls.Count();

                    int i = 0;
                    foreach (string sql in sqls)
                    {
                        if (!string.IsNullOrEmpty(sql.Trim()))
                        {
                            i++;
                            targetInterpreter.Feedback(FeedbackInfoType.Info, $"({i}/{count}), executing {sql}");
                            targetInterpreter.ExecuteNonQuery(sql.Trim());
                        }
                    }
                }
                targetInterpreter.Feedback(FeedbackInfoType.Info, "End sync schema.");
            }

            if (this.Option.GenerateScriptMode.HasFlag(GenerateScriptMode.Data))
            {
                List <TableColumn> identityTableColumns = new List <TableColumn>();
                if (generateIdentity)
                {
                    identityTableColumns = targetSchemaInfo.Columns.Where(item => item.IsIdentity).ToList();
                }

                if (this.Option.PickupTable != null)
                {
                    sourceSchemaInfo.PickupTable = this.Option.PickupTable;
                }

                sourceInterpreter.AppendScriptsToFile("", GenerateScriptMode.Data, true);
                targetInterpreter.AppendScriptsToFile("", GenerateScriptMode.Data, true);

                using (DbConnection dbConnection = targetInterpreter.GetDbConnector().CreateConnection())
                {
                    identityTableColumns.ForEach(item =>
                    {
                        if (targetInterpreter.DatabaseType == DatabaseType.SqlServer)
                        {
                            targetInterpreter.SetIdentityEnabled(dbConnection, item, false);
                        }
                    });

                    sourceInterpreter.OnDataRead += async(table, columns, data, dbDataReader) =>
                    {
                        try
                        {
                            StringBuilder sb = new StringBuilder();

                            (Table Table, List <TableColumn> Columns)targetTableAndColumns = this.GetTargetTableColumns(targetSchemaInfo, this.Target.DbOwner, table, columns);

                            Dictionary <string, object> paramters = targetInterpreter.AppendDataScripts(this.Target.DbInterpreter.Option, sb, targetTableAndColumns.Table, targetTableAndColumns.Columns, new Dictionary <long, List <Dictionary <string, object> > >()
                            {
                                { 1, data }
                            });

                            try
                            {
                                script = sb.ToString();
                                sb.Clear();
                            }
                            catch (OutOfMemoryException e)
                            {
                                sb.Clear();
                            }

                            if (!this.Option.SplitScriptsToExecute)
                            {
                                if (this.Option.BulkCopy && targetInterpreter.SupportBulkCopy)
                                {
                                    if (async)
                                    {
                                        await targetInterpreter.BulkCopyAsync(dbConnection, dbDataReader, table.Name);
                                    }
                                    else
                                    {
                                        targetInterpreter.BulkCopy(dbConnection, dbDataReader, table.Name);
                                    }
                                }
                                else
                                {
                                    if (async)
                                    {
                                        await targetInterpreter.ExecuteNonQueryAsync(dbConnection, script, paramters, false);
                                    }
                                    else
                                    {
                                        targetInterpreter.ExecuteNonQuery(dbConnection, script, paramters, false);
                                    }
                                }
                            }
                            else
                            {
                                string[] sqls = script.Split(new char[] { this.Option.ScriptSplitChar }, StringSplitOptions.RemoveEmptyEntries);

                                foreach (string sql in sqls)
                                {
                                    if (!string.IsNullOrEmpty(sql.Trim()))
                                    {
                                        if (this.Option.BulkCopy && targetInterpreter.SupportBulkCopy)
                                        {
                                            if (async)
                                            {
                                                await targetInterpreter.BulkCopyAsync(dbConnection, dbDataReader, table.Name);
                                            }
                                            else
                                            {
                                                targetInterpreter.BulkCopy(dbConnection, dbDataReader, table.Name);
                                            }
                                        }
                                        else
                                        {
                                            if (async)
                                            {
                                                await targetInterpreter.ExecuteNonQueryAsync(dbConnection, sql, paramters, false);
                                            }
                                            else
                                            {
                                                targetInterpreter.ExecuteNonQuery(dbConnection, sql, paramters, false);
                                            }
                                        }
                                    }
                                }
                            }

                            targetInterpreter.FeedbackInfo($"End write data to table {table.Name}, handled rows count:{data.Count}.");
                        }
                        catch (Exception ex)
                        {
                            ConnectionInfo sourceConnectionInfo = sourceInterpreter.ConnectionInfo;
                            ConnectionInfo targetConnectionInfo = targetInterpreter.ConnectionInfo;

                            throw new TableDataTransferException(ex)
                                  {
                                      SourceServer    = sourceConnectionInfo.Server,
                                      SourceDatabase  = sourceConnectionInfo.Database,
                                      SourceTableName = table.Name,
                                      TargetServer    = targetConnectionInfo.Server,
                                      TargetDatabase  = targetConnectionInfo.Database,
                                      TargetTableName = table.Name
                                  };
                        }
                    };

                    if (async)
                    {
                        await sourceInterpreter.GenerateDataScriptsAsync(sourceSchemaInfo);
                    }
                    else
                    {
                        sourceInterpreter.GenerateDataScripts(sourceSchemaInfo);
                    }

                    identityTableColumns.ForEach(item =>
                    {
                        if (targetInterpreter.DatabaseType == DatabaseType.SqlServer)
                        {
                            targetInterpreter.SetIdentityEnabled(dbConnection, item, true);
                        }
                    });
                }
            }
        }
Ejemplo n.º 4
0
 public virtual SchemaInfo GetSchemaInfo(SelectionInfo selectionInfo, bool getAllIfNotSpecified = true)
 {
     return(this.InternalGetSchemalInfo(selectionInfo, getAllIfNotSpecified, false).Result);
 }
Ejemplo n.º 5
0
        private async Task InternalConvert(SchemaInfo schemaInfo = null)
        {
            DbInterpreter sourceInterpreter = this.Source.DbInterpreter;

            sourceInterpreter.Option.TreatBytesAsNullForScript = true;

            SelectionInfo selectionInfo = new SelectionInfo();

            if (schemaInfo != null)
            {
                selectionInfo.UserDefinedTypeNames = schemaInfo.UserDefinedTypes.Select(item => item.Name).ToArray();
                selectionInfo.TableNames           = schemaInfo.Tables.Select(t => t.Name).ToArray();
                selectionInfo.ViewNames            = schemaInfo.Views.Select(item => item.Name).ToArray();
            }

            SchemaInfo sourceSchemaInfo = await sourceInterpreter.GetSchemaInfoAsync(selectionInfo);

            #region Set data type by user define type

            List <UserDefinedType> 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 (!string.IsNullOrEmpty(this.Target.DbOwner))
            {
                SchemaInfoHelper.TransformOwner(targetSchemaInfo, this.Target.DbOwner);
            }

            ColumnTranslator columnTranslator = new ColumnTranslator(targetSchemaInfo.TableColumns, this.Source.DbInterpreter.DatabaseType, this.Target.DbInterpreter.DatabaseType);
            targetSchemaInfo.TableColumns = columnTranslator.Translate();

            ViewTranslator viewTranslator = new ViewTranslator(targetSchemaInfo.Views, sourceInterpreter, this.Target.DbInterpreter, this.Target.DbOwner);
            targetSchemaInfo.Views = viewTranslator.Translate();

            if (this.Option.EnsurePrimaryKeyNameUnique)
            {
                SchemaInfoHelper.EnsurePrimaryKeyNameUnique(targetSchemaInfo);
            }

            if (this.Option.EnsureIndexNameUnique)
            {
                SchemaInfoHelper.EnsureIndexNameUnique(targetSchemaInfo);
            }

            DbInterpreter targetInterpreter = this.Target.DbInterpreter;
            bool          generateIdentity  = targetInterpreter.Option.GenerateIdentity;

            if (generateIdentity)
            {
                targetInterpreter.Option.InsertIdentityValue = true;
            }

            string script = "";

            sourceInterpreter.Subscribe(this.observer);
            targetInterpreter.Subscribe(this.observer);

            DataTransferErrorProfile dataErrorProfile = null;

            using (DbConnection dbConnection = targetInterpreter.GetDbConnector().CreateConnection())
            {
                this.isBusy = true;

                if (this.Option.UseTransaction)
                {
                    dbConnection.Open();
                    this.transaction = dbConnection.BeginTransaction();
                }

                #region Schema sync
                if (this.Option.GenerateScriptMode.HasFlag(GenerateScriptMode.Schema))
                {
                    script = targetInterpreter.GenerateSchemaScripts(targetSchemaInfo);

                    if (this.Option.ExecuteScriptOnTargetServer)
                    {
                        if (string.IsNullOrEmpty(script))
                        {
                            this.Feedback(targetInterpreter, $"The script to create schema is empty.", FeedbackInfoType.Error);
                            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
                            {
                                string[] sqls  = script.Split(new string[] { targetInterpreter.ScriptsSplitString }, StringSplitOptions.RemoveEmptyEntries);
                                int      count = sqls.Count();

                                int i = 0;
                                foreach (string sql in sqls)
                                {
                                    if (!string.IsNullOrEmpty(sql.Trim()))
                                    {
                                        i++;

                                        if (!targetInterpreter.HasError)
                                        {
                                            targetInterpreter.Feedback(FeedbackInfoType.Info, $"({i}/{count}), executing:{Environment.NewLine} {sql}");
                                            await targetInterpreter.ExecuteNonQueryAsync(dbConnection, this.GetCommandInfo(sql.Trim(), null, transaction));
                                        }
                                    }
                                }
                            }
                        }
                        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
                            };
                        }
                    }

                    if (sourceInterpreter.Option.ScriptOutputMode.HasFlag(GenerateScriptOutputMode.WriteToFile))
                    {
                        sourceInterpreter.AppendScriptsToFile("", GenerateScriptMode.Data, true);
                    }

                    if (targetInterpreter.Option.ScriptOutputMode.HasFlag(GenerateScriptOutputMode.WriteToFile))
                    {
                        targetInterpreter.AppendScriptsToFile("", GenerateScriptMode.Data, true);
                    }

                    foreach (var item in identityTableColumns)
                    {
                        if (targetInterpreter.DatabaseType == DatabaseType.SqlServer)
                        {
                            await targetInterpreter.SetIdentityEnabled(dbConnection, item, false);
                        }
                    }

                    if (this.Option.ExecuteScriptOnTargetServer || targetInterpreter.Option.ScriptOutputMode.HasFlag(GenerateScriptOutputMode.WriteToFile))
                    {
                        sourceInterpreter.OnDataRead += async(table, columns, data, dataTable) =>
                        {
                            if (!this.hasError)
                            {
                                try
                                {
                                    StringBuilder sb = new StringBuilder();

                                    (Table Table, List <TableColumn> Columns)targetTableAndColumns = this.GetTargetTableColumns(targetSchemaInfo, this.Target.DbOwner, table, columns);

                                    if (targetTableAndColumns.Table == null || targetTableAndColumns.Columns == null)
                                    {
                                        return;
                                    }

                                    Dictionary <string, object> paramters = targetInterpreter.AppendDataScripts(sb, targetTableAndColumns.Table, targetTableAndColumns.Columns, new Dictionary <long, List <Dictionary <string, object> > >()
                                    {
                                        { 1, data }
                                    });

                                    script = sb.ToString().Trim().Trim(';');

                                    if (this.Option.ExecuteScriptOnTargetServer)
                                    {
                                        if (this.Option.BulkCopy && targetInterpreter.SupportBulkCopy)
                                        {
                                            await targetInterpreter.BulkCopyAsync(dbConnection, dataTable, table.Name);
                                        }
                                        else
                                        {
                                            await targetInterpreter.ExecuteNonQueryAsync(dbConnection, this.GetCommandInfo(script, paramters, this.transaction));
                                        }

                                        targetInterpreter.FeedbackInfo($"Table \"{table.Name}\":{data.Count} records transferred.");
                                    }
                                }
                                catch (Exception ex)
                                {
                                    sourceInterpreter.CancelRequested = true;

                                    this.Rollback();

                                    ConnectionInfo sourceConnectionInfo = sourceInterpreter.ConnectionInfo;
                                    ConnectionInfo targetConnectionInfo = targetInterpreter.ConnectionInfo;

                                    DataTransferException dataTransferException = new DataTransferException(ex)
                                    {
                                        SourceServer   = sourceConnectionInfo.Server,
                                        SourceDatabase = sourceConnectionInfo.Database,
                                        SourceObject   = table.Name,
                                        TargetServer   = targetConnectionInfo.Server,
                                        TargetDatabase = targetConnectionInfo.Database,
                                        TargetObject   = table.Name
                                    };

                                    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 = table.Name
                                        });
                                    }
                                }
                            }
                        };
                    }

                    await sourceInterpreter.GenerateDataScriptsAsync(sourceSchemaInfo);

                    foreach (var item in identityTableColumns)
                    {
                        if (targetInterpreter.DatabaseType == DatabaseType.SqlServer)
                        {
                            await targetInterpreter.SetIdentityEnabled(dbConnection, item, true);
                        }
                    }
                }
                #endregion

                if (this.transaction != null && !this.cancelRequested)
                {
                    this.transaction.Commit();
                }

                this.isBusy = false;
            }

            if (dataErrorProfile != null && !this.hasError && !this.cancelRequested)
            {
                DataTransferErrorProfileManager.Remove(dataErrorProfile);
            }
        }