SSMS插件开发指南

清泛原创
创建步骤同VS-Addin,请查看《VS Addin插件基本开发入门》。

一、调试参数,要启动新的SSMS实例进行调试:

C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe
C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\


二、运行配置(有两种方法:一是自己写注册表,二是在“生成”选项卡中,选中“为COM互操作注册”

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell\Addins\SSMSAddin.Connect]
"FriendlyName"="SSMSAddin "
"LoadBehavior"=dword:00000001
"Description"="SSMSAddin desc "

SSMS加载插件的原理和VS插件的原理略有不同,VS直接加载Addins目录中.Addin文件,指定载入哪个dll;而SSMS读取注册表中Addins子项目(HKEY_CURRENT_USER而不是HKEY_LOCAL_MACHINE),然后加载注册表已注册的COM组件(由于开启“COM互操作注册”,VS编译时已经将DLL注册到注册表)。

以上是SMSS 2008加载插件的方式。获取DTE方式:
_applicationObject = (DTE2)ServiceCache.ExtensibilityModel;

注意:开发使用的VS版本不能高于SSMS的版本,否则会出现各种各样意想不到的问题。
SSMS2008一般采用VS2005或VS2008开发,SSMS2012采用VS2012开发。SSMS2008与SSMS2012一些获取对象等细节方面也有少量差异。最好使用各自的VS版本生成基本代码后,再添砖加瓦。

三、本文案例采用VS2012开发SSMS2012插件,其他版本的请读者自行尝试,万变不离其中。
SMSS 2012则完全不同,它加载的方式同VS 2012。不过Addins文件夹不在“%userprofile%\Documents\Visual Studio 2012\Addins”中,而在“C:\ProgramData\Application Data\Microsoft\MSEnvShared\AddIns”中。
Putting SSMSAddin.addin into %PROGRAMDATA%\Application Data\Microsoft\MSEnvShared\Addins\ 
(C:\ProgramData\Application Data\Microsoft\MSEnvShared\Addins\)
Will install add-in for all users on the machine.

Putting SSMSAddin.addin into %APPDATA%\Microsoft\MSEnvShared\Addins\ 
(C:\Users\UserName\AppData\Roaming\Microsoft\MSEnvShared\Addins\) 
will install for specific user only. 
OnConnection()函数开始恢复这样:
_applicationObject = (DTE2)application;
_addInInstance = (AddIn)addInInst;
if (connectMode == ext_ConnectMode.ext_cm_Startup)
(此处还是这个,同SMSS 2008)。

另外还需要额外引入三个dll,文件如下:




如果SSMS 2012不加载插件,可以尝试修改如下配置:





四、功能开发:
探索点:弹出SSMS对话框、SQL执行的事件、结果Grid数据的获取。
需求:工具栏添加一个菜单,点击菜单弹出对话框,点“Execute SQL”后弹出测试消息框,显示结果Grid第一个单元格的内容。

