SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


Enumareting and Listing SQL Server Instances Within a Network with SqlDataSourceEnumerator class


Using SqlDataSourceEnumerator class in the System.Data.Sql namespace, it is easy for developers to enumerate and get a list of existing visible Microsoft SQL Server database instances within a network.

Using the Instance property of the SqlDataSourceEnumerator class you can create an instance of the SqlDataSourceEnumerator object that can be used to retrieve information about available SQL Server instances.
Microsoft SQL Server 2000, SQL Server 2005 and SQL Server 2008 (Katmai) database instances within a network can be listed using this new class "SqlDataSourceEnumerator".
GetDataSources() method of the SqlDataSourceEnumerator class returns information in a DataTable object that can be used to display data about the retrieved SQL database instances on a DataGridView or in a ComboBox object.





Import the Imports System.Data.Sql if it is not imported within the project by default.

Imports System.Data.Sql
Code

Calling the SqlDataSourceEnumerator.Instance will get the public shared instance of SqlDataSourceEnumerator class whose GetDataSources() method will be used to return information about the SQL Servers in a DataTable object.

Dim dataTable As DataTable = SqlDataSourceEnumerator.Instance.GetDataSources()
Code

After the information about SQL Servers is stored and retrieved using the DataTable object instance, you can bind this data to a DataGridView object as follows;

DataGridView1.DataSource = dataTable
Code


SqlDataSourceEnumerator

As you see, DataTable that is returned by the GetDataSources() method of the SqlDataSourceEnumerator class includes the following columns:

  • ServerName : ServerName is the name of the server where the SQL Server database Instance is running on.
  • InstanceName : InstanceName is the name of the SQL database instance. If the instance is installed as the Default instance then InstanceName is blank.
  • IsClustered : IsClustered displays whether the SQL Server Instance is a part of a Cluster SQL Server installation.
  • Version : Version is the Microsoft SQL Server version number.
  • For example, on my computer there is a running SQL Server 2008 (CTP4) instance named "KATMAI", you can see also the version number on the GridView 10.0.1049.14 for SQL Server 2008.
    Version number begins with 9.00.x for SQL Server 2005 installations and 8.00.x for SQL Server 2000 installations.
    The instance list retrieved by SqlDataSourceEnumerator class includes servers within the same network. And may vary according to the network traffic.

    If you want to display SQL Server instances in a dropdown list or a combobox, you can also use the below sample code

    For Each datarow As DataRow In dataTable.Rows
      Dim datasource As String = datarow("ServerName").ToString
      If Not datarow("InstanceName") Is DBNull.Value Then
        datasource &= String.Format("\{0}", datarow("InstanceName"))
      End If
      ComboBox1.Items.Add(datasource)
    Next
    Code

    You can download the sample project for enumarating SQL Server database instances from file dowloads titled List SQL Server Instances using .Net Framework SqlDataSourceEnumerator class.



    SQL Server

    SQL Server 2019 Installation
    download SQL Server 2019
    download SQL Server 2017
    download SQL Server 2016
    download SQL Server 2014
    download SQL Server 2012
    MacOS ve SQL Server 2019


    Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.