Russ Thomas – SQL Judo

The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded

SSMS Day 29: Identifying Options & Debugging Add-In

31 Days of SSMS: Table of Contents

Up until now we’ve built an application shell and deployed it to an install of SSMS that we are willing to mess around with.  Doing this on the same box your paycheck depends on is not without risk – so … you’ve been warned.  Today we’ll start the actual “messing around” part.

Back before fancy debuggers and frameworks for building test cases – developers commonly debugged by simply sending updates back to the screen through printlines or dialog prompts.  You’d then run the same program you ultimately planned to deploy and with each button click you might see a prompt such as “Hello World”.  When you saw that you knew your line of code had been reached.  Deeper debugging would display messages such as “The value of InputParameter is:  5”.  Essentially the equivalent of data tips, breakpoints, and stepping through code that we use today.  Building and debugging an SSMS Add-In follows this old approach.

Remember that I want my tool to change the background color of the code window based on what our connection is.  First let’s see if I can change the color at all.

The three most important tools you need to find out what is possible are, knowledge of where the SSMS assemblies (DLLs) might be found, Visual Studio Object Browser, and the Internet.

Looking through the SSMS install folder we see a lot of assemblies.  Nothing jumps out at me as the one Assembly that controls the document.  You can Google each assembly and get a minimal amount of insight into what it might contain however.  I start looking at anything that says UI or document or editor etc.  The following for example looks interesting:  Microsoft.SqlServer.Dts.Runtime.Design   –  following that link we find that is an assembly for SSIS.  Not what I want.

Eventually I end up looking at SqlPackageBase.dll  ( we already added that a couple days ago, just play along ).  Microsoft doesn’t offer anything on MSDN specifically but there is a whole bunch of other stuff on the web that makes me think I’m on to something.

Now I’m going to make a reference to it in my Visual Studio project and look deeper with Object Browser.  Looking through this assembly through the Object Browser reveals methods, procedures, interfaces, all kinds of good stuff.

objbrowser

This is an incredibly useful tool for finding what is available.  The most useful feature of the Object Browser is the search window.  It will search all referenced assemblies for keywords.  This is what I find when I search FontsAndColors for example.  This is good stuff.

objsearch

Eventually using this combination of approaches I finally figure out (mostly from google and documentation related to Visual Studio) that the following code will allow me to change the background color of my code window.  ( psst, _addInInstance has already been declared by the add in project ).

changecolorGreat – now I need to figure out how to ascertain the connection underneath an SSMS document window.  This time I have a little more luck with Bing.  Rumor through tribal knowledge is that I can get my connection information via the SqlEditors.dll.  Specifically they give me some test code to get the connection info object.

conninfo

Looking deeper at this type via the Object Browser I learn that the CurrentlyActiveWndConnectionInfo object has the exact property I am looking for. ServerName. Let’s see if it works like I am expecting. I am going to wire up my tool menu to display the content of that property in my text window when I click the tool button.  Add the following code to the Exec procedure the AddIn project created for you.

  handled = false;
  if(executeOption == vsCommandExecOption.vsCommandExecOptionDoDefault)
  {
      if(commandName == "ssms_bazing.Connect.ssms_bazing")
      {
          // get current connection information
          Microsoft.SqlServer.Management.UI.VSIntegration.Editors.IScriptFactory scriptFactory =
              Microsoft.SqlServer.Management.UI.VSIntegration.ServiceCache.ScriptFactory;

          CurrentlyActiveWndConnectionInfo connInfo = scriptFactory.CurrentlyActiveWndConnectionInfo;               

          // display current connection info in document
          Document document = ((DTE2)ServiceCache.ExtensibilityModel).ActiveDocument;

          if (document != null)
          {
              //replace currently selected text
              TextSelection selection = (TextSelection)document.Selection;
              selection.Insert("--" + connInfo.UIConnectionInfo.ServerName, (Int32)EnvDTE.vsInsertFlags.vsInsertFlagsContainNewText);
          }                    
                
          handled = true;
          return;
      }
  }

I recompile (replacing the DLL the AddIn references) and bam.  Clicking the tool menu now displays my current connection server name in the code window.

connectionLet’s try again and this time add a color other than white to my background.

  Properties props = _addInInstance.DTE.Properties["FontsAndColors", "TextEditor"];
  EnvDTE.FontsAndColorsItems prop = (EnvDTE.FontsAndColorsItems)props.Item("FontsAndColorsItems").Object;
  prop.Item("Plain Text").Background = (uint)ColorTranslator.ToOle(Color.LightSalmon)

conncolor

Ok, now comes the challenging part. I need to subscribe to an event. I need to change the color based on server connection when the document opens and again if there is a connection change event. After all – I can change background color without an AddIn via Tools->Options. The point of this AddIn is to be dynamic based on connection, not because I really like LightSalmon as a color.

Searching around I am able to find some tribal knowledge on subscribing to document opened events in Visual Studio AddIn’s.

With some slight modification I am able to change this code to work for me.  I declare the event object as a class level _applicationObjects.Events.  And then subscribe to it with the following.

DocumentEvents docEvents = events.DocumentEvents;
docEvents.DocumentOpened += new _dispDocumentEvents_DocumentOpenedEventHandler(docEvents_DocumentOpenedBazing);

This get’s us really close. I just need to move the color and connection code from menu item clicked procedure we originally used to the new procedure I just linked the Document Opened event to.

docEvents_DocumentOpenedBazing

The background color can now be based on a connection when the code window first connects. This code still needs to be written but making a quick .dat file to link server names to colors with a default for anything not listed is trivial. The only problem left to solve is how to update the color if the SSMS user changes the connection on an existing code window. Red Gate does it so we know that event exists. We just need to dig.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: