frmmain.cs

可视化的数据库连接与查询,数据库实例列表

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

namespace BDCS.DataBaseInformation
{
    public partial class frmMain : Form
    {
        public frmMain(string DatabaseName)
        {
            InitializeComponent();
            this.m_DataBase = DatabaseName;
            this.lblTitle.Text = DatabaseName;
            this.GetTables();
            //this.InitListView();            
        }

        private string m_DataBase;
        private Microsoft.SqlServer.Management.Smo.Table m_CurrentTable;
        private Microsoft.SqlServer.Management.Smo.Column m_CurrentColumn;

        private void InitListView()
        {
            if (this.tvObject.SelectedNode == null)
                return;

            this.lvObject.Clear();

            switch (this.tvObject.SelectedNode.SelectedImageIndex)
            {
                case 0 :
                    this.lvObject.Columns.Add("表名", 100, HorizontalAlignment.Left);
                    this.lvObject.Columns.Add("Schema", 100, HorizontalAlignment.Center);
                    this.lvObject.Columns.Add("记录数", 100, HorizontalAlignment.Center);
                    this.lvObject.Columns.Add("创建日期", 100, HorizontalAlignment.Center);
                    break;
                case 1 :
                    this.lvObject.Columns.Add("字段名成", 100, HorizontalAlignment.Left);
                    this.lvObject.Columns.Add("字段类型", 100, HorizontalAlignment.Left);
                    this.lvObject.Columns.Add("长度", 40, HorizontalAlignment.Center);
                    this.lvObject.Columns.Add("主键", 40, HorizontalAlignment.Center);
                    this.lvObject.Columns.Add("允许空值", 40, HorizontalAlignment.Center);
                    break;
                default :
                    this.lvObject.Clear();
                    foreach (Microsoft.SqlServer.Management.Smo.Column col in this.m_CurrentTable.Columns)
                        this.lvObject.Columns.Add(col.Name, 50, HorizontalAlignment.Left);
                    break;
            }
        }

        private void GetTables()
        {
            TreeNode nodeTreeNode;

            this.Cursor = Cursors.WaitCursor;

            this.tvObject.Nodes.Clear();
                        
            nodeTreeNode = new TreeNode(this.m_DataBase, 0, 0);

            this.tvObject.Nodes.Add(nodeTreeNode);

            TreeNodeCollection nodeCollection = nodeTreeNode.Nodes;

            foreach (Microsoft.SqlServer.Management.Smo.Table tbl in frmConnect.CurrentServer.Databases[this.m_DataBase].Tables)
            {
                if (this.chkNotEmpty.Checked)
                    if (tbl.RowCount == 0)
                        continue;
              

                nodeTreeNode = new TreeNode(tbl.Schema + "." + tbl.Name, 1, 1);

                nodeCollection.Add(nodeTreeNode);

            }

            this.Cursor = Cursors.Default;

        }

        private void tvObject_AfterSelect(object sender, TreeViewEventArgs e)
        {
            this.Cursor = Cursors.WaitCursor;

            TreeNode nodeCurrent = e.Node;

            nodeCurrent.Nodes.Clear();

            string[] lvData = new string[4];

            if (nodeCurrent.SelectedImageIndex == 0)
            {
                this.GetTables();

                this.lvObject.Columns.Clear();

                this.lvObject.Columns.Add("表名", 100, HorizontalAlignment.Left);
                this.lvObject.Columns.Add("Schema", 100, HorizontalAlignment.Center);
                this.lvObject.Columns.Add("记录数", 100, HorizontalAlignment.Center);
                this.lvObject.Columns.Add("创建日期", 100, HorizontalAlignment.Center);

                this.lvObject.Items.Clear();

                foreach (Microsoft.SqlServer.Management.Smo.Table tbl in frmConnect.CurrentServer.Databases[this.m_DataBase].Tables)
                {
                    lvData[0] = tbl.Name;
                    lvData[1] = tbl.Schema;
                    lvData[2] = tbl.RowCount.ToString();
                    lvData[3] = tbl.CreateDate.ToShortDateString() + " " + tbl.CreateDate.ToShortTimeString();

                    ListViewItem lvi = new ListViewItem(lvData, 0);

                    lvObject.Items.Add(lvi);

                    lvi = null;
                }
                this.lblAggr.Text = "数据库“" + this.m_DataBase + "”共有" + frmConnect.CurrentServer.Databases.Count.ToString() + "个表。";
            }
            else if (nodeCurrent.SelectedImageIndex == 1)
            {
                foreach (Microsoft.SqlServer.Management.Smo.Table tbl in frmConnect.CurrentServer.Databases[this.m_DataBase].Tables)
                {
                    if (tbl.Name == nodeCurrent.Text.Substring( nodeCurrent.Text.IndexOf(".") + 1 ))
                    {
                        this.m_CurrentTable = tbl;
                        break;
                    }
                }
                this.GetFields(e.Node.Nodes);
                this.lblAggr.Text = "数据库“" + this.m_DataBase + "”共有" + frmConnect.CurrentServer.Databases.Count.ToString() + "个表；表“" + this.m_CurrentTable.Schema + "." + this.m_CurrentTable.Name + "”共有" + this.m_CurrentTable.Columns.Count.ToString() + "个字段。";
            }
            else
            { 
                foreach (Microsoft.SqlServer.Management.Smo.Column col in this.m_CurrentTable.Columns)
                {
                    if (col.Name == nodeCurrent.Text)
                    {
                        this.m_CurrentColumn = col;
                        break;
                    }
                }
                this.GetRows();
            }

            this.Cursor = Cursors.Default;

        }

