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
});
}
public virtual Task <SchemaInfo> GetSchemaInfoAsync(SelectionInfo selectionInfo, bool getAllIfNotSpecified = true)
{
return(this.InternalGetSchemalInfo(selectionInfo, getAllIfNotSpecified, true));
}
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);
}
});
}
}
}
public virtual SchemaInfo GetSchemaInfo(SelectionInfo selectionInfo, bool getAllIfNotSpecified = true)
{
return(this.InternalGetSchemalInfo(selectionInfo, getAllIfNotSpecified, false).Result);
}
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);
}
}