Getting SQL Servers instances and databases’ names with .NET (VB.NET and C#)

Versión en castellano

This is an example of a form that show SQL Server data sources (some like the “Add Connection” in Visual Studio).

Create a new Windows Forms project, set name “MyConnections”, and create a new form frmConnections. In this form add 2 comboboxes: cmbServers and cmbDataBases (yes, I’m working a lot the names :P).

Add this code to the form:

VB.NET

Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient

Public Class frmConnections
 Private servers As SqlDataSourceEnumerator
 Private tableServers As DataTable
 Private server As String


 Public Sub New()
  InitializeComponent()
  servers = SqlDataSourceEnumerator.Instance
  tableServers = New DataTable()
 End Sub

End Class
 

C#

using System;
using System.Windows.Forms;
using System.Data;
using System.Data.Sql;
using System.Collections.Generic;

namespace MyConnections
{
      public partial class frmConnections: Form
      {
            SqlDataSourceEnumerator servers;
            System.Data.DataTable tableServers;
            String server;

            public frmConnections ()
            {
                  InitializeComponent();
                  servers = SqlDataSourceEnumerator.Instance;
                  tableServers = new DataTable();
            }
      }
}

To get a list of available instances of SQL Server use the SqlDataSourceEnumerator.Instance object, namely the getDataSource() method that returns a DataTable with the characteristics of instances: server name, instance name, if it is part of a cluster and its version ("8.00.x" for SQL Server 2000 and "9.00.x" for SQL Server 2005).

Now, the cmbServers click event:

VB.NET

Private Sub cmbServers_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbServers.Click


  ' Check if datatable is empty
  If tableServers.Rows.Count = 0 Then

   ' Get a datatable with info about SQL Server 2000 and 2005 instances
   tableServers = servers.GetDataSources()

   ' List that will be combobox’s datasource   
Dim listServers As List(Of String) = New List(Of String)

   ' For each element in the datatable add a new element in the list
   For Each rowServer As DataRow In tableServers.Rows

    ' SQL Server instance could have instace name or only server name,
    ' check this for show the name
If String.IsNullOrEmpty(rowServer ("InstanceName").ToString()) Then
     listServers.Add(rowServer ("ServerName").ToString())
    Else
     listServers.Add(rowServer ("ServerName") & "\\" & rowServer ("InstanceName"))
    End If

   Next

   'Set servers list to combobox’s datasource
   Me.cmbServers.DataSource = listServers
  End If

 End Sub

C#

private void cmbServers_Click(object sender, EventArgs e)
{

      // Check if datatable is empty
      if (tableServers.Rows.Count == 0)
      {
      // Get a datatable with info about SQL Server 2000 and 2005 instances
      tableServers = servers.GetDataSources();


      // List that will be combobox’s datasource
      List listServers = new List();


      // For each element in the datatable add a new element in the list
      foreach (DataRow rowServer in tableServers.Rows)
      {

            // Server instance could have instace name or only server name,
            // check this for show the name
            if (String.IsNullOrEmpty(rowServidor["InstanceName"].ToString()))
                  listServers.Add(rowServer ["ServerName"].ToString());
            else
                  listServers.Add(rowServer["ServerName"] + "\\" + rowServer["InstanceName"]);
       }

      // Set servers list to combobox’s datasource
      this.cmbServers.DataSource = listServers;
      }
}

For the combobox with the databases list we must execute a query againts sys.databases view:

VB.NET

 Private Sub cmbDataBases_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbDataBases.Click

  Dim listDataBases As List(Of String) = New List(Of String)
  Dim connectString As String
  Dim selectSQL As String

  ' Check if user was selected a server to connect
  If Me.cmbServers.Text = "" Then
   MsgBox("Must select a server")
   Return
  End If

  server = Me.cmbServers.Text

  'Set connection string with selected server and integrated security
  connectString = "Data Source=" & server & " ;Integrated Security=True;Initial Catalog=master"


  Using con As New SqlConnection(connectString)

   ' Open connection
   con.Open()

   'Get databases names in server in a datareader
   selectSQL = "select name from sys.databases;"

   Dim com As SqlCommand = New SqlCommand(selectSQL, con)
   Dim dr As SqlDataReader = com.ExecuteReader()

  
   While (dr.Read())
    listDataBases.Add(dr(0).ToString())
   End While

   'Set databases list as combobox’s datasource
   Me.cmbDataBases.DataSource = listaBasesDatos

  End Using

 End Sub

C#

private void cmbBasesdeDatos_Click(object sender, EventArgs e)
{
      string select;
      string connectString;
      List listDataBases = new List();

// Check if user was selected a server to connect      
 if (this.cmbServers.Text == "")
 {
  MessageBox.Show("Must select a server");
  Return;
 }

      this.server = this.cmbServers.Text;

      // Set connection string with selected server and integrated security      
      connectString = "Data Source=" + this.server + ";Integrated Security=True;Initial Catalog=master";
               
      using (System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(connectString))
      {
            // Open connection
            con.Open();

            //Get databases names in server in a datareader 
            select = "select name from sys.databases;";

            System.Data.SqlClient.SqlCommand com = 
                         new System.Data.SqlClient.SqlCommand(select, con);
            System.Data.SqlClient.SqlDataReader dr = com.ExecuteReader();

            while (dr.Read())
            {
                  listDataBases.Add(dr[0].ToString());
            }                         
               
            // Set databases list as combobox’s datasource
            this.cmbDataBases.DataSource = listDataBases;
      }
}

And that’s all, we have a form that show SQL Server instances and theirs databases.


Note: if you don’t use integrated security, must change connection string to add user and password attributes.

Happy codding ;)