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.

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:

Click on Create Project in Visual Studio 2005.

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

Figure 1

Please provide a name for the project.

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

Wizard Step 1: Select a Programming Language. You can select your preferred language. For my project I will select Visual Basic.

Figure 2

Wizard Step 2: Select an application host. The options are to create Microsoft Visual Studio 2005, Microsoft Visual Studio 2005 Macros or both. The basic idea is whether you want to create an Add-In or a Macro. Keep in mind the Macros can be converted to an Add-In.

Figure 3

Wizard Step 3: Enter a Name and Description. You can provide a unique name and description for the add-in.

Figure 4

Wizard Step 4: Choose Add-In Options. The options are whether to create command bar UI or Add-In when the host application starts and the add-in will never put up modal UI. The options are self explanatory.

Figure 5

Wizard Step 5: Choosing "Help About" Information. If you want to have your company's information in he About box then you can select this option.

Figure 6

Wizard Step 6: Summary. This will just provide the options you have selected. On clicking finish the wizard will create the project for you.

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 SSMS.

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. You will have to do plenty of trial and error to find the exact solution that might work for you. The assemblies required for SSMS are located under

C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\

AND

C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\

Please note 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\.

Based on your requirement needs, you will have to do some research to find the assemblies that are required for your application.

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.

As we were working on an Add-In which would be accessed by right clicking on the database or table name in the object explorer, we had to find the assembly which would allow us to do that. That is when we found the use of an assembly called objectexplorer.

In our product, I was also looking forward to displaying the stored procedure in a new Query Window. So, I found out that I could reference the following assemblies to do that:

Microsoft.sqlserver.sqltools.VSIntegration

sqlWorkbench.Interfaces

Also, if your add-in will be displaying some windows model forms then you might have to include System.Windows.Forms.

The assembly references will totally depend on your application.

As mentioned earlier, the add-in creates a class that implements IDTExtensibility2. In this section we will discuss some of the methods that need to be implemented in the class. The methods that need to be implemented are:

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. For SSMS add-in we will write our code in the OnConnection method. 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:

Listing 1

_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, _ObjectExplorer are declared in the class as follows:

Listing 2

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

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

Listing 3

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.

The above class that implemented 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.

Listing 4

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
Return Nothing
End Function

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

Listing 5

objSQLServer2005AddIn.ObjectExplorer.GetSelectedNodes(sz, selNodes)

The SelNodes is passed as a reference. This is an array of the type Microsoft.SQLServer.Management.UI.VSIntegration.ObjectExplorer.INodeInformation.

So, if the user has selected the database Northwind under 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. The constructor event looks like:

Listing 6

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. If the database object is selected then the node.context will look something like: "Server[@Name='ETGPHADNIS\SQL2005']/Database[@Name='SQLReportApplication']." In this example I have selected the database object SQLReportApplication.

If the table object is selected then the node.context will look something like:

"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.

Listing 7

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. In this section we saw how a menu item can be added to the objects context menu.

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.

Listing 8

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.

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 real complex, it is not that complex at all. In order to create a new Query Window, you just need to do the following.

Listing 9

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:

Listing 10

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.

None of the programmers are perfect and sometimes you do need to debug the code to find out about those small creatures that cause your applications to fail. So, how do we set up this application for debugging? It is pretty simple. Just 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

Wow! We have come a long way on creating an add-in and you must be excited to see how it works. But actually it is disappointing to see that it does not work yet. The reason being you have to make some registry entries to make this application work. The registry entry needs to be made in

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

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.

SQL Server Management Objects (SMO)

SQL SP Generator

Speed up Development on Backend and Middle Tier with SQL Scripts

In this article I have tried to explain most of the information that is needed to build an Add-In for your SSMS. Things might change with SQL 2008 or it might remain the same. The assemblies might change a little bit and there will be some support provided by Microsoft for the add-ins. There are various daily tasks that you can automate or provide a better way then what is provided by SSMS. You just need to visualize your tool and keep on looking for ways to code it and implement it.