What's New in .Net 2.0 - Locating SQL Server Instances on the Network with SqlDataSourceEnumerator

^{Current .Net Version: 2.0.50727}

Mike's C#, VS.NET and SQL Blog

What's New in .Net 2.0 - Locating SQL Server Instances on the Network with SqlDataSourceEnumerator
Author:	Michael G. Last Updated:12/21/2005 1:07:14 PM
Category(ies):	C# 2.0, .Net Framework 2.0
Description:	Learn how to leverage the new SqlDataSourceEnumerator class (in the System.Data.Sql namespace) of the FCL to search for running instances of SQL Server 2000 and later.
	Add & View Comments About this Article

Tested with Microsoft Visual Studio 2005 8.0.50727.42; .NET Framework 2.0.50727

Locating Active Instances of SQL Server on a Local Area Network

You no longer need the services of SQL-DMO to search for running instances of SQL Server on the local area network. The new SqlDataSourceEnumerator class of the new System.Data.Sql namespace in the FCL allows you to retrieve a list of active SQL Server instances on the network. Only instances running SQL Server 2000 and later can be detected (including MSDE versions).

An instance of the SqlDataSourceEnumerator class is available via the SqlDataSourceEnumerator's Instance property. SqlDataSourceEnumerator does not contain any constructors. Therefore, SqlDataSourceEnumerator cannot be instantiated via the new keyword. It's the SqlDataSourceEnumerator instance that exposes a method called GetDataSources. GetDataSources returns a DataTable object containing rows, one for each instance of SQL Server found on the network.

GetDataSources() DataTable Columns

ServerName - The name of the default SQL Server instance found on the network.
InstanceName - The name of an instance found on an SQL Server.
IsClustered - Yes if the SQL Server instance is part of a cluster; otherwise, No.
Version - The version of the SQL Server instance.

Looking for Instances of SQL Server (2000+) on a Local Network: DataTable tblSqlServers = SqlDataSourceEnumerator.Instance.GetDataSources();

Custom SqlDataSourcesHelper Class

If you don't want to have to deal with the DataTable generated by calling GetDataSources(), you can use a custom class I created that provides you with a type-safe and enumerable collection of found SQL Server instances.

