Monday, 6 August 2012

C# Tips : Find or Discover all SQL Server Instances on Local Area Network (LAN)

We can get all SQL server instances of our Local Network (LAN).
We are using GetDataSources method of the System.Data.Sql.SqlDataSourceEnumerator class.
We are using System.Data.Sql.SqlDataSourceEnumerator.Instance static property and call it's GetDataSources Method.
GetDataSources Method returns DataTable , this datatable contains ServerName, InstanceName, IsClustered and Version columns.

  • ServerName : Name of the server where the SQL Server instance is hosted
  • InstanceName : Name of the SQL Server instance. the string is empty if the SQL Server is the default instance
  • IsClustered : Indicate that whether the SQL Server instance is part of a cluster
  • Version : Version of the SQL Server instance

Here is example for this.
In this Example it will list out all sql server instance accessible and visible on the network.

C# Examples :
        using (DataTable dtSqlSources = System.Data.Sql.SqlDataSourceEnumerator.Instance.GetDataSources())
        {
            Response.Write("<b>SQL Server Instances:</b>");
            Response.Write("<br/><br/>");

            foreach (DataRow objDR in dtSqlSources.Rows)
            {
                Response.Write(string.Format("<b>Server Name : </b>{0}", objDR["ServerName"]));
                Response.Write("<br/>");
                Response.Write(string.Format("<b>Instance Name : </b>{0}", objDR["InstanceName"]));
                Response.Write("<br/>");
                Response.Write(string.Format("<b>Version : </b>{0}", objDR["Version"]));
                Response.Write("<br/>");
                Response.Write(string.Format("<b>Is Clustered : </b>{0}", objDR["IsClustered"]));
                
                Response.Write("<br/><br/>");
            }
        }

VB.net Examples :
        Using dtSqlSources As DataTable = System.Data.Sql.SqlDataSourceEnumerator.Instance.GetDataSources()
            Response.Write("<b>SQL Server Instances:</b>")
            Response.Write("<br/><br/>")

            For Each objDR As DataRow In dtSqlSources.Rows
                Response.Write(String.Format("<b>Server Name : </b>{0}", objDR("ServerName")))
                Response.Write("<br/>")
                Response.Write(String.Format("<b>Instance Name : </b>{0}", objDR("InstanceName")))
                Response.Write("<br/>")
                Response.Write(String.Format("<b>Version : </b>{0}", objDR("Version")))
                Response.Write("<br/>")
                Response.Write(String.Format("<b>Is Clustered : </b>{0}", objDR("IsClustered")))

                Response.Write("<br/><br/>")
            Next
        End Using

Output :


This type of C# Tips is very useful in day to day programming life.

Note : Give Us your valuable feedback in comments. Give your suggestions in this article so we can update our articles accordingly that.



2 comments:

  1. hi..Im student from Informatics engineering, this article is very informative, thanks for sharing :)

    ReplyDelete