Zuhaib

Yesterday in a project I had to build a feature where use can change the sql server connection while deployment. I wanted to build something exactly like the sql server management studio login dialog box. So first thing I had to do is retrieve a list of sql server instances on a network. After a little bit of googling I found two solutions.

1) Enumerate SQL Server Instances Using SQLDMO:

To do this you have to add reference to SQLDMO.dll in your Program Files\Microsoft SQL Server\80\Tools\Binn

[csharp]public static List GetAllSqlServerInstances() {
/* Enumerate sql server instances using SQLDMO */
NameList sqlList = null;
Application sqlApp = null;

var sqlServers = new List();

try {
sqlApp = new ApplicationClass();
sqlList = sqlApp.ListAvailableSQLServers();
foreach (string server in sqlList)
sqlServers.Add(server);
return sqlServers;
} catch {
return null;
} finally {
if (sqlList != null)
sqlList = null;
if (sqlApp != null)
sqlApp = null;
}
}[/csharp]

If you choose to do it with SQLDMO then you have to bundle SQLDMO dependencies with your package or the client computer should have sql server tools installed.

2) Obtain List of SQL Server Instances Using ODBC (SQLBrowseConnect):

Fortunately the second method that I used uses ODBC’s SQLBrowseConnect and which is probably installed by default on all windows operating system. The article that I found on CodeProject was exactly what I was looking for after facing the SQLDMO dependency problem.

To obtain a list of sql server instances using SQLBrowseConnect add the SQLInfoEnumerator.cs to your project and put do something like this:

[csharp]public static List GetAllSqlServerInstances() {
/* Enumerates sql server instances using ODBC */
var sie = new SQLInfoEnumerator();
return sie.EnumerateSQLServers().ToList();
}[/csharp]

And to obtain a list of databases in a particular sql server instance do this:

[csharp]public static List GetAllDataBases(string server, string userName, string password) {
SQLInfoEnumerator sie = new SQLInfoEnumerator();
sie.SQLServer = server;
sie.Username = userName;
sie.Password = password;
return sie.EnumerateSQLServersDatabases().ToList();
}[/csharp]

Links:

SQLInfoEnumerator.cs