Click here to Skip to main content
13,352,624 members (34,773 online)
Click here to Skip to main content
Add your own
alternative version

Stats

19.7K views
326 downloads
22 bookmarked
Posted 29 Jul 2008

Server Availability

, 29 Jul 2008
Rate this:
Please Sign up or sign in to vote.
Making generalized database server connection utility

Other Downloads

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

  • 29th July, 2008: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Jinal Desai
Software Developer (Senior) Amultek Software Solutions Pvt Ltd.
India India
Jinal Desai
Sr. Software Engineer
Amultek Software Solutions Pvt Ltd.
Ahmedabad

You may also be interested in...

Pro
Pro

Comments and Discussions

 
BugProject Missing - Contains Virus Pin
RHBrooks14-Jun-17 12:16
memberRHBrooks14-Jun-17 12:16 
GeneralAn alternative requires zero configuration and DLL's to make it work... Pin
Member 4373905-Aug-08 12:54
memberMember 4373905-Aug-08 12:54 
GeneralNot so general Pin
PIEBALDconsult29-Jul-08 15:36
memberPIEBALDconsult29-Jul-08 15:36 
GeneralRe: Not so general Pin
Member 43739012-Aug-08 16:54
memberMember 43739012-Aug-08 16:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.180111.1 | Last Updated 29 Jul 2008
Article Copyright 2008 by Jinal Desai
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid