form1.cs

基于SQL SERVER 2005的SQL查询系统,利用SMO实现数据库的操作以及查询

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

// http://code.msdn.microsoft.com/sqlquery

//http://www.connectionstrings.com/default.aspx
//Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\Northwind.mdb;User Id=admin;Password=;
//DSN=myDSN;Uid=sa;Pwd=sa;
//Driver={SQL Server};Server=devpc\sqlexpress;Database=test

//** SQL Compact
//Data Source=c:\ProgramData\Microsoft\eHome\EPG\d4961aeb7b4145a4b49b9e73e87eb9a2.sdf

//TODO: select output format: text, insert statements

namespace SqlQuery
{
    using System.Data.Common;
    using System.Data.Odbc;
    using System.Data.SqlClient;
    using System.IO;
    using System.Xml;
    using System.Xml.Xsl;

    public partial class Form1 : Form
    {
        protected bool queryRunning = false;
        protected bool doCommit = true;

        public Form1()
        {
            InitializeComponent();
        }

        protected override void OnLoad(EventArgs e)
        {
            base.OnLoad(e);

            this.ClientSize = Properties.Settings.Default.FormClientSize;
            this.Location = Properties.Settings.Default.FormLocation;
            this.WindowState = Properties.Settings.Default.FormWindowsState;
            splitContainer1.SplitterDistance = Properties.Settings.Default.SplitterDistance;

            UpdateStatusStrip();

            DriverComboBox.Items.Clear();
            DriverComboBox.Items.Add(Properties.Resources.ProviderNative);
            DriverComboBox.Items.Add(Properties.Resources.ProviderODBC);
            DriverComboBox.Items.Add(Properties.Resources.ProviderSQLCompact);

            foreach (string itm in DriverComboBox.Items)
            {
                if (itm == Properties.Settings.Default.DBDriver)
                    DriverComboBox.SelectedItem = itm;
            }
            if (DriverComboBox.SelectedItem == null)
                DriverComboBox.SelectedItem = DriverComboBox.Items[0];

            QueryTextBox.SelectionLength = 0;
        }

        protected override void OnHandleCreated(EventArgs e)
        {
            base.OnHandleCreated(e);

            MessageToolStripStatusLabel.Text = Properties.Resources.StatusReady;
        }

        protected override void OnClosing(CancelEventArgs e)
        {
            base.OnClosing(e);

            if (queryRunning)
                e.Cancel = true;
        }

        protected override void OnClosed(EventArgs e)
        {
            base.OnClosed(e);

            Properties.Settings.Default.FormWindowsState = this.WindowState;
            if (this.WindowState == FormWindowState.Normal)
            {
                Properties.Settings.Default.FormClientSize = this.ClientSize;
                Properties.Settings.Default.FormLocation = this.Location;
            }
            Properties.Settings.Default.SplitterDistance = splitContainer1.SplitterDistance;
            Properties.Settings.Default.DBDriver = DriverComboBox.Text;

            Properties.Settings.Default.Save();
        }

        private void RunButton_Click(object sender, EventArgs e)
        {
            try
            {
                MessageToolStripStatusLabel.Text = Properties.Resources.StatusRunning;
                this.Refresh();

                RecordsToolStripStatusLabel.Text = "";
                TimeExecToolStripStatusLabel.Text = "";
                TimeTotalToolStripStatusLabel.Text = "";

                string s = QueryTextBox.SelectedText;
                if (s == "" || s == null)
                    s = QueryTextBox.Text;

                RunQuery(s);
            }
            finally
            {
                if (MessageToolStripStatusLabel.Text=="")
                    MessageToolStripStatusLabel.Text = Properties.Resources.StatusReady;
            }
        }

        protected DbConnection ConnectSQLCompact(string constr)
        {
            return new System.Data.SqlServerCe.SqlCeConnection(constr);
        }

        internal void RunQuery(string query)
        {
            try
            {
                int tickStart = Environment.TickCount;

                try
                {
                    DbConnection con = null;
                    if (DriverComboBox.Text == Properties.Resources.ProviderSQLCompact)
                        con = ConnectSQLCompact(ConnectionTextBox.Text);
                    if (DriverComboBox.Text == Properties.Resources.ProviderNative)
                        con = new SqlConnection(ConnectionTextBox.Text);
                    if (DriverComboBox.Text == Properties.Resources.ProviderODBC)
                        con = new OdbcConnection(ConnectionTextBox.Text);

                    using (con)
                    {
                        if (con.State != ConnectionState.Open)
                            con.Open();

                        using (DbCommand cmd = con.CreateCommand())
                        {
                            try
                            {
                                cmd.CommandType = CommandType.Text;
                                cmd.CommandText = query;
                                cmd.Transaction = con.BeginTransaction();

                                try
                                {
                                    switch (cmd.CommandType)
                                    {
                                        case CommandType.Text:
                                            if ((string)toolStripDropDownButton1.Tag == Properties.Resources.XSLhtml)
                                                ExecuteReaderXSL(cmd, Properties.Resources.htmlTableXSL);
                                            if ((string)toolStripDropDownButton1.Tag == Properties.Resources.XSLcsv)
                                                ExecuteReaderXSL(cmd, Properties.Resources.csvText);
                                            break;
                                    }

                                    TimeTotalToolStripStatusLabel.Text = (Environment.TickCount - tickStart).ToString();
                                }
                                catch
                                {
                                    throw;
                                }
                            }
                            finally
                            {
                                if (Properties.Settings.Default.Commit)
                                    cmd.Transaction.Commit();
                                else
                                    cmd.Transaction.Rollback();
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageToolStripStatusLabel.Text = ex.Message;
                    MessageToolStripStatusLabel.ToolTipText = ex.Message;
                    return;
                }

                MessageToolStripStatusLabel.Text = "";
                MessageToolStripStatusLabel.ToolTipText = "";
            }
            finally
            {
                queryRunning = false;
            }
        }

        internal void ExecuteReaderXSL(DbCommand cmd, string xsltext)
        {
            int tickStart = Environment.TickCount;

            MemoryStream mxsl = new MemoryStream(Encoding.Default.GetBytes(xsltext));
            MemoryStream mout = new MemoryStream();
            MemoryStream m = new MemoryStream();
            XmlWriter xw = XmlWriter.Create(m);

            XslCompiledTransform xsl = new XslCompiledTransform();
            xsl.Load(new XmlTextReader(mxsl));

            try
            {
                xw.WriteStartDocument(true);
                xw.WriteStartElement("root");

                using (DbDataReader rd = cmd.ExecuteReader())
                {
                    TimeExecToolStripStatusLabel.Text = (Environment.TickCount - tickStart).ToString();

                    int i = 0;
                    int total = 0;
                    int totalResult = 0;