sqlassistance.cs

数据库操作的小工具

using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;

namespace DetectDbObject
{
    class SqlAssistance
    {
        private string _sqlServerInstance;

        public string SqlServerInstance
        {
            get { return _sqlServerInstance; }
            set { _sqlServerInstance = value; }
        }

        public SqlAssistance(string sqlServerInctance)
        {
            this.SqlServerInstance = sqlServerInctance;
        }

        #region Get all databases from current instance

        public void GetServerDatabases(TreeView tree)
        {
            try
            {
                // clear current nodes (to avoid cross-thread problem, use anonymous delegates)
                tree.Invoke(new MethodInvoker(delegate
                {
                    tree.Nodes.Clear();
                }));

                // create a list of strings to hold name of databases
                List<string> databases = new List<string>();

                // create a server object to interact with sql server inctance
                Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(this.SqlServerInstance);

                // iterate through each database that exist in server object
                foreach (Microsoft.SqlServer.Management.Smo.Database db in server.Databases)
                    databases.Add(db.Name);                

                // iterate through list object and add each item (database name) to treeView
                foreach (string str in databases)
                {
                    // create a node to hold database name (main node or level 0)
                    TreeNode dbNode = new TreeNode(str);

                    // create a node named 'Stored Procedures' to hold storedProcedures for each database (level 1)
                    // then add child named 'Objects' to add + mark for 'spsNode' node
                    TreeNode spsNode = new TreeNode("Stored Procedures");
                    spsNode.Nodes.Add("Objects");

                    // create a node named 'Tables' to hold tables for each database (level 1)
                    // then add child named 'Objects' to add + mark for 'Tables' node
                    TreeNode tablesNode = new TreeNode("Tables");
                    tablesNode.Nodes.Add("Objects");                    

                    // add Tabales and StoredProcedures node as child to database node
                    dbNode.Nodes.Add(spsNode);
                    dbNode.Nodes.Add(tablesNode);                    

                    // add db node to treeView (to avoid cross-thread problem, use anonymous delegates)
                    tree.Invoke(new MethodInvoker(delegate
                    {
                        tree.Nodes.Add(dbNode);
                    }
                    ));
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public void GetServerDatabases(TreeView tree,ImageList imageList,int dbImageIndex,int tableImageIndex,int spImageIndex)
        {
            try
            {
                // clear current nodes (to avoid cross-thread problem, use anonymous delegates)
                tree.Invoke(new MethodInvoker(delegate
                {
                    tree.Nodes.Clear();
                }));

                // create a list of strings to hold name of databases
                List<string> databases = new List<string>();

                // create a server object to interact with sql server inctance
                Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(this.SqlServerInstance);

                // iterate through each database that exist in server object
                foreach (Microsoft.SqlServer.Management.Smo.Database db in server.Databases)
                    databases.Add(db.Name);                

                // iterate through list object and add each item (database name) to treeView
                foreach (string str in databases)
                {
                    // create a node to hold database name (main node or level 0)
                    TreeNode dbNode = new TreeNode(str);
                    dbNode.ImageIndex = dbImageIndex;                    

                    // create a node named 'Stored Procedures' to hold storedProcedures for each database (level 1)
                    // then add child named 'Objects' to add + mark for 'spsNode' node
                    TreeNode spsNode = new TreeNode("Stored Procedures");
                    spsNode.ImageIndex = spImageIndex;
                    spsNode.SelectedImageIndex = spImageIndex;
                    spsNode.Nodes.Add("Objects");

                    // create a node named 'Tables' to hold tables for each database (level 1)
                    // then add child named 'Objects' to add + mark for 'Tables' node
                    TreeNode tablesNode = new TreeNode("Tables");
                    tablesNode.ImageIndex = tableImageIndex;
                    tablesNode.SelectedImageIndex = tableImageIndex;
                    tablesNode.Nodes.Add("Objects");

                    // add Tabales and StoredProcedures node as child to database node                    
                    dbNode.Nodes.Add(spsNode);
                    dbNode.Nodes.Add(tablesNode);

                    // add db node to treeView (to avoid cross-thread problem, use anonymous delegates)
                    tree.Invoke(new MethodInvoker(delegate
                    {
                        // set tree.ImageList to our imageList parameter to use it's imageIndex
                        tree.ImageList = imageList;

                        tree.Nodes.Add(dbNode);
                    }
                    ));
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        #endregion

        #region Get all tables from current databse

        public void GetDatabaseTables(TreeView tree, TreeNode tablesNode)
        {
            try
            {
                // clear current nodes
                tablesNode.Nodes.Clear();

                // create a list of strings to hold name of tables
                List<string> tables = new List<string>();

                // get database name from parent node (database node is parent node of tablesNode)
                string database = tablesNode.Parent.Text;

                // create a server object to interact with sql server inctance
                Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(this.SqlServerInstance);

                // set SystemObject for Table type when server loading data from server to avoid overhead and prevent to hang
                server.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.Table), "IsSystemObject");

                // iterate through each table that exist in database object
                foreach (Microsoft.SqlServer.Management.Smo.Table table in server.Databases[database].Tables)
                {
                    if (!table.IsSystemObject)
                    {
                        tables.Add(table.Name);
                    }
                }                

                // iterate through list object and add each item (table name) to treeView
                foreach (string str in tables)
                {
                    // create a node to hold table name (child node or level 1)
                    // then add child named 'Objects' to add + mark for table node
                    TreeNode tableNode = new TreeNode(str);
                    tableNode.Nodes.Add("Objects");

                    // add tableNode to tablesNode
                    tablesNode.Nodes.Add(tableNode);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public void GetDatabaseTables(TreeView tree, TreeNode tablesNode, ImageList imageList, int tableImageIndex)
        {            
            try
            {
                // clear current nodes
                tablesNode.Nodes.Clear();

                // set tree.ImageList to our imageList parameter to use it's imageIndex
                tree.ImageList = imageList;

                // create a list of strings to hold name of tables
                List<string> tables = new List<string>();

                // get database name from parent node (database node is parent node of tablesNode)
                string database = tablesNode.Parent.Text;

                // create a server object to interact with sql server inctance
                Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(this.SqlServerInstance);

                // set SystemObject for Table type when server loading data from server to avoid overhead and prevent to hang
                server.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.Table), "IsSystemObject");

                // iterate through each table that exist in database object
                foreach (Microsoft.SqlServer.Management.Smo.Table table in server.Databases[database].Tables)
                {
                    if (!table.IsSystemObject)
                    {
                        tables.Add(table.Name);
                    }
                }               

                // iterate through list object and add each item (table name) to treeView
                foreach (string str in tables)
                {
                    // create a node to hold table name (child node or level 1)
                    // then add child named 'Objects' to add + mark for table node
                    TreeNode tableNode = new TreeNode(str);
                    tableNode.ImageIndex = tableImageIndex;
                    tableNode.SelectedImageIndex = tableImageIndex;
                    tableNode.Nodes.Add("Objects");

                    // add tableNode to tablesNode
                    tablesNode.Nodes.Add(tableNode);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        #endregion

        #region Get all stored procedures from current database

        public void GetDatabaseSps(TreeView tree, TreeNode spsNode)
        {
            try
            {
                // clear current nodes
                spsNode.Nodes.Clear();

                // create a list of strings to hold name of sps
                List<string> sps = new List<string>();

                // get database name from parent node (database node is parent node of tablesNode)
                string database = spsNode.Parent.Text;

                // create a server object to interact with sql server inctance
                Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(this.SqlServerInstance);

                // set SystemObject for StoredProcedure type when server loading data from server to avoid overhead and prevent to hang
                server.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.StoredProcedure), "IsSystemObject");

                // iterate through each sp that exist in database object
                foreach (Microsoft.SqlServer.Management.Smo.StoredProcedure sp in server.Databases[database].StoredProcedures)
                {