C# - Finding SQL Server Data Sources and Databases

For those exploring how to finding data sources, database names, etc - using C#, you can do the following:

// Retrieve the enumerator instance, and then retrieve the data sources.
SqlDataSourceEnumerator instance = SqlDataSourceEnumerator.Instance;
DataTable dtDatabaseSources = instance.GetDataSources();
  
// Populate the data sources into DropDownList.            
foreach (DataRow row in dtDatabaseSources.Rows)
    if (!string.IsNullOrWhiteSpace(row["InstanceName"].ToString()))
        cboDatasources.Items.Add(row["ServerName"].ToString() 
            + "\\" + row["InstanceName"].ToString());

The code above however, did not show the local instances of SQL Server 2008 R2. So, to acquire your local instances as well, you will need to use the SMO ManagedComputer object, which provides an interface to the WMI Provider for Configuration Management.

You will need to add the following references to the project (using VS2010), in C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\: Microsoft.SqlServer.Management.Sdk.Sfc, Microsoft.SqlServer.Smo, and Microsoft.SqlServer.SqlWmimanagement.

In your project, you will need to add the following using statements:

using System.Data.Sql;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo.Wmi;

The ManagedComputer object has many functions. It can access client protocols, connection settings, server aliases, instances of SQL Server, as well as SQL Server services. It is versatile enough to pick up different versions of SQL Server, including 2008 and 2008 R2.

// Determine local system name
string hostName = System.Net.Dns.GetHostName().ToString();
ManagedComputer mc = new ManagedComputer(hostName);
  
// Check to see if 64-bit architecture and add to list
mc.ConnectionSettings.ProviderArchitecture = ProviderArchitecture.Use64bit;
foreach (ServerInstance si in mc.ServerInstances)
    cboDatasources.Items.Add(hostName + "\\" + si.Name);
  
// if not on list, and 32-bit add to list
mc.ConnectionSettings.ProviderArchitecture = ProviderArchitecture.Use32bit;
foreach (ServerInstance si in mc.ServerInstances)
    if (!cboDatasources.Items.Contains(hostName + "\\" + si.Name))
        cboDatasources.Items.Add(hostName + "\\" + si.Name);

In the code above, once a data source is selected in the drop down lists, the SelectedIndexChanged event would execute the following code:

// Connect to Data Source selected in DropDownList
using (SqlConnection sqlConn = new SqlConnection("Data Source = " +
    cboDatasources.SelectedText.ToString() + "; Integrated Security = True;"))
{
    // Open Connection
    sqlConn.Open();
    // Retrieve Database Schema
    DataTable dtDatabaseNames = sqlConn.GetSchema("Databases");
    // Close Connection
    sqlConn.Close();
  
    // Retrieve List of Database Names
    foreach (DataRow row in dtDatabaseNames.Rows)
        cboDatabaseNames.Items.Add(row["Database_Name"].ToString());
}