The art and science of creating SSMS 2012 add-ins- Part 3 – Creating Toolbars and new Windows

In the previous two parts of our series, we worked on foundations of an SSMS 2012 add-in. We talked about the mechanism that is behind loading add-ins into SSMS, about the add-in file and the creation of basic Visual Studio 2012 add-in project. We also discussed main parts of the extensibility API and setting up debugging of our add-in. In this article, we‘ll expand that story with the steps to further modify SSMS UI, by creating toolbars and new windows. We also take a look at the method for controlling commands appearance in SSMS 2012

Creating and controlling a Query Editor window

Let’s continue where we left of in the previous article and add some functionality to our newly created command through the Exec method which is being invoked after you click on the command. We’ll create a new query window and fill it with some text. But first we will need to reference SqlPackageBase.dll and SqlWorkbench interfaces which can be found at the following location: C:\Program Files\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\. Now, we can use the ScriptFactory interface which enabled us to create a new Query Editor window. Let’s go to the Exec method in our main Connect class

if(commandName == "TestAddin.Connect.TestAddin")
    {
           DTE2 dte = ServiceCache.ExtensibilityModel.DTE as DTE2;
           Document activeDocument = null;

           IScriptFactory scriptFactory = ServiceCache.ScriptFactory;
           if(scriptFactory != null)
           {
              scriptFactory.CreateNewBlankScript(ScriptType.Sql);
              activeDocument = dte.ActiveDocument;
           }

           if(activeDocument != null)
           {
              TextSelection ts = activeDocument.Selection as TextSelection;
              ts.Insert("This Query Window was created with our TestAddin.",
              (int)vsInsertFlags.vsInsertFlagsInsertAtStart);
           }

           handled = true;
        }

We check for the command that invoked the Exec method and create a new script by calling the CreateNewBlankScript method using the IscriptFactory interface from the SqlWorkbench.Interfaces.dll

Then we use the TextSelection interface to access properties and methods needed to control the content of our new window

Note:
Be aware that the line:

DTE2 dte = ServiceCache.ExtensibilityModel.DTE as DTE2;

would throw a null exception if you try to use this code with SSMS 2005 or SSMS 2008. In that case, you would have to reference Microsoft.SqlServer.SqlTools.VSIntegration instead of SqlPackageBase.dll

Controlling the representation of a command

Now, let’s imagine that we need to mark that the command in the menu is currently activated. For example, if there is an active Query Editor document the command should be represented as activated and if there are none the command should be normal. We would use the QueryStatus method of the Connect class

if(commandName == "TestAddin.Connect.TestAddin")
{
  DTE2 dte = ServiceCache.ExtensibilityModel.DTE as DTE2;
  Document activeDocument = null;

  activeDocument = dte.ActiveDocument;

  if(activeDocument == null)
  {
    status = vsCommandStatus.vsCommandStatusSupported |      
vsCommandStatus.vsCommandStatusEnabled;
    commandText = "Enable Our Command";
  }
  else
  {
    status = vsCommandStatus.vsCommandStatusSupported | 
vsCommandStatus.vsCommandStatusEnabled | vsCommandStatus.vsCommandStatusLatched;
    commandText = "Our Command Enabled";
  }
}

What is the result?

When we start SSMS our command will appear normally in the menu with text “Enable Our Command”. If we activate it and there is a Query Editor window in SSMS, it will appear as ‘pressed’ and with text “Our Command Enabled”. Each new click would create a new Query Editor window and the command appearance would revert to the original one only after we disable all Query Windows

Note:
Be aware that the QueryStatus method is invoked many times while SSMS is active and that you should not place any performance demanding task in it. It is meant for simple checks and changing the status of commands based on them

Implementing a user control as a window in SSMS

Now, we are going to dive deeper into adapting SSMS to our needs. Let’s say that you need your own form opened as a SSMS tool window. For that purpose, you will need a simple usercontrol in the project and you would use the following code in the Exec method

if(commandName == "TestAddin.Connect.TestAddinToolWindowForm")
{
    Assembly a = Assembly.GetExecutingAssembly();

    object controlObject = null;

    Windows2 toolWindows = dte.Windows as Windows2;
    Window2 toolWindow;

    toolWindow = (Window2)toolWindows.CreateToolWindow2(_addInst, a.Location,     
"TestAddin.TestAddinToolWindowForm ", string.Empty, Guid.NewGuid().ToString(), 
ref controlObject);

    toolWindow.WindowState = vsWindowState.vsWindowStateNormal;
    toolWindow.IsFloating = false;
    toolWindow.Visible = true;
}
 

Note that I’ve created a new command called “TestAddinToolWindowForm” for this purpose.
After the command is clicked, we use Reflection to get the current assembly where our UserControl is located, which is in this case the executing assembly. Then we call the CreateToolWindow2 method and pass to it, respectively:

• _adInst is the object passed to the OnConnection method which is casted to add-In interface
• Path of the assembly where the UserControl is located
• Full name of the UserControl
• Caption for the window

Now the toolWindow is created and your UserControl with your Form or anything other in it is shown as a dockable window similar to Object Explorer in SSMS 2012

Note:
If you want the window to fill the workspace similarly to the newly opened Query Editor window you should set:

toolWindow.Linkable = false;

Implementing a toolbar with a command button

For the end of this part, let’s add a new toolbar to SSMS 2012. As we talked in one of the previous parts of the series, commands are unique and we can assign multiple different UI elements to a single command. In this case, we’ll create a toolbar with a button for our first command “TestAddin”

First, define a new commandbar object variable for the class, as we will use this reference to delete the toolbar on disconnection

CommandBar ourCustomToolbar;

Now we’ll add the toolbar to other commandbars in the OnConnection method, where we create other UI elements. We’ll also need a CommanBarButton object which we will place on the toolbar and which will be used to invoke our command

CommandBarButton ourCustomToolbarButton;
CommandBars cmdBars = (CommandBars)_applicationObject.CommandBars;

ourCustomToolbar = cmdBars.Add("Our add-in Toolbar",
                   MsoBarPosition.msoBarTop, System.Type.Missing, true);

Finally, we’ll add the commandbarbutton on the toolbar and connect it with the command we already created

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

       ourCustomToolbarButton = (CommandBarButton)command.AddControl
(ourCustomToolbar,
       ourCustomToolbar.Controls.Count + 1);
       ourCustomToolbarButton.Style = MsoButtonStyle.msoButtonIconAndCaption;
       ourCustomToolbar.Visible = true;
}

Don’t forget to delete the CommandBar in the onDisconection, since it is a temporary commandbar object and we talked about them in one of the previous part of the series. For that we will use the reference to the CommandBar object we created in the OnConnection. Note that there is no need to delete the CommandBarButton object, since it is deleted with the CommandBar

if(ourCustomToolbar != null)
{
      ourCustomToolbar.Delete();
}

Now you should have a toolbar with functioning button under the usual SSMS toolbars on startup. Notice how the QueryStatus method takes care of both representations of the “TestAddin” command, in the toolbar and in the menu item we created

And that is it for now. If you followed all articles in our series, you are pretty well equipped for starting to customize SSMS 2012 for your needs. Stay tuned, because we will continue our coverage of the SSMS extensibility with articles about context menu items creation and handling SSMS 2012 events

References:

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

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 2 – Customizing the add-in