querypanecontrol.cs

sqlce查询分析器

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;
using System.IO;

namespace CEQuery
{
    public partial class QueryPaneControl : UserControl
    {
        public string ConnectionString { get; set; }
        public string TableSelectQuery { get; set; }
        public string TableInsertQuery { get; set; }
        public bool UpdatablePane { get; set; }

        private SqlCeConnection connection;
        private SqlCeCommand cmdTable;
        private SqlCeDataAdapter daTable;
        private DataSet dsResultTable;

        public event StatusEventHandler StatusEvent;
        protected virtual void OnStatusEvent(StatusEventArgs e)
        {
            StatusEvent(this, e);
        }

        private string[] keyWords = { "INSERT", "UPDATE", "DELETE", "CREATE", "ALTER", "DROP", "SELECT", "FROM", "WHERE", "GROUP", "ORDER", "BY", "INTO", "HAVING", "VALUES", "SET", "INNER", "LEFT", "RIGHT", "OUTER", "CROSS", "JOIN", "ON", "UNION", "IN", "AS", "AND", "OR", "DISTINCT",
                                        "TABLE","DEFAULT","NULL","NOT","CONSTRAINT","REFERERENCES","UNIQUE","INDEX","PRIMARY","KEY","CLUSTERED","NONCLUSTERED","ADD","FOREIGN","ASC","DESC","REFERENCES" };
        
        public QueryPaneControl()
        {
            InitializeComponent();
            UpdatablePane = false;
        }
        
