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