How many times you created a .NET application in which you need to know about all of the database systems on your company’s network? May be you need to create an application for database administrator of your SQL Server database so that he can see a list of all the SQL Server databases installed in the company. In this small tutorial I will show you how you can obtain the list of all SQL Server database instances.
SqlDataSourceEnumerator class in System.Data.Sql namespace provides a mechanism for enumerating all instances of SQL Servers in a given network. This class exposes a method called GetDataSources() that returns a DataTable object containing the list of SQL Servers with some basic information about the server. private void Form1_Load(object sender, EventArgs e) //create a new instance of our SqlDataSourceEnumerator
Below is a code listing and screen shot of a simple Windows project I built that shows you how to use SqlDataSourceEnumerator class.
SqlDataSourceEnumerator sqlEnumerator = SqlDataSourceEnumerator.Instance;
//get the datatable containing our sql servers
DataTable sqlServersTable = sqlEnumerator.GetDataSources();
dataGridView1.DataSource = sqlServersTable;
private void Form1_Load(object sender, EventArgs e)
//create a new instance of our SqlDataSourceEnumerator
List of SQL Server Instances
Please keep in mind following points when you enumerate SQL Servers by using above code:
1. SQL Server Browser service must be running on the client system to obtain the information correctly from SQL Server 2005.
2. Enumerating servers will only find SQL Server 2000 and later versions of the database.
I hope this tutorial will help many of you in the future.