For years I’ve been using excel or dynamic SQL to generate SQL scripts to do data loads and updates and to generate IN predicates . For a small number of rows or values this is quicker than importing the required data into SQL Server. A few days ago I idly wondered how difficult it would be to create a SQL Server Management Studio extension to do these jobs directly in SSMS. The answer was quite hard but the results are quite satisfying.
As a simple use case I wanted to add an SSMS command that would take a list of values in the query editor and convert it to a query like: select * from table_name where column_name in (val1, val2… valN). Here’s what I came up with.
SSMS Extensions Development Environment
I wouldn’t have got anywhere with this project without this brilliant article which covers the absolute basics of getting an extension running. It’s a must read if you want to create an SSMS extension. I won’t discuss what Stefan covers. Just follow these instruction and you’re good to go. Definitely follow his advice to use Visual Studio 2015 – I tried and failed with 2017. Specifically I used Version 14 Update 3. Also note that I’ve only tested this with SSMS V17.9.
The only issue I found was when I started debugging my project I got the following error:
System.InvalidOperationException was unhandled
Message: An unhandled exception of type 'System.InvalidOperationException' occurred in PresentationCore.dll
Additional information: The visual tree has been changed during a 'VisualTreeChanged' event.
This is a bug that’s fixed in Visual Studio 2017 but I couldn’t find a fix for it in 2015. You can workaround it by disabling the UI Debugging Tools for XAML. This disables some of the debugging tools but this didn’t cause me any issues.
Accessing the SSMS Query Window Text
If you’ve followed the tutorial above you’ll have a VSIX project which includes a Command class. And this includes a MenuItemCallback method. This is the method that gets run when the menu item is selected. To keep things simple I’ll put all my code in this method even though that’s not good practice. The first thing I need is to access the selected text in the SSMS query window. This is pretty simple:
DTE2 dte = (DTE2)ServiceProvider.GetService(typeof(DTE)); extSelection ts = (TextSelection) dte.ActiveDocument.Selection; string t = ts.Text;
DTE2 is the top level automation object in the Visual Studio automation model. The project will already have a reference to the containing namespaces: EnvDTE and EnvDTE80. Note that each version of Visual Studio will include a different version of this namespace and each version maintains backward compatibility but includes additional functionality.
The GetService method is described in not a lot of detail in the MS documentation. Once I’ve got this reference I can easily get the currently selected text. Now just parse the selection and created a comma separated list to use as an IN predicate:
string[] crlf = new string[] { "\r\n" };
string[] values = t.Split(crlf, StringSplitOptions.RemoveEmptyEntries);
string query = "(";
foreach (string value in values) {
query += value + ",";
}
query = query.TrimEnd(',') + ")";
And then delete the existing selection and replace it with the list:
ts.Delete(); ts.Insert(query);
Accessing the SSMS Object Explorer Nodes
So far so fairly straightforward. Now I want to get access to table and column information so I can select a column in SSMS and use this information to create the query. I also need the column datatype so I know whether the values in the IN predicate need to be string literals or not. At first I thought I’d have to somehow query the data dictionary tables and build a form to do this. Then I realised everything is available in the Object Explorer if I can access it. This however was challenging.
The interface I need is IObjectExplorerService. This is part of the namespace: Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer which is in the following DLL: SqlWorkbench.Interfaces.dll. If you have SQL Server Management Studio installed you can find the DLL here: C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\SqlWorkbench.Interfaces.dll. So I need to add a reference to this DLL and this to the command class:
using Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer;
Now I can access this Interface in the same way as the DTE2 interface:
IObjectExplorerService objectExplorerService = (IObjectExplorerService)ServiceProvider.GetService(typeof(IObjectExplorerService));
Getting access the selected nodes in the object explorer is simple enough.
INodeInformation[] selNodes; int selNodesArraySize; objectExplorerService.GetSelectedNodes(out selNodesArraySize, out selNodes);
And I can have a look at what information the INodeInformation interface lets me see about the selected node.
string nodeProperties = selNodes[0].InvariantName
+ " " + selNodes[0].NavigationContext
+ " " + selNodes[0].UrnPath
+ " " + selNodes[0].Context
+ " " + selNodes[0];
VsShellUtilities.ShowMessageBox(
this.ServiceProvider,
nodeProperties,
title,
OLEMSGICON.OLEMSGICON_INFO,
OLEMSGBUTTON.OLEMSGBUTTON_OK,
OLEMSGDEFBUTTON.OLEMSGDEFBUTTON_FIRST);
If I select an item in the explorer and run my command the message box will show me the various properties. Here’s what I get for a table node and a column node:
| Property | Table Node | Column Node |
|---|---|---|
| InvariantName | dbo.courses | course_id |
| UrnPath | Server/Database/Table | Server/Database/Table/Column |
| Name | courses | course_id |
| NavigationContext | Server[@Name='SV1']/Database[@Name='bi']/Table[@Name='courses' and @Schema='dbo'] | "Server[@Name='SV1']/Database[@Name='bi']/Table[@Name='courses' and @Schema='dbo']/Column[@Name='course_id']" |
But none of the properties for a column give me the datatype – even though this is shown in the node text label on the tree. At this point I nearly gave up on this approach. But I found a clue in Nicholas Ross’s SSMS Schema Folders repo. (This is a great extension BTW). I admit I’m a bit out of my depth at this point but as I understand it the Object Explorer is implementing a Windows Forms Treeview Control. And you can access it using reflection. So if I add this to the command class:
using System.Reflection; using System.Windows.Forms;
I can access the TreeView like this:
var TreeProperty = objectExplorerService.GetType().GetProperty("Tree", BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.IgnoreCase);
TreeView tree = (TreeView) TreeProperty.GetValue(objectExplorerService, null);
And tree.SelectedNode.Text gets the node label which for a column includes the datatype. Hallelujah! Now I can finish building the query. I’ll get the column and table names from the TreeView and create a list of SQL Server datatypes that will require literal strings:
string column = tree.SelectedNode.Text;
string columnType = column.Substring(column.IndexOf('('));
string columnName = column.Substring(0, column.IndexOf('('));
string tableName = tree.SelectedNode.Parent.Parent.Text;
List<string> literalDataTypes = new List<string> { "char"
,"varchar"
,"text"
,"nchar"
,"nvarchar"
,"ntext"
,"date"
,"datetimeoffset"
,"datetime2"
,"smalldatetime"
,"datetime"
,"time" };
Then update the query build to add in “select * from tableName where columnName in ” and use literal strings for the values if required:
string literalColumn = literalDataTypes.Any(column.Contains) ? "'" : "";
TextSelection ts = (TextSelection) dte.ActiveDocument.Selection;
string t = ts.Text;
string[] crlf = new string[] { "\r\n" };
string[] values = t.Split(crlf, StringSplitOptions.RemoveEmptyEntries);
string query = "select * from " + tableName + " where " + columnName + "in (";
foreach (string value in values) {
query += literalColumn + value + literalColumn + ",";
}
query = query.TrimEnd(',') + ")";
In Conclusion
Having a go at building a SQL Server Management Studio extension was a lot of fun but I came close to giving up a few times. The documentation is very sparse and in a lot of cases is limited to autogenerated Microsoft interface descriptions. Maybe a better C# developer than me would have had an easier time of it. On the upside the debugging between SSMS and Visual Studio worked perfectly. At some point I’d like to revisit the UI options – placement of the command menus, making a context menu etc. And look at how to produce an extension that will work across SSMS versions. For now here’s the complete code of the MenuItemCallback. At some point I’ll put the whole project on GitHub.
private void MenuItemCallback(object sender, EventArgs e)
{
DTE2 dte = (DTE2)ServiceProvider.GetService(typeof(DTE));
IObjectExplorerService objectExplorerService = (IObjectExplorerService)ServiceProvider.GetService(typeof(IObjectExplorerService));
var TreeProperty = objectExplorerService.GetType().GetProperty("Tree", BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.IgnoreCase);
TreeView tree = (TreeView)TreeProperty.GetValue(objectExplorerService, null);
string column = tree.SelectedNode.Text;
string columnType = column.Substring(column.IndexOf('('));
string columnName = column.Substring(0, column.IndexOf('('));
string tableName = tree.SelectedNode.Parent.Parent.Text;
List<string> literalDataTypes = new List<string> { "char"
,"varchar"
,"text"
,"nchar"
,"nvarchar"
,"ntext"
,"date"
,"datetimeoffset"
,"datetime2"
,"smalldatetime"
,"datetime"
,"time" };
string literalColumn = literalDataTypes.Any(column.Contains) ? "'" : "";
TextSelection ts = (TextSelection) dte.ActiveDocument.Selection;
string t = ts.Text;
string[] crlf = new string[] { "\r\n" };
string[] values = t.Split(crlf, StringSplitOptions.RemoveEmptyEntries);
string query = "select * from " + tableName + " where " + columnName + "in (";
foreach (string value in values) {
query += literalColumn + value + literalColumn + ",";
}
query = query.TrimEnd(',') + ")";
ts.Delete();
ts.Insert(query);
}