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; }
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 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; }