A Visual SQL Query Designer

using System;
using System.IO;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using System.Data.OleDb;
using Microsoft.Win32;

namespace QueryDesigner
{
    public partial class Form1 : Form
    {
        //------------------------------------------------------------------------------
        #region ** fields

        // current connection string and corresponding schema
        string _connString;
        OleDbSchema _schema;

        // max number of records shown on the preview dialog
        const int MAX_PREVIEW_RECORDS = 5000;

        #endregion

        //------------------------------------------------------------------------------
        #region ** ctor

        public Form1()
        {
            InitializeComponent();

            // make combo owner-drawn
            var cmb = _cmbConnString.ComboBox;
            cmb.DrawMode = DrawMode.OwnerDrawFixed;
            cmb.DrawItem += cmb_DrawItem;

            // load recently used connection strings
            var mru = Properties.Settings.Default.RecentConnections;
            if (mru != null)
            {
                foreach (string connString in mru)
                {
                    _cmbConnString.Items.Add(connString);
                }
            }

            // autosize toolstrip
            _toolStrip.Layout += _toolStrip_Layout;
            _tab.SelectedIndexChanged += _tab_SelectedIndexChanged;
        }

        #endregion

        //------------------------------------------------------------------------------
        #region ** object model

        /// <summary>
        /// Gets or sets the connection string.
        /// </summary>
        public string ConnectionString
        {
            get { return _connString; }
            set
            {
                if (value != ConnectionString)
                {
                    // this may take a while
                    Cursor = Cursors.WaitCursor;

                    // look for item in the list
                    var items = _cmbConnString.Items;
                    var index = items.IndexOf(value);

                    // get schema for the new connection string
                    _schema = OleDbSchema.GetSchema(value);

                    // handle good connection strings
                    if (_schema != null)
                    {
                        // add good values to the list
                        if (index < 0)
                        {
                            items.Insert(0, value);
                        }
                        else if (index > 0)
                        {
                            items.RemoveAt(index);
                            items.Insert(0, value);
                        }

                        // trim list
                        while (items.Count > _cmbConnString.MaxDropDownItems)
                        {
                            items.RemoveAt(items.Count - 1);
                        }
                    }
                    else // handle bad connection strings
                    {
                        // remove from list
                        if (index >= 0)
                        {
                            items.RemoveAt(index);
                        }

                        // do not store bad values
                        value = string.Empty;
                    }

                    // save new value
                    _connString = value;

                    // show new value in combo box and table tree
                    _cmbConnString.Text = value;
                    UpdateTableTree();

                    // new connection, clear SQL
                    _txtSql.Text = string.Empty;

                    // update ui
                    UpdateUI();

                    // done
                    Cursor = null;
                }
            }
        }
        /// <summary>
        /// Gets a SQL statement that corresponds to the element that
        /// is currently selected (table, view, stored procedure, or
        /// explicit sql statement).
        /// </summary>
        public string SelectStatement
        {
            get
            {
                // table/view/sproc
                if (_tab.SelectedTab == _pgTables)
                {
                    var nd = _treeTables.SelectedNode;
                    return nd == null || nd.Tag == null || _schema == null
                        ? string.Empty
                        : OleDbSchema.GetSelectStatement(nd.Tag as DataTable);
                }
                else // explicit sql statement
                {
                    return _txtSql.Text;
                }
            }
        }

        #endregion

        //------------------------------------------------------------------------------
        #region ** event handlers

        // adjust combobox size to fill toolstrip
        void _toolStrip_Layout(object sender, LayoutEventArgs e)
        {
            int width = _toolStrip.ClientRectangle.Width - 15;
            foreach (ToolStripItem item in _toolStrip.Items)
            {
                if (item != _cmbConnString && item.Visible)
                {
                    width -= item.Width - item.Margin.Left - item.Margin.Right;
                }
            }
            width = Math.Max(100, Math.Min(500, width));
            _cmbConnString.Width = width;
        }

