#region License // Copyright 2005-2019 Paul Kohler (https://github.com/paulkohler/minisqlquery). All rights reserved. // This source code is made available under the terms of the GNU Lesser General Public License v3.0 // https://github.com/paulkohler/minisqlquery/blob/master/LICENSE #endregion using System; using System.Collections; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Drawing.Printing; using System.IO; using System.Windows.Forms; using ICSharpCode.TextEditor; using ICSharpCode.TextEditor.Document; using ICSharpCode.TextEditor.Gui.CompletionWindow; using MiniSqlQuery.Commands; using MiniSqlQuery.Core; using MiniSqlQuery.Core.Commands; using MiniSqlQuery.Properties; using WeifenLuo.WinFormsUI.Docking; namespace MiniSqlQuery { /// The query form. public partial class QueryForm : DockContent, IQueryEditor, IPrintableContent { /// The _host window. private readonly IHostWindow _hostWindow; /// The _services. private readonly IApplicationServices _services; /// The _settings. private readonly IApplicationSettings _settings; /// The _sync lock. private static object _syncLock = new object(); /// Stores the widths of the columns for this window. private Dictionary _columnSizes = new Dictionary(); /// When tru the grid is being resized on fill, used to avoid overriting column width values. private bool _resizingGrid; /// The _highlighting provider loaded. private bool _highlightingProviderLoaded; /// The _is dirty. private bool _isDirty; /// The _runner. private QueryRunner _runner; /// The status message for this window. private string _status = string.Empty; /// The row count for this window (tab dependent). private int? _rowCount; /// The _text find service. private ITextFindService _textFindService; private bool _cleaningTabs; TextArea _textArea; CodeCompletionWindow _completionWindow; /// Initializes a new instance of the class. public QueryForm() { InitializeComponent(); txtQuery.ContextMenuStrip = contextMenuStripQuery; LoadHighlightingProvider(); txtQuery.Document.DocumentChanged += DocumentDocumentChanged; _textArea = txtQuery.ActiveTextAreaControl.TextArea; contextMenuStripQuery.Items.Add(CommandControlBuilder.CreateToolStripMenuItem()); contextMenuStripQuery.Items.Add(CommandControlBuilder.CreateToolStripMenuItem()); editorContextMenuStrip.Items.Add(CommandControlBuilder.CreateToolStripMenuItem()); editorContextMenuStrip.Items.Add(CommandControlBuilder.CreateToolStripMenuItemSeparator()); editorContextMenuStrip.Items.Add(CommandControlBuilder.CreateToolStripMenuItem()); editorContextMenuStrip.Items.Add(CommandControlBuilder.CreateToolStripMenuItem()); editorContextMenuStrip.Items.Add(CommandControlBuilder.CreateToolStripMenuItem()); CommandControlBuilder.MonitorMenuItemsOpeningForEnabling(editorContextMenuStrip); } /// Initializes a new instance of the class. /// The services. /// The settings. /// The host window. public QueryForm(IApplicationServices services, IApplicationSettings settings, IHostWindow hostWindow) : this() { _services = services; _settings = settings; _hostWindow = hostWindow; var completionProvider = _services.Resolve(); if (completionProvider.Enabled) { _textArea.KeyEventHandler += completionProvider.KeyEventHandlerFired; } } public CodeCompletionWindow CodeCompletionWindow { get { return _completionWindow; } set { _completionWindow = value; if (_completionWindow != null) { _completionWindow.Closed += CompletionWindowClosed; } } } private void CompletionWindowClosed(object sender, EventArgs e) { if (_completionWindow != null) { _completionWindow.Closed -= CompletionWindowClosed; _completionWindow.Dispose(); _completionWindow = null; } } /// Gets or sets AllText. public string AllText { get { return txtQuery.Text; } set { txtQuery.Text = value; } } /// /// Gets a reference to the batch of queries. /// /// The query batch. public QueryBatch Batch { get { return _runner == null ? null : _runner.Batch; } } /// Gets a value indicating whether CanReplaceText. public bool CanReplaceText { get { return true; } } /// Gets or sets CursorColumn. public int CursorColumn { get { return txtQuery.ActiveTextAreaControl.Caret.Column; } set { txtQuery.ActiveTextAreaControl.Caret.Column = value; } } /// Gets or sets CursorLine. public int CursorLine { get { return txtQuery.ActiveTextAreaControl.Caret.Line; } set { txtQuery.ActiveTextAreaControl.Caret.Line = value; } } /// Gets CursorOffset. public int CursorOffset { get { return txtQuery.ActiveTextAreaControl.Caret.Offset; } } /// Gets EditorControl. public Control EditorControl { get { return txtQuery; } } /// Gets FileFilter. public string FileFilter { get { return "SQL Files (*.sql)|*.sql|All Files (*.*)|*.*"; } } /// Gets or sets FileName. public string FileName { get { return txtQuery.FileName; } set { txtQuery.FileName = value; SetTabTextByFilename(); } } /// Gets a value indicating whether IsBusy. public bool IsBusy { get; private set; } /// Gets or sets a value indicating whether IsDirty. public bool IsDirty { get { return _isDirty; } set { if (_isDirty != value) { _isDirty = value; SetTabTextByFilename(); } } } /// Gets PrintDocument. public PrintDocument PrintDocument { get { return txtQuery.PrintDocument; } } /// Gets SelectedText. public string SelectedText { get { return txtQuery.ActiveTextAreaControl.SelectionManager.SelectedText; } } /// Gets TextFindService. public ITextFindService TextFindService { get { if (_textFindService == null) { _textFindService = _services.Resolve(); } return _textFindService; } } /// Gets TotalLines. public int TotalLines { get { return txtQuery.Document.TotalNumberOfLines; } } /// The execute query. /// The sql. public void ExecuteQuery(string sql) { if (IsBusy) { _hostWindow.DisplaySimpleMessageBox(this, "Please wait for the current operation to complete.", "Busy"); return; } if (_settings.ConnectionDefinition == null) { _hostWindow.DisplaySimpleMessageBox(this, "Please select a connection.", "Select a Connection"); return; } lock (_syncLock) { IsBusy = true; } _runner = QueryRunner.Create(_settings.ProviderFactory, _settings.ConnectionDefinition.ConnectionString, _settings.EnableQueryBatching, _settings.CommandTimeout); UseWaitCursor = true; queryBackgroundWorker.RunWorkerAsync(sql); } /// The load highlighting provider. public void LoadHighlightingProvider() { if (_highlightingProviderLoaded) { return; } // see: http://wiki.sharpdevelop.net/Syntax%20highlighting.ashx string dir = Path.GetDirectoryName(GetType().Assembly.Location); FileSyntaxModeProvider fsmProvider = new FileSyntaxModeProvider(dir); HighlightingManager.Manager.AddSyntaxModeFileProvider(fsmProvider); // Attach to the text editor. txtQuery.SetHighlighting("SQL"); _highlightingProviderLoaded = true; } /// The clear selection. public void ClearSelection() { txtQuery.ActiveTextAreaControl.SelectionManager.ClearSelection(); } /// The highlight string. /// The offset. /// The length. public void HighlightString(int offset, int length) { if (offset < 0 || length < 1) { return; } int endPos = offset + length; txtQuery.ActiveTextAreaControl.SelectionManager.SetSelection( txtQuery.Document.OffsetToPosition(offset), txtQuery.Document.OffsetToPosition(endPos)); SetCursorByOffset(endPos); } /// The insert text. /// The text. public void InsertText(string text) { if (string.IsNullOrEmpty(text)) { return; } int offset = txtQuery.ActiveTextAreaControl.Caret.Offset; // if some text is selected we want to replace it if (txtQuery.ActiveTextAreaControl.SelectionManager.IsSelected(offset)) { offset = txtQuery.ActiveTextAreaControl.SelectionManager.SelectionCollection[0].Offset; txtQuery.ActiveTextAreaControl.SelectionManager.RemoveSelectedText(); } txtQuery.Document.Insert(offset, text); int newOffset = offset + text.Length; // new offset at end of inserted text // now reposition the caret if required to be after the inserted text if (CursorOffset != newOffset) { SetCursorByOffset(newOffset); } txtQuery.Focus(); } /// The load file. public void LoadFile() { txtQuery.LoadFile(FileName); IsDirty = false; } /// The save file. /// public void SaveFile() { if (FileName == null) { throw new InvalidOperationException("The 'FileName' cannot be null"); } txtQuery.SaveFile(FileName); IsDirty = false; } /// The set syntax. /// The name. public void SetSyntax(string name) { LoadHighlightingProvider(); txtQuery.SetHighlighting(name); } /// The find string. /// The value. /// The start index. /// The comparison type. /// The find string. public int FindString(string value, int startIndex, StringComparison comparisonType) { if (string.IsNullOrEmpty(value) || startIndex < 0) { return -1; } string text = AllText; int pos = text.IndexOf(value, startIndex, comparisonType); if (pos > -1) { ClearSelection(); HighlightString(pos, value.Length); } return pos; } /// The replace string. /// The value. /// The start index. /// The length. /// The replace string. public bool ReplaceString(string value, int startIndex, int length) { if (value == null || startIndex < 0 || length < 0) { return false; } if ((startIndex + length) > AllText.Length) { return false; } txtQuery.Document.Replace(startIndex, length, value); return true; } /// The set text find service. /// The text find service. public void SetTextFindService(ITextFindService textFindService) { // accept nulls infering a reset _textFindService = textFindService; } /// The set cursor by location. /// The line. /// The column. /// The set cursor by location. public bool SetCursorByLocation(int line, int column) { if (line > TotalLines) { return false; } txtQuery.ActiveTextAreaControl.Caret.Line = line; txtQuery.ActiveTextAreaControl.Caret.Column = column; return true; } /// The set cursor by offset. /// The offset. /// The set cursor by offset. public bool SetCursorByOffset(int offset) { if (offset >= 0) { txtQuery.ActiveTextAreaControl.Caret.Position = txtQuery.Document.OffsetToPosition(offset); return true; } return false; } /// The cancel task. public void CancelTask() { if (queryBackgroundWorker.IsBusy && _runner != null) { _runner.Cancel(); } } /// The execute task. public void ExecuteTask() { if (!string.IsNullOrEmpty(SelectedText)) { ExecuteQuery(SelectedText); } else { ExecuteQuery(AllText); } } /// The set status. /// The text. public void SetStatus(string text) { _status = text; UpdateHostStatus(); } public void SetRowCount(int? rows) { _rowCount = rows; UpdateHostStatus(); } /// The create default font. /// protected Font CreateDefaultFont() { return new Font("Courier New", 8.25F, FontStyle.Regular, GraphicsUnit.Point); } /// The update host status. protected void UpdateHostStatus() { _hostWindow.SetStatus(this, _status); _hostWindow.SetResultCount(this, _rowCount); } /// The create query complete message. /// The start. /// The end. /// The create query complete message. private static string CreateQueryCompleteMessage(DateTime start, DateTime end) { TimeSpan ts = end.Subtract(start); string msg = string.Format( "Query complete, {0:00}:{1:00}.{2:000}", ts.Minutes, ts.Seconds, ts.Milliseconds); return msg; } /// The add tables. private void AddTables() { ClearGridsAndTabs(); SetRowCount(null); if (Batch != null) { string nullText = _settings.NullText; int counter = 1; _resizingGrid = true; foreach (Query query in Batch.Queries) { DataSet ds = query.Result; if (ds != null) { foreach (DataTable dt in ds.Tables) { DataGridView grid = new DataGridView(); DataGridViewCellStyle cellStyle = new DataGridViewCellStyle(); grid.AllowUserToAddRows = false; grid.AllowUserToDeleteRows = false; grid.Dock = DockStyle.Fill; grid.Name = "gridResults_" + counter; grid.ReadOnly = true; grid.DataSource = dt; grid.DataError += GridDataError; grid.DefaultCellStyle = cellStyle; cellStyle.NullValue = nullText; cellStyle.Font = CreateDefaultFont(); grid.DataBindingComplete += GridDataBindingComplete; grid.Disposed += GridDisposed; grid.ColumnWidthChanged += OnColumnWidthChanged; TabPage tabPage = new TabPage(); tabPage.Controls.Add(grid); tabPage.Name = "tabPageResults_" + counter; tabPage.Padding = new Padding(3); tabPage.Text = string.Format("{0}/Table {1}", ds.DataSetName, counter); tabPage.UseVisualStyleBackColor = false; _resultsTabControl.TabPages.Add(tabPage); // create a reasonable default max width for columns int maxColWidth = Math.Max(grid.ClientSize.Width / 2, 100); // Autosize the columns then change the widths, gleaned from SO - http://stackoverflow.com/a/1031871/276563 grid.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.DisplayedCells); for (int i = 0; i < grid.Columns.Count; i++) { int columnWidth = grid.Columns[i].Width; grid.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.None; string headerText = grid.Columns[i].HeaderText; if (!string.IsNullOrEmpty(headerText) && _columnSizes.ContainsKey(headerText)) { // use the previous column size in case its been adjusted etc grid.Columns[i].Width = _columnSizes[headerText]; } else { // reset to a the smaller of the 2 sizes, this is mainly for the bigger text columns that throw the size out grid.Columns[i].Width = Math.Min(columnWidth, maxColWidth); if (!string.IsNullOrEmpty(headerText)) { _columnSizes[headerText] = grid.Columns[i].Width; } } } // set the row count for the first tab for now. if (counter == 1) { SetRowCount(dt.Rows.Count); } counter++; } } } if (!string.IsNullOrEmpty(Batch.Messages)) { RichTextBox rtf = new RichTextBox(); rtf.Font = CreateDefaultFont(); rtf.Dock = DockStyle.Fill; rtf.ScrollBars = RichTextBoxScrollBars.ForcedBoth; rtf.Text = Batch.Messages; TabPage tabPage = new TabPage(); tabPage.Controls.Add(rtf); tabPage.Name = "tabPageResults_Messages"; tabPage.Padding = new Padding(3); tabPage.Dock = DockStyle.Fill; tabPage.Text = Resources.Messages; tabPage.UseVisualStyleBackColor = false; _resultsTabControl.TabPages.Add(tabPage); } _resizingGrid = false; } } public void OnColumnWidthChanged(object sender, DataGridViewColumnEventArgs e) { if (_resizingGrid) { return; } string headerText = e.Column.HeaderText; if (!string.IsNullOrEmpty(headerText)) { _columnSizes[headerText] = e.Column.Width; } } /// Iterate backweards through list of tabs disposing grid and removing the tab page. private void ClearGridsAndTabs() { try { _cleaningTabs = true; for (int i = _resultsTabControl.TabPages.Count - 1; i >= 0; i--) { TabPage tabPage = _resultsTabControl.TabPages[i]; if (tabPage.Controls.Count > 0) { tabPage.Controls[0].Dispose(); // dispose grid } _resultsTabControl.TabPages.Remove(tabPage); tabPage.Dispose(); } } finally { _cleaningTabs = false; } } private void SetResultCountOnTabSelectedIndexChanged(object sender, EventArgs e) { if (_cleaningTabs) { return; } // get the tab var tabPage = _resultsTabControl.TabPages[_resultsTabControl.SelectedIndex]; // get the grid control, should be first var dataGridView = tabPage.Controls[0] as DataGridView; // default to blank row count int? rows = null; if (dataGridView != null) { var data = dataGridView.DataSource as DataTable; if (data != null) { rows = data.Rows.Count; } } _rowCount = rows; UpdateHostStatus(); } /// The document document changed. /// The sender. /// The e. private void DocumentDocumentChanged(object sender, DocumentEventArgs e) { IsDirty = true; } /// Change the format style of date time columns. This has to be done post-bind. /// /// private void GridDataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e) { DataGridView grid = sender as DataGridView; if (grid == null) { return; } DataTable dt = grid.DataSource as DataTable; if (dt == null) { return; } string nullText = _settings.NullText; string dateTimeFormat = _settings.DateTimeFormat; for (int i = 0; i < dt.Columns.Count; i++) { if (dt.Columns[i].DataType == typeof(DateTime)) { DataGridViewCellStyle dateCellStyle = new DataGridViewCellStyle(); dateCellStyle.NullValue = nullText; dateCellStyle.Format = dateTimeFormat; grid.Columns[i].DefaultCellStyle = dateCellStyle; } } } /// The grid data error. /// The sender. /// The e. private void GridDataError(object sender, DataGridViewDataErrorEventArgs e) { e.ThrowException = false; } /// Clean up event subscriptions. /// /// private void GridDisposed(object sender, EventArgs e) { DataGridView grid = sender as DataGridView; if (grid == null) { return; } grid.DataBindingComplete -= GridDataBindingComplete; grid.Disposed -= GridDisposed; grid.ColumnWidthChanged -= OnColumnWidthChanged; } /// The query form_ activated. /// The sender. /// The e. private void QueryForm_Activated(object sender, EventArgs e) { UpdateHostStatus(); } /// The query form_ deactivate. /// The sender. /// The e. private void QueryForm_Deactivate(object sender, EventArgs e) { _hostWindow.SetStatus(this, string.Empty); } /// The query form_ form closing. /// The sender. /// The e. private void QueryForm_FormClosing(object sender, FormClosingEventArgs e) { if (_isDirty) { DialogResult saveFile = _hostWindow.DisplayMessageBox( this, "Contents changed, do you want to save the file?\r\n" + TabText, "Save Changes?", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1, 0, null, null); if (saveFile == DialogResult.Cancel) { e.Cancel = true; } else if (saveFile == DialogResult.Yes) { CommandManager.GetCommandInstance().Execute(); } } } /// The query form_ load. /// The sender. /// The e. private void QueryForm_Load(object sender, EventArgs e) { } /// The runner batch progress. /// The sender. /// The e. private void RunnerBatchProgress(object sender, BatchProgressEventArgs e) { // push the progress % through to the background worker decimal i = Math.Max(1, e.Index); decimal count = Math.Max(1, e.Count); queryBackgroundWorker.ReportProgress(Convert.ToInt32(i / count * 100m)); } /// The set tab text by filename. private void SetTabTextByFilename() { string dirty = string.Empty; string text = "Untitled"; string tabtext; if (_isDirty) { dirty = " *"; } if (txtQuery.FileName != null) { text = FileName; tabtext = Path.GetFileName(FileName); } else { text += _settings.GetUntitledDocumentCounter(); tabtext = text; } TabText = tabtext + dirty; ToolTipText = text + dirty; } /// The copy tool strip menu item_ click. /// The sender. /// The e. private void copyToolStripMenuItem_Click(object sender, EventArgs e) { CopyForm win = null; try { DataGridView grid = (DataGridView)_resultsTabControl.SelectedTab.Controls[0]; if (grid.SelectedCells.Count == 0) { return; } win = new CopyForm(); if (win.ShowDialog() == DialogResult.Cancel) { return; } SortedList headers = new SortedList(); SortedList rows = new SortedList(); string delimiter = win.Delimiter; string line = string.Empty; for (int i = 0; i < grid.SelectedCells.Count; i++) { DataGridViewCell cell = grid.SelectedCells[i]; DataGridViewColumn col = cell.OwningColumn; if (!headers.ContainsKey(col.Index)) { headers.Add(col.Index, col.Name); } if (!rows.ContainsKey(cell.RowIndex)) { rows.Add(cell.RowIndex, cell.RowIndex); } } if (win.IncludeHeaders) { for (int i = 0; i < headers.Count; i++) { line += (string)headers.GetByIndex(i); if (i != headers.Count) { line += delimiter; } } line += "\r\n"; } for (int i = 0; i < rows.Count; i++) { DataGridViewRow row = grid.Rows[(int)rows.GetKey(i)]; DataGridViewCellCollection cells = row.Cells; for (int j = 0; j < headers.Count; j++) { DataGridViewCell cell = cells[(int)headers.GetKey(j)]; if (cell.Selected) { line += cell.Value; } if (j != (headers.Count - 1)) { line += delimiter; } } line += "\r\n"; } if (!string.IsNullOrEmpty(line)) { Clipboard.Clear(); Clipboard.SetText(line); _hostWindow.SetStatus(this, "Selected data has been copied to your clipboard"); } } finally { if (win != null) { win.Dispose(); } } } /// The query background worker_ do work. /// The sender. /// The e. private void queryBackgroundWorker_DoWork(object sender, DoWorkEventArgs e) { string sql = (string)e.Argument; _runner.BatchProgress += RunnerBatchProgress; _runner.ExecuteQuery(sql); } /// The query background worker_ progress changed. /// The sender. /// The e. private void queryBackgroundWorker_ProgressChanged(object sender, ProgressChangedEventArgs e) { SetStatus(string.Format("Processing batch {0}%...", e.ProgressPercentage)); } /// The query background worker_ run worker completed. /// The sender. /// The e. private void queryBackgroundWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e) { try { _runner.BatchProgress -= RunnerBatchProgress; if (e.Error != null) { // todo: improve! _hostWindow.DisplaySimpleMessageBox(this, e.Error.Message, "Error"); SetStatus(e.Error.Message); } else { _hostWindow.SetPointerState(Cursors.Default); string message = CreateQueryCompleteMessage(_runner.Batch.StartTime, _runner.Batch.EndTime); if (_runner.Exception != null) { message = "ERROR - " + message; } AddTables(); SetStatus(message); txtQuery.Focus(); } } finally { UseWaitCursor = false; lock (_syncLock) { IsBusy = false; } } } /// The select all tool strip menu item_ click. /// The sender. /// The e. private void selectAllToolStripMenuItem_Click(object sender, EventArgs e) { DataGridView grid = (DataGridView)_resultsTabControl.SelectedTab.Controls[0]; grid.SelectAll(); } } }