Server Availability

• Download demo project (using general .NET classes) - 4.86 KB

Other Downloads

• SQLDMO.DLL
• Microsoft.SqlServer.ConnectionInfo.DLL
• Microsoft.SqlServer.Smo.DLL
• Microsoft.SqlServer.SmoEnum.DLL
• Microsoft.SqlServer.SqlEnum.DLL

Introduction

Let me summarize the steps for making a connection through this utility:

1. Fetch the list of server instances available on LAN.
2. Give user functionality to select particular server instance name from the list of server instances.
3. Fetch the list of databases by connecting with the selected server instance.
4. Give user functionality to give user name and password if there is any to connect with database. (It is needed only when user selects SQL Authentication mode).
5. Give user functionality to select particular database name from the list.
6. Connect with the database and give user confirmation that connection has been successfully established.

In Detail

There are mainly three methods to find the list of servers in the local area network:

1. Using SQLDMO class
2. Using SQLSMO class
3. Using general .NET class

Each and every method has its own pros and cons:

1. Using SQLDMO Class

There are more cons than advantages of using the SQLDMO class:

• It only lists SQL Server 2000 server instances over the LAN.
• It requires SQLDMO.DLL in specific path C:\Program Files\Microsoft SQL Server\80\Tools\Binn
• It requires SQLDMO.DLL to be registered under Windows server registry through
  regsvr32 C:\Program Files\Microsoft SQL Server\80\Tools\Binn\SQLDMO.DLL command

You cannot use SQLDMO class directly in your application. For using SQLDMO class, there is a wrapper provided by Microsoft - Interop.SQLDMO.DLL. The wrapper provides you sqldmo wrapper classes to use inside your application.

You can fetch the list of server instances in LAN by using the following code:

 /*
For getting list of server instances name.
*/
//Declaring new object of type SQLServer defined in Interop.SQLDMO.DLL
SQLDMO.SQLServer o_SqlServer;
//Instantiating object o_SqlServer
o_SqlServer = new SQLDMO.SQLServer();
//Declaring namelist object to store list of SQL server instances
SQLDMO.NameList o_List;
//Declaring Application object to fetch list of SQL servers
SQLDMO.Application o_SqlApplication;
//Fetching list of SQL server instances over the LAN
o_List = o_SqlApplication.ListAvailableSQLServers();
//You can access particular server name by using integer index number.
o_List.Item(index);

/*
Fetch list of databases based on server instance name user has selected
*/
List<String> o_DBList = new List<string>();
o_SqlServer.LoginSecure = true;
if (userName == String.Empty)
{
    o_SqlServer.Connect(serverName, null, null);
}
else
{
    o_SqlServer.Connect(serverName, userName, password);
}
foreach (Database o_database in o_SqlServer.Databases)
{
   o_DBList.Add(o_database.Name);
}

2. Using SQLSMO Class

It will give you a list of both SQL Server 2000 and SQL Server 2005.

There are some pitfalls of using SQLSMO which prevent us from using this method. It requires four DLLs along with the solution or setup to carry, to use this method.

• Microsoft.SqlServer.ConnectionInfo.dll
• Microsoft.SqlServer.Smo.dll
• Microsoft.SqlServer.SmoEnum.dll
• Microsoft.SqlServer.SqlEnum.dll

/*
For getting list of server instances name.
*/
DataTable serverName;
 // get all data sources available in network using SMO
 serverName = SmoApplication.EnumAvailableSqlServers();
 //To access server names
 foreach (DataRow ds in serverName.Rows)
{
    string ds_server_name;
    ds_server_name = ds["Name"].ToString();
}

/*
Fetch list of databases based on server instance name user has selected
*/
// SMO server object
private Server server = new Server("selected-server instance-name");  
foreach (Microsoft.SqlServer.Management.Smo.Database db in selected_ds.Databases)
{
    //To access database name
    String db_name;
    db_name = db.Name
}

3. Using General .NET Class

.NET provides some internal classes that come along with .NET Framework which will give you the list of server instances. Once you get the server, there are some mechanisms which you can use to connect with the server and fetch the list of databases. Once you get the server instance name and database name, it is very easy to connect with that particular selected database in the selected server instance name.

/*
For getting list of server instances name.
*/
System.Data.Sql.SqlDataSourceEnumerator servers = _
System.Data.Sql.SqlDataSourceEnumerator.Instance;
System.Data.DataTable serversTable = servers.GetDataSources();
foreach(DataRow row in serversTable.Rows)
{
    //row[0]=networked computer name
    //row[1]=database server instance name
    //to get full name for connection use following method
    string serverinsance_name;
    serverinsance_name=row[0].ToString() + @"\" + row[1].ToString()
}

/*
Fetch list of databases based on server 
instance name user have selected
*/
//In generalized method you need to make connection 
//with the selected server instance name
//for fetching list of databases
String ml_conxString;
DataTable o_DatabaseList;
if(string.IsNullOrEmpty(UserName.Text))
{
    ml_conxString = "Data Source=" + ServerName.Text.Trim() + ";_
    Integrated Security=True";
}
else
{
    ml_conxString = "Data Source=" + ServerName.Text.Trim() + ";_ 
    Initial Catalog=" + DataBaseName.Text.Trim() + ";_
    User ID = " + UserName.Text.Trim() + ";Pwd = " + Password.Text;
}
using (SqlConnection sqlConx = new SqlConnection(ml_conxString))
{
    sqlConx.Open();
    o_DatabaseList = sqlConx.GetSchema("Databases");
    sqlConx.Close();
}
//To access database name
foreach (DataRow row in o_DatabaseList.Rows)
{
    string ml_databasename;
    ml_databasename = row["database_name"].ToString();
}

//Testing connection is same as we have fetched 
//database list based on server instance name
String ml_conxString;
if(string.IsNullOrEmpty(UserName.Text))
{
    ml_conxString = "Data Source=" + ServerName.Text.Trim() + ";_
    Initial Catalog=" + DataBaseName.Text.Trim() + ";Integrated Security=True";
}
else
{
    ml_conxString = "Data Source=" + ServerName.Text.Trim() + ";_
    Initial Catalog=" + DataBaseName.Text.Trim() + ";_
    User ID = " + UserName.Text.Trim() + ";Pwd = " + Password.Text;
}
SqlConnection sqlConx = new SqlConnection(ml_conxString);
sqlConx.Open();
sqlConx.Close(); 

Summary

There are mainly three methods to find the list of servers in the local area network:

1. Using SQLDMO class
2. Using SQLSMO class
3. Using general .NET class

The best method out of the three is the last one because it does not have any dependency on any external DLL.

History

• 29^th July, 2008: Initial post