Introduction
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.
Background
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
ComboBox1.Height = Me.Size.Height + 5
Populate_ComboBox()
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.
Private Sub Populate_ComboBox()
ComboBox1.Items.Clear()
If (mShow_Servers = Server_Types.Local) Or _
(mShow_Servers = Server_Types.Both_Local_and_Network) Then
Dim Value As String
Dim Values As String() = Get_Local_Servers()
If Not (Values Is Nothing) Then
For Each Value In Values
ComboBox1.Items.Add(".\" & Value)
Next
End If
End If
If (mShow_Servers = Server_Types.Network) Or _
(mShow_Servers = Server_Types.Both_Local_and_Network) Then
Try
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
ComboBox1.Items.Add(Network_Servers(i).ToString())
End If
Next
End If
Network_Servers = Nothing
Catch ex As Exception
MessageBox.Show(ex.Message)
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.
Private Function Get_Local_Servers() As String()
If Is_Local_SqlServer_Running() Then
Try
Dim HKEY_LOCAL_MACHINE As Microsoft.Win32.RegistryKey = _
My.Computer.Registry.LocalMachine()
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 Sub_Key.GetValueNames()
End If
End If
Catch ex As Exception
MessageBox.Show(ex.Message)
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
Get
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.
