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