private int Process(string mode) { try { Directory.CreateDirectory(_options.OutputDirectory); ExcelReader excelReader = new ExcelReader(_options.ReadEmptyRecords, _options.WorksheetsToRead?.ToArray()); ValueRenderer valueRenderer = new ValueRenderer(_options.NullReplacements?.ToArray()); IQueryMaker queryMaker = QueryMakerFactory.Create(mode, valueRenderer); TableScriptGenerator tableScriptGenerator = new TableScriptGenerator(queryMaker); IEnumerable <Table> tables = excelReader.Read(_options.InputFile); foreach (Table table in tables) { string filePath = Path.Combine(_options.OutputDirectory, table.Name + ".sql"); Write($"writing {filePath} ..."); if (table.Records.Any()) { using (Script script = tableScriptGenerator.GenerateTableScript(table)) { using (FileStream fileStream = File.Create(filePath)) { script.Content.CopyTo(fileStream); WriteLine(" done"); } } } else { WriteLine(" empty (skipped)"); } } return(0); } catch (Exception ex) { Error.WriteLine($"Error: {ex.GetType().Name}"); Error.WriteLine($"Error: {ex.Message}"); #if DEBUG Error.WriteLine(ex.StackTrace); #endif return(1); } }
public void CanGenerateScripts() { IQueryMaker queryMakerSubstitute = Substitute.For <IQueryMaker>(); queryMakerSubstitute.GenerateQuery(Arg.Any <ExcelToSQLScripts.Models.Record>()).Returns("random string\n"); TableScriptGenerator tableScriptGenerator = new TableScriptGenerator(queryMakerSubstitute); Table table = Utils.GetTable(2); using (Script script = tableScriptGenerator.GenerateTableScript(table)) { script.Name.Should().Be("Employees"); script.Content.Should().NotBeNull(); StreamReader streamReader = new StreamReader(script.Content); string content = streamReader.ReadToEnd(); content.Should().Be("random string\nrandom string\n"); } }
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); }