Navigation

	Home Page
	Index
	All Pages

Quick Search
Advanced Search »

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

Extend Functionality in SQL Server 2005 Management Studio with Add-ins

This article is reproduced from the following URL: Extend Functionality in SQL Server 2005 Management Studio with Add-ins, an article written by Ameet Phadnis, and published on Aug 7, 2007.

Table of Contents [Hide/Show]    Introduction    Basic Concept    Assemblies for SQL Server Management Studio    Implementing IDTExtensibility2          OnConnection          Declarations          Declaring SQLObjectNode    Context Menu Addition Class          GetInstanceOnSelection          Getting the Selected Object Node          SQLObjectNode Constructor          Adding a Menu Item    The Menu class to perform actions          Invoke Method    Copying the contents to New Query Window          Creating a New Query Window          Complete Code    Debugging    Registry    Setup Package

{outline||Part <1> - ||Listing

.<1> - }

Introduction¶

My entire career I have been looking for ways to automate the development process for developers. The urge to find new solutions might be coming from my laziness to keep on doing the same stuff over and over again. Around 4 years ago I had designed a way to create Stored Procedures on the SQL Server database. You can find more information on the Stored Procedure Generator by viewing my article titled Speed up with SQL Stored Procedure Generator. But that process was too manual. I had to keep in mind what kind of parameters I needed to pass to get the desired results. Again, I was tired of that too. So, then I started thinking about writing a Windows.NET program to resolve this issue.

By then Microsoft had released the SQL Server 2005. This product included a GUI called SQL Server Management Studio (SSMS). The whole idea behind SSMS was that it was based on Visual Studio 2005. So, I started wondering whether I could write an Add-In as we do for Visual Studio 2005. I was so wrong with this assumption. I did lot of research and most of them pointed out, "Microsoft doesn't support SQL Server 2005 add-ins." Then they came with an announcement that they would support it fully in SQL Server 2008. So, I started digging more. My co-workers and I were determined to write a Stored Procedure Generator Windows Application. But I was determined to write a SQL 2005 add-in which would not require the user to enter the database connection string. If any of the readers have used all the tools available for SQL Stored Procedure Generators then you might have realized that you have to enter the database connection string in order to get table information.

My perseverance paid off and one day I stumbled onto a code on the web. I have not been able to find it any more. It was in a blog entry by some Russian genius. But there was no code explanation. So, I had to do some trial and error while we started coding for our SQL SP Generator. So, here I am trying to explain the whole process on how you can extend the functionality in SQL Management Studio with add-ins. Please keep in mind that this applies only to SQL Server Management Studio 2005 and the process might change with SQL Server Management Studio 2008.

Basic Concept¶

The basic concept of the SSMS add-in is the same as Visual Studio add-in. Before I get into the concept behind SSMS Add-In, I would like to present some basic concepts of the Visual Studio Add-In.

The steps involved in creating the Visual Studio Add-In are as follows.

1. Click on Create Project in Visual Studio 2005.

2. The template for Visual Studio 2005 Add-In is located under Other Project Types > Extensibility > Visual Studio Add-In.

Figure 1

3. Provide a name for the project.

4. The six-step wizard will walk you through creating the Add-in.

Figure 2

Figure 3

Figure 4

Figure 5

Figure 6

Figure 7

Please note that the Wizard has created a class called Connect which implements IDTEExtensibility2. (I will explain the Connect class in a little bit.) Also, take a look at the References tab and you will notice the following references have been added.

• EnvDTE
• EnvDTE80
• Extensibility
  

All the above steps are also the same steps for creating an add-in for SQL Server Managment Studio.

Assemblies for SQL Server Management Studio¶

The .NET platform or any product comes with tons and tons of assemblies. Some of the assemblies are stored with the product exe. The first task while writing the add-in is to determine the assemblies we will need. The assembly information (as per my knowledge) is not documented anywhere on the Microsoft website. Based on your requirements, you will have to do some research to find the assemblies that are required for your application.

• The assemblies required for SSMS are located in the following two folders.
  1. C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\
  2. C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\
     

• The assembly locations can be guessed looking at the folder structure. Anything related to IDE will be located under C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\.
  

• In order to use SQL Management Objects (SMO) we need to include the following assemblies: Microsoft.sqlServer.ConnectionInfo and Microsoft.sqlServer.RegSvrEnum. (SMO are objects designed for programmatic management of Microsoft SQL Server. You can use SMO to build customized SQL Server Management applications.)
  

• In order to enable right clicking on the database or table name in the object explorer, use the assembly called objectexplorer.
  

• In order to display a stored procedure in a new Query Window, reference the following assemblies.
  1. Microsoft.sqlserver.sqltools.VSIntegration
  2. sqlWorkbench.Interfaces
     

The assembly references will totally depend on your application.

Implementing IDTExtensibility2¶

As mentioned earlier, the add-in creates a class that implements IDTExtensibility2. Implement the following methods.

OnConnection - Receives notification that the Add-in is being loaded. The parameters are:

• Application - The Root object of the host application
• connectMode - How the add-in is loaded
• addInInst - Represents the Add-In
  

OnDisconnection - As the name suggests, the Add-in is being unloaded. The parameters are:

• disconnectMode - How the add-In is unloaded
• Custom - Array of parameters specific for the host application
  

OnAddInsUpdate - Receives notification that the collection of Add-Ins has changed. Parameters are:

• Custom - Array of parameters specific to the host application
  

OnStartupComplete - Receives notification that the host application has completed loading. Parameters are:

• Custom - Array of parameters specific to the host application
  

OnBeginShutdown - Receives notification that the host application is being unloaded.

• Custom - Array of parameters specific to the host application.
  

For our application we are mainly concerned with OnConnection. As we were writing the Add-In for recognizing the database object or table object we needed to have an event handler that would recognize the object that was selected. So, we wrote an event handler called OnSelectionChanged. This event handler was provided to handle the IObjectExplorerEventProvider's SelectionChanged event. So, now our code for OnConnection looks like:

OnConnection¶

_ApplicationObject = Ctype(Application, DTE2)
_addInInstance = Ctype(AddINInst, AddIn)
_ObjectExplorer = ServiceCache.GetObjectExplorer
Dim typeOfProvider as Type = GetType(IObjectExplorerEventProvider)
Dim provider as IObjectExplorerEventProvider = _
Ctype(ObjectExplorer.GetService(typeOfProvider), IObjectExplorerEventProvider)
AddHandler provider.SelectionChanged, AddressOf OnSelectionChanged

The _ApplicationObject, _addInINstance, and _ObjectExplorer are declared in the class as follows:

Declarations¶

Private _ApplicationObject as DTE2
Friend _addINInstance as AddIn
Private _ObjectExplorer as IObjectExplorerService

In our SQL SP Generator application, we have declared another Friend class called SQLObjectNode. So, we can call the functions through the SelectionChanged event handler that is declared in the Connect class, like this.

Declaring SQLObjectNode¶

Dim objSQLDBNode as SQLObjectNode = SQLObjectNode.GetInstanceOnSelection(Me)

This is all we need to do on this class. This class just implements the IDTExtensibility2 and lets the application know what to do in it.

Context Menu Addition Class¶

The above class that implements IDTExtensibility2 instructed the host application to go to the GetInstanceOnSelection method under the SQLObjectNode object. Now we need to instruct the application to add visual items to certain objects. In our SQL SP Generator application we wanted it to be added to the Database object or Table Object. We also need to make sure the context menu is added only once to the object. The following code is implemented in GetInstanceOnSelection.

GetInstanceOnSelection¶

Friend Shared Function GetInstanceOnSelection( _
  ByVal objSQLServer2005AddIn As SQLServer2005AddIn) As SQLObjectNode

    Dim node As INodeInformation
    Dim sz As Int16
    Dim selNodes() As INodeInformation
    objSQLServer2005AddIn.ObjectExplorer.GetSelectedNodes(sz, selNodes)

    If sz > 0 Then

        node = selNodes(0)

        If Not node Is Nothing Then

            Dim context As String = node.Context
            Dim tnode As SQLObjectNode

            If (objSQLServer2005AddIn._objNodesDict.ContainsKey(context)) Then

                tnode = objSQLServer2005AddIn._objNodesDict(context)

                Else

                    tnode = New SQLObjectNode(node, objSQLServer2005AddIn)
                    objSQLServer2005AddIn._objNodesDict.Add(context, tnode)

                End If

                Return tnode

            End If

        End If

    End If

    Return Nothing

