frmmain.cs

数据库操作的小工具

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using GetDatabaseSp;
using System.Data.Sql;
using NovinMedia.Data;
using DatabaseHelper.Properties;
using SP_Gen.Classes;
using System.IO;
using System.CodeDom;
using System.CodeDom.Compiler;
using Microsoft.CSharp;
using Microsoft.VisualBasic;
using System.Reflection;

namespace DatabaseHelper
{
    public partial class frmMain : Form
    {
        // this dataTable object hold all information about all storedProcedures (include spName,parameters, ...)
        DataTable dtSPs;

        // create new instance from this class to retrieve all database objects via Smo
        DetectDbObject.SqlAssistance sql;

        public frmMain()
        {
            InitializeComponent();
            this.LoadPlugIns();
            this.sql = new DetectDbObject.SqlAssistance(this.cmbInstances.Text);
        }

        // define an enameration for available language providers
        public enum Providers
        {
            VbProvider = 1,
            CsProvider = 2
        };   

        // get connection string for use to generate sql sps
        private string GetConnectionString()
        {
            string connStr = string.Empty;

            if (!this.checkTrustConnection.Checked)
            {
                connStr =
                    string.Format("Server={0:G};Database=master;User ID={1:G};Password={2:G};Trusted_Connection=False;",
                                  this.cmbInstances.Text, this.txtUser.Text, this.txtPass.Text);
            }
            else
                connStr =
                    string.Format("Data Source={0:G};Initial Catalog=master;Integrated Security=SSPI;",
                                  this.cmbInstances.Text);

            return connStr;
        }

        #region Events

        // initialize app
        private void Form1_Load(object sender, EventArgs e)
        {            
            this.PrintToLog("Program Started.");
            this.PrintToLog("---- Welcome to Database Helper version 2.0.0 ----");
            this.checkTrustConnection.Select();
            this.checkTrustConnection.Checked = true;
            this.LoadSettings();
        }

        // Detect all objects for current node before expanding
        private void treeView1_BeforeExpand(object sender, TreeViewCancelEventArgs e)
        {
            if (e.Node.Level == 1)
            {
                if (e.Node.Text == "Tables")
                {
                    MethodInvoker invoker = delegate
                    {
                        this.sql.GetDatabaseTables(this.treeView1, e.Node, this.imageList1, 1);
                    };
                    invoker.Invoke();
                }
                else if (e.Node.Text == "Stored Procedures")
                {
                    MethodInvoker invoker = delegate
                    {
                        this.sql.GetDatabaseSps(this.treeView1, e.Node, this.imageList1, 10);
                    };
                    invoker.Invoke();
                }
            }
            else if (e.Node.Level == 2)
            {
                if (e.Node.Parent.Text == "Tables")
                {
                    MethodInvoker invoker = delegate
                    {
                        this.sql.GetTableColumns(this.treeView1, e.Node, this.imageList1, 6);
                    };
                    invoker.Invoke();
                }
                else if (e.Node.Parent.Text == "Stored Procedures")
                {
                    MethodInvoker invoker = delegate
                    {
                        this.sql.GetProcedureParameters(this.treeView1, e.Node, this.imageList1, 11);
                    };
                    invoker.Invoke();
                }
            }
        }

        // detect all dataSource(s) for current netWork
        private void btnDetect_Click(object sender, EventArgs e)
        {
            try
            {
                this.btnDetect.Enabled = false;
                this.cmbInstances.Enabled = false;
                this.cmbInstances.Items.Clear();
                this.cmbInstances.Text = "Retrieving DataSource(s) ...";
                this.PrintToLog("Retrieving DataSource(s) ...");
                System.Windows.Forms.Application.DoEvents();

                DataTable servers = SqlDataSourceEnumerator.Instance.GetDataSources();
                for (int i = 0; i < servers.Rows.Count; i++)
                {
                    cmbInstances.Items.Add(servers.Rows[i]["ServerName"] + "\\" + servers.Rows[i]["InstanceName"]);
                }
                if (this.cmbInstances.Items.Count > 0)
                    cmbInstances.SelectedIndex = 0;

                this.btnDetect.Enabled = true;
                this.cmbInstances.Enabled = true;
                this.PrintToLog(string.Format("Found {0} DataSource(s) in the network", this.cmbInstances.Items.Count));
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error");
            }
        }

        // connect to selected dataSource and retrieve all database,tables and sps
        private void btnConnect_Click(object sender, EventArgs e)
        {
            if (this.checkTrustConnection.Checked)
            {
                string dataSource = this.cmbInstances.Text;
                this.PrintToLog(string.Format("Retrieving object(s) from datasource : {0}", dataSource));
                this.btnConnect.Enabled = false;
                System.Windows.Forms.Application.DoEvents();
                this.backgroundWorker1.RunWorkerAsync();
            }
            else
            {
                if (this.txtUser.Text == string.Empty || this.txtPass.Text == string.Empty)
                {
                    MessageBox.Show("You selected UnTrusted Connection, Please fill UserName and Password", "Login information required");
                    this.btnConnect.Enabled = true;
                    this.PrintToLog("Error in connection");
                }
                else
                {
                    string dataSource = this.cmbInstances.Text;
                    this.PrintToLog(string.Format("Retrieving object(s) from datasource : {0}", dataSource));
                    this.btnConnect.Enabled = false;
                    System.Windows.Forms.Application.DoEvents();
                    this.backgroundWorker1.RunWorkerAsync();
                }
            }
        }

        // change connection mode
        private void checkTrustConnection_CheckedChanged(object sender, EventArgs e)
        {
            this.PrintToLog(string.Format("Trusted Connection set to : {0}", this.checkTrustConnection.Checked));
            this.lblUser.Enabled = !this.checkTrustConnection.Checked;
            this.lblPass.Enabled = !this.checkTrustConnection.Checked;

            this.txtUser.Enabled = !this.checkTrustConnection.Checked;
            this.txtPass.Enabled = !this.checkTrustConnection.Checked;
        }

        // generate code and sps
        private void btnGenerateCode_Click(object sender, EventArgs e)
        {
            if (this.treeView1.SelectedNode == null)
                MessageBox.Show("Please Select a Database", "No database selected");
            else
            {
                if (this.txtSaveTo.Text == string.Empty)
                {
                    MessageBox.Show("Please enter path to generate Bussiness layer(s)", "Path reqiuired");
                }
                else
                {
                    this.GenerateSqlSpAndClassCodeAndGetSps();
                }
            }
        }

        // select outPut folder(s)
        private void btnSaveTo_Click(object sender, EventArgs e)
        {
            if (this.folderBrowserDialog1.ShowDialog() == DialogResult.OK)
            {
                this.PrintToLog(string.Format("Selected path is {0}", this.folderBrowserDialog1.SelectedPath));
                this.txtSaveTo.Text = this.folderBrowserDialog1.SelectedPath;
            }
        }