コード例 #1
0
        public void Convert(SchemaInfo schemaInfo = null, bool getAllIfNotSpecified = true)
        {
            DbInterpreter sourceInterpreter = this.Source.DbInterpreter;

            sourceInterpreter.Option.TreatBytesAsNullForScript = true;

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

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

            SchemaInfo sourceSchemaInfo = sourceInterpreter.GetSchemaInfo(tableNames, userDefinedTypeNames, getAllIfNotSpecified);
            SchemaInfo targetSchemaInfo = SchemaInfoHelper.Clone(sourceSchemaInfo);

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

            targetSchemaInfo.Columns = ColumnTranslator.TranslateColumn(targetSchemaInfo.Columns, this.Source.DbInterpreter.DatabaseType, this.Target.DbInterpreter.DatabaseType);

            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[] { "GO" + Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);

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

                    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 += (table, columns, data, dataTable) =>
                    {
                        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)
                            {
                                targetInterpreter.BulkCopy(dbConnection, dataTable, table.Name);
                                //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()))
                                    {
                                        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
                                  };
                        }
                    };

                    sourceInterpreter.GenerateDataScripts(sourceSchemaInfo);

                    identityTableColumns.ForEach(item =>
                    {
                        if (targetInterpreter.DatabaseType == DatabaseType.SqlServer)
                        {
                            targetInterpreter.SetIdentityEnabled(dbConnection, item, true);
                        }
                    });
                }
            }
        }
コード例 #2
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);
            }
        }