public static async Task <bool> Start(IWin32Window owner, string filePath, NotebookManager manager)
{
var schema = await ReadDatabaseSchema(owner, manager);
if (schema == null)
{
return(false);
}
var extension = Path.GetExtension(filePath).ToLower();
switch (extension)
{
case ".csv":
case ".txt":
return(await ImportCsv(owner, filePath, manager, schema));
case ".xls":
case ".xlsx":
case ".xlsm":
case ".xlsb":
return(await ImportXls(owner, filePath, manager, schema));
default:
throw new InvalidOperationException($"The file type \"{extension}\" is not supported.");
}
}
public ExplorerControl(NotebookManager manager, IWin32Window mainForm, Slot <bool> operationInProgress)
{
InitializeComponent();
_list.EnableDoubleBuffer();
_detailsLst.EnableDoubleBuffer();
_mainForm = mainForm;
_manager = manager;
_operationInProgress = operationInProgress;
_manager.NotebookChange += (sender, e) => HandleNotebookChange(e);
_contextMenuStrip.SetMenuAppearance();
_list.SmallImageList = _detailsLst.SmallImageList = _paddedImageList = _imageList.PadListViewIcons();
_operationInProgress.Change += (a, b) => {
if (a && !b)
{
List_SelectedIndexChanged(null, EventArgs.Empty);
}
};
_list.GotFocus += (sender, e) => {
_manager.CommitOpenEditors();
_manager.Rescan(notebookItemsOnly: true);
};
Load += (sender, e) => {
_splitContainer.SplitterDistance = _splitContainer.Height - 300;
};
}
public ImportXlsBookForm(string filePath, DatabaseSchema schema, NotebookManager manager)
{
InitializeComponent();
_filePath = filePath;
_databaseSchema = schema;
_manager = manager;
_dockPanel = DockPanelUtil.CreateDockPanel();
_dockPanel.DockLeftPortion = 300;
_dockPanelContainer.Controls.Add(_dockPanel);
_sheetsControl = new ImportXlsSheetsControl();
{
var dc = new UserControlDockContent("Workbook", _sheetsControl, DockAreas.DockLeft)
{
CloseButtonVisible = false
};
dc.Show(_dockPanel, DockState.DockLeft);
}
_optionsControl = new ImportXlsSheetControl();
{
var dc = new UserControlDockContent("Worksheet", _optionsControl)
{
CloseButtonVisible = false,
Icon = Properties.Resources.TableSheetIco
};
dc.Show(_dockPanel);
}
_sqlControl = new SqlTextControl(readOnly: true);
_sqlLoadControl = new LoadingContainerControl {
ContainedControl = _sqlControl
};
{
var dc = new UserControlDockContent("Import Script", _sqlLoadControl, DockAreas.DockBottom)
{
CloseButtonVisible = false
};
dc.Show(_dockPanel, DockState.DockBottomAutoHide);
}
_outputPreviewControl = new ImportPreviewControl();
_outputPreviewLoadControl = new LoadingContainerControl {
ContainedControl = _outputPreviewControl
};
{
var dc = new UserControlDockContent("Preview", _outputPreviewLoadControl, DockAreas.DockBottom)
{
CloseButtonVisible = false,
Icon = Properties.Resources.TableImportIco
};
dc.Show(_dockPanel, DockState.DockBottomAutoHide);
}
}
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);
}
};
}
public ConsoleServer(NotebookManager manager, string consoleName)
{
_manager = manager;
ConsoleName = consoleName;
_server = new HttpServer(0);
_port = _server.Port;
_server.Request += Server_Request;
// read previous variable data if present
var state = manager.Notebook.UserData.ConsoleStates.FirstOrDefault(x => x.ConsoleName == consoleName);
if (state != null)
{
var keys = state.VarNames;
var values = ArrayUtil.GetArrayElements(Convert.FromBase64String(state.VarDataB64));
_variables = keys.Zip(values, (k, v) => Tuple.Create(k, v)).ToDictionary(x => x.Item1, x => x.Item2);
}
}
public ConsoleDocumentControl(string name, NotebookManager manager, IWin32Window mainForm)
{
InitializeComponent();
ItemName = name;
_manager = manager;
_notebook = manager.Notebook;
_mainForm = mainForm;
_contextMenuStrip.SetMenuAppearance();
_server = new ConsoleServer(manager, name);
var url = $"http://127.0.0.1:{_server.Port}/console";
_browser.Navigate(url);
_browser.PreviewKeyDown += (sender, e) => {
if (e.KeyData == (Keys.Control | Keys.C))
{
EnableDisableContextMenu();
_copyMnu.PerformClick();
}
else if (e.KeyData == (Keys.Control | Keys.X))
{
EnableDisableContextMenu();
_cutMnu.PerformClick();
}
else if (e.KeyData == (Keys.Control | Keys.V))
{
EnableDisableContextMenu();
_pasteMnu.PerformClick();
}
else if (e.KeyData == (Keys.Control | Keys.A))
{
_selectAllMnu.PerformClick();
}
else if (e.KeyData == Keys.Delete)
{
_browser.Document.ExecCommand("delete", false, null);
}
else
{
manager.HandleAppHotkeys(e.KeyData);
}
};
}
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 QueryDocumentControl(string name, NotebookManager manager, IWin32Window mainForm, Slot <bool> operationInProgress)
{
InitializeComponent();
ItemName = name;
_manager = manager;
_notebook = manager.Notebook;
_mainForm = mainForm;
_operationInProgress = operationInProgress;
_resultToolStrip.SetMenuAppearance();
_grid.EnableDoubleBuffering();
_textCtl = new SqlTextControl(false)
{
Dock = DockStyle.Fill,
Padding = new Padding(4, 0, 0, 0)
};
_sqlPanel.Controls.Add(_textCtl);
// if this tool window has been pulled off into a floating window, then the MainForm's key handler won't
// trigger on F5, so catch it here.
_textCtl.Scintilla.KeyDown += async(sender, e) => {
if (e.KeyCode == Keys.F5)
{
await Execute();
}
};
Load += (sender, e) => {
string initialText = _manager.GetItemData(ItemName);
if (initialText != null)
{
_textCtl.SqlText = initialText;
}
_textCtl.SqlTextChanged += (sender2, e2) => _manager.SetDirty();
ShowResult(0);
};
}
public HelpDocumentControl(NotebookManager manager, string homeUrl, string initialUrl = "about:blank")
{
InitializeComponent();
_homeUrl = homeUrl;
_toolStrip.SetMenuAppearance();
_contextMenuStrip.SetMenuAppearance();
_browser.DocumentTitleChanged += (sender, e) => UpdateToolbar();
_browser.CanGoBackChanged += (sender, e) => UpdateToolbar();
_browser.CanGoForwardChanged += (sender, e) => UpdateToolbar();
_browser.Navigated += (sender, e) => UpdateToolbar();
_browser.DocumentTitleChanged += (sender, e) => UpdateToolbar();
_browser.Navigate(initialUrl);
_browser.PreviewKeyDown += (sender, e) => {
if (e.KeyData == (Keys.Control | Keys.C))
{
EnableDisableContextMenu();
_copyMnu.PerformClick();
}
else if (e.KeyData == (Keys.Control | Keys.A))
{
_selectAllMnu.PerformClick();
}
else if (e.KeyData == (Keys.Alt | Keys.Left))
{
EnableDisableContextMenu();
_backMnu.PerformClick();
}
else if (e.KeyData == (Keys.Alt | Keys.Right))
{
EnableDisableContextMenu();
_forwardMnu.PerformClick();
}
else
{
manager.HandleAppHotkeys(e.KeyData);
}
};
}
} // the ultimate result of this forum
public ImportCsvForm(string filePath, DatabaseSchema schema, NotebookManager manager)
{
InitializeComponent();
_filePath = filePath;
_databaseSchema = schema;
_manager = manager;
_dockPanel = new DockPanel {
Dock = DockStyle.Fill,
DocumentStyle = DocumentStyle.DockingWindow,
Theme = new VS2012LightTheme {
ShowWindowListButton = false,
ShowAutoHideButton = false,
ForceActiveCaptionColor = true
},
DockTopPortion = 0.5,
AllowEndUserDocking = false,
AllowEndUserNestedDocking = false,
ShowDocumentIcon = true
};
_dockPanelContainer.Controls.Add(_dockPanel);
_optionsControl = new ImportCsvOptionsControl(schema)
{
Dock = DockStyle.Fill
};
{
var dc = new UserControlDockContent("Import Options", _optionsControl, DockAreas.DockTop)
{
CloseButtonVisible = false
};
dc.Show(_dockPanel, DockState.DockTop);
}
_columnsControl = new ImportColumnsControl {
Dock = DockStyle.Fill
};
_columnsLoadControl = new LoadingContainerControl {
ContainedControl = _columnsControl
};
{
var dc = new UserControlDockContent("Columns", _columnsLoadControl, DockAreas.Float | DockAreas.DockTop)
{
CloseButtonVisible = false
};
dc.Show(_dockPanel, new Rectangle(-50000, -50000, 100, 100)); // hide brief flash of this floating window
dc.DockHandler.FloatPane.DockTo(_dockPanel.DockWindows[DockState.DockTop]);
dc.DockAreas = DockAreas.DockTop;
}
DockContent inputPreviewDc;
_inputPreviewControl = new ImportTextFilePreviewControl {
Dock = DockStyle.Fill
};
_inputPreviewLoadControl = new LoadingContainerControl {
ContainedControl = _inputPreviewControl
};
{
var dc = new UserControlDockContent("Original File", _inputPreviewLoadControl)
{
CloseButton = false,
CloseButtonVisible = false,
ControlBox = false,
Icon = Properties.Resources.PageWhiteTextIco
};
dc.Show(_dockPanel);
inputPreviewDc = dc;
}
_sqlControl = new SqlTextControl(readOnly: true)
{
Dock = DockStyle.Fill
};
_sqlLoadControl = new LoadingContainerControl {
ContainedControl = _sqlControl
};
{
var dc = new UserControlDockContent("Import Script", _sqlLoadControl)
{
CloseButton = false,
CloseButtonVisible = false,
ControlBox = false,
Icon = Properties.Resources.ScriptIco
};
dc.Show(_dockPanel);
}
_outputPreviewControl = new ImportPreviewControl {
Dock = DockStyle.Fill
};
_outputPreviewLoadControl = new LoadingContainerControl {
ContainedControl = _outputPreviewControl
};
{
var dc = new UserControlDockContent("Preview", _outputPreviewLoadControl)
{
CloseButton = false,
CloseButtonVisible = false,
ControlBox = false,
Icon = Properties.Resources.TableImportIco
};
dc.Show(_dockPanel);
}
inputPreviewDc.Activate(); // select "Original File" tab initially
Load += async(sender, e) => {
ValidateOptions();
await UpdateControls(inputChange : true);
};
var o = _optionsControl;
Bind.OnChange(new Slot[] { o.TargetTableName },
async(sender, e) => {
ValidateOptions();
await UpdateControls(columnsChange: true);
});
Bind.OnChange(new Slot[] { o.FileEncoding },
async(sender, e) => await UpdateControls(inputChange: true));
Bind.OnChange(new Slot[] { o.IfTableExists, o.SkipLines, o.HasColumnHeaders },
async(sender, e) => await UpdateControls(columnsChange: true));
Bind.OnChange(new Slot[] { o.IfConversionFails, _columnsControl.Change, _optionsError, _columnsError,
_inputPreviewError },
async(sender, e) => await UpdateScriptAndOutputPreview());
Bind.BindAny(new[] { _columnsLoadControl.IsOverlayVisible, _inputPreviewLoadControl.IsOverlayVisible,
_outputPreviewLoadControl.IsOverlayVisible, _sqlLoadControl.IsOverlayVisible },
x => _okBtn.Enabled = !x);
Text = $"Import {Path.GetFileName(_filePath)}";
o.TargetTableName.Value = Path.GetFileNameWithoutExtension(_filePath);
}
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> ImportXls(IWin32Window owner, string filePath, NotebookManager manager,
DatabaseSchema schema)
{
string importSql;
using (var f = new ImportXlsBookForm(filePath, schema, manager)) {
if (f.ShowDialog(owner) != DialogResult.OK)
{
return(false);
}
importSql = f.GeneratedImportSql;
}
return(await RunImportScript(importSql, owner, filePath, manager));
}
private static async Task <bool> ImportXls(IWin32Window owner, string filePath, NotebookManager manager,
DatabaseSchema schema)
{
using (var f = new ImportXlsBookForm(filePath, schema, manager)) {
f.ShowDialog(owner);
}
return(true);
}
public MainForm(string filePath, bool isNew)
{
InitializeComponent();
_menuStrip.SetMenuAppearance();
_menuStrip.Items.Insert(0, _searchTxt = new CueToolStripTextBox {
Alignment = ToolStripItemAlignment.Right,
CueText = "Search Help",
ToolTipText = "Search the built-in documentation (Ctrl+H)",
AutoSize = false,
Margin = new Padding(0, 0, 5, 0)
});
_searchTxt.InnerTextBox.KeyDown += (sender, e) => {
if (e.KeyCode == Keys.Enter)
{
e.Handled = true;
e.SuppressKeyPress = true;
var text = _searchTxt.Text;
OpenHelp("/search?q=" + System.Net.WebUtility.UrlEncode(text));
_searchTxt.Text = "";
}
};
if (isNew)
{
_notebook = new Notebook(filePath, isNew);
}
else
{
var f = new WaitForm("SQL Notebook", $"Opening notebook \"{Path.GetFileNameWithoutExtension(filePath)}\"", () => {
_notebook = new Notebook(filePath, isNew);
});
using (f) {
f.StartPosition = FormStartPosition.CenterScreen;
f.ShowDialog();
if (f.ResultException != null)
{
throw f.ResultException;
}
}
}
_isNew = isNew;
_manager = new NotebookManager(_notebook, _isTransactionOpen);
_importer = new Importer(_manager, this);
_dockPanel = new DockPanel {
Dock = DockStyle.Fill,
Theme = new VS2012LightTheme {
ToolStripRenderer = new MenuRenderer()
},
DocumentStyle = DocumentStyle.DockingWindow,
DefaultFloatWindowSize = new Size(700, 700),
ShowDocumentIcon = true,
DockLeftPortion = 250
};
_toolStripContainer.ContentPanel.Controls.Add(_dockPanel);
_contentsPane = new UserControlDockContent("Table of Contents",
_explorer = new ExplorerControl(_manager, this, _operationInProgress),
DockAreas.DockLeft | DockAreas.DockRight);
_contentsPane.CloseButtonVisible = false;
_contentsPane.Show(_dockPanel, DockState.DockLeft);
_manager.NotebookItemOpenRequest += Manager_NotebookItemOpenRequest;
_manager.NotebookItemCloseRequest += Manager_NotebookItemCloseRequest;
_manager.NotebookItemsSaveRequest += Manager_NotebookItemsSaveRequest;
_manager.NotebookDirty += (sender, e) => SetDirty();
_manager.NotebookItemRename += Manager_NotebookItemRename;
_manager.StatusUpdate += Manager_StatusUpdate;
_manager.HandleHotkeyRequest += Manager_HandleHotkeyRequest;
// show a progressbar in the taskbar button and the statusbar when a operation is in progress
_operationInProgress.Change += (oldValue, newValue) => {
if (!oldValue && newValue)
{
this.BeginTaskbarProgress();
_statusLbl.Visible = true;
// this restarts the statusbar progress marquee from the beginning
_statusProgressbar.Style = ProgressBarStyle.Continuous;
_statusProgressbar.Style = ProgressBarStyle.Marquee;
_statusProgressbar.Visible = true;
_cancelLnk.IsLink = true;
_cancelLnk.Text = "Cancel";
_cancelLnk.Visible = true;
}
else if (oldValue && !newValue)
{
_notebook.EndUserCancel();
this.EndTaskbarProgress();
_statusProgressbar.Visible = false;
_statusLbl.Visible = false;
_cancelLnk.Visible = false;
}
};
Slot.Bind(
() => BeginInvoke(new MethodInvoker(() =>
_importMnu.Enabled = _saveAsMnu.Enabled = !_operationInProgress && !_isTransactionOpen
)),
_operationInProgress, _isTransactionOpen);
Slot.Bind(
() => BeginInvoke(new MethodInvoker(() =>
_exportMnu.Enabled = !_operationInProgress
)),
_operationInProgress);
Slot.Bind(
() => BeginInvoke(new MethodInvoker(() =>
_saveBtn.Enabled = _saveMnu.Enabled = !_operationInProgress && _isDirty && !_isTransactionOpen
)),
_operationInProgress, _isDirty, _isTransactionOpen);
_isDirty.Change += (a, b) => SetTitle();
Slot.Bind(
() => BeginInvoke(new MethodInvoker(() =>
_openTransactionLbl.Visible = _isTransactionOpen
)),
_isTransactionOpen);
if (isNew)
{
switch (Settings.Default.AutoCreateInNewNotebooks)
{
case 1: // New note
Load += (sender, e) => {
var name = _manager.NewNote();
OpenItem(new NotebookItem(NotebookItemType.Note, name));
_isDirty.Value = false;
};
break;
case 2: // New console
Load += (sender, e) => {
var name = _manager.NewConsole();
OpenItem(new NotebookItem(NotebookItemType.Console, name));
_isDirty.Value = false;
};
break;
case 3: // New script
Load += (sender, e) => {
var name = _manager.NewScript();
OpenItem(new NotebookItem(NotebookItemType.Script, name));
_isDirty.Value = false;
};
break;
}
}
Load += async(sender, e) => {
_manager.Rescan();
var updateAvailable = await IsUpdateAvailable();
if (updateAvailable)
{
_appUpdateLbl.Visible = true;
}
};
SetTitle();
}
public Importer(NotebookManager manager, IWin32Window owner)
{
_manager = manager;
_notebook = manager.Notebook;
_owner = owner;
}
public NoteDocumentControl(string name, NotebookManager manager)
{
InitializeComponent();
ItemName = name;
_manager = manager;
_toolStrip.SetMenuAppearance();
_contextMenuStrip.SetMenuAppearance();
_browser.PreviewKeyDown += (sender, e) => {
if (e.KeyData == (Keys.Control | Keys.C))
{
EnableDisableContextMenu();
_copyMnu.PerformClick();
}
else if (e.KeyData == (Keys.Control | Keys.X))
{
EnableDisableContextMenu();
_cutMnu.PerformClick();
}
else if (e.KeyData == (Keys.Control | Keys.V))
{
EnableDisableContextMenu();
_pasteMnu.PerformClick();
}
else if (e.KeyData == (Keys.Control | Keys.A))
{
_selectAllMnu.PerformClick();
}
else if (e.KeyData == (Keys.Control | Keys.B))
{
_boldBtn.PerformClick();
}
else if (e.KeyData == (Keys.Control | Keys.I))
{
_italicBtn.PerformClick();
}
else if (e.KeyData == (Keys.Control | Keys.U))
{
_underlineBtn.PerformClick();
}
else if (e.KeyData == (Keys.Control | Keys.L))
{
_alignLeftBtn.PerformClick();
}
else if (e.KeyData == (Keys.Control | Keys.E))
{
_alignCenterBtn.PerformClick();
}
else if (e.KeyData == (Keys.Control | Keys.R))
{
_alignRightBtn.PerformClick();
}
else if (e.KeyData == Keys.Delete)
{
_browser.Document.ExecCommand("delete", false, null);
}
else
{
manager.HandleAppHotkeys(e.KeyData);
}
};
using (var fonts = new InstalledFontCollection()) {
foreach (var family in fonts.Families.OrderBy(x => x.Name))
{
_fontNameCmb.Items.Add(family.Name);
}
}
_browser.DocumentText = "<html><body></body></html>";
((IHTMLDocument2)_browser.Document.DomDocument).designMode = "On";
var doc = (IHTMLDocument2)_browser.Document.DomDocument;
doc.write(_manager.GetItemData(ItemName));
var markupContainer = (IMarkupContainer2)doc;
markupContainer.RegisterForDirtyRange(_sink, out _sinkCookie);
_sink.Change += (sender2, e2) => _manager.SetDirty();
_timer.Start();
Load += (sender, e) => { _browser.Focus(); _browser.Select(); };
}
