The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded
From time to time I wish SSMS did something that it doesn’t. Or possibly did something better. 3rd party tools like those offered by Red Gate extend the environment in incredibly useful ways… but sometimes I just want to tinker myself. Sometimes my needs are unique – and no one out there makes exactly what I’m looking for.
I am a tinkerer by nature. I enjoy wood-working. I change my own oil, brakes, filters, and wipers. I’m the only one who ever takes a wrench to my mountain bikes. So, when the fancy strikes me, I play around with a little code as well. I like the creative process around software development. The next few days of this series will focus on creating your own Add-In/Extension for SSMS.
Let’s get the bad news out of the way right off the bat. Extending SSMS is not supported by Microsoft. This is entirely a roll your own kind of undertaking. It’s not easy, nor is it straightforward.
Good News / More Bad News:
Still with me? Got your beverage? Ok, there is *some* good news. The reason you need the Visual Studio Extensibility SDK linked to above is because building Add-Ins for Visual Studio *IS supported by Microsoft. Since SSMS is built on top of Visual Studio there is more than just a little cross-over. The process for building Add-Ins for VS provides lots of hints and clues as to what you need for SSMS.
Ready for more bad news? For several years now Microsoft has supported extending Visual Studio through the “Add-In” framework. While Microsoft doesn’t support the same for SSMS, they don’t discourage it either (there words, not mine). So there has been a lot of leakage into the SSMS world. Microsoft has now deprecated this framework and is moving to VSPackage extensions for extending Visual Studio. Tribal knowledge on extending SSMS with VSPackage is exceedingly scarce. It’s likely to remain scarce until the Add-In framework is gone for good and more SSMS developer attention is drawn towards VSPackage.
While it’s been deprecated – building an Add-In today is still not without merit. Deployment will be different but many of the techniques, assemblies, and approaches are the same. So, we will build an Add-In and then attempt to migrate it to a VSPackage on the last post of this series to get an idea about where the future of SSMS extensibility might be going.
The sample project I am going to build for this series is something I’ll just call ssms_bazing. Its a purposely horrible name as I don’t want to create trademark issues.
SSMS offers a colored status bar that will change depending on your connection. Red Gate takes this a step further by offering a colored tab, a little more visible. I really really really want to know when I am connected to a production box. So my add-in will change the full background color of the query window based on current connection.
On the last day we’ll take a look at VSPackage and go a step further. What if instead of changing the background color I wanted a background watermark – like a picture of Scary DBA Grant Fritchey ( @gfritchy ) staring you down? Think about it!
Let’s get started with the Add-In. Open up Visual Studio and go to New Project. With the SDK installed go to Other Project Types and select Extensibility. Notice both Visual Studio Package and Visual Studio Add-In are present. We’re looking at VS Add-In right now.
Visual Studio will build your bare project template after a short Wizard gathers some baseline information. Most the screens are pretty self explanatory. Project Name, where the project code files should go on disk, what language (C#, VB.Net) your Add-In will be written in.
Step 4 however is of interest.
I’d like a toolbar option but I don’t necessarily need it to start as soon as SSMS opens (just when the query window does). As SSMS doesn’t do builds the way VS does you can ignore that last option regarding modal UI.
Once your done with the wizard you’ll now have a project ready for tinkering. This is about as far as you’ll get on documented processes. From here on out its tribal knowledge, finding what is lucky enough to work between both VS and SSMS, and what you can figure out on your own (more on that tomorrow).
Converting this VS Add-In to an SSMS Add-In Project
The first thing you’ll need to do is to add a reference to the SqlPackageBase.dll. Right click the references node in the Solution Explorer and select Add.
This DLL can be found in the install folder of your version of SSMS in the Tools\Binn\ManagementStudio\ sub folder – find it by selecting browse on the Add Reference Dialog. This DLL gives us an interface for working with the document behind the query editor.
You’ll also need a reference to SqlWorkbench.Interfaces.dll in that same folder. This provides interfaces to the active window that we’ll need in the final dll, SQLEditors.
The final reference to SQLEditors.dll can be found in the same set of sub folders just two deeper in \Extensions\Application. This dll contains the actual connection information of the currently active window via CurrentlyActiveWndConnectionInfo.
With these three DLLs in your reference list go ahead and add four additional using statements at the top of your VS project. If you have experience working with Visual Studio or software development everything so far should have been pretty strait-forward.
Looking through the folders under Management Studio you’ve no doubt noticed that there are a whole bunch of DLLs / Assemblies available for use and reference. Further you might be asking yourself, how did I know to add the DLLs we did.
Discovering which ones have what information, interfaces, classes, or services your looking for is a topic entirely of it’s self. We’ll cover that tomorrow. At this point you’re project is set up and ready for development, debugging, and further exploration.