        private void QueryPaneControl_Load(object sender, EventArgs e)
        {
            foreach (string keyWord in keyWords)
            {
                rtQuery.Settings.Keywords.Add(keyWord);
            }
            // Set the colors that will be used.
            rtQuery.Settings.KeywordColor = Color.Blue;
            rtQuery.Settings.CommentColor = Color.Green;
            rtQuery.Settings.StringColor = Color.Gray;
            rtQuery.Settings.IntegerColor = Color.Red;

            // Let's not process strings and integers.
            rtQuery.Settings.EnableStrings = false;
            rtQuery.Settings.EnableIntegers = false;
            rtQuery.Settings.Comment = "--";
            rtQuery.Settings.CommentColor = Color.Green;
            rtQuery.Settings.EnableComments = true;
            // Let's make the settings we just set valid by compiling
            // the keywords to a regular expression.
            rtQuery.CompileKeywords();

            //// Load a file and update the syntax highlighting.
            //rtQuery.ProcessAllLines();            

            connection = new SqlCeConnection(ConnectionString);
            if (!string.IsNullOrEmpty(this.TableInsertQuery))
            {
                rtQuery.Text = this.TableInsertQuery;
            }
            if (!string.IsNullOrEmpty(this.TableSelectQuery))
            {
                rtQuery.Text = this.TableSelectQuery;
                rtQuery.Enabled = false;
                PrepareManipulationBed();
            }

            this.rtQuery.DragDrop += new DragEventHandler(rtQuery_DragDrop);
            //this.rtQuery.KeyPress += new KeyPressEventHandler(rtQuery_KeyPress);
            //this.rtQuery.KeyDown +=new KeyEventHandler(rtQuery_KeyDown);
            
        }
        private void rtQuery_DragDrop(object sender, DragEventArgs e)
        {
            rtQuery.SelectedText = "[" + ((TreeNode)e.Data.GetData(typeof(TreeNode))).Tag.ToString() + "]"; 
        }
        private void rtQuery_KeyPress(object sender, KeyPressEventArgs e)
        {
            //if (Convert.ToInt32(e.KeyChar) == 22)
            //{
            //    rtQuery.Tag = "ProcessAll";
            //}

        }        
        private void rtQuery_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.F5)
            {
                RunQuery();
            }
            if (e.Control && e.KeyCode == Keys.V)
            {
                rtQuery.Tag = "ProcessAll";
            }
            if (e.Control && e.KeyCode == Keys.Z)
            {
                rtQuery.Tag = "UnDo";
            }
            if (e.Control && e.KeyCode == Keys.Y)
            {
                if (rtQuery.CanRedo)
                {
                    rtQuery.Redo();                    
                }
            }
        }        
        private void updateToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                daTable.Update(dsResultTable);
                PrepareManipulationBed();
                MessageBox.Show("Table has been updated successfully.");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.InnerException != null ? ex.InnerException.Message : ex.Message);
            }
        }
        private void deleteToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                if (dgvQuery.SelectedRows.Count > 0)
                {
                    foreach (DataGridViewRow row in dgvQuery.SelectedRows)
                    {
                        if (!row.IsNewRow)
                            dgvQuery.Rows.Remove(row);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.InnerException != null ? ex.InnerException.Message : ex.Message);
            }
        }
        private void dgvQuery_MouseUp(object sender, MouseEventArgs e)
        {
            if (UpdatablePane)
            {
                if (e.Button == MouseButtons.Right)
                {
                    if (dgvQuery.RowCount > 0)
                    {
                        ctxGridMenu.Visible = true;
                    }
                    else
                    {
                        ctxGridMenu.Visible = false;
                    }
                }
            }
            else
            {
                ctxGridMenu.Visible = false;
            }
        }
        private void dgvQuery_DataError(object sender, DataGridViewDataErrorEventArgs e)
        {
            MessageBox.Show(e.Exception.InnerException != null ? e.Exception.InnerException.Message : e.Exception.Message);
        }

        public void RunQuery()
        {
            try
            {
                if (!rtQuery.Enabled)
                {
                    return;
                }
                if (ConnectionString != string.Empty)
                {
                    SqlCeCommand cmd = new SqlCeCommand();
                    cmd.Connection = connection;
                    if (connection.State != ConnectionState.Open)
                    {
                        connection.Open();
                    }
                    if (rtQuery.SelectedText.Length > 0)
                        cmd.CommandText = rtQuery.SelectedText;
                    else
                        cmd.CommandText = rtQuery.Text.Trim();

                    if (cmd.CommandText.Trim().ToUpper().StartsWith("INSERT") ||
                        cmd.CommandText.Trim().ToUpper().StartsWith("UPDATE") ||
                        cmd.CommandText.Trim().ToUpper().StartsWith("DELETE") ||
                        cmd.CommandText.Trim().ToUpper().StartsWith("CREATE") ||
                        cmd.CommandText.Trim().ToUpper().StartsWith("ALTER") ||
                        cmd.CommandText.Trim().ToUpper().StartsWith("DROP"))
                    {
                        int rowsEffected = cmd.ExecuteNonQuery();
                        MessageBox.Show("Query has been executed successfully.");
                        StatusEventArgs statusArgs = new StatusEventArgs();
                        statusArgs.Message = string.Format("{0} Records effected.", rowsEffected.ToString());
                        OnStatusEvent(statusArgs);
                    }
                    else
                    {
                        dgvQuery.DataSource = null;
                        SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
                        DataSet dsResult = new DataSet();
                        da.Fill(dsResult);
                        dgvQuery.DataSource = dsResult.Tables[0];
                        dgvQuery.ReadOnly = true;
                        //foreach (DataGridViewColumn col in dgvQuery.Columns)
                        //{
                        //    if (col.CellType == typeof(DataGridViewImageCell))
                        //    {

                        //        col.Visible = false;

                        //        DataGridViewColumn colAddl = new DataGridViewColumn(dgvQuery.Columns[0].CellTemplate);
                        //        colAddl.HeaderText = col.HeaderText;

                        //        colAddl.DataPropertyName = col.DataPropertyName;
                        //        dgvQuery.Columns.Add(colAddl);
                        //    }
                        //}

                        for (int counter = dgvQuery.Columns.Count - 1; counter >= 0; counter--)
                        {
                            if (dgvQuery.Columns[counter].CellType == typeof(DataGridViewImageCell))
                            {
                                DataGridViewTextBoxColumn colAddl = new DataGridViewTextBoxColumn();
                                colAddl.HeaderText = dgvQuery.Columns[counter].HeaderText;
                                colAddl.DataPropertyName = dgvQuery.Columns[counter].DataPropertyName;
                                dgvQuery.Columns.RemoveAt(counter);
                                dgvQuery.Columns.Insert(counter, colAddl);
                            }
                        }
                        StatusEventArgs statusArgs = new StatusEventArgs();
                        statusArgs.Message = string.Format("{0} Records found.", dsResult.Tables[0].Rows.Count.ToString());
                        OnStatusEvent(statusArgs);
                    }
                    connection.Close();
                }
                else
                {
                    MessageBox.Show("Select a DB first");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        public bool SaveQuery()
        {
            try
            {
                SaveFileDialog saveFileDialog = new SaveFileDialog();
                saveFileDialog.Title = "Save Query";
                saveFileDialog.Filter = "SQL Files (*.sql)|*.sql";
                saveFileDialog.RestoreDirectory = true;
                if (saveFileDialog.ShowDialog() == DialogResult.OK)
                {
                    File.WriteAllText(saveFileDialog.FileName, rtQuery.Text.Replace("\n", Environment.NewLine));
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }
        private void PrepareManipulationBed()
        {
            cmdTable = new SqlCeCommand();
            cmdTable.Connection = connection;
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            cmdTable.CommandText = this.TableSelectQuery;

            daTable = new SqlCeDataAdapter(cmdTable);
            dsResultTable = new DataSet();
            daTable.Fill(dsResultTable);
            dgvQuery.DataSource = dsResultTable.Tables[0];

            //foreach (DataGridViewColumn col in dgvQuery.Columns)
            //{
            //    if (col.CellType == typeof(DataGridViewImageCell))
            //    {
            //        col.Visible = false;
            //    }
            //}
            for (int counter = dgvQuery.Columns.Count - 1; counter >= 0; counter--)
            {
                if (dgvQuery.Columns[counter].CellType == typeof(DataGridViewImageCell))
                {
                    DataGridViewTextBoxColumn colAddl = new DataGridViewTextBoxColumn();
                    colAddl.HeaderText = dgvQuery.Columns[counter].HeaderText;
                    colAddl.DataPropertyName = dgvQuery.Columns[counter].DataPropertyName;
                    dgvQuery.Columns.RemoveAt(counter);
                    dgvQuery.Columns.Insert(counter, colAddl);
                }
            }
            dgvQuery.AllowUserToAddRows = true;
            dgvQuery.AllowUserToDeleteRows = true;
            dgvQuery.ReadOnly = false;

            SqlCeCommandBuilder cmdbuilder = new SqlCeCommandBuilder(daTable);

            StatusEventArgs statusArgs = new StatusEventArgs();
            statusArgs.Message = string.Format("{0} Records found.", dsResultTable.Tables[0].Rows.Count.ToString());
            OnStatusEvent(statusArgs);
        }
    }

    public delegate void StatusEventHandler(object sender, StatusEventArgs e);
    public class StatusEventArgs : EventArgs
    {
        public string Message { get; set; }
    }
}