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 ;)
