SQL-DMO：在几秒内搜索和重新生成存储过程

• 下载源代码 - 287.8 KB
• 下载另一个使用SMO（SQL Server Management Object）开发的版本 - 651.83 KB

引言

创建、生成和修改存储过程是在使用数据库应用程序时常见的任务。如果数据库中的存储过程数量较少，这些任务不会占用我们太多时间。

但是，对于包含数千个存储过程的数据库，我们需要花费时间来识别存储过程名称，然后进行一些点击来生成其脚本。为了防止这些枯燥的任务，我花费了几个小时使用SQL-DMO对象库创建了一个非常简单的工具，用于快速搜索和重新生成存储过程脚本。

Using the Code

代码非常简单，并且此示例并非完美，但它帮助了我的工作。;)

using System;
using System.Collections;
using SQLDMO;
using System.Text;

namespace SQLScriptGen
{
    /// <summary>
    /// The Utility class present for a SQL Server instant
    /// </summary>
    public class SQLServerUtil
    {
        private SQLServer myServer;
        private bool isConnected = false;
        public SQLServerUtil()
        {
            myServer = new SQLServer();
        }

        public delegate void delegateSendText (string info);
        public event delegateSendText OnInfo;
        public event delegateSendText OnSearchFound;

        /// <summary>
        /// Get List of available SQL Server on network
        /// </summary>
        /// <returns />
        public  ArrayList GetServers()
        {
            ArrayList ret = new ArrayList();
            try
            {
                NameList nl = myServer.Application.ListAvailableSQLServers();
                int i, n = nl.Count;
                for (i = 0; i< n; i++)
                {
                    string sn = nl.Item(i);
                    if (sn != null)
                        ret.Add(sn);
                }
            }
            catch (System.Exception ex)
            {
                if (OnInfo != null)
                    OnInfo(ex.Message);
            }

            return ret;
        }

        /// <summary>
        /// Connect to a SQL Server using Windows Authentication
        /// </summary>
        public bool Connect(string serverName)
        {
            Disconnect();
            try
            {
                myServer.LoginTimeout = 15;
                myServer.LoginSecure = true;
                myServer.Connect(serverName,null , null);
            }
            catch (Exception ex)
            {
                isConnected = false;
                if (OnInfo != null)
                    OnInfo(ex.Message);
            }
            isConnected = true;
            return isConnected;
        }

        /// <summary>
        /// Disconnect from the server 
        /// </summary>
        private void Disconnect()
        {
            try
            {
                myServer.DisConnect();
            }
            catch (Exception ex)
            {
                if (OnInfo != null)
                    OnInfo(ex.Message);
            }
        }

        /// <summary>
        /// Connect to a SQL Server using SQL Authentication
        /// </summary>
        public bool Connect(string serverName, string userName, string password)
        {
            Disconnect();
            try
            {
                myServer.LoginSecure = false;
                myServer.Connect(serverName, userName, password);
            }
            catch (Exception ex)
            {
                isConnected = false;
                if (OnInfo != null)
                    OnInfo(ex.Message);
            }
            isConnected = true;
            return isConnected;
        }

        /// <summary>
        /// Retrieve list of available Database names
        /// </summary>
        public ArrayList GetDatabaseNames()
        {
            ArrayList ret = new ArrayList();
            try
            {
                foreach (_Database db in myServer.Databases)
                {
                    if (db != null)
                        ret.Add(db.Name);
                }

            }
            catch (System.Exception ex)
            {
                if (OnInfo != null)
                    OnInfo(ex.Message);
            }
            return ret;
        }

        /// <summary>
        /// Get script of a store procedure
        /// </summary>
        public string GetSPScript(string dbName, string spName, bool createFile)
        {
            string ret = "";
            try
            {
                string fileName = "output\\" + spName + ".sql";
                if (!createFile)
                    fileName = null;
                ret = myServer.Databases.Item
		  (dbName, "dbo").StoredProcedures.Item(spName, "dbo")
                    .Script(SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Drops 
                    | SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_IncludeHeaders
                    | SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Default
                    , fileName,
                    SQLDMO.SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_Default );
            }
            catch (Exception ex)
            {
                if (OnInfo != null)
                    OnInfo(ex.Message);

            }
            return ret;
        }

        /// <summary>
        /// Simple searching method
        /// </summary>
        public void SearchSP(string dbName, string searchText, bool inNameOnly)
        {
            try
            {
                StoredProcedures colSP = myServer.Databases.Item
					(dbName, "dbo").StoredProcedures;
            
                if (inNameOnly)
                {
                    foreach (_StoredProcedure sp in colSP)
                    {

                        if (sp.Name.IndexOf(searchText) >=0)
                        {
                            if (OnSearchFound != null)
                            {
                                OnSearchFound(sp.Name);
                                System.Windows.Forms.Application.DoEvents();
                            }
                        }
                    }
                }
                else
                {
                    foreach (_StoredProcedure sp in colSP)
                    {

                        if (sp.Text.IndexOf(searchText) >=0)
                        {
                            if (OnSearchFound != null)
                            {
                                OnSearchFound(sp.Name);
                            }
                        }
                    }
                }
            }
            catch (System.Exception ex)
            {
                if(OnInfo != null)
                {
                    OnInfo(ex.Message);
                }
            }
        }
    }
} 

玩得开心！