DevPinoy.org
A Filipino Developers Community
   
List SQL Servers using SqlDataSourceEnumerator

I wrote an article a few years ago using SQL SMO and just realized today that I can also list SQL Severs without importing an additional assembly to my project(Microsoft.SqlServer.Management.Smo) by using the SqlDataSourceEnumerator class. SqlDataSourceEnumerator is a class that provides a mechanism for enumerating all intances of SQL Server in a given network. SqlDataSourceEnumerator exposes a method called GetDataSources() that returns a DataTable containing the list of SQL Servers and some basic information about the server. Below is a sample function I built that shows you how to use the SqlDataSourceEnumerator class.

[C# Version]

/// <summary>
/// A method that returns a list of all available SQL Servers in the network
/// </summary>
/// <param name="shouldSortList">Specifies whether the list should be sorted or not</param>
/// <returns>a generic list of string containing the servers found</returns>
private List<string> GetSqlServers(bool shouldSortList)
{
   //create the list that would hold our servers
   List<string> listOfServers = new List<string>();

   //create a new instance of our SqlDataSourceEnumerator
   SqlDataSourceEnumerator sqlEnumerator = SqlDataSourceEnumerator.Instance;

   //get the datatable containing our sql servers
   DataTable sqlServersTable = sqlEnumerator.GetDataSources();

   //iterate thru all the rows
   foreach (DataRow rowOfData in sqlServersTable.Rows)
   {
      //get the server name
      string serverName = rowOfData["ServerName"].ToString();
      //get the instance name
      string instanceName = rowOfData["InstanceName"].ToString();

      //check if the instance name is empty
      if (!instanceName.Equals(String.Empty))
      {
         //append the instance name to the server name
         serverName += String.Format("\\{0}", instanceName);
      }

      //add the server to our list
      listOfServers.Add(serverName);
   }

   //sort the list if the sort option is specified
   if (shouldSortList)
   {
      //sort it!
      listOfServers.Sort();
   }

   //return our list
   return listOfServers;
}

/// <summary>
/// A method that returns a list of all available SQL Servers in the network
/// </summary>
/// <returns>a generic list of string containing the servers found</returns>
private List<string> GetSqlServers()
{
   //get the servers with the list sorted
   return GetSqlServers(true);
}

[VB.NET Version]

''' <summary>
''' A method that returns a list of all available SQL Servers in the network
''' </summary>
''' <param name="shouldSortList">Specifies whether the list should be sorted or not</param>
''' <returns>a generic list of string containing the servers found</returns>
Private Function GetSqlServers(ByVal shouldSortList As Boolean) As List(Of String)
   'create the list that would hold our servers 
   Dim listOfServers As New List(Of String)()

   'create a new instance of our SqlDataSourceEnumerator 
   Dim sqlEnumerator As SqlDataSourceEnumerator = SqlDataSourceEnumerator.Instance

   'get the datatable containing our sql servers 
   Dim sqlServersTable As DataTable = sqlEnumerator.GetDataSources()

   'iterate thru all the rows 
   For Each rowOfData As DataRow In sqlServersTable.Rows
      'get the server name 
      Dim serverName As String = rowOfData("ServerName").ToString()
      'get the instance name 
      Dim instanceName As String = rowOfData("InstanceName").ToString()

      'check if the instance name is empty 
      If Not instanceName.Equals(String.Empty) Then
         'append the instance name to the server name 
         serverName += String.Format("\{0}", instanceName)
      End If

      'add the server to our list 
      listOfServers.Add(serverName)
   Next

   'sort the list if the sort option is specified 
   If shouldSortList Then
      'sort it! 
      listOfServers.Sort()
   End If

   'return our list 
   Return listOfServers
End Function

''' <summary>
''' A method that returns a list of all available SQL Servers in the network
''' </summary>
''' <returns>a generic list of string containing the servers found</returns>
Private Function GetSqlServers() As List(Of String)
   'get the servers with the list sorted 
   Return GetSqlServers(True)
End Function

One thing to note about the SqlDataSourceEnumerator.GetDataSources which is mentioned in the documentation is that it will not always return a complete list of the available servers, and the list might not be the same on every call. Which in layman terms mean that its not the most accurate way of listing SQL Servers. I suggest you use the NetServerEnum function with the server type specified to SV_TYPE_SQLSERVER if you want something that is more accurate. The only drawback with NetServerEnum is that you need to use PInvoke but don't worry the guys at PInvoke.NET already has a sample class which you can use as a reference when trying to deal with the murky waters of PInvoke.

I hope this helps someone in the future. If you are interested you can download the source code for this article in both VB.NET and C# here.


Posted 12-11-2007 11:31 AM by keithrull

Comments

DotNetKicks.com wrote List SQL Servers using SqlDataSourceEnumerator
on 08-05-2008 12:35 AM

You've been kicked (a good thing) - Trackback from DotNetKicks.com

Copyright DevPinoy 2005-2008