SqlDataSourcesHelper Class public class SqlDataSourcesHelper : System.Collections.IEnumerable { private bool blnListSet = false; private System.Data.DataTable tbl; private System.Collections.Generic.List<sqldatasource> list = new System.Collections.Generic.List<sqldatasource>(); /// <summary> /// A contructor that retrieves the list of active SQL Server instances /// on the network and saves them to a data table. /// </summary> public SqlDataSourcesHelper() { this.tbl = System.Data.Sql.SqlDataSourceEnumerator.Instance.GetDataSources(); this.SetList(); } /// <summary> /// Saves the provided data table of active SQL Server instances to /// this class instance. /// </summary> /// <param name="SqlDataSources">System.Data.DataTable</param> public SqlDataSourcesHelper(System.Data.DataTable SqlDataSources) { this.tbl = SqlDataSources; this.SetList(); } private void SetList() { if (!this.blnListSet) { if (this.tbl != null) { if (tbl.Columns.Contains("ServerName") && tbl.Columns.Contains("InstanceName") && tbl.Columns.Contains("IsClustered") && tbl.Columns.Contains("Version")) { foreach (DataRow row in this.tbl.Rows) { SqlDataSource ds = new SqlDataSource(); if (!row.IsNull("ServerName")) ds.ServerName = row["ServerName"].ToString(); if (!row.IsNull("InstanceName")) ds.InstanceName = row["InstanceName"].ToString(); if (!row.IsNull("IsClustered")) { if (row["IsClustered"].ToString().ToLower() == "yes") ds.IsClustered = true; else ds.IsClustered = false; } if (!row.IsNull("Version")) ds.Version = row["Version"].ToString(); this.list.Add(ds); } } } this.blnListSet = true; } } public System.Collections.IEnumerator GetEnumerator() { return this.list.GetEnumerator(); } /// <summary> /// An indexer that retrieves an instance of a SqlDataSource. /// </summary> /// <param name="Index">The oridinal position of the SqlDataSource in the /// collection of sources.</param> /// <returns>SqlDataSource at the Index provided.</returns> public SqlDataSource this[int Index] { get { SqlDataSource ds = new SqlDataSource(); if (Index <= (this.list.Count - 1)) ds = this.list[Index]; return ds; } } /// <summary> /// An indexer that retrieves an instance of a SqlDataSource. /// The InstanceName can be null. If more than two instances exist /// for a single server, only the first instance is retrieved. /// </summary> /// <param name="ServerName">The name of the SQL Server containing at /// least one instance.</param> /// <param name="InstanceName">The optional instance name (can be null). </param> /// <returns></returns> public SqlDataSource this[String ServerName, String InstanceName] { get { SqlDataSource dsSource = new SqlDataSource(); foreach (SqlDataSource ds in this.list) { if (ds.ServerName != null && InstanceName == null && ServerName != null) { if (ds.ServerName.ToLower() == ServerName.ToLower()) { dsSource = ds; break; } } else if (ds.ServerName != null && InstanceName != null && ds.InstanceName != null) { if (ds.ServerName.ToLower() == ServerName.ToLower() && ds.InstanceName.ToLower() == InstanceName.ToLower()) { dsSource = ds; break; } } } return dsSource; } } /// <summary> /// Returns a list of found SQL Server instances on the network. /// </summary> public System.Collections.Generic.List<sqldatasource> SqlDataSources { get { return this.list; } private set { this.list = value; } } /// <summary> /// The number of active SQL Server instances found on the network. /// </summary> public int Count { get { return this.list.Count; } } /// <summary> /// Determines if a matching server name and optional instance name /// have been found in the list. /// </summary> /// <param name="ServerName">The name of the SQL Server containing /// at least one instance. InstanceName can be null.</param> /// <param name="InstanceName">The instance of SQL Server found.</param> /// <returns>True if found; otherwise, false.</returns> public bool Contains(String ServerName, String InstanceName) { bool bln = false; foreach (SqlDataSource ds in this.list) { if (ds.ServerName != null && InstanceName == null && ServerName != null) { if (ds.ServerName.ToLower() == ServerName.ToLower()) { bln = true; break; } } else if (ds.ServerName != null && InstanceName != null && ds.InstanceName != null) { if (ds.ServerName.ToLower() == ServerName.ToLower() && ds.InstanceName.ToLower() == InstanceName.ToLower()) { bln = true; break; } } } return bln; } public struct SqlDataSource { private String strServerName; private String strInstanceName; private bool? blnIsClustered; private String strVersion; public String ServerName { get { return this.strServerName; } internal set { this.strServerName = value; } } public String InstanceName { get { return this.strInstanceName; } internal set { this.strInstanceName = value; } } public bool? IsClustered { get { return this.blnIsClustered; } internal set { this.blnIsClustered = value; } } public String Version { get { return this.strVersion; } internal set { this.strVersion = value; } } public SqlDataSource( String ServerName, String InstanceName, bool? IsClustered, String Version) { this.strServerName = ServerName; this.strInstanceName = InstanceName; this.blnIsClustered = IsClustered; this.strVersion = Version; } } }

Using the Custom SqlDataSourcesHelper Class

The custom SqlDataSourcesHelper class takes two constructors: 1) an empty one that immediately calls GetDataSources() and 2) another that takes an already populated DataTable (generated separately by GetDataSources()).

Using the Custom SqlDataSourcesHelper Class // Letting the SqlDataSourcesHelper call GetDataSources(): SqlDataSourcesHelper sqlDataSources = new SqlDataSourcesHelper(); foreach (SqlDataSourcesHelper.SqlDataSource dataSource in sqlDataSources) { Console.WriteLine("Server Name: {0}", dataSource.ServerName); } // Calling GetDataSources() yourself: DataTable tblSources = System.Data.Sql.SqlDataSourceEnumerator.Instance.GetDataSources(); SqlDataSourcesHelper sqlDataSources = new SqlDataSourcesHelper(tblSources); foreach (SqlDataSourcesHelper.SqlDataSource dataSource in sqlDataSources) { Console.WriteLine("Server Name: {0}", dataSource.ServerName); }

Comments

Click Here to Add your Comment

By Norbert @ 3/30/2006 5:21:36 AM

Hi,

Thank you for this useful code snippet! I am creating a deployment utility app. which helps installing SQL databases, and it required to determine the available SQL Server 2005 instances in the current environment. Your explanation helped me to solve the problem quickly.

By Ralph @ 4/24/2006 2:52:45 PM

The code is totally helpful.

Now, the second question is: Once queried the SQL Servers on the network, how do I locate the databases that each server has?

Thanks for your answer.