Click here to Skip to main content
11,789,706 members (54,808 online)
Click here to Skip to main content

SQL Server Combo Control

, 4 Aug 2008 CPOL 40.5K 3.2K 38
Rate this:
Please Sign up or sign in to vote.
Lists all instances of SQL Server found on a local network.


This article shows how to create a custom combo box control that lists the fully qualified SQL Server instance names for all servers connected to a network and running locally.


This article uses the code found in James Curran's article, "Locate SQL Server instances on the local network". I converted his C# SqlServerInfo class to VB.NET, and used it to populate my combo box control with the SQL Server instance names found on my local network. The conversion to VB.NET was the only change to the original source code. The SqlServerInfo class broadcasts a UDP packet, using port 1434, and waits for response from any SQL Server connected to the local network. For a more in depth explanation, you can go to James Curran's article.

I wrote code that populates the combo box control with the instance names running on the local computer, and created a composite control using Visual Studio 2008. I am working on a Source Control project that uses SQL Server to store multiple versions of project files in a database. The database can either be located on the same machine as my source control program, or it can connect across a network. So, I needed a way of listing SQL Server instance names that were found locally and across a network. Initially, I tried using the NetServerEnum Windows API function to retrieve a list of all SQL Servers. This method was extremely slow, and it did not give me the fully qualified instance name.

The custom control's Load event calls the Populate_ComboBox() method, whose purpose is exactly as its name suggests.

Private Sub SQLExpressCombo_Load(ByVal sender As System.Object, _
            ByVal e As System.EventArgs) Handles MyBase.Load

    'set default size.
    ComboBox1.Height = Me.Size.Height + 5


End Sub

If SQL Server is running locally, then the combo box is populated with all local instance names first. The local instance names are retrieved from the Registry Key, HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\. This Registry key contains the names of all instance names. This code has only been tested on SQL Server 2005 Express. I could not test it on MSDE 2000, because MSDE 2000 is not compatible with Windows XP Service Pack 3.

The second half of this method retrieves any instance names found on the network. Get_Network_Servers returns an array of util.SqlServerInfo objects, each of which contains information on each instance running on the network.

I should also mention that whether or not the combo box is populated with both local instance names and instance names found on the network is determined by the value of the mShow_Servers variable. You can filter what is shown in the combo box drop list by setting the custom control's Servers property.

''' Populates the ComboBox control with the SQL Server Instance names.
Private Sub Populate_ComboBox()

    'Clear ComboBox List

    If (mShow_Servers = Server_Types.Local) Or _
        (mShow_Servers = Server_Types.Both_Local_and_Network) Then

        Dim Value As String

        'Get all instance names from registry.
        Dim Values As String() = Get_Local_Servers()

        If Not (Values Is Nothing) Then

            'Iterate through instance names.
            For Each Value In Values

                'add instance name to combobox
                ComboBox1.Items.Add(".\" & Value)


        End If

    End If

    If (mShow_Servers = Server_Types.Network) Or _
        (mShow_Servers = Server_Types.Both_Local_and_Network) Then


            'Get all Network Servers
            Dim Network_Servers() As Util.SqlServerInfo = Get_Network_Servers()

            If Network_Servers.GetLength(0) > 0 Then

                Dim i As Integer

                For i = 0 To Network_Servers.GetUpperBound(0)

                    If Network_Servers(i).ServerName <> Nothing Then


                    End If


            End If

            'Cleanup References.
            Network_Servers = Nothing

        Catch ex As Exception
        End Try

    End If

End Sub

The Get_Local_Servers function declares HKEY_LOCAL_MACHINE as a Registry key object and then uses this object to retrieve a sub key. Sub_Key represents "HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\", which is where you will find the local instance names for the local machine. Once you have the sub key, you just need to call the GetValueNames() method to retrieve an array of strings which contain the instance names for the local machine.

''' Returns the Instance names for all local servers.
Private Function Get_Local_Servers() As String()

    'Only add local instance names if sqlservr process is running.
    If Is_Local_SqlServer_Running() Then


            Dim HKEY_LOCAL_MACHINE As Microsoft.Win32.RegistryKey = _
            Dim Sub_Key As Microsoft.Win32.RegistryKey = _
                HKEY_LOCAL_MACHINE.OpenSubKey("Software\" & _ 
                "Microsoft\Microsoft SQL Server\Instance Names\SQL\", False)

            If Not IsNothing(Sub_Key) Then

                If Sub_Key.ValueCount > 0 Then

                    'Return all instance names from registry.
                    Return Sub_Key.GetValueNames()

                End If

            End If

        Catch ex As Exception
        End Try

    End If

    Return Nothing

End Function

The only thing left to discuss is how the properties and events are carried over from the original combo box control.

Notice how the BackColorChanged event handler declaration uses the Shadows keyword. The Shadows keyword specifies that a property or procedure will re-declare an existing property or procedure with an identical name. So, the BackColorChanged event handler in the base class will be replaced by the above declaration. When ComboBox1's BackColorChanged event handler is triggered, we raise the same event handler for the SQL combo box control.

Public Shadows Event BackColorChanged(ByVal sender As System.Object, _
                     ByVal e As System.EventArgs)
Private Sub ComboBox1_BackColorChanged(ByVal sender As System.Object, _
                    ByVal e As System.EventArgs) _
                    Handles ComboBox1.BackColorChanged
    RaiseEvent BackColorChanged(sender, e)
End Sub

The combo box controls SelectedIndexChanged event handler does not require the Shadows keyword, because there is no such event handler in the base class. Other than that, the declaration is identical to the last example.

Public Event SelectedIndexChanged(ByVal sender As System.Object, _
                                  ByVal e As System.EventArgs)
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged

    RaiseEvent SelectedIndexChanged(sender, e)

End Sub

Again, the custom control's Text property Shadows the Text property in the base class.

Public Shadows Property Text() As String
       Return ComboBox1.Text
   End Get
   Set(ByVal value As String)
       ComboBox1.Text = value
   End Set
End Property

Using the Code

To use the SQL combo box control, just compile the code and add SQL_Express_DropListBox.dll to your Visual Studio Toolbox. If you are using a firewall, you will need to configure port 1434 for UDP packets; otherwise, you will not see any servers on your local network. The combo box control has a refresh timer, which is enabled by setting the Enable_Timer property to True.

Points of Interest

An alternate method for getting the local instance names would have been to search under "HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\" for all keys with a name that starts with "MSSQL.". Each instance name would be numbered: MSSQL.1, MSSQL.2, ... and so on. You would then read the value of each of these keys to get the instance name.

SQL Server 2000 and MSDE 2000 servers running on a local network will show up in the combo list box control, but will not show up if they are running locally. This is because Microsoft changed the way the Registry data is stored for SQL Server 2005. I had planned to write code that would include local MSDE 2000 servers, but MSDE 2000 is not compatible with Windows XP Service Pack 3. I was not able to run MSDE 2000 on my workstation after I installed Service Pack 3.


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


About the Author

Web Developer
United States United States
No Biography provided

You may also be interested in...

Comments and Discussions

GeneralMy vote of 1 Pin
hoangthu197430-Nov-09 20:39
memberhoangthu197430-Nov-09 20:39 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.1509028.1 | Last Updated 4 Aug 2008
Article Copyright 2008 by Emrys81055
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid