存储过程以及视图关键字搜索工具

[c#]代码库

/*
该工具可以搜索SQL Server 2012数据库中的存储过程以及视图的名称和定义内容中的关键字，并可复制及保存找到的存储过程和视图的定义内容，有助于新接手系统的软件工程师研究已有软件系统。
*/

using Microsoft.SqlServer.Management.Sdk.Sfc;
using Microsoft.SqlServer.Management.Smo;
using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Threading;
using System.Windows.Forms;

namespace SearchKeywordInSPAndView
{
    public partial class frmMain : Form
    {
        public frmMain(SMO smoDatabaseSearch, string DatabaseName)
        {
            this.smoDatabaseSearch = smoDatabaseSearch;
            this.smoDatabaseSearch.DatabaseName = DatabaseName;
            InitializeComponent();
        }

        private HashSet<string> matchedView = null;
        private string ViewSPName;
        private string SPdescription;
        private string ViewDescription;
        private Dictionary<string, string> dicUrnName = new Dictionary<string, string>();
        private HashSet<string> keyWords = new HashSet<string>();
        private bool isBusy = false;
        private bool isSearchContents = true;

        private struct pairSQLKeyword
        {
            public string name;
            public string sql;
            public string keyWord;
        }

        private SMO smoDatabaseSearch;
        private string[] keyWordLines;
        private int viewCount;

        private void btnSearchWordInSP_Click(object sender, EventArgs e)
        {
            if (lstViewSP.Items.Count > 0)
                lstViewSP.Items.Clear();
            if (keyWords.Count > 0)
                keyWords.Clear();
            txtViewSPDescription.Text = string.Empty;
            isBusy = true;
            if (dicUrnName.Count > 0)
                dicUrnName.Clear();
            keyWordLines = txtKeywords.Lines;
            foreach (string line in keyWordLines)
            {
                string word = line.Trim();
                if (word.Length > 0)
                    keyWords.Add(word);
            }
            if (keyWords.Count == 0)
            {
                MessageBox.Show("没有找到可以用于搜索关键字！");
                txtViewSP.Text = string.Empty;
                txtKeywords.Text = string.Empty;
            }
            else
            {
                btnSearchWordInSP.Enabled = false;
                btnCancelSearch.Enabled = true;
                txtViewSP.Text = "列表加载中......";
                lstViewSP.BeginUpdate();
                searchSPWorker.RunWorkerAsync();
            }
        }

        private void btnCancelSearch_Click(object sender, EventArgs e)
        {
            btnCancelSearch.Enabled = false;
            smoDatabaseSearch.CancelCommand();
            if (searchSPWorker.IsBusy && searchSPWorker.WorkerSupportsCancellation)
                searchSPWorker.CancelAsync();
            else if (searchViewWorker.IsBusy && searchViewWorker.WorkerSupportsCancellation)
                searchViewWorker.CancelAsync();
            btnCancelSearch.Enabled = true;
        }

        private void btnSaveViewSP_Click(object sender, EventArgs e)
        {
            try
            {
                DialogResult dr = folderBrowserDialog1.ShowDialog();
                if (dr == DialogResult.OK)
                {
                    //初始化脚本生成器
                    Scripter scripter = new Scripter(smoDatabaseSearch.Server);
                    //下面这些是可选项：作用是向脚本生成器中添加需要生成的脚本内容
                    //Default 获取或设置布尔属性值指定创建所引用对象是否包含在生成的脚本。
                    scripter.Options.Add(ScriptOption.ContinueScriptingOnError);
                    //ContinueScriptingOnError 获取或设置布尔属性值指定的脚本是否继续操作时遇到错误后。
                    scripter.Options.Add(ScriptOption.ConvertUserDefinedDataTypesToBaseType);
                    //ConvertUserDefinedDataTypesToBaseType 获取或设置布尔属性值指定是否将用户定义的数据类型转换成最合适的SQL Server基本数据类型生成的脚本中。
                    // IncludeIfNotExists 获取或设置一个布尔属性值，指定包括它在脚本之前，是否检查一个对象是否存在。
                    scripter.Options.Add(ScriptOption.ExtendedProperties);
                    if (lstViewSP.Items.Count > 0)
                    {
                        foreach (ListViewItem lvi in lstViewSP.Items)
                        {
                            //声明统一资源名称集合对象
                            UrnCollection collection = new UrnCollection();
                            switch (lvi.Text)
                            {
                                case "存储过程":
                                    StoredProcedure SP = smoDatabaseSearch.StoredProcedures[dicUrnName[lvi.SubItems[1].Text]];
                                    //The UrnCollection class represents a collection of Urn objects that represent Uniform Resource Name (URN) addresses.
                                    collection.Add(SP.Urn);

                                    //声明字符串集合对象：存储collection中的所有string对象（在这里其中有3个string对象）
                                    StringCollection SPSqls = scripter.Script(collection);
                                    StringBuilder SPSB = new StringBuilder();
                                    //遍历字符串集合对象sqls中的string对象，选择要输出的脚本语句：
                                    foreach (string s in SPSqls)
                                        SPSB.Append(s).Append("\r\nGO\r\n\r\n");
                                    File.WriteAllText(folderBrowserDialog1.SelectedPath + @"\" + SP.Name + ".sql", SPSB.ToString());
                                    break;
                                case "视图":
                                    Microsoft.SqlServer.Management.Smo.View view = smoDatabaseSearch.Views[dicUrnName[lvi.SubItems[1].Text]];
                                    //The UrnCollection class represents a collection of Urn objects that represent Uniform Resource Name (URN) addresses.
                                    collection.Add(view.Urn);
                                    //声明字符串集合对象：存储collection中的所有string对象（在这里其中有3个string对象）
                                    StringCollection ViewSqls = scripter.Script(collection);
                                    StringBuilder ViewSB = new StringBuilder();
                                    //遍历字符串集合对象sqls中的string对象，选择要输出的脚本语句：
                                    foreach (string s in ViewSqls)
                                        ViewSB.Append(s).Append("\r\nGO\r\n\r\n");
                                    File.WriteAllText(folderBrowserDialog1.SelectedPath + @"\" + view.Name + ".sql", ViewSB.ToString());
                                    break;
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnCopyText_Click(object sender, EventArgs e)
        {
            try
            {
                Clipboard.SetText(txtViewSPDescription.Text);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnSaveText_Click(object sender, EventArgs e)
        {
            try
            {
                saveFileDialog1.FileName = string.Format("[{0}].{1}{2}.sql", smoDatabaseSearch.DatabaseName, txtViewSP.Text, DateTime.Today.ToString("yyyyMMdd"));
                DialogResult dr = saveFileDialog1.ShowDialog();
                if (dr == DialogResult.OK)
                    File.WriteAllText(saveFileDialog1.FileName, txtViewSPDescription.Text);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void showSPWorker_DoWork(object sender, DoWorkEventArgs e)
        {
            isBusy = true;
            smoDatabaseSearch.StoredProcedureName = ViewSPName;
            SPdescription = smoDatabaseSearch.StoredProcedureCreationScript;
            isBusy = false;
        }

        private void showSPWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            txtViewSPDescription.Text = SPdescription;
        }

        private void showViewWorker_DoWork(object sender, DoWorkEventArgs e)
        {
            isBusy = true;
            smoDatabaseSearch.ViewName = ViewSPName;
            ViewDescription = smoDatabaseSearch.ViewCreationScript;
            isBusy = false;
        }

        private void showViewWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            txtViewSPDescription.Text = ViewDescription;
        }

        private void searchSPWorker_DoWork(object sender, DoWorkEventArgs e)
        {
            if (isSearchContents)
            {
                foreach (string keyword in keyWords)
                {
                    if (searchSPWorker.CancellationPending)
                    {
                        e.Cancel = true;
                        return;
                    }
                    foreach (Urn urn in smoDatabaseSearch.Database.EnumMatchingSPs(keyword))
                    {
                        if (searchSPWorker.CancellationPending)
                        {
                            e.Cancel = true;
                            return;
                        }
                        string name = urn.GetAttribute("Name");
                        string SPUrnName = "[" + urn.GetAttribute("Schema") + "].[" + urn.GetAttribute("Name") + "]";
                        if (!dicUrnName.ContainsKey(SPUrnName))
                        {
                            dicUrnName.Add(SPUrnName, name);
                            AddToSpList(SPUrnName);
                        }
                    }
                }
            }
            else
            {
                foreach (string keyword in keyWords)
                {
                    if (searchSPWorker.CancellationPending)
                    {
                        e.Cancel = true;
                        return;
                    }
                    string sql = @"SELECT SCHEMA_NAME(sp.schema_id) AS [Schema],sp.name AS [Name] FROM sys.all_objects AS sp
                                   WHERE (sp.type = N'P' OR sp.type = N'RF' OR sp.type=N'PC')and(CAST(
                                       CASE 
                                       WHEN sp.is_ms_shipped = 1 THEN 1
                                       WHEN (
                                           SELECT 
                                               major_id 
                                           FROM 
                                               sys.extended_properties
                                           WHERE 
                                               major_id = sp.object_id and 
                                               minor_id = 0 and 
                                               class = 1 and 
                                               name = N'microsoft_database_tools_support') 
                                           IS NOT NULL THEN 1
                                       ELSE 0
                                   END AS bit)=N'0') AND sp.name LIKE '%' + @keyword + '%'
                                   ORDER BY [Schema] ASC, [Name] ASC";
                    DataTable views = smoDatabaseSearch.GetDataTable(sql, new SqlParameter() { ParameterName = "@keyword", SqlDbType = SqlDbType.VarChar, Value = keyword });
                    foreach (DataRow row in views.Rows)
                    {
                        string name = (string)row["Name"];
                        string SPUrnName = "[" + (string)row["Schema"] + "].[" + name + "]";
                        if (!dicUrnName.ContainsKey(SPUrnName))
                        {
                            dicUrnName.Add(SPUrnName, name);
                            AddToSpList(SPUrnName);
                        }
                    }
                }
            }
        }

        private void searchSPWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            if (e.Cancelled)
            {
                txtViewSP.Text = string.Empty;
                MessageBox.Show("数据库存储过程和触发器关键字搜索已取消！");
                btnSearchWordInSP.Enabled = true;
                btnCancelSearch.Enabled = false;
                txtViewSP.Text = string.Empty;
                lstViewSP.EndUpdate();
                btnSaveViewSP.Enabled = dicUrnName.Count > 0;
                isBusy = false;
                return;
            }
            else
            {
                searchViewWorker.RunWorkerAsync();
                if (dicUrnName.Count == 0)
                    MessageBox.Show("没有在存储过程中找到关键字！");
            }
        }

        private void searchViewWorker_DoWork(object sender, DoWorkEventArgs e)
        {
            viewCount = 0;
            if (isSearchContents)
            {
                Dictionary<string, string> viewSqls = smoDatabaseSearch.GetCreateViewScripts();
                matchedView = new HashSet<string>();
                foreach (string viewName in viewSqls.Keys)
                {
                    if (searchViewWorker.CancellationPending)
                    {
                        e.Cancel = true;
                        return;
                    }
                    foreach (string keyword in keyWords)
                    {
                        if (searchViewWorker.CancellationPending)
                        {
                            e.Cancel = true;
                            return;
                        }
                        Urn ViewUrn = smoDatabaseSearch.Views[viewName].Urn;
                        string ViewUrnName = "[" + ViewUrn.GetAttribute("Schema") + "].[" + ViewUrn.GetAttribute("Name") + "]";
                        if (!dicUrnName.ContainsKey(ViewUrnName))
                        {
                            viewCount++;
                            dicUrnName.Add(ViewUrnName, viewName);
                            AddToViewList(new pairSQLKeyword() { name = ViewUrnName, sql = viewSqls[viewName], keyWord = keyword });
                        }
                    }
                }
            }
            else
            {
                foreach (string keyword in keyWords)
                {
                    if (searchViewWorker.CancellationPending)
                    {
                        e.Cancel = true;
                        return;
                    }
                    string sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME LIKE '%' + @keyword + '%'";
                    DataTable views = smoDatabaseSearch.GetDataTable(sql, new SqlParameter() { ParameterName = "@keyword", SqlDbType = SqlDbType.VarChar, Value = keyword });
                    foreach (DataRow row in views.Rows)
                    {
                        if (searchViewWorker.CancellationPending)
                        {
                            e.Cancel = true;
                            return;
                        }
                        Microsoft.SqlServer.Management.Smo.View view = smoDatabaseSearch.Views[(string)row["TABLE_NAME"]];
                        string ViewUrnName = "[" + view.Urn.GetAttribute("Schema") + "].[" + view.Urn.GetAttribute("Name") + "]";
                        if (!dicUrnName.ContainsKey(ViewUrnName))
                        {
                            viewCount++;
                            dicUrnName.Add(ViewUrnName, view.Name);
                            AddToViewList(ViewUrnName);
                        }
                    }
                }
            }
        }

        private void searchViewWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            lstViewSP.EndUpdate();
            btnSaveViewSP.Enabled = dicUrnName.Count > 0;
            btnSearchWordInSP.Enabled = true;
            btnCancelSearch.Enabled = false;
            txtViewSP.Text = string.Empty;
            if (e.Cancelled)
            {
                txtViewSP.Text = string.Empty;
                MessageBox.Show("数据库存储过程和触发器关键字搜索已取消！");
            }
            else if (viewCount == 0)
                MessageBox.Show("没有在数据库的视图中找到关键字！");
            if (isBusy && !ShowInTaskbar)
                notifyIcon1.ShowBalloonTip(1000, notifyIcon1.BalloonTipTitle, "搜索关键字已完成！", notifyIcon1.BalloonTipIcon);
            isBusy = false;
        }

        private void AddToSpList(string content)
        {
            ThreadPool.QueueUserWorkItem(new WaitCallback(GetSpNameAsync), content);
        }

        private void GetSpNameAsync(object param)
        {
            if (param == null || !(param is string))
            {
                //Report Error
                this.BeginInvoke(new Action(() =>
                {
                    MessageBox.Show("Invalid argument: param", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }));
                return;
            }

            string content = param as string;
            //Complete
            this.BeginInvoke(new Action<string>(OnGetSpNameAsyncCompleted), content);
        }

        private void OnGetSpNameAsyncCompleted(string SPname)
        {
            ListViewItem lvi = new ListViewItem();
            lvi.Text = "存储过程";
            lvi.SubItems.Add(SPname);
            lstViewSP.Items.Add(lvi);
        }

        private void AddToViewList(pairSQLKeyword content)
        {
            ThreadPool.QueueUserWorkItem(new WaitCallback(GetViewNameAsync), content);
        }

        private void GetViewNameAsync(object param)
        {
            if (param == null || !(param is pairSQLKeyword))
            {
                //Report Error
                this.BeginInvoke(new Action(() =>
                {
                    MessageBox.Show("Invalid argument: param", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }));
                return;
            }

            pairSQLKeyword pair = (pairSQLKeyword)param;
            //Complete
            this.BeginInvoke(new Action<string, string, string>(OnGetViewNameAsyncCompleted), pair.name, pair.sql, pair.keyWord);
        }

        private void OnGetViewNameAsyncCompleted(string name, string sql, string keyword)
        {
            if (sql.Contains(keyword))
            {
                ListViewItem lvi = new ListViewItem();
                lvi.Text = "视图";
                lvi.SubItems.Add(name);
                lstViewSP.Items.Add(lvi);
            }
        }

        private void AddToViewList(string content)
        {
            ThreadPool.QueueUserWorkItem(new WaitCallback(GetOtherViewNameAsync), content);
        }

        private void GetOtherViewNameAsync(object param)
        {
            if (param == null || !(param is string))
            {
                //Report Error
                this.BeginInvoke(new Action(() =>
                {
                    MessageBox.Show("Invalid argument: param", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }));
                return;
            }

            string content = param as string;
            //Complete
            this.BeginInvoke(new Action<string>(OnGetOtherViewNameAsyncCompleted), content);
        }

        private void OnGetOtherViewNameAsyncCompleted(string Viewname)
        {
            ListViewItem lvi = new ListViewItem();
            lvi.Text = "视图";
            lvi.SubItems.Add(Viewname);
            lstViewSP.Items.Add(lvi);
        }

        private void lstViewSP_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                if (!isBusy && lstViewSP.SelectedItems.Count > 0)
                {
                    txtViewSP.Text = lstViewSP.SelectedItems[0].SubItems[1].Text;
                    ViewSPName = dicUrnName[txtViewSP.Text];
                    txtViewSPDescription.Text = "加载中......";
                    if (!btnCopyText.Enabled)
                        btnCopyText.Enabled = true;
                    if (!btnSaveText.Enabled)
                        btnSaveText.Enabled = true;
                    if (lstViewSP.SelectedItems[0].Text == "存储过程")
                        showSPWorker.RunWorkerAsync();
                    else if (lstViewSP.SelectedItems[0].Text == "视图")
                        showViewWorker.RunWorkerAsync();
                }
            }
            catch
            {

            }
        }

        private void notifyIcon1_MouseDoubleClick(object sender, MouseEventArgs e)
        {
            if (this.WindowState == FormWindowState.Minimized)
            {
                this.Show();
                this.WindowState = FormWindowState.Normal;
                notifyIcon1.Visible = false;
                this.ShowInTaskbar = true;
            }
        }

        private void frmMain_SizeChanged(object sender, EventArgs e)
        {
            if (this.WindowState == FormWindowState.Minimized) //判断是否最小化
            {
                this.ShowInTaskbar = false; //不显示在系统任务栏
                notifyIcon1.Visible = true; //托盘图标可见
                if (isBusy)
                    notifyIcon1.ShowBalloonTip(1000);
                else
                    notifyIcon1.ShowBalloonTip(1000, notifyIcon1.BalloonTipTitle, "双击还原搜索工具！", notifyIcon1.BalloonTipIcon);
            }
        }

        private void radSearchInContents_CheckedChanged(object sender, EventArgs e)
        {
            isSearchContents = true;
        }

        private void radSeachInNames_CheckedChanged(object sender, EventArgs e)
        {
            isSearchContents = false;
        }

        private void frmMain_FormClosed(object sender, FormClosedEventArgs e)
        {
            Application.Exit();
        }
    }
}

[代码运行效果截图]

[源代码打包下载]