Basically there are 2 ways to do this. The first one is by using SQL-DMO (Distributed Management Objects) which is a set classic classes that were used to programmatically manipulate and interact with SQL Servers. Please reffer to this article by MisterClay to see this classes in action.
On the other hand, there is SQL SMO (SQL Management Objects) the new set of classes designed to be the next generation of classes to serve as replacement to the old SQL-DMO.
In this article, i'll show you how to programmatically retrieve a list of available SQL Servers on the network and then retrieve the underlying databases on that SQL Server.
The first thing that we need to do is add references to our project. We'll need to have refence on this two libraries:
Microsoft.SqlServer.Management.Smo
Microsoft.SqlServer.ConnectionInfo
This libraries contain the classes that we need to use for this demo.
Next we need 2 listboxes, the first listbox will contain the list of servers and the second one if for its databases. Next we need to wire-up the list of SQL Servers to the first listbox. We'll put this code on the Form_Load event of our application
Here's the C# Code:
private void MainForm_Load(object sender, EventArgs e)
{
//just list local servers, set to false if you want to see all servers
DataTable dataTable = SmoApplication.EnumAvailableSqlServers(true);
lstServers.ValueMember = "Name";
lstServers.DataSource = dataTable;
}
Here's the VB.NET
Private Sub MainForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'just list local servers, set to false if you want to see all servers
Dim dataTable = SmoApplication.EnumAvailableSqlServers(True)
lstServers.ValueMember = "Name"
lstServers.DataSource = dataTable
End Sub
The SMOApplication class contains a method called EnumAvailableSQLServers. This method returns a DataTable that contains a list of the available servers. This method accepts a boolean parameter wherein you can specify if you want to list all servers in the network or just list down those in the local machine.
Next we need to create a SelectedIndexChange for our lstServers control. This is so that we can list the databases from the selected server. Please reffer to the code below:
C# Version
private void lstServers_SelectedIndexChanged(object sender, EventArgs e)
{
lstDatabases.Items.Clear();
if (lstServers.SelectedIndex != -1)
{
string serverName = lstServers.SelectedValue.ToString();
Server server = new Server(serverName);
try
{
foreach (Database database in server.Databases)
{
lstDatabases.Items.Add(database.Name);
}
}
catch (Exception ex)
{
string exception = ex.Message;
}
}
}
VB.NET Version
Private Sub lstServers_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstServers.SelectedIndexChanged
lstDatabases.Items.Clear()
If lstServers.SelectedIndex <> -1 Then
Dim serverName As String = lstServers.SelectedValue.ToString()
Dim server As Server = New Server(serverName)
Try
For Each database As Database In server.Databases
lstDatabases.Items.Add(database.Name)
Next
Catch ex As Exception
Dim exception As String = ex.Message
End Try
End If
End Sub
And thats how easy it is. Pretty slick huh? I wish i had this back in Visual basic 6!
Get the code here: SQLDMODemo.zip (29.2 KB)
Posted
Jun 08 2006, 09:40 PM
by
keithrull