基础代码请自行使用VS2012完成,包括添加一个用户控件作为对话框窗口。
OnConnection相关函数如下:
/// <summary>实现 IDTExtensibility2 接口的 OnConnection 方法。接收正在加载外接程序的通知。</summary>
		/// <param term='application'>宿主应用程序的根对象。</param>
		/// <param term='connectMode'>描述外接程序的加载方式。</param>
		/// <param term='addInInst'>表示此外接程序的对象。</param>
		/// <seealso class='IDTExtensibility2' />
		public void OnConnection(object application, ext_ConnectMode connectMode, object addInInst, ref Array custom)
		{
            _applicationObject = (DTE2)application;
            _addInInstance = (AddIn)addInInst;
            if (connectMode == ext_ConnectMode.ext_cm_Startup)
            {
                object[] contextGUIDS = new object[] { };
                Commands2 commands = (Commands2)_applicationObject.Commands;
                string toolsMenuName;

                try
                {
                    //若要将此命令移动到另一个菜单,则将“工具”一词更改为此菜单的英文版。
                    //  此代码将获取区域性,将其追加到菜单名中,然后将此命令添加到该菜单中。
                    //  您会在此文件中看到全部顶级菜单的列表
                    //  CommandBar.resx.
                    ResourceManager resourceManager = new ResourceManager("SSMSAddin.CommandBar", Assembly.GetExecutingAssembly());
                    CultureInfo cultureInfo = new System.Globalization.CultureInfo(_applicationObject.LocaleID);
                    string resourceName;
                    if (cultureInfo.TwoLetterISOLanguageName == "zh")
                    {
                        System.Globalization.CultureInfo parentCultureInfo = cultureInfo.Parent;
                        resourceName = String.Concat(parentCultureInfo.Name, "Tools");
                    }
                    else
                    {
                        resourceName = String.Concat(cultureInfo.TwoLetterISOLanguageName, "Tools");
                    }
                    toolsMenuName = resourceManager.GetString(resourceName);
                }
                catch
                {
                    //我们试图查找“工具”一词的本地化版本,但未能找到。
                    //  默认值为 en-US 单词,该值可能适用于当前区域性。
                    toolsMenuName = "Tools";
                }

                //将此命令置于“工具”菜单上。
                //查找 MenuBar 命令栏,该命令栏是容纳所有主菜单项的顶级命令栏:
                Microsoft.VisualStudio.CommandBars.CommandBar menuBarCommandBar = ((Microsoft.VisualStudio.CommandBars.CommandBars)_applicationObject.CommandBars)["MenuBar"];

                //在 MenuBar 命令栏上查找“工具”命令栏:
                CommandBarControl toolsControl = menuBarCommandBar.Controls[toolsMenuName];
                CommandBarPopup toolsPopup = (CommandBarPopup)toolsControl;

                //如果希望添加多个由您的外接程序处理的命令,可以重复此 try/catch 块,
                //  只需确保更新 QueryStatus/Exec 方法,使其包含新的命令名。
                try
                {
                    //将一个命令添加到 Commands 集合:
                    Command command = commands.AddNamedCommand2(_addInInstance, "SSMSAddin", Connect.ADDIN_NAME, Connect.ADDIN_DESC, true, 59, ref contextGUIDS, (int)vsCommandStatus.vsCommandStatusSupported + (int)vsCommandStatus.vsCommandStatusEnabled, (int)vsCommandStyle.vsCommandStylePictAndText, vsCommandControlType.vsCommandControlTypeButton);

                    //将对应于该命令的控件添加到“工具”菜单:
                    if ((command != null) && (toolsPopup != null))
                    {
                        command.AddControl(toolsPopup.CommandBar, 1);
                    }
                }
                catch (System.ArgumentException)
                {
                    //如果出现此异常,原因很可能是由于具有该名称的命令
                    //  已存在。如果确实如此,则无需重新创建此命令,并且
                    //  可以放心忽略此异常。
                }

                // 弹出对话框
                const string TOOLWINDOW_GUID = "{6CCD0EE9-20DB-4636-9149-665A958D8A9A}";

                object myUserControlObject = null;
                EnvDTE80.Windows2 windows2 = (EnvDTE80.Windows2)_applicationObject.Windows;
                string assembly = System.Reflection.Assembly.GetExecutingAssembly().Location;

                myToolWindow = windows2.CreateToolWindow2(_addInInstance, assembly,
                   typeof(MyUserControl).FullName, ADDIN_NAME, TOOLWINDOW_GUID, ref myUserControlObject);

                // 设置DTE对象
                MyUserControl muc = myToolWindow.Object as MyUserControl;
                muc.ApplicationObject = _applicationObject;


                // SQL执行后事件
                cmdEvent = _applicationObject.Events.CommandEvents["{52692960-56BC-4989-B5D3-94C47A513E8D}", 1];
                cmdEvent.AfterExecute += new _dispCommandEvents_AfterExecuteEventHandler(cmdEvent_AfterExecuteSQL);
            }
		}

        void cmdEvent_AfterExecuteSQL(string Guid, int ID, object CustomIn, object CustomOut)
        {
            MessageBox.Show("SQL Executed.");
        }

        CommandEvents cmdEvent;
        Window myToolWindow = null;
运行效果:
获取Grid数据相关代码:
private void button1_Click(object sender, EventArgs e)
        {
            IGridControl grid = ShellUtilities.GetActiveGridControl();
            if (grid == null)
                return;
            IGridStorage gs = grid.GridStorage;
            string text = gs.GetCellDataAsString(0, 1);
            richTextBox1.AppendText(text);

            // 查看所有的命令、事件GUID
            //foreach (EnvDTE.Command cmd in _applicationObject.Commands)
            //{
            //    richTextBox1.AppendText(String.Format("Name = {0} | GUID = {1} | ID = {2}", cmd.Name, cmd.Guid, cmd.ID) + Environment.NewLine);
            //}
        }
ShellUtilities.cs代码如下:
using System;
using System.Collections.Generic;
using System.Linq;
using EnvDTE80;
using EnvDTE;
using Microsoft.SqlServer.Management.UI.VSIntegration;
using Microsoft.VisualStudio.Shell.Interop;
using Microsoft.SqlServer.Management.UI.VSIntegration.Editors;
using Microsoft.SqlServer.Management.UI.Grid;
using System.Reflection;
using System.Collections;
using System.Windows.Forms;

namespace SSMSAddin
{
    /// <summary>
    /// 操作SSMS对象
    /// </summary>
    public class ShellUtilities
    {
        /// <summary>
        /// 获取当前活动文档视图窗口
        /// </summary>
        /// <param name="wf"></param>
        /// <returns></returns>
        public static Control GetDocView()
        {
            object obj;
            GetCurrentActiveFrameDocView().GetProperty((int)__VSFPROPID.VSFPROPID_DocView, out obj);
            return obj as Control;
        }

        /// <summary>
        /// 获取当前文档视图窗口中的DataGrid
        /// </summary>
        /// <returns></returns>
        public static IGridControl GetActiveGridControl()
        {
            try
            {
                SqlScriptEditorControl sqlEditorCtl = GetDocView() as SqlScriptEditorControl;

                object sqlResultsControl = GetNonPublicField(sqlEditorCtl, "m_sqlResultsControl");
                object gridResultsPage = GetNonPublicField(sqlResultsControl, "m_gridResultsPage");
                CollectionBase gridContainers = (CollectionBase)GetNonPublicField(gridResultsPage, "m_gridContainers");

                IEnumerator ienum = gridContainers.GetEnumerator();
                ienum.MoveNext();

                object gridResultGrid = GetNonPublicField(ienum.Current, "m_grid");
                return gridResultGrid as IGridControl;
            }
            catch (Exception e)
            {
                // Log
                return null;
            }
        }

        /// <summary>
        /// 获取当前文档视图窗口中的DataGrid(另种方式)
        /// </summary>
        /// <returns></returns>
        public static IGridControl GetActiveGridControl2()
        {
            try
            {
                IList<Control> ctlList = GetDocView().ExpandControl().Where(s => s.GetType().FullName.Contains("GridResultsGrid")).ToList();
                if (ctlList.Count == 0)
                    return null;

                return ctlList[0] as IGridControl;
            }
            catch (Exception e)
            {
                // Log
                return null;
            }
        }

        /// <summary>
        /// 向SQL编辑器插入文本
        /// </summary>
        /// <param name="text"></param>
        public static void Insert2SqlScriptEditor(string text)
        {
            Document document = ((DTE2)ServiceCache.ExtensibilityModel).ActiveDocument;
            if (document != null)
            {
                TextSelection selection = (TextSelection)document.Selection;
                selection.Insert(text, (Int32)EnvDTE.vsInsertFlags.vsInsertFlagsContainNewText);
            }
        }


        #region 私有方法
        
        /// <summary>
        /// 获取当前活动的文档
        /// </summary>
        /// <returns></returns>
        private static IVsWindowFrame GetCurrentActiveFrameDocView()
        {
            object obj = null;
            // 第一个参数:1-当前活动窗口,2-当前活动文档
            ServiceCache.VSMonitorSelection.GetCurrentElementValue(2, ref obj);
            return obj as IVsWindowFrame;
        }

        /// <summary>
        /// 获取对象的非公开属性
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="field"></param>
        /// <returns></returns>
        private static object GetNonPublicField(object obj, string field)
        {
            FieldInfo f = obj.GetType().GetField(field, BindingFlags.NonPublic | BindingFlags.Instance);
            return f.GetValue(obj);
        }

        #endregion
    }
}
运行效果:
工程源代码下载:SSMSAddin.zip

该部分源码研究通过查阅英文资料、反编译ssmsboost等,对于有SSMS插件开发需求的小伙伴们,应该能够少走很多弯路。
http://www.ssmsboost.com/ 一款功能强大的SSMS插件,反编译研究可以得到不少方案。)

SSMS 插件

分享到:
评论加载中,请稍后...
创APP如搭积木 - 创意无限,梦想即时!
回到顶部