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