End Function

In the above example, in order to get a handle on the selected object node, you use the following method.

Getting the Selected Object Node¶

objSQLServer2005AddIn.ObjectExplorer.GetSelectedNodes(sz, selNodes)

The SelNodes, passed as a reference, is an array of the type Microsoft.SQLServer.Management.UI.VSIntegration.ObjectExplorer.INodeInformation. So, if the user has selected the database Northwind in the object explorer then the selNodes will contain reference to that object.

The remaining code checks whether the context key was already added to the dictionary. If not then we create the SQLObjectNode object and add the context key to the dictionary. When the code creates a new SQLObjectNode (using tnode = New SQLObjectNode(node, objSQLServer2005AddIn)) it calls the constructor event for the object, which looks like this.

SQLObjectNode Constructor¶

Private Sub New(ByVal node As INodeInformation, _
ByVal objSQLServer2005AddIn As SQLServer2005AddIn)
  Me._objSQLServer2005AddIn = objSQLServer2005AddIn
 
  If node Is Nothing Then

    _isTable = False

  Else

    Dim context As String = node.Context

    If context.Contains("Server[@Name='") AndAlso _
        context.Contains("']/Database[@Name='") AndAlso _
        context.Contains("/Table[@Name") AndAlso _
        context.Contains("@Schema") AndAlso _
        node("Name") IsNot Nothing AndAlso _
        node("Schema") IsNot Nothing AndAlso _
        node("Name").ToString().Trim() <> "" AndAlso _
        node("Schema").ToString().Trim() <> "" Then

      _isTable = True
      _isDB = False

    ElseIf context.Contains("Server[@Name='") AndAlso _
        context.Contains("']/Database[@Name='") AndAlso _
        node("Name") IsNot Nothing AndAlso _
        node("Name").ToString().Trim() <> "" Then

      _isDB = True
      _isTable = False

    End If
 
  End If

  If _isTable Then

    _name = node("Name").ToString()
    _schema = node("Schema").ToString()
    _database = node.Parent.Name
    _cnnStr = "Database=" + database + ";" + node.Connection.ConnectionString

    If Not objSQLServer2005AddIn.TableMenuRegistered Then

      AddMenuItems(CType(node.GetService(GetType(IMenuHandler)), _
        IMenuHandler), True)
      objSQLServer2005AddIn.TableMenuRegistered = True

    End If

  ElseIf _isDB Then

    _name = node("Name").ToString
    _database = _name
    _cnnStr = "Database=" + database + ";" + node.Connection.ConnectionString

    If Not objSQLServer2005AddIn.DBMenuRegistered Then

      AddMenuItems(CType(node.GetService(GetType(IMenuHandler)), _
                    IMenuHandler), False)

      objSQLServer2005AddIn.DBMenuRegistered = True

    End If

  End If

End Sub

The above example just looks at whether the node context is a table or database. The way it figures it out is by looking at the text in node.context.

Object Type	Example of node.context
database	"Server[@Name='ETGPHADNIS\SQL2005']/Database[@Name='SQLReportApplication']"
table	"Server[@Name='ETGPHADNIS\SQL2005']/Database[@Name='SQLReportApplication']/Table[@Name='tblApplication' and @Schema='dbo']"

In this example I have selected the table called tblApplication under the SQLReportApplication database. You can experiment with the other objects using the above example. Now we need to finally add the menu item to those objects. The following code will assist you in adding your menu item.

Adding a Menu Item¶

Dim objMenuItem As New SPGeneratorMenu(objSQLServer2005AddIn)
objMenuItem.Text = strItemName
Try
    Dim testing As HierarchyObject = _
            DirectCast(menuItemHandler, HierarchyObject)
 testing.AddChild(strItemName, objMenuItem)
Catch ex As Exception
    Throw ex
End Try

In the above code I am instantiating my object which has all the business rules for creating the Stored Procedures. I assign the Text property to the name that should appear on the menu item. After that I get a handle to the context menu object for the selected node and add my own menu item to it.

