SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SSMS Addin Wrapper - Source Code and Usage

Code First, KTHX!

Here, as promised, is the source code for the SSMS wrapper.


My apologies for the brief delay on this. When I started working on this project I had only intended to write the wrapper and release just that. Once I had the idea for my SSMSDeploy addin (there's a link to a commentated youtube video demonstrating SSMSDeploy in my previous post) my focus shifted to making the wrapper cover more of the things required by my own addin. For this reason, the event_manager class in this wrapper doesn't cover much functionality. However, the code that is there should give you a pretty good idea of what the intention was, and how to extend that class if you so desire.

The code here is only that used to create a library which you can link to in your projects (or include in another project in your solution). You can use it in any way you wish, but if you redistribute it please give credit (these terms are covered by a copyright header in the code file).

Overview of the Wrapper

All interaction with the wrapper is done through the SSMS class, which is designed to encapsulate the functionality of the DTE (but the DTE is also made available in case you want to do something the wrapper doesn't yet encapsulate). More specifically, interaction is done through three managers contained within the SSMS class: the window manager, the command manager, and the event manager

The window manager is there to handle all of your window interaction needs. You can use it to create new dockable windows hosting your user addin's user controls, or to interact with the query windows in the IDE. If I was going to extend the addin, this is also where I would handle interaction with the object explorer, results window, and so on.

The command manager is there to manage the existing commands in the IDE and any commands you might want to add. Right now, that basically means the creation of menus and menu items on the SSMS menu bar, and the associated commands.

The event manager is there to take the pain out of working with SSMS events. Finding these events and registering your own handlers for them can be a pain. For instance, the query execute event is identified in the IDE with the GUID {52692960-56BC-4989-B5D3-94C47A513E8D} and an ID of 1. The wrapper makes this a lot easier to find and use. The event manager is the least functional part of the wrapper, but there is code there wrapping up the query execute event which shows the design pattern I intended to use

How To Use The Wrapper

If you want to make use of this wrapper, you're still going to have to start off with an extensibility project. You can find step by step instructions on how to do this properly for SSMS by Jon Sayce here, or by Joseph Cooney here, or by Ameed Phadnis here, so I'm not going to go through all that. Instead I'll show you what to do in order to be able to use the wrapper once you've gotten the project open with its template code.

Instantiating the Wrapper

The template calls your addin class "Connect" by default, so that's how I'll refer to it here. The first thing you'll want to do is create an instance of the SSMS wrapper class in your Connect.OnConnection method, like so:
      public void OnConnection(object application, ext_ConnectMode connectMode, object addInInst, ref Array custom) {
         _SSMS = new SSMSW08.SSMS(addInInst);

Adding Menus and Items

If you want to use your own menus and the like (If you don't you can skip this section) you'll also need to tell the IDE that your Connect class can be a command target by having your Connect class implement the IDTCommandTarget interface. The extensibility project doesn't add this automatically, so add it to the provided template:
namespace MyAddin1
    /// The object for implementing an Add-in.
    public class Connect : IDTExtensibility2, IDTCommandTarget 

This means you also need to write the QueryStatus and Exec functions that make up this interface. Fear not, you can just hand this off to the wrapper. Add the following functions to your Connect class
      public void QueryStatus(string commandName, vsCommandStatusTextWanted neededText, ref vsCommandStatus status, ref object commandText) {
         _SSMS.command_manager.QueryStatus(commandName, neededText, ref status, ref commandText);

      public void Exec(string commandName, vsCommandExecOption executeOption, ref object varIn, ref object varOut, ref bool handled) {
         _SSMS.command_manager.Exec(commandName, executeOption, ref varIn, ref varOut, ref handled);

Now that we have this interface implemented we can add menu items to the IDE window and respond to them, just tell the command_manager to create them for you, and pass it the function you want to run when the menu item is used. For instance, to make a menu under the tools menu that says "MyAddinMenu", with a single sub-menu item that says "MySubMenuItem", and which, when invoked, runs a function MyFunction, you would add this in your OnConnection method, (make sure you include the if block):
if (connectMode == ext_ConnectMode.ext_cm_Startup) {
   _SSMS.command_manager.create_popup_menu("Tools", "MyAddinMenu", "A tooltip", 0);
   _SSMS.command_manager.create_popup_menu_command("MyAddinMenu", "arbitrary_command_name", "MySubMenuItem", "Another tooltip", 0, null, MyFunction);               

The null being passed there is another function pointer, the one that you want called when the IDE queries the status of the command (part of the command target interface we just implemented). You usually don't have to worry about running anything specific here, so by passing null we let the wrapper run a default handler that tells the IDE that the command is available for use.

The wrapper will automatically handle the cleanup of menu items when the program exits.

Creating a Window

This is easy enough. Add a user control in your addin project and treat it like any other form (create buttons, textboxes, whatever), because we will use the wrapper to tell the IDE to create a window hosting this control. Essentially we're creating a form, but one that will fit in the IDE (so, for instance, you can dock it, hide it, etc). Add a member of type Window to your Connect class (let's say its called _myWindow). That will be the window hosting your control. If your user control class was called, say, "MyUserControl", then you would create its window lke this:
object obj = null;
_myWindow = _SSMS.window_manager.create_tool_window(
    System.Reflection.Assembly.GetExecutingAssembly().Location, // this tells the IDE where to find your class
    "MyAddin.MyUserControl",  // yes, pass the namespace and class name as a string like this
    "My Window Title", 
    "{21e5ac34-1e26-4399-81d1-5cfe33670d48}", // some arbitrary GUID, just go make one online
    ref obj

Responding to SSMS Events

Let's say you want to popup a message box after any query is executed in SSMS (please don't really do this...). Your addin would define a function like so:
public void MyAfterQueryExecute(string Guid, int ID, object CustomIn, object CustomOut) {
    // do something...

And you can then register that function as a handler using the wrapper:
_SSMS.event_manager.register_after_event(_SSMS.event_manager.query.execute, MyAfterQueryExecute);

Unfortunately at the moment the only event that is set up to be accessed easily using the named notation is the execute event, but you could add more to the wrapper quite easily (you just have to look up what their GUID and ID are, there were instructions on doing that in the pages I linked to previously).


That's all I have time for today, but I hope it's enough to get people started.

By the way, I still haven't decided what to do with my own SSMSDeploy addin and source code (sell on the internet, sell to Redgate/Microsoft, give away and ask for donations, etc...). Still taking suggestions!

Don Halloran

SQL Server, SSIS, SSAS and maybe even SSRS rants and raves (and, on occasion, useful code or designs).


Posted by Anonymous on 10 January 2011

Pingback from  Dew Drop – January 10, 2011 | Alvin Ashcraft's Morning Dew

Leave a Comment

Please register or log in to leave a comment.