Mark Gilbert’s Blog

In Part 1 of this series we looked at how the Scripter uses SQL Management Objects (SMO) to script out DROP and CREATE statements for each object selected.  Originally, I wrote the Scripter as a standalone executable, just so I could get something up and running quickly.  The next step was to integrate the Scripter with SQL Server Management Studio (SSMS) as a plug-in.

I wanted the scripter to appear on the context menus for various objects in the SSMS hierarchy:

• Database
• “Views” folder
• Individual views 
• “Programming/Stored Procedures” folder 
• Individual stored procedures 
• “Programming/Functions” folder

I discovered two interesting points about building an SSMS Plug-in.  First, I could find no official documentation on Microsoft’s site (or any other site for that matter) about how to write one of these.  Second, the classes used to write the plug-in for SSMS appear to be the same ones that would be needed to write a plug-in for Visual Studio 2005.

What I did find was the source code for another SSMS plug-in called the Enisey Data Scripter (http://www.codeproject.com/useritems/enisey.asp).  This plug-in was designed to script out data from tables, and as a result added itself as a context menu item to the Table objects in SSMS.  It seemed like as good a starting point as I was going to get.

There were several project settings that had to be specified in order to get various things to work.  These were discovered partially by examining the Enisey Data Scripter, and partially through trial-and-error:

• Application / Application Type of “Class Library” 
• Compile / “Register for COM interop” is checked 
• Debug / Start External Program of “C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe” 
• Project References: 
  • Extensibility.dll – provides access to the Extensibility namespace 
  • Microsoft.SqlServer.SqlTools.VSIntegration – provides access to the Microsoft.SqlServer.Management.UI.VSIntegration namespace.  This required me to add the following path to the Reference Paths: “C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\”. 
  • ObjectExplorer.dll – provides access to the Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer namespace.  This required me to add the following path to the Reference Paths: “C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\”.  Additionally, the assembly was called “SQL Server Object Explorer Package” in the references dialog, not ObjectExplorer.dll as you might first expect. 
  • SqlWorkbench.Interfaces.dll  - provides access to the INodeInformation and IObjectExplorerService interfaces.  This required me to add the following path to the Reference Paths: “C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\”.  Additionally, it was called “SQL Server Object Explorer Interface Package” in the references dialog, not SqlWorkbench.Interfaces.dll as you might expect.

Once these changes were made, I had to add the following registry key:

Windows Registry Editor Version 5.00 [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\Shell\AddIns\SSMSScriptor.Plugin]
“Description”=”Copyright (c) 2006, Mark Gilbert, mgilbert@blue-granite.com”
“FriendlyName”=”SQL Server Management Studio Extension”
“LoadBehavior”=dword:00000001

SSMScriptor.dll” is the filename of my class library when it builds, so the key had to be named “SSMScriptor.Plugin”.  Additionally, the “AddIns” folder that it resides in didn’t exist on my machine initially, but its parent “Shell” folder did.

Once all of these settings have been made, we can move on to the code itself.  After studying the Enisey scripter intently for obscene amounts of time (ok, it was only a couple of hours, but that’s long in computer-years), I found three classes that seemed to be most relevant to the task of building a plug-in.  These three classes have the following equivalents in my Scripter: Plugin.vb, MenuNode.vb, and ScripterMenuItem.vb.  The main entry point is PlugIn.vb.

The PlugIn class implements the IDTExtensibility2 interface.  There are several event handler methods that are included with this interface, but the only one that the Scripter uses is OnConnection.  This method registers a custom handler called OnSelectionChanged, which fires when the user right-clicks on an object in the tree to bring up its context menu.  If the item selected is a valid object (see the “valid” list at the beginning of this post), then the Scripter menu item is added to that menu using the shared function MenuNode.GetInstanceOnSelectedNode.  A NodesDict dictionary object is used to track which objects have had the menu item loaded for it, and prevents the GetInstanceOnSelectedNode function from adding a second copy of the item to the menu.

The magic of determining whether the current node is a “valid” one or not is done in the MenuNode class constructor, which is invoked by GetInstanceOnSelectedNode.  This evaluates the currently selected node’s Context property.  There is a block of comments near the beginning of the constructor which gives an example of the Context value for each of the nodes that the Scripter can attach to.The Context value is examined, and the appropriate private Boolean variable is set to indicate the type of node that is currently selected:

‘ Set some specific internal variables to denote the node’s type.
Me.m_blnIsSproc = blnDoServerDatabaseElementsExist AndAlso _
    blnDoObjectNameSchemaElementsExist AndAlso _
    strCurrentContext.Contains(“/StoredProcedure[@Name") Me.m_blnIsSProcsFolder = blnDoServerDatabaseElementsExist AndAlso _
    strCurrentContext.Contains("/Folder[@Name='StoredProcedures' and @Type='StoredProcedure']“)  Me.m_blnIsView = blnDoServerDatabaseElementsExist AndAlso _
    blnDoObjectNameSchemaElementsExist AndAlso _
    strCurrentContext.Contains(“/View[@Name”)
…

Next, the Server and Database names (which are included in every node Context value at the database level and lower in the hierarchy) are extracted, and saved in private variables:

Me.m_strServerName = niCurrent.Context.Split(“‘”)(1)
Me.m_strDatabase = niCurrent.Context.Split(“‘”)(3) 

Finally the type and name of the object currently selected are saved in another pair of private variables:

…
ElseIf (Me.m_blnIsSProcsFolder) Then
    Me.m_strObjectName = Common.ALL_OBJECTS
    Me.m_otObjectType = Common.ObjectTypeEnum.StoredProcedures
    ‘ If the menu has already been registered for the Stored Procedure folder, then don’t do it again.
    If (Not plgCurrent.IsMenuRegisteredForSProcFolder) Then
        Me.AddScripterMenuItem(niCurrent)plgCurrent.IsMenuRegisteredForSProcFolder = True
    End If 
ElseIf (Me.m_blnIsSproc) Then
    Me.m_strObjectName = niCurrent(“Name”).ToString()
    Me.m_otObjectType = Common.ObjectTypeEnum.StoredProcedures
    ‘ If the menu has already been registered for a specific stored procedure, then it will be registered for all of them
    If (Not plgCurrent.IsMenuRegisteredForSProcNode) Then
        Me.AddScripterMenuItem(niCurrent)plgCurrent.IsMenuRegisteredForSProcNode = True
    End If 
…

At this point, the user merely sees a new item in the context menus for whatever they’ve right-clicked on.  When the user actually selects the Scripter from the context menu, the third class, ScripterMenuItem, comes into play.ScripterMenuItem inherits from ToolsMenuItemBase, and as a result has two overridden methods: Invoke and Clone.  The latter is not valid for the Scripter, and as a result merely throws an exception.  The former is called when the user clicks on an item in the context menu, and does the work of instantiating the SSMSScriptor form, and passing it the Server, Database, Object Type, and Object Name property values for the object currently selected to the form’s constructor.

In the third and final part of “SSMS Scripter Internals”, we’ll look at the splash screen used with SSMS Scripter, and how threading is leveraged to make it work.