Configuring Connections and Connecting to Data in Microsoft .NET Framework 3.5

Lesson 5: Enumerating the Available SQL Servers on a Network

This lesson describes how to return a list of visible SQL Server instances on a network comparable to the Server Name drop-down list in the Add Connection dialog box.

The .NET Framework offers applications a way to discover SQL Server instances on a network so your programs can process this information when necessary. To retrieve the list of available SQL Servers, use the Instance property of the SqlDataSourceEnumerator class and call the GetDataSources method. The GetDataSources method returns a DataTable that contains information for each SQL server that is visible on the network. The returned data table contains the columns listed in Table 5-11.

Table 5-11 DataTable Schema Returned by the GetDataSources Method

Why Do Only Some or No SQL Servers Appear in My Grid?

Depending on how your network or even your individual machine is set up, the list of available servers might or might not be complete. In addition to things such as network traffic and timeout issues, the way your network implements security can cause servers to be hidden from the returned list as well. If you are running SQL Server 2005, a service named SQL Browser needs to be running for you to see SQL Server instances. And even if your SQL Browser service is running, your firewall might be blocking the request for SQL information. The firewall is likely to be blocking communication requests through port 1433, which is the default port that SQL Server default instances are set up to use. There are obvious security implications concerning turning on the SQL Browser service as well as enabling communications through specific ports through your firewall, but these are beyond the scope of this book. A good resource is the “SQL Browser Service” section of SQL Server 2005 Books Online, and I encourage you to read that before changing any settings on your firewall or SQL Server configuration.

Lab: Returning the List of Visible SQL Servers

In this lab you will practice enumerating the SQL Servers on your network.

EXERCISE 1: Enumerating the SQL Servers on a Network

To demonstrate how to retrieve the list of visible SQL servers, let’s create a small application to display the information returned from the GetDataSources method in a DataGridView.

1. Create a new Windows application named SqlServerEnumerator.

2. Add a DataGridView to the form and name it VisibleSqlServers.

   NOTE: DATAGRIDVIEW

   The DataGridView is the control typically used for displaying data. The DataGridView is discussed in more detail in Chapter 8.

3. Add a Button control below the grid and set its Name property to GetDataSourcesButton.

4. Set the Button’s Text property to Get Visible Servers.

5. Double-click the Get Visible Servers button to create the Click handler and switch to code view.

6. Add code so that the handler looks like the following:

   ' VB
   Dim instance As System.Data.Sql.SqlDataSourceEnumerator = _
       System.Data.Sql.SqlDataSourceEnumerator.Instance
   VisibleSqlServers.DataSource = instance.GetDataSources
   
   // C#
   System.Data.Sql.SqlDataSourceEnumerator instance =
       System.Data.Sql.SqlDataSourceEnumerator.Instance;
   VisibleSqlServers.DataSource = instance.GetDataSources();

Now run the application and click the Get Visible Servers button. All visible SQL servers on your network appear in the grid, looking similar to Figure 5-3.

Figure 5-3 Grid showing all visible SQL servers on your network

Lesson Summary

• You can use the SqlDataSourceEnumerator object to return a list of visible SQL servers on a network.

• The list of servers returned might not be complete, due to factors such as firewall settings and protocol configurations on the SQL Server services.

Lesson Review

The following questions are intended to reinforce key information presented in this lesson. The questions are also available on the companion CD if you prefer to review them in electronic form.

1. What object is used to return the list of visible SQL Servers?

   1. VisibleSqlServers

   2. GetDataSources

   3. SqlDataSourceEnumerator

   4. ServerName

2. What factors can cause SQL servers to be invisible on the network? (Choose all that apply.)

   1. The computer’s firewall settings

   2. The amount of network traffic

   3. The availability of the SQL Browser service

   4. The Visibility property of the SQL Server

3. Which of the following pieces of information is available through the SqlServerEnumerator object? (Choose all that apply.)

   1. The name of the SQL server

   2. The number of databases currently on the server

   3. The version number of the server

   4. The instance name for servers that are not running default instances