private List <FileRow> ParseDataContent(bool skipFirstLine)
{
switch (SourceType)
{
case SourceType.Excel:
case SourceType.Text:
if (!File.Exists(txtFilePath.Text))
{
throw new Exception("Specified file does not exist");
}
break;
}
switch (SourceType)
{
case SourceType.Excel:
return(ExcelParser.GetValues(txtFilePath.Text, cboSheetName.Text, skipFirstLine));
case SourceType.Text:
return(TextParser.GetValues(txtFilePath.Text, GetDelimiter(), skipFirstLine));
case SourceType.Sql:
return(SqlParser.GetValues(cboConnectionSource.SelectedValue.ToString(), cboTableNameSource.SelectedValue.ToString(), txtFilter.Text));
default:
throw new Exception($"SourceType={SourceType} is not handled");
}
}
private void GenerateDatabase()
{
var skippedKeys = new[] { "dtproperties", "sandeslatt_user", "LogItem", "WebStat", "Log", "Song" };
try
{
Cursor = Cursors.WaitCursor;
txtResult.Text = string.Empty;
SaveSettings();
var count = 0;
var tableNames = SqlParser.GetTablesOrderedForInsert(cboConnectionSource.SelectedValue.ToString());
// DELETE
txtResult.Text += "-- First clear all tables in reverse order" + Environment.NewLine;
for (var i = tableNames.Count - 1; i >= 0; i--)
{
var tableName = tableNames[i];
if (tableName.ContainsAny(skippedKeys))
{
txtResult.Text += $"-- Skipped {tableName}" + Environment.NewLine;
}
else
{
txtResult.Text += $"DELETE FROM {tableName}" + Environment.NewLine;
}
}
txtResult.Text += Environment.NewLine + Environment.NewLine;
// INSERT
foreach (var tableName in tableNames)
{
var fileRows = SqlParser.GetValues(cboConnectionSource.SelectedValue.ToString(), tableName, "");
if (tableName.ContainsAny(skippedKeys))
{
txtResult.Text += $"-- Table {tableName} skipped" + Environment.NewLine;
continue;
}
// Create column structs to satisfy ResultCreator
var sqlColumns = SqlParser.GetColumns(cboConnectionTarget.SelectedValue.ToString(), tableName);
var mappedColumns = sqlColumns.Select((x, i) => new ColumnMap(x, new List <FileColumn>()
{
new FileColumn {
Index = i, Name = x.ColumnName
}
})).ToList();
var hasIdentity = sqlColumns.Any(x => x.IsIdentity);
// Print result
var result = ResultCreator.GetResult(CommandType.Insert, fileRows, mappedColumns, tableName);
if (hasIdentity)
{
txtResult.Text += $"SET IDENTITY_INSERT {tableName} ON;" + Environment.NewLine;
txtResult.Text += result + Environment.NewLine;
txtResult.Text += $"SET IDENTITY_INSERT {tableName} OFF;" + Environment.NewLine + Environment.NewLine;
}
else
{
txtResult.Text += result + Environment.NewLine + Environment.NewLine;
}
count += fileRows.Count;
}
lblResultCount.Text = $"{count} records generated at {DateTime.Now.ToString("HH:mm:ss")}";
SetShowState();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Generate", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
Cursor = Cursors.Default;
txtResult.Refresh();
}
}