The Menu class to perform actions¶

This is the final class that you will need to actually perform the desired action. This menu item class is inherited from the ToolsMenuItemBase. In the previous section you saw how we added the SPGeneratorMenu object to the context menu. The SPGeneratorMenu class has been inherited from ToolsMenuItemBase. The only method that needs to be overridden is Invoke. The code for Invoke in our scenario is below.

Invoke Method¶

Dim tnode As SQLObjectNode = _
SQLObjectNode.GetInstanceOnSelectedNode(_objSQLServer2005AddIn)
If Not tnode Is Nothing Then
  Dim frmGenerator As frmSPGenerator
  Try
 
  If tnode.IsDB Then
    frmGenerator = New frmSPGenerator(tnode.cnnStr, tnode.database, _
        False, _objSQLServer2005AddIn)
  Else
    frmGenerator = New frmSPGenerator(tnode.cnnStr, tnode.Name, _
        True, _objSQLServer2005AddIn)
  End If
  frmGenerator.ShowDialog()
  Catch ex As Exception
  MessageBox.Show(ex.Message)
  Finally
  frmGenerator = Nothing
  If Not sw Is Nothing Then
    sw.Dispose()
    sw = Nothing
  End If
  End Try
End If

The above code only displays the frmSPGenerator form which is a Wizard to create Stored Procedures.

Copying the contents to New Query Window¶

The above sections explained how you could add a context menu and perform the desired operation. In a real life scenario, most of the times you will need to interact with the editor of the SSMS. In our SQL SP Generator we had to create the stored procedures and then copy those stored procedures to the Query Window. Even though it sounds complex, it's not. In order to create a new Query Window, you just need to do the following.

Creating a New Query Window¶

Dim scriptFactory As Editors.IScriptFactory = ServiceCache.ScriptFactory
scriptFactory.CreateNewBlankScript(Editors.ScriptType.Sql, _
    scriptFactory.CurrentlyActiveWndConnectionInfo.UIConnectionInfo, Nothing)

The above code will create a new blank query document for you. Now you can use the VS.NET objects to control the context in this query window. You will be utilizing the EnvDTE.TextDocument class. So, my complete code looks like:

Complete Code¶

Dim objEnvDocument As EnvDTE.Document
Dim objTextDocument As EnvDTE.TextDocument
Dim objEndPoint As EnvDTE.EditPoint
Dim objDBProcedures As New DBProcedures
 
Dim scriptFactory As Editors.IScriptFactory = ServiceCache.ScriptFactory
scriptFactory.CreateNewBlankScript(Editors.ScriptType.Sql, _
scriptFactory.CurrentlyActiveWndConnectionInfo.UIConnectionInfo, Nothing)
 
objEnvDocument = _objSQLServer2005AddIn.ApplicationObject.ActiveDocument
objTextDocument = objEnvDocument.Object()
objEndPoint = objTextDocument.EndPoint.CreateEditPoint
objEndPoint.Insert(objDBProcedures.GetCopyrightComments)
objEndPoint.Insert(objDBProcedures.GetDisclaimer)
 
objEnvDocument = _objSQLServer2005AddIn.ApplicationObject.ActiveDocument
objTextDocument = objEnvDocument.Object()
objEndPoint = objTextDocument.EndPoint.CreateEditPoint
objEndPoint.Insert(strProcedure.ToString)

The Insert method will push all the text you need to the current active document starting at the EditPoint you created.

Debugging¶

To setup the application for debugging, go into Project > Properties and set the Start Action to Start external program and point it to C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe.

Figure 8

Registry¶

The app will not work until you make some registry entries in the following registry key.

HKEY_LOCAL_MACHINE
  \SOFTWARE
    \Microsoft
      \Microsoft SQL Server
        \90
          \Tools
            \Shell
              \Addins
                \<your Add-in class>

My registry entry looks like the figure below.

Figure 9

Setup Package¶

You can create a setup package for your add-in the same way you create setup packages for your other applications. Make sure that you exclude the assemblies that are part of the SSMS. Also, you can use the Setup Package's registry window to define the registry entry you want. The Setup package explanation is beyond the scope of this article.