private void ParseSqlColumns()
{
try
{
SaveSettings();
flowColumns.Controls.Clear();
var fileColumns = ParseInputColumns();
var sqlColumns = SqlParser.GetColumns(cboConnectionTarget.SelectedValue.ToString(), cboTableNameTarget.Text);
foreach (var sqlColumn in sqlColumns)
{
var columnMap = new ColumnMap(sqlColumn, fileColumns);
columnMap.IsModified += columnMap_IsModified;
flowColumns.Controls.Add(columnMap);
}
SetShowState();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "ParseSqlColumns", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private List <FileColumn> ParseInputColumns()
{
switch (SourceType)
{
case SourceType.Excel:
case SourceType.Text:
var rowItems = ParseDataContent(false);
var inputColumns = rowItems[0].Values.Select((value, index) => new FileColumn {
Index = index, Name = value.ToString()
}).ToList();
return(inputColumns);
case SourceType.Sql:
var sqlColumns = SqlParser.GetColumns(cboConnectionTarget.SelectedValue.ToString(), cboTableNameTarget.Text);
var inputColumns2 = sqlColumns.Select(s => new FileColumn {
Index = s.ColumnOrdinal, Name = s.ColumnName
}).ToList();
return(inputColumns2);
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();
}
}
