ADO.NET 3.5 Cookbook, 2nd Edition

11.1. Enumerating SQL Servers

Problem

You need to get a list of SQL Servers available on the network.

Solution

Use either the SmoApplication.EnumAvailableSqlServers() method or the SqlDataSourceEnumerator.Instance.GetDataSources() method to retrieve details about each SQL Server on the network. The solution demonstrates both approaches. In each case the solution retrieves information about each available visible SQL Server and outputs those details to the console.

Both approaches use the SQL Server Browser service, which provides information about installed SQL Server instances. Start and stop the SQL Server Browser service using SQL Server Configuration Manager.

This solution needs a reference to the Microsoft.SqlServer.Smo assembly. You can download this assembly from Microsoft Download Center if it is not available on your computer.

The C# code in Program.cs in the project EnumerateSqlServers is shown in Example 11-1.

Example 11-1. File: Program.cs for EnumerateSqlServers solution

using System; using System.Data; using System.Data.Sql; using Microsoft.SqlServer.Management.Smo; namespace EnumerateSqlServers { class Program { static void Main(string[] args) { // Enumerate the SQL Servers using SMO DataTable dtSmo = SmoApplication.EnumAvailableSqlServers( ); Console.WriteLine( "---SmoApplication.EnumAvailableSqlServers( )---"); // Iterate over results and output results to console foreach (DataRow row in dtSmo.Rows) { foreach (DataColumn col in dtSmo.Columns) { Console.WriteLine("{0} = ...