frmsametable.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 frmSameTable : Form
    {
        public frmSameTable(string DatabaseName, string FieldName, bool NotEmpty, int RecordCount)
        {
            InitializeComponent();
            this.m_FieldName = FieldName;
            this.m_DataBaseName = DatabaseName;
            this.m_NotEmpty = NotEmpty;
            this.m_RecordCount = RecordCount;
        }

        public string Title
        {
            set
            {
                this.Text = "包含名称为＂" + value + "＂的字段的表";
            }
        }

        private bool m_NotEmpty = false;

        private int m_RecordCount = 100;

        private string m_FieldName = string.Empty;

        private string m_DataBaseName = string.Empty;

        private Microsoft.SqlServer.Management.Smo.Table m_CurrentTable = null;

        private Microsoft.SqlServer.Management.Smo.Column m_CurrentColumn = null;

        private void frmSameTable_Load(object sender, EventArgs e)
        {
            int intCount = 0;
            this.Cursor = Cursors.WaitCursor;
            foreach (Microsoft.SqlServer.Management.Smo.Database db in frmConnect.CurrentServer.Databases)
            {
                if (db.Name == this.m_DataBaseName)
                {
                    foreach ( Microsoft.SqlServer.Management.Smo.Table tbl in db.Tables )
                    {
                        if (this.m_NotEmpty && tbl.RowCount == 0)
                            continue;

                        foreach (Microsoft.SqlServer.Management.Smo.Column col in tbl.Columns)
                        {
                            if (col.Name == this.m_FieldName)
                            {
                                TreeNode nodeTreeNode;

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

                                this.tvObject.Nodes.Add(nodeTreeNode);

                                intCount++;

                                break;
                            }
                        }
                    }
                    break;
                }
            }

            this.lblAggr.Text = "具有字段“" + this.m_FieldName + "”的表共有" + intCount.ToString() + "个。";

            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 == 1)
            {
                foreach (Microsoft.SqlServer.Management.Smo.Table tbl in frmConnect.CurrentServer.Databases[this.m_DataBaseName].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_DataBaseName + "”共有" + 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);

            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.m_RecordCount.ToString() + " " + strColumns + " FROM ";

            strSQL += "[" + this.m_DataBaseName + "].[" + 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 btnCancel_Click(object sender, EventArgs e)
        {
            this.m_CurrentColumn = null;
            this.m_CurrentTable = null;
            GC.Collect();
            this.Close();
        }

        private void panBottom_Paint(object sender, PaintEventArgs e)
        {

        }



    }
}