private async Task ExecuteCore(string sql)
{
ScriptOutput output = null;
Exception exception = null;
_manager.PushStatus("Running your script...");
await Task.Run(() => {
try {
output = _manager.ExecuteScript(sql);
} catch (Exception ex) {
exception = ex;
}
});
_manager.PopStatus();
if (exception != null)
{
throw exception;
}
else
{
var resultSets = new List <DataTable>();
foreach (var sdt in output.DataTables)
{
resultSets.Add(sdt.ToDataTable());
}
if (output.TextOutput.Any())
{
var dt = new DataTable("Output");
dt.Columns.Add("printed_text", typeof(string));
foreach (var line in output.TextOutput)
{
var row = dt.NewRow();
row.ItemArray = new object[] { line };
dt.Rows.Add(row);
}
resultSets.Insert(0, dt);
}
_grid.DataSource = null;
_results.ForEach(x => x.Dispose());
_results.Clear();
_results.AddRange(resultSets);
ShowResult(0);
}
}
private async Task UpdateInputPreview()
{
var loadId = Guid.NewGuid();
_inputPreviewLoadId = loadId;
_inputPreviewLoadControl.PushLoad();
try {
var tempTableName = Guid.NewGuid().ToString();
var fileEncoding = _optionsControl.FileEncoding.Value;
var text = await Task.Run(() => {
try {
var importSql =
@"IMPORT TXT @filePath INTO @tableName (number, line)
OPTIONS (TAKE_LINES: 1000, TEMPORARY_TABLE: 1, FILE_ENCODING: @encoding);";
_manager.ExecuteScript(importSql, new Dictionary <string, object> {
["@filePath"] = _filePath,
["@tableName"] = tempTableName,
["@encoding"] = fileEncoding
});
var dt = _manager.ExecuteScript($"SELECT line FROM {tempTableName.DoubleQuote()} ORDER BY number")
.DataTables[0];
return(string.Join(Environment.NewLine, dt.Rows.Select(x => x[0].ToString())));
} finally {
_manager.ExecuteScript($"DROP TABLE IF EXISTS {tempTableName.DoubleQuote()}");
}
});
if (_inputPreviewLoadId == loadId)
{
_inputPreviewError.Value = null;
_inputPreviewControl.PreviewText = text;
}
} catch (UncaughtErrorScriptException ex) {
if (_inputPreviewLoadId == loadId)
{
_inputPreviewError.Value = $"Error loading the input file:\r\n{ex.ErrorMessage}";
_inputPreviewLoadControl.SetError(_inputPreviewError.Value);
}
} finally {
_inputPreviewLoadControl.PopLoad();
}
}
public static async Task <bool> Start(IWin32Window owner, string filePath, NotebookManager manager)
{
DatabaseSchema schema = null;
manager.PushStatus("Reading notebook...");
try {
schema = await Task.Run(() => DatabaseSchema.FromNotebook(manager.Notebook));
} catch (Exception ex) {
MessageForm.ShowError(owner,
"Import Error",
"Failed to read the list of tables in the notebook.",
ex.Message);
return(false);
}
manager.PopStatus();
string importSql;
using (var f = new ImportCsvForm(filePath, schema, manager)) {
if (f.ShowDialog(owner) != DialogResult.OK)
{
return(false);
}
importSql = f.GeneratedImportSql;
}
manager.PushStatus($"Importing \"{Path.GetFileName(filePath)}\"...");
try {
await Task.Run(() => {
manager.ExecuteScript(importSql, withTransaction: true);
});
manager.Rescan();
} catch (Exception ex) {
manager.PopStatus();
MessageForm.ShowError(owner, "Import Error", "The import failed.", ex.GetErrorMessage());
return(false);
}
manager.SetDirty();
manager.PopStatus();
return(true);
}
private static async Task <bool> RunImportScript(string importSql, IWin32Window owner, string filePath,
NotebookManager manager)
{
manager.PushStatus($"Importing \"{Path.GetFileName(filePath)}\"...");
try {
await Task.Run(() => {
manager.ExecuteScript(importSql, transactionType: NotebookManager.TransactionType.Transaction);
});
manager.Rescan();
} catch (Exception ex) {
manager.PopStatus();
MessageForm.ShowError(owner, "Import Error", "The import failed.", ex.GetErrorMessage());
return(false);
}
manager.SetDirty();
manager.PopStatus();
return(true);
}
private async void ExportMnu_Click(object sender, EventArgs e)
{
SaveOpenItems();
var scripts = _manager.Items.Where(x => x.Type == NotebookItemType.Script).Select(x => x.Name);
var tables = _manager.Items.Where(x => x.Type == NotebookItemType.Table).Select(x => x.Name);
var views = _manager.Items.Where(x => x.Type == NotebookItemType.View).Select(x => x.Name);
NotebookItem item;
bool doSaveAs;
using (var f = new ExportForm(scripts, tables, views)) {
var result = f.ShowDialog(this);
item = f.NotebookItem;
if (result == DialogResult.Yes)
{
doSaveAs = false; // open
}
else if (result == DialogResult.No)
{
doSaveAs = true; // save
}
else
{
return;
}
}
string filePath;
if (doSaveAs)
{
var f = new SaveFileDialog {
AddExtension = true,
AutoUpgradeEnabled = true,
CheckPathExists = true,
DefaultExt = ".csv",
Filter = "CSV files|*.csv",
OverwritePrompt = true,
SupportMultiDottedExtensions = true,
Title = "Save CSV As",
ValidateNames = true
};
using (f) {
if (f.ShowDialog(this) == DialogResult.OK)
{
filePath = f.FileName;
}
else
{
return;
}
}
}
else
{
filePath = GetTemporaryExportFilePath();
}
_manager.PushStatus("Running the selected script...");
try {
string sql;
if (item.Type == NotebookItemType.Script)
{
sql = $"EXECUTE {item.Name.DoubleQuote()}";
}
else
{
sql = $"SELECT * FROM {item.Name.DoubleQuote()}";
}
var output = await Task.Run(() => _manager.ExecuteScript(sql));
_manager.PopStatus();
_manager.PushStatus("Writing CSV file. Please wait.");
await Task.Run(() => {
using (var stream = File.CreateText(filePath)) {
output.WriteCsv(stream);
}
});
} catch (Exception ex) {
ErrorBox("Export Error", "The data export failed.", ex.Message);
return;
} finally {
_manager.PopStatus();
}
if (doSaveAs)
{
// if the user selected "save as", then open Explorer and select the file
Process.Start(new ProcessStartInfo {
FileName = "explorer.exe",
Arguments = $"/e, /select, \"{filePath}\""
});
}
else
{
// if the user selected "open", then open the CSV file directly
Process.Start(filePath);
}
}