        private void GetRows()
        {
            this.lvObject.Items.Clear();
            this.lvObject.Columns.Clear();

            foreach (Microsoft.SqlServer.Management.Smo.Column col in this.m_CurrentTable.Columns)
                this.lvObject.Columns.Add(col.Name, 50, HorizontalAlignment.Left);

            if (this.nudRecord.Value == 0)
                return;

            string strColumns = string.Empty;

            foreach (Microsoft.SqlServer.Management.Smo.Column col in this.m_CurrentTable.Columns)
                strColumns += ", [" + col.Name + "]";

            strColumns = strColumns.Substring(1);

            string strSQL = "SELECT TOP " + this.nudRecord.Value.ToString() + " " + strColumns + " FROM ";

            strSQL += "[" + this.m_DataBase + "].[" + this.m_CurrentTable.Schema + "].[" + this.m_CurrentTable.Name + "]";

            System.Data.DataSet dsTable = new DataSet();

            System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(strSQL, frmConnect.ConnectionString);

            da.Fill(dsTable);

            string[] lvData = new string[dsTable.Tables[0].Columns.Count];

            foreach (System.Data.DataRow dr in dsTable.Tables[0].Rows)
            {
                for (int i = 0; i < dsTable.Tables[0].Columns.Count; i++)
                {
                    lvData[i] = dr[i].ToString();
                }

                ListViewItem lvi = new ListViewItem(lvData, 0);

                this.lvObject.Items.Add(lvi);

                lvi = null;
            }

            lvData = null;

        }

        private void GetFields(TreeNodeCollection nodeCurrentCollection)
        {
            TreeNode nodeTreeNode;

            this.lvObject.Items.Clear();

            this.lvObject.Columns.Clear();

            this.lvObject.Columns.Add("字段名成", 100, HorizontalAlignment.Left);
            this.lvObject.Columns.Add("字段类型", 100, HorizontalAlignment.Left);
            this.lvObject.Columns.Add("主键", 40, HorizontalAlignment.Center);
            this.lvObject.Columns.Add("允许空值", 40, HorizontalAlignment.Center);

            string[] lvData = new string[5];

            foreach (Microsoft.SqlServer.Management.Smo.Column col in this.m_CurrentTable.Columns)
            {
                lvData[0] = col.Name;
                lvData[1] = col.DataType.Name;
                
                lvData[2] = col.InPrimaryKey ? "√" : "×";
                lvData[3] = col.Nullable ? "√" : "×";

                ListViewItem lvi = new ListViewItem(lvData, 0);

                lvObject.Items.Add(lvi);

                lvi = null;

                if (col.InPrimaryKey)
                    nodeTreeNode = new TreeNode(col.Name, 3, 3);
                else
                    nodeTreeNode = new TreeNode(col.Name, 2, 2);

                nodeCurrentCollection.Add(nodeTreeNode);

            }

            lvData = null;

        }


        private void lvObject_DoubleClick(object sender, EventArgs e)
        {
            if (this.tvObject.SelectedNode != null)
            {
                if (this.tvObject.SelectedNode.SelectedImageIndex == 1)
                {
                    if (System.Windows.Forms.MessageBox.Show("要在数据库“" + this.m_DataBase + "”中查找具有字段“" + this.lvObject.SelectedItems[0].Text + "”的表吗？", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                    {
                        frmSameTable frm = new frmSameTable(this.m_DataBase, this.lvObject.SelectedItems[0].Text, this.chkNotEmpty.Checked, System.Convert.ToInt32( this.nudRecord.Value));
                        frm.Title = this.lvObject.SelectedItems[0].Text;
                        frm.ShowDialog();
                    }
                }
            }
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            this.m_CurrentColumn = null;
            this.m_CurrentTable = null;
            frmConnect.CurrentServer = null;
            GC.Collect();
            this.Close();
            Application.Exit();
        }

    }
}