Click here to Skip to main content
15,867,568 members
Articles / Programming Languages / Visual Basic

Alter SQLServer 2005 Most Recent List

Rate me:
Please Sign up or sign in to vote.
4.00/5 (2 votes)
15 Mar 2010CPOL2 min read 25.2K   209   2   4
Demonstrates how to Adjust the SQL2005 MRU using Reflection and the SQL Assemblies

Introduction

I have a number of Servers in my Microsoft SQL Server Management Studio Server list that are no longer accessible, and not wanting to completely rebuild my list I began by posting a question on ServerFault about being able to Edit the MRU list for SQL Server Management Studio and being told it was not possible, I investigated whether infact it really would be. So I dug out Reflector and had a look inside the SQL Management Studio DLLs to find out, turns out... you can.

Using the Code

Because this code requires some libraries that are included with Microsoft SQL Server Management Studio, I cannot redistribute them. But if you open the project and add a reference to them, you'll then be able to compile.

References that need to be added can be either from SSMS Express or Full for version 2005. (I have noted the locations in CheckAssembly.vb.)

'Express - 'C:\Program Files\Microsoft SQL Server\90\Tools\Binn\
VSShell\Common7\IDE\Microsoft.SqlServer.Express.ConnectionDlg.dll

'Full - C:\Program Files\Microsoft SQL Server\90\Tools\Binn\
VSShell\Common7\IDE\ConnectiondDlg.dll	 

Note that to find the information about what properties and objects are used inside the component, we need to use Reflector.

reflectoroutput.png

Once we actually know how to find the properties and methods of interest, we need to actually hookup to them so we can make use of them. It is important to note that because a number of the objects are declared privately inside the library, we can't use strong types so we just use GetType to ensure that things work as we expect.

VB.NET
For Each pp As PropertyInfo In _
  GetType(ConnectionDlg.Personalization).GetProperties_
	(BindingFlags.Static Or BindingFlags.NonPublic)
    If String.Equals(pp.Name, "Instance") Then
        t = CType(pp.GetValue(GetType(ConnectionDlg.Personalization), Nothing), _
         ConnectionDlg.Personalization)
        Exit For
    End If
Next

Now that we have found the reflected internal instance of the Object, we can start to operate on that to build up our Onscreen List of Servers that can be used. The EnumObject method is how I called the reflected method and then enabled the ability to also delete from the same list.

VB.NET
 Dim d As System.Collections.Specialized.HybridDictionary = Nothing

For Each ff As FieldInfo In t.GetType.GetFields_
	(BindingFlags.NonPublic Or BindingFlags.Instance)
    Debug.Print(ff.Name)

    Select Case ff.Name
        Case "typeTable"
            d = CType(ff.GetValue(t), System.Collections.Specialized.HybridDictionary)
    End Select
Next

If d IsNot Nothing Then
    sl = d.Item(g)
    EnumObject(sl, False)
End If

EnumObject is recursive because the list is a linked list and we need to be able to traverse the list to find the actual data, because the original object has a String method which just returns a copy of the data, but not the actual objects.

VB.NET
Private Sub EnumObject(ByVal o As Object, ByVal remove As Boolean)
    Dim head As Object
    Dim nextitem As Object

    If o Is Nothing Then Return

    'Debug.Print("Enum object - " & o.GetType.ToString & " (Fields)")
    For Each ff As FieldInfo In o.GetType.GetFields_
	(BindingFlags.NonPublic Or BindingFlags.Instance)
        If String.Equals(ff.Name, "head", StringComparison.OrdinalIgnoreCase) Then
            head = ff.GetValue(o)
            EnumObject(head, remove)
        End If

        If String.Equals(ff.Name, "next", StringComparison.OrdinalIgnoreCase) Then
            nextitem = ff.GetValue(o)

            EnumObject(nextitem, remove)
        End If

    Next   
    
    'more code here about actually getting the data
End Sub

Finally, once we have displayed the list of servers to the user, we allow them to select one in the Listbox and then click Remove. This will then call EnumObjects again, but enabling the Delete of the record.

Points of Interest

Because all of the methods and classes are Private inside the SQL libraries, the only real way to access them is using Reflection.

Beware this has only been tested with SQL2005 and is only really something I did to solve my particular issue.

If you choose to run this program, make sure your SQL Server Management Studio is closed before you run it, because I'm not sure exactly who writes the file and when.

History

  • 2010-03-11 First release

License

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


Written By
Team Leader
Australia Australia
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Generalsimilar add-in Pin
Andrei b>g21-Mar-12 13:17
Andrei b>g21-Mar-12 13:17 
GeneralSSMS 2008 Pin
diegopandolfo2-Mar-11 2:37
diegopandolfo2-Mar-11 2:37 
GeneralRe: SSMS 2008 Pin
Paul Farry3-Mar-11 17:07
professionalPaul Farry3-Mar-11 17:07 
GeneralRe: SSMS 2008 Pin
Andrei b>g21-Mar-12 13:26
Andrei b>g21-Mar-12 13:26 

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.