Reader Level:
ARTICLE

Get All Instances of SQL Server in C#

Posted by Ibrahim Ersoy Articles | .NET Assemblies July 09, 2012
If you're writing a Database Manager application or simply want to retrieve all the instances,this trick might come in handy.
  • 1
  • 0
  • 30939
 

We'll be using SQL SMO(SQL Management Objects)

First of All you need to add reference to Microsoft.SqlServer.smo.dll file which is located in:

For 64-bit Windows 7:
[Your drive]:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll

For 32-bit Windows 7:
[Your drive]:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll

 

Now that we've added it to our project,we can start coding!

Add a ListBox control to your project:


DataTable dataTable = SmoApplication.EnumAvailableSqlServers(true);
listBox1.ValueMember = "Name";
listBox1.DataSource = dataTable;



By running this code alone,you'll get all the available sql servers inside your listbox control.

art3.png


Ok lets develop it further.Lets see what databases our instances have

So to do this,you need to add another ListBox.Then in your listbox1's selectedindexchanged event you need to check which server selected.So you need to create a server object first.

After this,you'll iterate through this server to populate all the databases in newly created listbox.

Here is the code to do that:


listBox2.Items.Clear();
if (listBox1.SelectedIndex != -1)
{
string serverName = listBox1.SelectedValue.ToString();
Server server = new Server(serverName);
try
{
foreach (Database database in server.Databases)
{
listBox2.Items.Add(database.Name);
}
}
catch (Exception ex)
{
string exception = ex.Message;
}
}

After we run the project we'll be getting our Databases.

art4.png


Hope it helps! 

COMMENT USING

Trending up