The art and science of creating SSMS 2012 add-ins – Part 2 – Customizing the add-in

In the previous part of this article we went through the mechanism that the SSMS uses to load an add-in, we created an add-in project in Visual Studio and prepared it for debugging. In this part, we will cover the main class of the add-in, the creation of simple UI elements and the methods that you can use to communicate with SSMS

Let’s start with the two different approaches that exist regarding UI elements creation

Commands and CommandBars

First, we should make a distinction between Commands and CommandBars. Command is an interface that represent a command in the environment. Once it is created in SSMS it will remain there until deletion. It can be executed through UI elements such as menu item buttons or programmatically but it is not an UI element itself. CommandBar interface represents those UI elements. CommandBars can be permanent or temporary

  • Temporary CommandBars are deleted each time that you shut down the host application and thus need to be recreated each time. They can make the host application to load slower but are easier for manipulation
  • Permanent CommandBars are created only once during the lifetime of the add-in. They are created after the first startup of the host application with the add-in and are removed on add-in uninstallation. They do not slowdown loading time of the host application since they are not recreated each time but they are harder to work with

In general, there is no need to use permanent CommandBars unless your add-in contains a lot of commands

Take a look at the MSVS generated code in the OnConnection method of the connect class. If the CommandPreload element value in the .Addin file is set to 1 the OnConnection method will be called with parameter connectMode == ext_ConnectMode.ext_cm_UISetup which marks the first starting of the host application with the add-in. Thus the CommandBars will be created only once and will not be recreated when you restart the host application because the connectMode will never again have that value (connectMode value passed to the OnConnection method is controlled by a key in the registry). There are some other differences that exist around implementing temporary and permanent CommandBars which are not covered in this article. We decided to work with the temporary CommandBars in this example since they are easier to implement. Because of that, we changed the value of the CommandPreload element to 0 in the .Addin file, and that will cause the OnConnection method to be invoked with connectMode == ext_ConnectMode.ext_cm_Startup value

Adjusting the OnConnection method

  • Since we decided to recreate UI elements each time that our add-in starts, we need to change the condition inside the OnConnection method of the Connect class from:
if(connectMode == ext_ConnectMode.ext_cm_UISetup)


if(connectMode == ext_ConnectMode.ext_cm_Startup)

because ext_ConnectMode.ext_cm_Startup is passed to the OnConnection method each time that the add-in is loaded

Temporary CommandBar creation

  • Now, let’s add one temporary CommandBar next to the Tools menu item in SSMS

Place this code:

CommandBarPopup _ourAddinMenu = 
menuBarCommandBar.Controls.Add(MsoControlType.msoControlPopup, Type.Missing, 
Type.Missing,toolsControl.Index,  true) as CommandBarPopup;

_ourAddinMenu.Caption = "Our Addin";

Just before the TRY CATCH block inside the OnConnection method

It creates a menu item “Our Addin” in the position of the “Tools” menu item, moving the rest of the menu items them to the right

Adding a control to a CommandBar

Now change:

if ((command != null) && (toolsPopup != null))
           command.AddControl(toolsPopup.CommandBar, 1);


if ((command != null) && (toolsPopup != null))
           command.AddControl(_ourAddinMenu.CommandBar.CommandBar, 1);

Since we want to add TestAddin command to our newly created menu item

Deleting a command

Since we don’t want to leave anything behind after our add-in is uninstalled, we will have to delete created commands (they are not deleted on shutdown automatically as temporary CommandBars are) each time that the host application is closed, in which case OnDisconection method is invoked

public void OnDisconnection(ext_DisconnectMode disconnectMode, ref Array custom)
          Commands2 commands = (Commands2)_applicationObject.Commands;
              Command addinCommand = commands.

          catch (System.ArgumentException e)
              System.Diagnostics.Debug.Print("Error deleting commands on

Note: By default command name will be <Your Addin Name>.Connect.<Your Addin Name>

And we are almost there

  • Place a breakpoint on the OnConnection method and start your project.
    You should be able to debug your add-in now. After you click on a command you just created, Exec method will be invoked. Use it to implement your add-in logic

Hint: You can change the status (Enabled, Disabled, Hidden…) of the add-in commands using the QueryStatus method

Congrats, you‘ve just created your first add-in for SSMS 2012

Happy coding!

[1] “How to write a SQL Server Management Studio Add-in for Denali (CTP3) (Part 1)” – T –SQL Tidy Blog
[2] “HOWTO: Adding buttons, CommandBars and toolbars to Visual Studio .NET from an add-in” – by Carlos J. Quintero
[3] “The Black Art of Writing a SQL Server Management Studio 2005 Add-In” – by Joseph Cooney
[4] “Building a SQL Server Management Studio Add-in” – by Jon Sayce
[5] ”How To: Create SQL Server Management Studio Addin” – by Shai Raiten

See also:
The art and science of creating SSMS 2012 add-ins – Part 1 – Laying the Foundation
The art and science of creating SSMS 2012 add-ins – Part 3 – Creating Toolbars and new Windows

February 20, 2015