Click here to Skip to main content
14,828,012 members
Articles / Database Development / SQL Server
Article
Posted 25 Jan 2016

Stats

34.6K views
1.7K downloads
22 bookmarked

Create SQL Server Management Studio 12 Addin

Rate me:
Please Sign up or sign in to vote.
4.74/5 (14 votes)
25 Jan 2016CPOL7 min read
Develop your own SSMS ObjectExplorer\CommandBar menus, within SQL Server 12

Download Addin Project

Download SQL Script\Data

Introduction

Like most DBA's I find myself repeating the same SQL statements over-and-over again. There has to be an easier way to speed up your SQL productivity. So, instead of typing that "SELECT COUNT(*) FROM XYZ_Table", I decided to develop my own SSMS (12) custom addin, using Visual Studio 13.

Background

Wouldn't it be so much easier to right click a table and get the row count or select a column and find the duplicate values in that table, or run generic common SQL statements against the Master database, all from mouse clicks instead of typing, cutting\pasting or loading a saved SQL query from file and executing it.

Prerequisites

What We Are Looking to Achieve

Below is an example of the custom table context menu that the addin will create (Custom SQL & Script Full Schema). You will also see logic that determines if a user has right clicked on a table or a column and display the appropriate context menu for that object. Below is the context menu for a table. For the column context menu, you are able to find duplicate values in that column.

Image 1

Below you can see that I have added a new context menu option for columns only. In this example, I find all duplicate values within a certain column. This example will be explained later with another screenshot and the explanation of the SQL\code behind it.

Image 2

We will also create menu commands to execute global SQL statements. For example below, we are using the "Tools" menu to execute a "Kill Transactions" SQL statement. I have designed the SQL in such a way, it doesn't fully execute the SQL, but it has some lines commented out. Which you can uncomment and run when you are comfortable with the SQL.

Image 3

Create Your Addin Project

There are a few screens to go through before you get a skeleton Addin project. Below you can see that you will mostly default your way through. Just make sure to install the Visual Studio 13 SDK, as this contains the new Addin project template used in Visual Studio 13.

Image 4

Image 5

Image 6

Image 7

Image 8

Image 9

Image 10

Project Settings and Deployment Gotchas

Breaking Changes Between SSMS Versions

In the past, when you had to develop an Addin for SSMS 2005 and then develop the same Addin code for SSMS 20088\R2, your code would not compile, as a number of properties\methods were deprecated and not backward compatible. With SSMS 12+, Microsoft have re-engineered (albeit using Visual Studio 10) the objects behind the Addin. Thus, making it more compatible going forward...I would take this with a pinch of salt, but the conflicts should be dramatically reduced.

Deployment Folders

When you want to test your addin within SQL Server, you will need to let SQL Server know that it has to attach the addin when it starts. To do this, simply copy your .Addin file into one of the following paths. Option 2 below, will attach the addin for all users of SQL Server. If the folder(s) below don't exist, then you just need to create the folder structure.

  1. C:\Users\UserName\AppData\Roaming\Microsoft\MSEnvShared\Addins
  2. C:\ProgramData\Microsoft\MicrosoftSQL Server Management Studio\11.0\Addins

Assembly References

The area I found most problematic, was the assemblies. As there are similar named and conflicting versions. But the list below should cover most of your Addin tasks. You will be able to see in the project the paths to the assembly (properties of assembly) and use that as a guide to finding the assembly within your environment.

Image 11

Project Properties

Right click your project and select properties from the context menu. Set the external application that will startup your addin, to the path of your SQL Server Management Studio executable. Something along the line of:

C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe

Remove the "Command line arguments" and "Working directory" entries.

Image 12

Configure Addin File XML

Before you can deploy or test your Addin, you will need to configure it. Basically, there are only three elements you need to update.

  1. Name - change this to Microsoft SQL Server Management Studio
  2. Version - insert an asterisk, as this will cater for various versions of SQL Server (as there will be CTP versions etc.)
  3. Assembly - Enter the path to your addin assembly (debug when testing and release when you are ready to deploy)

