How to get SQL Server instances from network?

In this article we are going to see how can we get SQL server instance shared in LAN(Loca Area Network). This is a technical code post. I am using VS 2010. And I will show how to do that. I have used Authentication mode for SQL Server(to keep it generalizing form)

So, let's break down the objective.
First, we have get the pc names which have sql server instances running.
Then we have to get the sql server instance names.
Then we have to get the Data Bases which are stored in that instance.
We must know the userName and password of the sql server instance to log in and access the database.
I have use List to store server & instance names. You may use array list or your own collection.
There are two data class(as data contract) SQLServer & Database which are basically property classes.

public class SQLServer
    {
        public string PCName { get; set; }
        public string InstanceName { get; set; }
        public string IP { get; set; }
        public string Version { get; set; }
        public bool IsClustered { get; set; }
    }

public class Database
    {
        public string Name { get; set; }
        public string Remarks { get; set; }
        public int size { get; set; }
 
    }

Then, I have used single Manager Class that handle the operation of locating the server and instances. In the manager class, GerServers() will search for instances and provide list of SQLServer objects(that means we are getting an array where each element contains information of a single instance) . I have used SqlDataSourceEnumerator.Instance.GetDataSources() method to get server information. Actually we are parsing table and getting information as object initiation(new). Main point is, when we get the instance information as data source, first element is host name, then instance name, then clustered property info, then version info.

 public List<SQLServer> GetServers()
        {
            DataTable dataSource = SqlDataSourceEnumerator.Instance.GetDataSources();
            List<SQLServer> servers = new List<SQLServer>();
            foreach (var row in dataSource.Rows)
            {
                DataRow dataRow = row as DataRow;
                string serverName = dataRow.ItemArray[0].ToString();
                string instanceName = dataRow.ItemArray[1].ToString();
                bool isClustered = dataRow.ItemArray[2].ToString() == "Yes";
                string version = dataRow.ItemArray[3].ToString();
                SQLServer myServer = new SQLServer() { InstanceName=instanceName,
                PCName=serverName,Version=version,IsClustered=isClustered};
                servers.Add(myServer);
            }
            return servers;
        }

And, I have used GetDBs method that takes a sql connection and provides list of database obects(an list of db info)

public List<Database> GetDBs(SqlConnection connection)
        {
            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand("sp_databases", connection);
                SqlDataReader reader = command.ExecuteReader();
                List<Database> dbs = new List<Database>();
 
                while (reader.Read())
                {
                    Database mydb = new Database()
                    {
                        Name = reader[0].ToString(),
                        Remarks = reader[2].ToString(),
                        size = (int)reader[1]
                    };
                    dbs.Add(mydb);
                }
                return dbs;
            }
            catch(Exception ex)
            {
                throw ex;                
                
            } 
            finally
            {        
                connection.Close();
            }            
        } 

These are main 2 method that works. There are several helper methods used for string management. Full project is very simple. You may use in your project . Here is the project code.

Note : To keep the sql server in LAN Share,  go to SQL Server configuration Manager ->

 
then , from left panel select SQL Server Network Configuration , -> select instance -> select TCP/IP from right panel and double click. From up coming properties window, select enable = Yes.
 

Now your sql server instance is accessible from LAN Share. We need this to test our previous code. And, be careful that your instance is tuning(from windows service).

Thanks...:)