Other Downloads
Introduction
Let me summarize the steps for making a connection through this utility:
- Fetch the list of server instances available on LAN.
- Give user functionality to select particular server instance name from the list of server instances.
- Fetch the list of databases by connecting with the selected server instance.
- 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).
- Give user functionality to select particular database name from the list.
- 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:
- Using
SQLDMO
class - Using
SQLSMO
class - 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:
SQLDMO.SQLServer o_SqlServer;
o_SqlServer = new SQLDMO.SQLServer();
SQLDMO.NameList o_List;
SQLDMO.Application o_SqlApplication;
o_List = o_SqlApplication.ListAvailableSQLServers();
o_List.Item(index);
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
DataTable serverName;
serverName = SmoApplication.EnumAvailableSqlServers();
foreach (DataRow ds in serverName.Rows)
{
string ds_server_name;
ds_server_name = ds["Name"].ToString();
}
private Server server = new Server("selected-server instance-name");
foreach (Microsoft.SqlServer.Management.Smo.Database db in selected_ds.Databases)
{
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.
System.Data.Sql.SqlDataSourceEnumerator servers = _
System.Data.Sql.SqlDataSourceEnumerator.Instance;
System.Data.DataTable serversTable = servers.GetDataSources();
foreach(DataRow row in serversTable.Rows)
{
string serverinsance_name;
serverinsance_name=row[0].ToString() + @"\" + row[1].ToString()
}
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();
}
foreach (DataRow row in o_DatabaseList.Rows)
{
string ml_databasename;
ml_databasename = row["database_name"].ToString();
}
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:
- Using
SQLDMO
class - Using
SQLSMO
class - 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