sqlassistance.cs

数据库操作的小工具

                    if (!sp.IsSystemObject)
                    {
                        sps.Add(sp.Name);
                    }
                }                

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

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

        public void GetDatabaseSps(TreeView tree, TreeNode spsNode, ImageList imageList, int spImageIndex)
        {
            try
            {
                // clear current nodes
                spsNode.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 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)
                {
                    if (!sp.IsSystemObject)
                    {
                        sps.Add(sp.Name);
                    }
                }                

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

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

        #endregion

        #region Get all columns from current table

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

                // create a list of Microsoft.SqlServer.Management.Smo.Column to hold information of all columns
                List<Microsoft.SqlServer.Management.Smo.Column> columns = new List<Microsoft.SqlServer.Management.Smo.Column>();

                // get database name from parent node (database node is parent node of tablesNode, the tablesNode is parent of tableNode)
                string database = tableNode.Parent.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 Name,DataType for Column type when server loading data from server to avoid overhead and prevent to hang
                server.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.Column), new string[] { "Name", "DataType" });

                // iterate through each column that exist in table object
                foreach (Microsoft.SqlServer.Management.Smo.Column col in server.Databases[database].Tables[tableNode.Text].Columns)
                {
                    columns.Add(col);
                }                

                // iterate through list object and add each item (column name) to treeView
                foreach (Microsoft.SqlServer.Management.Smo.Column col in columns)
                {
                    // create a node to hold column name (level 2)
                    // then add dataType to it's text
                    TreeNode colNode = new TreeNode(col.Name);
                    colNode.Text += "," + col.DataType.ToString();

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

        public void GetTableColumns(TreeView tree, TreeNode tableNode, ImageList imageList, int columnImageIndex)
        {
            try
            {
                // clear current nodes
                tableNode.Nodes.Clear();

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

                // create a list of Microsoft.SqlServer.Management.Smo.Column to hold information of all columns
                List<Microsoft.SqlServer.Management.Smo.Column> columns = new List<Microsoft.SqlServer.Management.Smo.Column>();

                // get database name from parent node (database node is parent node of tablesNode, the tablesNode is parent of tableNode)
                string database = tableNode.Parent.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 Name,DataType for Column type when server loading data from server to avoid overhead and prevent to hang
                server.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.Column), new string[] { "Name", "DataType" });

                // iterate through each column that exist in table object
                foreach (Microsoft.SqlServer.Management.Smo.Column col in server.Databases[database].Tables[tableNode.Text].Columns)
                {
                    columns.Add(col);
                }                

                // iterate through list object and add each item (column name) to treeView
                foreach (Microsoft.SqlServer.Management.Smo.Column col in columns)
                {
                    // create a node to hold column name (level 2)
                    // then add dataType to it's text
                    TreeNode colNode = new TreeNode(col.Name);
                    colNode.ImageIndex = columnImageIndex;
                    colNode.SelectedImageIndex = columnImageIndex;
                    colNode.Text += "," + col.DataType.ToString();

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

        #endregion

        #region Get all stored procedure parameters from current stored procedures

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

                // create a list of Microsoft.SqlServer.Management.Smo.Column to hold information of all stored procedure parameters
                List<Microsoft.SqlServer.Management.Smo.StoredProcedureParameter> parameters = new List<Microsoft.SqlServer.Management.Smo.StoredProcedureParameter>();

                // get database name from parent node (database node is parent node of spsNode, the spsNode is parent of spNode)
                string database = spNode.Parent.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 Name,DataType for StoredProcedureParameter type when server loading data from server to avoid overhead and prevent to hang
                server.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.StoredProcedureParameter), new string[] { "Name", "DataType" });

                // iterate through each storedProcedureParameter that exist in storedProcedure object
                foreach (Microsoft.SqlServer.Management.Smo.StoredProcedureParameter parameter in server.Databases[database].StoredProcedures[spNode.Text].Parameters)
                {
                    parameters.Add(parameter);
                }                

                // iterate through list object and add each item (storedProcedureParameter name) to treeView
                foreach (Microsoft.SqlServer.Management.Smo.StoredProcedureParameter parameter in parameters)
                {
                    // create a node to hold parameter name (level 2)
                    TreeNode parameterNode = new TreeNode(parameter.Name);
                    parameterNode.Text += "," + parameter.DataType.ToString() + "(" + parameter.DataType.MaximumLength.ToString() + ")";

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

        public void GetProcedureParameters(TreeView tree, TreeNode spNode, ImageList imageList, int spParameterImageIndex)
        {
            try
            {
                // clear current nodes
                spNode.Nodes.Clear();

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

                // create a list of Microsoft.SqlServer.Management.Smo.Column to hold information of all stored procedure parameters
                List<Microsoft.SqlServer.Management.Smo.StoredProcedureParameter> parameters = new List<Microsoft.SqlServer.Management.Smo.StoredProcedureParameter>();

                // get database name from parent node (database node is parent node of spsNode, the spsNode is parent of spNode)
                string database = spNode.Parent.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 Name,DataType for StoredProcedureParameter type when server loading data from server to avoid overhead and prevent to hang
                server.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.StoredProcedureParameter), new string[] { "Name", "DataType"});

                // iterate through each storedProcedureParameter that exist in storedProcedure object
                foreach (Microsoft.SqlServer.Management.Smo.StoredProcedureParameter parameter in server.Databases[database].StoredProcedures[spNode.Text].Parameters)
                {
                    parameters.Add(parameter);
                }                

                // iterate through list object and add each item (storedProcedureParameter name) to treeView
                foreach (Microsoft.SqlServer.Management.Smo.StoredProcedureParameter parameter in parameters)
                {
                    // create a node to hold parameter name (level 2)
                    TreeNode parameterNode = new TreeNode(parameter.Name);
                    parameterNode.ImageIndex = spParameterImageIndex;
                    parameterNode.SelectedImageIndex = spParameterImageIndex;
                    parameterNode.Text += "," + parameter.DataType.ToString() + "(" + parameter.DataType.MaximumLength.ToString() + ")";

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

        #endregion
    }
}