Image 13

Turn Off PInvoke Exception When Debugging

When debugging, the PInvoke error will popup, this is not an issue at runtime, its because of the Long and Int conversions between C++ and C# are causing a conflict (debug only). So, to stop the popups, simply turn the PInvoke of within Visual Studio.

Click on Debug.Exceptions, and scroll down to the Pinvoke Stack Imbalance and uncheck it.

Image 14

Image 15

Project Structure

The addin has a very simple project structure, a main (connect.cs) class, a controller class, two menu item classes and the SSMSAdin.addin XML file that we will deploy into the Addin folder. There is a partial class to separate out the events and not clutter up the main Connect.cs class, as we will only be using a couple of the main events.

Image 16

Code Explanation

Create ObjectExplorer Context & CommandBar Menuitems

One of the IDTExtensibility2 member methods you will implement is the OnConnection. This is the starting point of your Addin. The snippet of code below, creates the new Tools menuitems and an event handler for when the ObjectExplorer action has changed (i.e. clicking on a table or a column - this event will capture that action), thus allowing you to control what context menu should be displayed for a particular ObjectExploer tree item.

C#
public void OnConnection(object application, ext_ConnectMode connectMode, object addInInst, ref Array custom)
        {
            applicationObject = (DTE2)application;
            addInInstance = (AddIn)addInInst;

            try
            {
                ContextService contextService = (ContextService)ServiceCache.ServiceProvider.GetService(typeof(IContextService));
                contextService.ActionContext.CurrentContextChanged += ActionContextOnCurrentContextChanged;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

            if (connectMode == ext_ConnectMode.ext_cm_UISetup)
            {
                object[] contextGUIDS = new object[] { };
                Commands2 commands = (Commands2)applicationObject.Commands;
                string toolsMenuName = "Tools";
                
                //Find the MenuBar command bar, which is the top-level command bar holding all the main menu items:
                Microsoft.VisualStudio.CommandBars.CommandBar menuBarCommandBar = ((Microsoft.VisualStudio.CommandBars.CommandBars)applicationObject.CommandBars)["MenuBar"];

                //Find the Tools command bar on the MenuBar command bar:
                CommandBarControl toolsControl = menuBarCommandBar.Controls[toolsMenuName];
                CommandBarPopup toolsPopup = (CommandBarPopup)toolsControl;

                //This try/catch block can be duplicated if you wish to add multiple commands to be handled by your Add-in,
                //  just make sure you also update the QueryStatus/Exec method to include the new command names.
                try
                {
                    //Add a command to the Commands collection:
                    cmdKillTrans = commands.AddNamedCommand2(addInInstance, "KillTrans", "Kill Transactions", "List all transactions that can be stopped", true, 9, ref contextGUIDS, (int)vsCommandStatus.vsCommandStatusSupported + (int)vsCommandStatus.vsCommandStatusEnabled, (int)vsCommandStyle.vsCommandStylePictAndText, vsCommandControlType.vsCommandControlTypeButton);

                    //Add a control for the command to the tools menu:
                    if ((cmdKillTrans != null) && (toolsPopup != null))
                    {
                        cmdKillTrans.AddControl(toolsPopup.CommandBar, 1);
                    }
                }
                catch (System.ArgumentException ex)
                {
                    MessageBox.Show(ex.Message);
                    DebugMessage(ex.Message);
                }

                //This try/catch block can be duplicated if you wish to add multiple commands to be handled by your Add-in,
                //  just make sure you also update the QueryStatus/Exec method to include the new command names.
                try
                {
                    //Add a command to the Commands collection:
                    cmdServerInfo = commands.AddNamedCommand2(addInInstance, "ServerInfo", "Server Information", "List server\\product information", true, 5, ref contextGUIDS, (int)vsCommandStatus.vsCommandStatusSupported + (int)vsCommandStatus.vsCommandStatusEnabled, (int)vsCommandStyle.vsCommandStylePictAndText, vsCommandControlType.vsCommandControlTypeButton);

                    //Add a control for the command to the tools menu:
                    if ((cmdServerInfo != null) && (toolsPopup != null))
                    {
                        cmdServerInfo.AddControl(toolsPopup.CommandBar, 2);
                    }
                }
                catch (System.ArgumentException ex)
                {
                    MessageBox.Show(ex.Message);
                    DebugMessage(ex.Message);
                }
                 //This try/catch block can be duplicated if you wish to add multiple commands to be handled by your Add-in,
                //  just make sure you also update the QueryStatus/Exec method to include the new command names.
                try
                {
                    //Add a command to the Commands collection:
                    commandBackUp = commands.AddNamedCommand2(addInInstance, "BackupInfo", "Backup Information", "List backup information", true, 8, ref contextGUIDS, (int)vsCommandStatus.vsCommandStatusSupported + (int)vsCommandStatus.vsCommandStatusEnabled, (int)vsCommandStyle.vsCommandStylePictAndText, vsCommandControlType.vsCommandControlTypeButton);

                    //Add a control for the command to the tools menu:
                    if ((commandBackUp != null) && (toolsPopup != null))
                    {
                        commandBackUp.AddControl(toolsPopup.CommandBar, 3);
                    }
                }
                catch (System.ArgumentException ex)
                {
                    MessageBox.Show(ex.Message);
                    DebugMessage(ex.Message);
                }

            }
        }

ObjectExplorer Event Handler

The event below id setup in ther OnConnection method above, thus we can capture each ObjectExplorer change. We make use of the static variables to stop the context menuitems from being created multiple times.

NB: UserTables is the InvariantName for Tables.

C#
private void ActionContextOnCurrentContextChanged(object sender, EventArgs e)
        {
            try
            {
                INodeInformation[] nodes;
                INodeInformation node;
                int nodeCount;
                IObjectExplorerService objectExplorer = (ObjectExplorerService)ServiceCache.ServiceProvider.GetService(typeof(IObjectExplorerService));

                objectExplorer.GetSelectedNodes(out nodeCount, out nodes);
                node = nodeCount > 0 ? nodes[0] : null;

                if (node != null)
                {
                    if (node.Parent.InvariantName == "UserTables")
                    {
                        if (!IsTableMenuAdded)
                        {
                            _tableMenu = (HierarchyObject)node.GetService(typeof(IMenuHandler));
                            SqlTableMenuItem item = new SqlTableMenuItem(applicationObject);
                            _tableMenu.AddChild(string.Empty, item);
                            IsTableMenuAdded = true;
                        }
                    }
                    else if (node.Parent.InvariantName == "Columns")
                    {
                        if (!IsColumnMenuAdded)
                        {
                            _tableMenu = (HierarchyObject)node.GetService(typeof(IMenuHandler));
                            SqlColumnMenuItem item = new SqlColumnMenuItem(applicationObject);
                            _tableMenu.AddChild(string.Empty, item);
                            IsColumnMenuAdded = true;
                        }
                    }
                }
            }
            catch (Exception ObjectExplorerContextException)
            {
                MessageBox.Show(ObjectExplorerContextException.Message);
            }
        }

Disconnecting

We need to perform house-keeping when disconnecting from SQL Server and remove the newly added menu items. They will be loaded again when SQL Server starts, as SQL Server looks in the Addin folder.

We remove them because, you can manually go in and delete the Addin file and they will still be hanging around in SQL Server (for ever!!!).

C#
public void OnDisconnection(ext_DisconnectMode disconnectMode, ref Array custom)
        {
            switch (disconnectMode)
            {
                case ext_DisconnectMode.ext_dm_HostShutdown:
                case ext_DisconnectMode.ext_dm_UserClosed:
                    if ((cmdServerInfo != null))
                    {
                        cmdServerInfo.Delete();
                    }
                    if ((commandBackUp != null))
                    {
                        commandBackUp.Delete();
                    }
                    if ((cmdKillTrans != null))
                    {
                        cmdKillTrans.Delete();
                    }                 
                    break;
            }
        }

Context Menu Click Events

The click event for the duplicate context menu item will retrieve the database, table and column name and build up the SQL statement. Display them in a new query window and execute it.

C#
private void Duplicate_Click(object sender, EventArgs e)
       {
           ToolStripMenuItem item = (ToolStripMenuItem)sender;
           bool generateColumnNames = (bool)item.Tag;

           Match match = columnRegex.Match(this.Parent.Context);
           if (match != null)
           {
               string columnName = match.Groups["Column"].Value;
               string tableName = match.Groups["Table"].Value;
               string schema = match.Groups["Schema"].Value;
               string database = match.Groups["Database"].Value;
               string connectionString = this.Parent.Connection.ConnectionString + ";Database=" + database;
               string sqlStatement = string.Format(SSMSAddin.Properties.Resources.SQLDuplicateColumnData, columnName, tableName);

               this.dteController.CreateNewScriptWindow(new StringBuilder(sqlStatement)); // create new document

               this.applicationObject.ExecuteCommand("Query.Execute"); // get query analyzer window to execute query
           }
       }

Image 17

We can see that there is in deed duplicate artists, if we do a select from the table - when we execute the above duplicate query, it will leave one unique artist.

Image 18

Testing\Running Addin Within SQL Server 12

Because we have set SSMS as the external application to test with (project properties), SSMS will startup automatically and we can step through (F10) the debugger.

NB: Remember to copy your .Addin file to one of the deployment folders, and the XML element Assembly references our Debug assembly.

Image 19

Below, are examples of each of the menu items we have created.

Table Context Menu

Table Count

Image 20

Generate Insert Script

Image 21

Column Context Menu

As we have seen in the previous section, there is a column context menu item. Notice the commented out lines, these can be uncommented out when you are confident in executing the SQL statement.

Image 22

Global - Command Bar Menu

From the Tools menu, we can see the newly created (global acting) SQL statements. I have included some common SQL statements here, like Killing Transactions, Server Information and displaying Backup Information.

Image 23

Below, I have executed the Server Information menu item, this will provide general information regarding your SQL Server.

Image 24

SQL Server 12 Version Details

Below are the version details of the SQL Server 12 I used to develop the Addin against (standard enough).

Image 25

Points of Interest

Now that you have seen how to reference tables or columns, it is relative easy to extend your custom Addin to perform more complex SQL statements. For example, when two columns are selected, there could be an option to select everything based on a join (as you can execute a SQL statement to find the FK's and then dynamically build up the SQL).

Useful Links

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Bert O Neill
Architect
Ireland Ireland
No Biography provided

Comments and Discussions

 
QuestionVS2015 Pin
Member 1407284619-Dec-18 5:26
MemberMember 1407284619-Dec-18 5:26 
BugDid anyone download or create like this poject and it works for him? Pin
Member 1350459020-Nov-17 7:38
MemberMember 1350459020-Nov-17 7:38 
Questionoutput for questions:The addin project does not work for me Pin
Member 135045905-Nov-17 9:54
MemberMember 135045905-Nov-17 9:54 
QuestionThe addin project does not work for me Pin
Member 135045905-Nov-17 9:52
MemberMember 135045905-Nov-17 9:52 
QuestionSSMS 17.2 Pin
Member 174850411-Oct-17 1:50
MemberMember 174850411-Oct-17 1:50 
AnswerRe: SSMS 17.2 Pin
Bert O Neill1-Nov-17 23:25
MemberBert O Neill1-Nov-17 23:25 
QuestionVS2015? Pin
Eric Moreau13-Mar-16 9:40
MemberEric Moreau13-Mar-16 9:40 
AnswerRe: VS2015? Pin
Bert O Neill21-Mar-16 3:20
MemberBert O Neill21-Mar-16 3:20 
SuggestionSQL 2014? Pin
Member 1031380229-Jan-16 9:34
MemberMember 1031380229-Jan-16 9:34 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.