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