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 static async Task <DatabaseSchema> ReadDatabaseSchema(IWin32Window owner, NotebookManager manager)
{
DatabaseSchema schema = null;
manager.PushStatus("Reading notebook...");
try {
schema = await Task.Run(() => DatabaseSchema.FromNotebook(manager.Notebook));
} catch (Exception ex) {
manager.PopStatus();
MessageForm.ShowError(owner,
"Import Error",
"Failed to read the list of tables in the notebook.",
ex.Message);
return(null);
}
manager.PopStatus();
return(schema);
}
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);
}
public TableDocumentControl(NotebookManager manager, string tableName, IWin32Window mainForm)
{
InitializeComponent();
_manager = manager;
_tableName = tableName;
_mainForm = mainForm;
_toolStrip.SetMenuAppearance();
_grid.EnableDoubleBuffering();
Load += async(sender, e) => {
Exception exception = null;
SimpleDataTable sdt = null;
manager.PushStatus("Reading table data...");
await Task.Run(() => {
try {
var n = _manager.Notebook;
sdt = n.SpecialReadOnlyQuery(
$"SELECT * FROM {_tableName.DoubleQuote()} LIMIT 1000",
new Dictionary <string, object>());
} catch (Exception ex) {
exception = ex;
}
});
manager.PopStatus();
if (exception == null)
{
var dt = new DataTable();
foreach (var col in sdt.Columns)
{
dt.Columns.Add(col);
}
foreach (var row in sdt.Rows)
{
var dtRow = dt.NewRow();
dtRow.ItemArray = row;
dt.Rows.Add(dtRow);
}
_grid.DataSource = dt;
_grid.AutoSizeColumns();
}
else
{
MessageForm.ShowError(_mainForm, "Preview Table", "An error occurred.", exception.Message);
}
};
}
private string Execute(string sql)
{
_manager.PushStatus("Running console command...");
_manager.CommitOpenEditors();
string response;
try {
Dictionary <string, object> newVars;
var result = _manager.ExecuteScriptEx(sql, _variables, true, out newVars);
_variables = newVars;
UpdateConsoleState();
_manager.SetDirty();
_manager.Rescan();
var parts = new List <string>();
if (result.ScalarResult == null && !result.TextOutput.Any() && !result.DataTables.Any())
{
parts.Add("<div style=\"overflow: hidden;\"> </div>");
}
if (result.ScalarResult != null)
{
parts.Add(
"<div style=\"overflow: hidden;\">" +
$"Returned: {WebUtility.HtmlEncode(result.ScalarResult.ToString())}" +
"</div>"
);
}
if (result.TextOutput.Any())
{
parts.Add(
"<div style=\"overflow: hidden; overflow-x: auto; padding-bottom: 18px;\"><pre style=\"margin: 0; padding: 0;\"><font face=\"Segoe UI\" size=\"2\">" +
string.Join("<br>", result.TextOutput.Select(WebUtility.HtmlEncode)) +
"</font></pre></div>"
);
}
var sb = new StringBuilder();
// inline the CSS so it gets included when the user copies tables to the clipboard
var cellCss = "border: 1px solid rgb(229, 229, 229); padding: 3px; padding-left: 6px; " +
"padding-right: 6px; text-align: left; vertical-align: top; font: 'Segoe UI' 9pt; " +
"max-height: 100px; overflow-y: auto;";
foreach (var dt in result.DataTables)
{
sb.Append("<div style=\"overflow-x: auto; overflow-y: hidden; padding-bottom: 18px;\">");
sb.Append("<table style=\"border-collapse: collapse;\"><thead><tr>");
foreach (var col in dt.Columns)
{
sb.Append($"<td style=\"{cellCss}\"><pre style=\"margin: 0; padding: 0;\">" +
$"<font face=\"Segoe UI\" size=\"2\"><b>{WebUtility.HtmlEncode(col)}</b></font></pre></td>");
}
sb.Append("</tr></thead><tbody>");
int count = 0;
foreach (var row in dt.Rows)
{
if (count >= 100)
{
break;
}
sb.Append("<tr>");
foreach (var cell in row)
{
string cellText = cell.ToString();
var cellBytes = cell as byte[];
if (cellBytes != null)
{
if (ArrayUtil.IsSqlArray(cellBytes))
{
cellText = "[" + string.Join(", ", ArrayUtil.GetArrayElements(cellBytes)) + "]";
}
}
sb.Append($"<td style=\"{cellCss}\"><pre style=\"margin:0; padding:0;\">" +
$"<font face=\"Segoe UI\" size=\"2\">{WebUtility.HtmlEncode(cellText)}</font></pre></td>");
}
sb.Append("</tr>");
count++;
}
sb.Append("</tbody></table></div>");
if (dt.Rows.Count > 100)
{
sb.Append($"<div>Table has {dt.Rows.Count} rows, showing 100.</div>");
}
}
parts.Add(sb.ToString());
var html = string.Join("", parts);
response = html;
} catch (Exception ex) {
response = $"<div style=\"overflow: hidden; color: red;\">{WebUtility.HtmlEncode(ex.Message)}</div>";
}
_manager.PopStatus();
return($"<div class=\"response\">{response}</div>");
}
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);
}
}
private async Task DoCommonImport(IImportSession session)
{
IReadOnlyList <ImportPreviewForm.SelectedTable> selectedTables = null;
bool csvHasHeaderRow = false, copyData = false;
using (var frm = new ImportPreviewForm(session)) {
if (frm.ShowDialog(_owner) != DialogResult.OK)
{
return;
}
selectedTables = frm.SelectedTables;
csvHasHeaderRow = frm.CsvHasHeaderRow;
copyData = frm.CopyData;
}
var fileSession = session as IFileImportSession;
var dbSession = session as IDatabaseImportSession;
if (fileSession != null)
{
fileSession.FileHasHeaderRow = csvHasHeaderRow;
}
Action import = () => {
_notebook.Invoke(() => {
_notebook.Execute("BEGIN");
try {
if (dbSession != null)
{
DoDatabaseImport(selectedTables, copyData, dbSession);
}
else if (fileSession != null)
{
DoDatabaseImport(selectedTables, fileSession);
}
else
{
throw new InvalidOperationException("Unexpected session type.");
}
_notebook.Execute("COMMIT");
session.AddToRecentlyUsed();
} catch {
try { _notebook.Execute("ROLLBACK"); } catch { }
throw;
}
});
};
_manager.PushStatus("Importing the selected tables...");
Exception exception = null;
await Task.Run(() => {
try {
import();
} catch (Exception ex) {
exception = ex;
}
});
_manager.PopStatus();
_manager.Rescan();
if (exception == null)
{
_manager.SetDirty();
}
else
{
MessageForm.ShowError(_owner, "Import Error", "The import failed.", exception.Message);
}
}
