martes, 12 de enero de 2010

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