        // form closing: save recently used connection strings
        protected override void OnFormClosing(FormClosingEventArgs e)
        {
            var mru = new System.Collections.Specialized.StringCollection();
            foreach (string item in _cmbConnString.Items)
            {
                mru.Add(item);
            }
            Properties.Settings.Default.RecentConnections = mru;
            Properties.Settings.Default.Save();
            base.OnFormClosing(e);
        }

        // validating combo: apply new connection string
        void _cmbConnString_Validating(object sender, CancelEventArgs e)
        {
            ConnectionString = _cmbConnString.Text;
        }

        // new connection picked: apply new connection string
        void _cmbConnString_SelectedIndexChanged(object sender, EventArgs e)
        {
            ConnectionString = _cmbConnString.Text;
        }

        // user pressed enter: apply new connection string
        void _cmbConnString_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar == 13)
            {
                e.Handled = true;
                ConnectionString = _cmbConnString.Text;
            }
        }

        // pick a new connection
        void _btnConnPicker_Click(object sender, EventArgs e)
        {
            // release mouse capture to avoid wait cursor
            _toolStrip.Capture = false;

            // get starting connection string
            // (if empty or no provider, start with SQL source as default)
            string connString = _cmbConnString.Text;
            if (string.IsNullOrEmpty(connString) || connString.IndexOf("provider=", StringComparison.OrdinalIgnoreCase) < 0)
            {
                connString = "Provider=SQLOLEDB.1;";
            }

            // let user change it
            ConnectionString = OleDbConnString.EditConnectionString(this, connString);
        }

        // trim items in combo using ellipsis (they're very long)
        void cmb_DrawItem(object sender, DrawItemEventArgs e)
        {
            var fmt = new StringFormat();
            fmt.LineAlignment = StringAlignment.Center;
            fmt.Trimming = StringTrimming.EllipsisPath;

            var text = (string)_cmbConnString.Items[e.Index];
            text = OleDbConnString.TrimConnectionString(text);

            var brush = (e.State & DrawItemState.Selected) != 0
                ? SystemBrushes.HighlightText
                : SystemBrushes.WindowText;

            e.DrawBackground();
            e.Graphics.DrawString(text, _cmbConnString.Font, brush, e.Bounds, fmt);
            e.DrawFocusRectangle();
        }

        // preview data when user hits enter on the tree
        void _treeTables_KeyPress(object sender, KeyPressEventArgs e)
        {
            if (e.KeyChar == 13)
            {
                e.Handled = true; // << suppress beep
                PreviewData();
            }
        }

        // preview data when user double-clicks a node
        void _treeTables_DoubleClick(object sender, EventArgs e)
        {
            var nd = _treeTables.SelectedNode;
            if (nd != null && nd.Tag is DataTable)
            {
                PreviewData();
            }
        }

        // preview data when user clicks the preview button
        void _btnPreviewData_Click(object sender, EventArgs e)
        {
            PreviewData();
        }

        // enable/disable buttons when a node or tab is selected
        void _treeTables_AfterSelect(object sender, TreeViewEventArgs e)
        {
            UpdateUI();
        }
        void _tab_SelectedIndexChanged(object sender, EventArgs e)
        {
            UpdateUI();
        }

        // invoke SQL builder
        void _btnSqlBuilder_Click(object sender, EventArgs e)
        {
            using (var dlg = new QueryDesignerDialog())
            {
                dlg.Font = this.Font;
                dlg.ConnectionString = ConnectionString;
                // dlg.SelectStatement = SelectStatement; // NOP (for now)
                if (dlg.ShowDialog(this) == DialogResult.OK)
                {
                    _txtSql.Text = dlg.SelectStatement;
                    _tab.SelectedTab = _pgSql;
                    UpdateUI();
                }
            }
        }

        // close the form
        void _btnOK_Click(object sender, EventArgs e)
        {
            DialogResult = DialogResult.OK;
            Close();
        }
        void _btnCancel_Click(object sender, EventArgs e)
        {
            DialogResult = DialogResult.Cancel;
            Close();
        }

        #endregion

        //------------------------------------------------------------------------------
        #region ** implementation

        // issue a warning
        void Warning(string format, params object[] args)
        {
            string msg = string.Format(format, args);
            MessageBox.Show(this, msg, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Warning);
        }

        // update table tree to reflect new connection string
        void UpdateTableTree()
        {
            // initialize table tree
            TreeNodeCollection nodes = _treeTables.Nodes;
            nodes.Clear();
            var ndTables = new TreeNode(Properties.Resources.Tables, 0, 0);
            var ndViews = new TreeNode(Properties.Resources.Views, 1, 1);
            var ndProcs = new TreeNode(Properties.Resources.StoredProcedures, 2, 2);

            // populate using current schema
            if (_schema != null)
            {
                // populate the tree
                _treeTables.BeginUpdate();
                foreach (DataTable dt in _schema.Tables)
                {
                    // create new node, save table in tag property
                    var node = new TreeNode(dt.TableName);
                    node.Tag = dt;

                    // add new node to appropriate parent
                    switch (OleDbSchema.GetTableType(dt))
                    {
                        case TableType.Table:
                            ndTables.Nodes.Add(node);
                            node.ImageIndex = node.SelectedImageIndex = 0;
                            break;
                        case TableType.View:
                            ndViews.Nodes.Add(node);
                            node.ImageIndex = node.SelectedImageIndex = 1;
                            break;
                        case TableType.Procedure:
                            ndProcs.Nodes.Add(node);
                            node.ImageIndex = node.SelectedImageIndex = 2;
                            break;
                    }
                }

                // add non-empty nodes to tree
                foreach (TreeNode nd in new TreeNode[] { ndTables, ndViews, ndProcs })
                {
                    if (nd.Nodes.Count > 0)
                    {
                        nd.Text = string.Format("{0} ({1})", nd.Text, nd.Nodes.Count);
                        nodes.Add(nd);
                    }
                }

                // expand tables node
                ndTables.Expand();

                // done
                _treeTables.EndUpdate();
                _tab.SelectedIndex = 0;
            }
        }

        // preview data for currently selected node
        void PreviewData()
        {
            // make sure we have a select statement
            var sql = SelectStatement;
            if (string.IsNullOrEmpty(sql))
            {
                return;
            }

            // create table to load with data and display
            var dt = new DataTable("Query");

            // if a table/view is selected, get table name and parameters
            if (_tab.SelectedTab == _pgTables)
            {
                // get table/view name
                var table = _treeTables.SelectedNode.Tag as DataTable;
                dt.TableName = table.TableName;

                // get view parameters if necessary
                var parms = OleDbSchema.GetTableParameters(table);
                if (parms != null && parms.Count > 0)
                {
                    var dlg = new ParametersDialog(parms);
                    dlg.Font = Font;
                    if (dlg.ShowDialog(this) != DialogResult.OK)
                    {
                        return;
                    }
                }
            }

            // get data
            try
            {
                using (var da = new OleDbDataAdapter(SelectStatement, ConnectionString))
                {
                    // get data
                    da.Fill(0, MAX_PREVIEW_RECORDS, dt);

                    // show the data
                    using (var dlg = new DataPreviewDialog(dt, Font, Size))
                    {
                        dlg.ShowDialog(this);
                    }
                }
            }
            catch (Exception x)
            {
                Warning(Properties.Resources.ErrGettingData, x.Message);
            }
        }

        // enable/disable buttons
        void UpdateUI()
        {
            // enable sql builder button if we have some tables
            _btnSqlBuilder.Enabled = _treeTables.Nodes.Count > 0;

            // enable data preview if we a select statement
            _btnPreviewData.Enabled = !string.IsNullOrEmpty(SelectStatement);
        }

        #endregion
    }
}