Mark Gilbert's Blog

Science and technology, served light and fluffy.

SSMS Scripter Internals Part 2 of 3: “Plugging In”

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. 

Advertisements

December 18, 2006 - Posted by | SQL Server, Tools and Toys

6 Comments

  1. […] describing how the plug-in worked: http://Mark-Gilbert.com/category/sql-server/.  In the “Part 2″ post in that series, I mentioned that I couldn’t find any decent documentation on how to write an SSMS […]

    Pingback by SSMS Scripter Republished to CodePlex.com « Mark Gilbert’s Blog | September 6, 2008

  2. The link you provided for the Enisey Data Script is not working thus making your article absolutely useless because you don’t provide solid examples of where you’re using these code blocks.

    Comment by Santiago Perez | September 15, 2011

    • @Santiago: you’re right about the link. It no longer works. It appears that Enisey is now a fully fledged product (a search for “Enisey Data Scripter” turns up some good links to it), and I presume that the authors decided to take the source code down from CodeProject to protect their IP. Given that my post was written nearly 5 years ago now (December 2006), a bad outbound link should not be surprising.

      I used Enisey as a guide to learning how to building my own plug-in for SQL Server Management Studio. As the title suggests, this was the second of a 3-part series, and therefore can’t really stand on it’s own. To get the rest of the series, go here: https://markegilbert.wordpress.com/category/sql-server/. Even if you had the Enisey source side by side with mine, the similarities you’d notice are the ones called out in this blog post. So, for “solid examples” of where I’m using these code blocks, download the full source for the SSMS Scripter from Codeplex: http://ssmsscripter.codeplex.com/.

      Comment by markegilbert | September 15, 2011

  3. Thanks so much, didn’t mean to come across rude. Just frustrated due to the lack of documentation out there. Thanks for the Source!

    Comment by Santiago Perez | September 15, 2011

    • @Santiago: You are most welcome. It’s unfortunate that in 5 years the official documentation hasn’t improved. Good luck!

      Comment by markegilbert | September 15, 2011

  4. wish u could update this for ssms2008r2 really loved this back when it came out learned alot but slowly changed systems and did not install this plug-in but looking at my bookmarks now really reminds me of the goodtimes..looking forward to it or atleast some help in getting started for 08r2 (specifically) so i may try to revive this.

    Comment by bharathyp | April 8, 2012


Sorry, the comment form is closed at this time.

%d bloggers like this: