private void CheckCircleReferences(Table table, TableMappingDefinition mappingDefinition)
{
if (_tablesToUpdateCircleReferences.ContainsKey(table.Name))
{
_tablesToUpdateCircleReferences[table.Name].ForEach(r =>
{
TableMappingDefinition temp = _tableMappingDefinitions.Single(d => d.DestinationTable.Name.Equals(r.OriginTableName));
FieldMappingDefinition fieldMappingDefinition = temp.FieldMappingDefinitions.SingleOrDefault(f => f.DestinationField.Name.Equals(r.OriginFieldName));
if (fieldMappingDefinition != null)
{
SourceTable sourceTable = temp.SourceTable;
Field sourceField = fieldMappingDefinition.SourceField;
DestinationTable destinationTable = temp.DestinationTable;
Field destinationField = fieldMappingDefinition.DestinationField;
FieldMappingInfo info = new FieldMappingInfo
{
DestinationField = destinationField,
DestinationTable = destinationTable,
SourceField = sourceField,
SourceTable = sourceTable
};
mappingDefinition.CircleReferences.Add(info);
}
});
}
}
public BcpGenerator(string serverName, string instanceName, TableMappingDefinition definition, BcpMode mode)
{
_serverName = serverName;
_instanceName = instanceName;
_definition = definition;
_mode = mode;
}
public BcpGenerator(string serverName, string instanceName, TableMappingDefinition definition, BcpMode mode)
{
_serverName = serverName;
_instanceName = instanceName;
_definition = definition;
_mode = mode;
}
private TableMappingDefinition CreateTableMappingDefinition(SourceTable srcTable, DestinationTable destTable, TableMappingConfiguration mappingConfig)
{
TableMappingDefinition mappingDefinition;
if (mappingConfig != null)
{
mappingDefinition = new TableMappingDefinition(srcTable, destTable, mappingConfig.FieldMappings);
}
else
{
mappingDefinition = new TableMappingDefinition(srcTable, destTable);
}
return(mappingDefinition);
}
private void HandleMaxTextUpdate(SqlGenerator sqlGenerator, TableMappingDefinition mappingDefinition, List <string> scriptNames)
{
Logger.Info("Handling max text");
SourceTable srcTable = mappingDefinition.SourceTable;
DestinationTable destTable = mappingDefinition.DestinationTable;
string postSqlPackagePath = Path.Combine(_packageOutputPath, CONVERSION_PACKAGE_FOLDER);
Logger.Info("Generating script for updating max text fields");
string script = sqlGenerator.GenerateVarCharMaxUpdateScript();
if (!string.IsNullOrEmpty(script))
{
var fileName = string.Format("{0}-{1}_TEXT.sql", srcTable.Name, destTable.Name);
SaveToFile(postSqlPackagePath, fileName, script);
scriptNames.Add(fileName);
}
}
private void HandleBcp(SqlGenerator sqlGenerator, BcpGenerator bcpGenerator, TableMappingDefinition mappingDefinition, Dictionary <string, string> bcpExportCommands, Dictionary <string, string> bcpImportCommands)
{
Logger.Info("Handling BCP");
SourceTable srcTable = mappingDefinition.SourceTable;
DestinationTable destTable = mappingDefinition.DestinationTable;
string bcpPackagePath = Path.Combine(_packageOutputPath, CONVERSION_PACKAGE_FOLDER);
// Generate and save format file
Logger.Info("Generating BCP format file for EXPORT");
string bcpExportFormatFile = bcpGenerator.GenerateFormatFile(BcpDirection.Export);
string exportFmtFileName = string.Format("{0}_EXPORT.fmt", destTable.Name);
string exportFmtFilePath = Path.Combine(CONVERSION_PACKAGE_FOLDER, exportFmtFileName);
SaveToFile(bcpPackagePath, exportFmtFileName, bcpExportFormatFile);
Logger.Info("Generating BCP format file for IMPORT");
string bcpImportFormatFile = bcpGenerator.GenerateFormatFile(BcpDirection.Import);
string importFmtFileName = string.Format("{0}_IMPORT.fmt", destTable.Name);
string importFmtFilePath = Path.Combine(CONVERSION_PACKAGE_FOLDER, importFmtFileName);
SaveToFile(bcpPackagePath, importFmtFileName, bcpImportFormatFile);
// Generate export, import commands
Logger.Info("Generating BCP Export/Import commands");
string bcpSelect = sqlGenerator.GenerateSelectStatement();
string dataFileName = string.Format("{0}-{1}_BCP.txt", srcTable.Name, destTable.Name);
string dataFilePath = Path.Combine(CONVERSION_PACKAGE_FOLDER, dataFileName);
File.Create(Path.Combine(_packageOutputPath, dataFilePath));
var bcpExportCommand = bcpGenerator.GenerateExportCommand(bcpSelect, exportFmtFilePath, dataFilePath);
bcpExportCommands.Add(dataFileName, bcpExportCommand);
var bcpImportCommand = bcpGenerator.GenerateImportCommand(importFmtFilePath, dataFilePath);
bcpImportCommands.Add(dataFileName, bcpImportCommand);
}
private TableMappingDefinition CreateTableMappingDefinition(SourceTable srcTable, DestinationTable destTable, TableMappingConfiguration mappingConfig)
{
TableMappingDefinition mappingDefinition;
if (mappingConfig != null)
{
mappingDefinition = new TableMappingDefinition(srcTable, destTable, mappingConfig.FieldMappings);
}
else
{
mappingDefinition = new TableMappingDefinition(srcTable, destTable);
}
return mappingDefinition;
}
private void HandleMaxTextUpdate(SqlGenerator sqlGenerator, TableMappingDefinition mappingDefinition, List<string> scriptNames)
{
Logger.Info("Handling max text");
SourceTable srcTable = mappingDefinition.SourceTable;
DestinationTable destTable = mappingDefinition.DestinationTable;
string postSqlPackagePath = Path.Combine(_packageOutputPath, CONVERSION_PACKAGE_FOLDER);
Logger.Info("Generating script for updating max text fields");
string script = sqlGenerator.GenerateVarCharMaxUpdateScript();
if(!string.IsNullOrEmpty(script))
{
var fileName = string.Format("{0}-{1}_TEXT.sql", srcTable.Name, destTable.Name);
SaveToFile(postSqlPackagePath, fileName, script);
scriptNames.Add(fileName);
}
}
private void HandleBcp(SqlGenerator sqlGenerator, BcpGenerator bcpGenerator, TableMappingDefinition mappingDefinition, Dictionary<string, string> bcpExportCommands, Dictionary<string, string> bcpImportCommands)
{
Logger.Info("Handling BCP");
SourceTable srcTable = mappingDefinition.SourceTable;
DestinationTable destTable = mappingDefinition.DestinationTable;
string bcpPackagePath = Path.Combine(_packageOutputPath, CONVERSION_PACKAGE_FOLDER);
// Generate and save format file
Logger.Info("Generating BCP format file for EXPORT");
string bcpExportFormatFile = bcpGenerator.GenerateFormatFile(BcpDirection.Export);
string exportFmtFileName = string.Format("{0}_EXPORT.fmt", destTable.Name);
string exportFmtFilePath = Path.Combine(CONVERSION_PACKAGE_FOLDER, exportFmtFileName);
SaveToFile(bcpPackagePath, exportFmtFileName, bcpExportFormatFile);
Logger.Info("Generating BCP format file for IMPORT");
string bcpImportFormatFile = bcpGenerator.GenerateFormatFile(BcpDirection.Import);
string importFmtFileName = string.Format("{0}_IMPORT.fmt", destTable.Name);
string importFmtFilePath = Path.Combine(CONVERSION_PACKAGE_FOLDER, importFmtFileName);
SaveToFile(bcpPackagePath, importFmtFileName, bcpImportFormatFile);
// Generate export, import commands
Logger.Info("Generating BCP Export/Import commands");
string bcpSelect = sqlGenerator.GenerateSelectStatement();
string dataFileName = string.Format("{0}-{1}_BCP.txt", srcTable.Name, destTable.Name);
string dataFilePath = Path.Combine(CONVERSION_PACKAGE_FOLDER, dataFileName);
File.Create(Path.Combine(_packageOutputPath, dataFilePath));
var bcpExportCommand = bcpGenerator.GenerateExportCommand(bcpSelect, exportFmtFilePath, dataFilePath);
bcpExportCommands.Add(dataFileName, bcpExportCommand);
var bcpImportCommand = bcpGenerator.GenerateImportCommand(importFmtFilePath, dataFilePath);
bcpImportCommands.Add(dataFileName, bcpImportCommand);
}
public SqlGenerator(TableMappingDefinition definition, bool checkExistData)
{
_definition = definition;
_checkExistData = checkExistData;
}
public SqlGenerator(TableMappingDefinition definition, bool checkExistData)
{
_definition = definition;
_checkExistData = checkExistData;
}
public void GenerateMigrationScripts()
{
int count = 0;
string outputPath = ConfigurationManager.AppSettings["SQLOutputFolder"];
outputPath = Path.Combine(outputPath, DateTime.Now.ToString("ddMMyyyy"));
List <string> scriptNames = new List <string>();
var temp = new List <string>();
while (count < _destinationDatabase.Tables.Count)
{
DestinationTable destinationTable = null;
SourceTable sourceTable = null;
try
{
// Get next table to migrate
destinationTable = GetNextTableCanMap();
if (destinationTable == null)
{
break;
}
Console.WriteLine("Processing " + destinationTable.Name);
// Check explicit mapping for source table - destination table
TableMappingDefinition mappingDefinition;
TableMappingConfiguration mappingConfig = GetTableMappingConfig(destinationTable.Name);
sourceTable = GetSourceTable(destinationTable.Name, mappingConfig);
if (mappingConfig != null)
{
mappingDefinition = new TableMappingDefinition(sourceTable, destinationTable, mappingConfig.FieldMappings);
mappingDefinition.IsIdentityInsert = mappingConfig.IsIdentityInsert;
}
else
{
mappingDefinition = new TableMappingDefinition(sourceTable, destinationTable);
}
// Retain mapping definition for later use
_tableMappingDefinitions.Add(mappingDefinition);
// Check circle references needed to update
CheckCircleReferences(destinationTable, mappingDefinition);
// Generate script
var scriptGenerator = new TableScriptGenerator(_sourceDatabase, mappingDefinition);
var script = scriptGenerator.GenerateScript();
var fileName = string.Format("{0}.{1}-{2}.sql", count++, sourceTable.Name, destinationTable.Name);
Utils.SaveToFile(outputPath, fileName, script);
scriptNames.Add(fileName);
temp.Add(destinationTable.Name);
destinationTable.IsMapped = true;
}
catch (MigrationException ex)
{
if (ex.ErrorCode == MigrationExceptionCodes.DATABASE_ERROR_TABLE_NOT_FOUND)
{
destinationTable.IsMapped = true;
// TODO: write log
Console.WriteLine(destinationTable.Name + " not mapped");
}
}
}
// Generate script clear temp database
var clearScript = string.Format(SqlScriptTemplates.TRUNCATE_TABLE, "[TempDatabase].dbo.[TrackingRecords]");
var clearFileName = string.Format("{0}.Clear.sql", count);
Utils.SaveToFile(outputPath, clearFileName, clearScript);
scriptNames.Add(clearFileName);
// Generate bat file
string batScript = BatGenerator.GenerateSqlExecuteScript(scriptNames, _options.ServerName, _options.InstanceName, outputPath);
Utils.SaveToFile(outputPath, "RunMigration.bat", batScript);
}
public TableScriptGenerator(SourceDatabase sourceDatabase, TableMappingDefinition definition)
{
_sourceDatabase = sourceDatabase;
_definition = definition;
}
