I use SSMS (SQL Server Management Studio) almost daily. One of my clients has an encrypted field in a database resulting from its own .Net encryption mechanism and it was not always easy to deal with that field when one wanted to read from/write to that field directly from SSMS.
I am not talking about creating a new command I can use in a SQL query. I really mean to create a tool that will have a UI and that will execute some .Net code.
Creating an extension using VS2017 for SSMS 18
The downloadable code
This month’s solution contains both VB and C# projects. The solution was created using Visual Studio 2017.
Also, I am targeting SSMS 18.
If you are trying any other combination of VS and SSMS, be warned that you are running for trouble! The main reason is that SSMS is built on top of the VS shell and is very sensible to versions.
Visual Studio extension development module required
In order to create the project, you need to have the Visual Studio extension development module installed. If you are not sure to have it, launch the Visual Studio Installer tool and click the Modify button. Ensure the module shown in figure 1 is installed.
Figure 1: Installing VS Extension development tools
Creating the new extension project
Because an extension is a special assembly, you need to use a specific template when you want to build something as such. And because SSMS is built on the same shell as Visual Studio itself, we must use the VSIX Project template as shown in figure 2.
Figure 2: Creating the new project
Notice here that I have entered VSIX in the filter to reduce the list of available templates to only what makes sense.
After you clicked the OK button, you should see a Getting started page to help you start with the kind of project as shown in figure 3.
Figure 3: Getting started with an extension project
As pointed to in figure 3, this file is actually part of your project so you can always get back to it if you want to see the links to Channel or other resources.
Adding a command to our project
The purpose of an extension is to be added to an existing shell. We need to add something to the menu to make it available to the user of the shell. This is the purpose of a command.
Figure 4: Adding a custom command to our project
Add a file to your project (Project, Add New Item…) and select the Custom Command template found under Extensibility as shown in figure 4 and give it a name (mine are Command1 and Command1VB depending on the language).
Now to prove it is working, open the Command1 (or Command1VB) file that was just created and find the Execute method in it. Place a breakpoint on the first line of this method and hit F5 (or whatever you do to Start Debugging). Don’t be surprised to see another instance of Visual Studio starting (might take some time the first time you run an extension project). This new instance will show “Experimental Instance” in its title bar.
After the instance of VS has fully started, look under the Tools menu. You should find a new menu item to the name of your command. And don’t be afraid to click on it and discover that the debugger will effectively stop on the breakpoint. If you continue running, you will see a message box triggered by the command generated by the template.
Figure 5: The new extension is now visible
We have just proven that we can create an extension and that we can also magically debug it. Pretty neat but we are not done yet!
Adding a dialog window to our project
You will surely want to add a dialog window so you can interact with your new tool. Add a file to your project (Project, Add New Item…) and select the Custom Tool Window template found under Extensibility as shown in figure 6 and give it a name (mine are ToolWindow1 or ToolWindow1VB depending on the language).
Figure 6: Adding a Custom Tool Window to your project
This new dialog won’t show magically. It needs to be shown when an action is done. As a matter of fact, we have such an action that we have already tested previously. The Execute method of the command of the previous step is exactly what we need.
Find the Execute method of your Custom Tool Window Command file added in figure 6 (mine is called ToolWindow1Command). Copy this Execute method.
Now find the Execute method of your command, the one in which we put a breakpoint to see that the debugger was actually caught. Delete or comment out this Execute method. Finally, paste the other Execute method.
The Execute method should read like this:
Private Sub Execute(sender As Object, e As EventArgs) ThreadHelper.ThrowIfNotOnUIThread() '' Get the instance number 0 of this tool window. This window Is single instance so this instance '' Is actually the only one. '' The last flag Is set to true so that if the tool window does Not exists it will be created. Dim window As ToolWindowPane = Me.package.FindToolWindow(GetType(ToolWindow1VB), 0, True) If window Is Nothing OrElse window.Frame Is Nothing Then Throw New NotSupportedException("Cannot create tool window") End If Dim windowFrame As IVsWindowFrame = window.Frame Microsoft.VisualStudio.ErrorHandler.ThrowOnFailure(windowFrame.Show()) End Sub
private void Execute(object sender, EventArgs e) { ThreadHelper.ThrowIfNotOnUIThread(); // Get the instance number 0 of this tool window. This window is single instance so this instance // is actually the only one. // The last flag is set to true so that if the tool window does not exists it will be created. ToolWindowPane window = this.package.FindToolWindow(typeof(ToolWindow1), 0, true); if ((null == window) || (null == window.Frame)) { throw new NotSupportedException("Cannot create tool window"); } IVsWindowFrame windowFrame = (IVsWindowFrame) window.Frame; Microsoft.VisualStudio.ErrorHandler.ThrowOnFailure(windowFrame.Show()); }
If you run this project again, you should now see your dialog being shown when you click your “Invoke Command1” menu item from the Tools menu of the experimental Visual Studio instance.
And now integrating into SSMS
We have now created the extension but the whole purpose was to bring it to SSMS. This is what we will be achieving here.
Open your file explorer and navigate to the folder containing the extensions for SSMS. By default, this folder sits at C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Extensions. In this folder, create a sub-folder specificallyyou’re your own extension (I named mine DemoSSMSExtensionCS). Keep that open.
Now open another file explorer and navigate to the bin\debug folder of your project. There might be a lot of files in there but if you just followed the steps here and don’t need any extra libraries, you only need to do a simple copy and paste of 4 files (replace DemoSSMSExtensionCS with your own project name):
After having copied these 4 files, open SSMS and look under the Tools menu. The same “Invoke Command1” should be found and clicking on it should display the dialog you have created.
Figure 7 shows my demo extension containing 2 textboxes with some very basic transformation.
Figure 7: The demo extension in action
Conclusion
Building extensions for your favorite tools is not too hard. You can get many benefits and be more productive building your own extensions instead of waiting for a guy in his basement or a company to build one for you. Give it a try!