Add-in to create and manager a server connection

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JJ - 20 Sep 2006 20:18 GMT

I want to have an add-in that opens the server explorer (the easy part). But
then I want to be able to establish a connection and tell it to open a stored
procedure from a specific database on that connection.

The reason is I want to take advantage of SQL Server debugging that is
already built into the IDE, however, my database has 4700 +/- stored
procedures. So when you expand the tree a message box pops up and says, "Are
you sure, this could take a while?" I thought it'd be cool to highlight the
sProc name in the code, then the addin would trigger the sProc to open.

I can get to the Server Explorer window, that's easy. But is it even
possible to do the things I mentioned above?

Thanks

Reply to this Message

Carlos J. Quintero [VB MVP] - 21 Sep 2006 09:36 GMT

I am not sure if the Server Explorer can be automated. Once you get the
toolwindow of the Server Explorer, get if its .Object property returns an
object which internally is EnvDTE.UIHierarchy. See my article:

HOWTO: Get the programmable inner object of a toolwindow

at:

http://www.mztools.com/resources_vsnet_addins.htm

Signature

Best regards,

Carlos J. Quintero

MZ-Tools: Productivity add-ins for Visual Studio
You can code, design and document much faster:
http://www.mztools.com

>I want to have an add-in that opens the server explorer (the easy part).
>But
[quoted text clipped - 14 lines]
>
> Thanks

Reply to this Message

JJ - 22 Sep 2006 14:07 GMT

Alright, I have it somewhat working but I still have a problem. I was able
to use the UIHeirarchy model to get the specific node I needed, which is
cool, because the developer can double-click (i.e. highlight) a stored
procedure in the code window, and (assuming it is spelled correctly with
proper capitalization) click on the add-in and it will automatically open the
sProc for debugging using the following code...

Dim z As EnvDTE.Window =
_applicationObject.Windows.Item(Constants.vsWindowKindServerExplorer)
Dim lssProc As String =
DirectCast(_applicationObject.ActiveDocument.Selection, TextSelection).Text
Dim lsDB As String =my database name ' as it exists in the server
explorer window
Try
Dim x As UIHierarchyItem = CType(z.Object,
EnvDTE.UIHierarchy).GetItem(String.Concat("Data Connections\", lsdb, "\Stored
Procedures\", lssProc))
x.Select(vsUISelectionType.vsUISelectionTypeSelect)
CType(z.Object, EnvDTE.UIHierarchy).DoDefaultAction()

Catch ex As Exception
MessageBox.Show(ex.Message)

End Try

There are obviously a few assumptions made with this code, i.e. the database
name must already exist in the developers IDE server explorer window. So
this code work perfectly if you have already expanded the stored procedure
list. But if it is the first time you are doing anything with stored
procedures it must expand the tree, which is okay, but with 4700 sProcs it
takes several minutes.

I wish I could by pass this. Oh well, it's good enough for now I suppose.

Reply to this Message

JJ - 22 Sep 2006 17:50 GMT

Okay, I have completely hacked this thing. lol. This will be a long post
because I am going to outline what I did to make it happen. I noticed on the
Menu Bar, under Data you can choose Add New and then Stored Procedure, as
long as you have a connection selected in the Server Explorer. This lead me
to believe that I could create a work around for my issue using this menu
option (and some other hacks).

Step one, select the connection in the Server Explorer. Easy...

Dim z As EnvDTE.Window =
_applicationObject.Windows.Item(Constants.vsWindowKindServerExplorer)
Dim lssProc As String =
DirectCast(_applicationObject.ActiveDocument.Selection, TextSelection).Text
Dim lsDB As String = "dev03-sql01.TRSRecalcDev.dbo"

z.Activate()
'Dim x As UIHierarchyItem = CType(z.Object,
EnvDTE.UIHierarchy).GetItem(String.Concat("Data Connections\", lsDB, "\Stored
Procedures\", lssProc))
Dim x As UIHierarchyItem = CType(z.Object,
EnvDTE.UIHierarchy).GetItem(String.Concat("Data Connections\", lsDB))
x.Select(vsUISelectionType.vsUISelectionTypeSelect)
CType(z.Object, EnvDTE.UIHierarchy).DoDefaultAction()

Step two, use the menu to open a new sProc window. Again, easy...

Dim commands As Commands2 = CType(_applicationObject.Commands,
Commands2)

Dim commandBars As CommandBars =
CType(_applicationObject.CommandBars, CommandBars)
Dim menuBarCommandBar As CommandBar = commandBars.Item("MenuBar")

'Find the Tools command bar on the MenuBar command bar:
DirectCast(DirectCast(menuBarCommandBar.Controls.Item("Data"),
CommandBarPopup).Controls("Add New"), CommandBarPopup).Controls("Stored
Procedure").Execute()

Step three, create a connection to the database and execute sp_helptext on
the selected sProc. I am not going to put the ADO code here, but basically
just dump the results into some sort of data object, I chose a datatable.
Once you have the DataTable results, spin through and put that code into your
new sProc window....

For Each x As DataRow In loDT.Rows
If x!text.ToString.Trim..StartsWith("CREATE") Then
x!Text = x!Text.ToString.Replace("CREATE", "Alter")
End If
lssProc &= x!Text.ToString
Next

DirectCast(_applicationObject.ActiveDocument.Selection,
TextSelection).SelectAll()
My.Computer.Clipboard.SetText(lssProc)
DirectCast(_applicationObject.ActiveDocument.Selection,
TextSelection).Paste()
DirectCast(_applicationObject.ActiveDocument.Selection,
TextSelection).StartOfDocument()

Finally, save the new sProc window. This is what establishes the connection
to the DB and allows you to be able to do SQL debugging...

_applicationObject.ActiveDocument.Save()

Set a breakpoint in this new window and let 'er fly. Works great for me.

I know this is a total hack job, but honestly, I had limited time to figure
this out, and it had to be resolved somehow. Loading 4700 stored procedure
in the server explorer tree just takes too long.

Thanks.

Reply to this Message

Add-in to create and manager a server connection

Free Magazines