formquery.cs

sqlce查询分析器

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

namespace CEQuery
{
    public partial class FormQuery : Form
    {
        //private string ConnectionString = string.Empty;
        public string ConnectionString { get; set; }
        private int tabCount = 1;
        public FormQuery()
        {
            InitializeComponent();            
        }              
        private void FormQuery_Load(object sender, EventArgs e)
        {
            this.tvTables.ItemDrag += new ItemDragEventHandler(tvTables_ItemDrag);

            AddQueryTab();
            BuildDBTree();

            if (!string.IsNullOrEmpty(ConnectionString))
            {
                string dbFile = ConnectionString.Split('=').Length > 1 ? ConnectionString.Split('=')[1] : string.Empty;
                this.Text = string.IsNullOrEmpty(dbFile) ? string.Empty : dbFile.Substring(0, dbFile.LastIndexOf(';'));
            }
        }        
        private void tvTables_ItemDrag(object sender, ItemDragEventArgs e)
        {
            DoDragDrop(e.Item, DragDropEffects.All);

        }
        private void tvTables_MouseUp(object sender, MouseEventArgs e)
        {
            if (e.Button == MouseButtons.Right)
            {
                tvTables.SelectedNode = tvTables.GetNodeAt(e.X, e.Y);
                if (tvTables.SelectedNode != null && tvTables.SelectedNode.Nodes.Count > 0)
                {
                    //ctxDataMenu.Visible = true;
                    insertToolStripMenuItem.Enabled = true;
                    generateScriptToolStripMenuItem.Enabled = true;
                    selectToolStripMenuItem.Enabled = true;
                }
                else
                {
                    //ctxDataMenu.Visible = false;
                    insertToolStripMenuItem.Enabled = false;
                    generateScriptToolStripMenuItem.Enabled = false;
                    selectToolStripMenuItem.Enabled = false;
                }
            }
        }
        private void selectToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //RunSelectTable(tvTables.SelectedNode.Text);
            AddDataManipulationTab(tvTables.SelectedNode.Text);
        }
        private void insertToolStripMenuItem_Click(object sender, EventArgs e)
        {
            DataManipulation dm = new DataManipulation();
            dm.TableName = tvTables.SelectedNode.Text;
            dm.Operation = DataOperation.INSERT;
            dm.ConnectionString = ConnectionString;
            if (dm.ShowDialog() == DialogResult.OK)
            {
                AddInsertQueryTab(dm.Query);
            }
        }        
        private void FormQuery_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.F6)
            {
                //this.tabQuery.TabPages.Add(new QueryTab());
                AddQueryTab();
            }
        }
        public void AddQueryTab()
        {
            string tabCaption = "Query " + tabCount.ToString();
            tabCount++;
            QueryPaneControl ctrl = new QueryPaneControl();
            ctrl.StatusEvent += new StatusEventHandler(ctrl_StatusEvent);
            ctrl.ConnectionString = this.ConnectionString;
            ctrl.Dock = DockStyle.Fill;
            TabPages.TabPage page = new TabPages.TabPage(tabCaption,ctrl,string.Empty);
            tabQuery.Add(page);
            tabQuery.CurrentPage = page;            
        }        
        public void AddInsertQueryTab(string insertQuery)
        {
            string tabCaption = "Query " + tabCount.ToString();
            tabCount++;
            QueryPaneControl ctrl = new QueryPaneControl();
            ctrl.StatusEvent += new StatusEventHandler(ctrl_StatusEvent);
            ctrl.ConnectionString = this.ConnectionString;
            ctrl.TableInsertQuery = insertQuery;
            ctrl.Dock = DockStyle.Fill;
            TabPages.TabPage page = new TabPages.TabPage(tabCaption, ctrl, string.Empty);
            tabQuery.Add(page);
            tabQuery.CurrentPage = page;            
        }
        private void AddDataManipulationTab(string tableName)
        {
            string tabCaption = "Query " + tabCount.ToString();
            tabCount++;
            QueryPaneControl ctrl = new QueryPaneControl();
            ctrl.StatusEvent += new StatusEventHandler(ctrl_StatusEvent);
            ctrl.ConnectionString = this.ConnectionString;
            ctrl.TableSelectQuery = "SELECT * FROM [" + tableName + "]";
            ctrl.UpdatablePane = true;
            ctrl.Dock = DockStyle.Fill;
            TabPages.TabPage page = new TabPages.TabPage(tabCaption, ctrl, string.Empty);
            tabQuery.Add(page);
            tabQuery.CurrentPage = page;            
        }
        public void RunActiveQuery()
        {
            if (tabQuery.Count > 0)
            {
                ((QueryPaneControl)tabQuery.CurrentPage.Control).RunQuery();
            }
        }
        public bool SaveActiveQuery()
        {
            if (tabQuery.Count > 0)
            {
                return ((QueryPaneControl)tabQuery.CurrentPage.Control).SaveQuery();
            }
            return false;
        }
        private void BuildDBTree()
        {
            tvTables.Nodes.Clear();
            if (!string.IsNullOrEmpty(ConnectionString))
            {
                using (SqlCeConnection connection = new SqlCeConnection(ConnectionString))
                {

                    SqlCeCommand cmd = new SqlCeCommand();
                    cmd.Connection = connection;
                    connection.Open();
                    cmd.CommandText = "SELECT table_name FROM information_schema.tables";
                    SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
                    DataSet dsTable = new DataSet();
                    da.Fill(dsTable);

                    cmd.CommandText = "SELECT Table_name, Column_name, Data_type,Character_maximum_length FROM information_schema.columns";
                    da = new SqlCeDataAdapter(cmd);
                    DataSet dsColumns = new DataSet();
                    da.Fill(dsColumns);

                    cmd.CommandText = "SELECT u.TABLE_NAME,u.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS c INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS u ON c.CONSTRAINT_NAME = u.CONSTRAINT_NAME WHERE c.CONSTRAINT_TYPE = 'PRIMARY KEY'";
                    da = new SqlCeDataAdapter(cmd);
                    DataSet dsPKColumns = new DataSet();
                    da.Fill(dsPKColumns);

                    tvTables.Nodes.Clear();
                    TreeNode node, childNode;
                    foreach (DataRow dr in dsTable.Tables[0].Rows)
                    {
                        node = new TreeNode(dr["table_name"].ToString());
                        node.Tag = dr["table_name"].ToString();
                        node.ImageIndex = 0;
                        node.SelectedImageIndex = 0;
                        DataRow[] columns = dsColumns.Tables[0].Select("Table_name = '" + dr["table_name"].ToString() + "'");
                        foreach (DataRow col in columns)
                        {
                            childNode = new TreeNode(col["Column_name"].ToString() + " (" + col["Data_type"].ToString() + (col["Character_maximum_length"] != DBNull.Value ? string.Concat("[", col["Character_maximum_length"].ToString(), "]") : string.Empty) + ")");
                            childNode.Tag = col["Column_name"].ToString();
                            DataRow[] pks = dsPKColumns.Tables[0].Select("Table_name = '" + dr["table_name"].ToString() + "' AND COLUMN_NAME = '" + col["Column_name"].ToString() + "'");

                            if (pks.Length > 0)
                            {
                                childNode.ImageIndex = 1;
                                childNode.SelectedImageIndex = 1;
                            }
                            else
                            {
                                childNode.ImageIndex = 2;
                                childNode.SelectedImageIndex = 2;
                            }
                            node.Nodes.Add(childNode);
                        }
                        tvTables.Nodes.Add(node); ;
                    }
                }

            }
            else
            {
                MessageBox.Show("Select a DB first");
            }
        }
        private void generateScriptToolStripMenuItem_Click(object sender, EventArgs e)
        {
            ScriptGenerator frmScript = new ScriptGenerator();
            frmScript.TableName = tvTables.SelectedNode.Text;
            frmScript.ConnectionString = ConnectionString;
            if (frmScript.ShowDialog() == DialogResult.OK)
            {
                ((MainForm)this.MdiParent).toolStripStatusLabel.Text = "Script has been generated successfully.";
            }
        }
        void ctrl_StatusEvent(object sender, StatusEventArgs e)
        {
           ((MainForm)this.MdiParent).toolStripStatusLabel.Text = string.IsNullOrEmpty(e.Message) ? string.Empty : e.Message;
        }
        private void refreshTreeToolStripMenuItem_Click(object sender, EventArgs e)
        {
            BuildDBTree();
        }
    }
}