Introduction
This article describes a C# class that utilises ODBC (SQLBrowseConnect
) to obtain a list of SQL Servers on a network and returns an array of instances. If an instance is supplied with a valid username/password, then a list of all the databases on the instance is returned.
Background
I needed a way to obtain a list of SQL Server instances without using SQLDMO. I found a C++ implementation of the SQLBrowseConnect
by Santosh Rao. This is a C# implementation.
Using the code
In order for the code to work, ODBC must be installed on your machine. Nearly all Microsoft Operating Systems have this installed. I have only tested this on Windows 2000 and XP. In order to use this, add a reference to the SQLEnumerator.cs file. The class is in the Moletrator.SQLDocumentor
namespace and is SQLInfoEnumerator
. The important work is done in the RetrieveInformation
method. This calls the relevant ODBC commands passing in the relevant values. The important item is the value of inputParam
. If this is blank then nothing is returned. When it contains a valid driver (DRIVER=SQL SERVER
for MS SQL Server), it will check for all instances of this driver on the network returning a string value which is then parsed.
If this string is expanded to include a valid SQL Server instance and a valid username/password, then a list of all the databases on the server instance is returned. If the username/password are not valid then the a list of SQL Server instances is returned:
- In order to get a list of SQL Server instances, create an instance of the class and call
EnumerateSQLServers
. The example below adds the list of SQL Servers to a list box SQLListBox
.
SQLInfoEnumerator sie = new SQLInfoEnumerator();
SQLListBox.Items.AddRange(sie.EnumerateSQLServers());
- To get a list of databases on a SQL Server instance, use the code below. The SQL Server instance is the selected instance from the list box populated in sample A. The username/password are entered by the user.
SQLInfoEnumerator sie = new SQLInfoEnumerator();
sie.SQLServer = listboxSQLServerInstances.SelectedItem.ToString();
sie.Username = textboxUserName.Text;
sie.Password = textboxPassword.Text;
SQLListBox.Items.AddRange(sie.EnumerateSQLServersDatabases());
The demo code contains a full GUI with an example on how to call each of the above methods.
History
18 April 2005 1:00 p.m. - Initial write.