SQL Server 2005 : Working with SQL Server Management Objects in Visual Studio (part 1) - Iterating Through Available Servers

To get started working with SMO, you must first set a reference in the Visual Studio project to the SMO assembly by choosing Project, Add Reference and selecting the Microsoft.SqlServer.Smo and Microsoft.SqlServer.ConnectionInfo components, as shown in Figure 1.

Figure 1. Setting a reference to SMO

You then add two using statements for the two namespaces that you will use most often. In C#, it looks like this:

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

In Visual Basic, it looks like this:

Imports Microsoft.SqlServer.Management.Common
Imports Microsoft.SqlServer.Management.Smo

Next you will want to work with SMO objects, the first being the ServerConnection object. This object represents a connection to an SQL Server instance. It lets you connect to a server and assign that connection to an SMO Server object. The Server object represents an actual SQL Server instance. The object allows you to retrieve the database server’s properties and perform tasks such as attaching a database. To help you understand how to use these objects, we will create a simple application to connect to an SQL Server instance using the ServerConnection object and then use a Server object to retrieve the SQL Server version, edition, and name of the instance. This application will ask the user for a user ID and password or allow the use of integrated security (as shown in Figure 2).

Figure 2. Simple SMO application

The code in Listing 6-1 shows how to connect to an SQL Server instance using either integrated security or SQL Server security. The results are shown in Figure 3.

Figure 3. Results of the simple SMO application

Listing 1. Connecting to an SQL Server database via SMO

private void btnConnect_Click(object sender, EventArgs e) { //ServerConnection object used to connect ServerConnection conn = new ServerConnection(); //pass in the name of the server/instance conn.ServerInstance = txtServer.Text.ToString(); //determine if integrated security is set if (chkSSPI.Checked == false) { //using sql security conn.LoginSecure = false; conn.Login = txtUID.Text.ToString(); conn.Password = txtPwd.Text.ToString(); } //try to connect and return server information try { Server svrLocal = new Server(conn); //pop up the message box to the user here //get the info via the Information property MessageBox.Show("You are not connected to: " + svrLocal.Name + System.Environment.NewLine + " Edition Info: " + svrLocal.Information.Edition + System.Environment.NewLine + " Version Info: " + svrLocal.Information.Version,"SMO Demos"); } catch (SmoException exSMO) { //catch SMO specific exceptions MessageBox.Show(exSMO.ToString()); } catch (Exception ex) { MessageBox.Show(ex.ToString()) } }

Tip

When you reference a local server with an instance name, you can use the syntax (local)\instance_name instead of the less flexible server_name\instance_name.

1. Iterating Through Available Servers

Depending on the type of functionality you need when performing your administrative tasks, you might have to programmatically enumerate a list of or iterate through available SQL Server instances. To perform this task in SMO, you can use the SmoApplication object’s EnumAvailableSqlServers method, passing in a Boolean value to tell SMO whether to look only locally. The EnumAvailableSqlServers method returns a DataTable with the results of the enumeration filled with lots of properties. The following code calls a reusable method in a helper class called ListAllKnownInstances, as shown in Listing 6-2. ListAllKnownInstances searches the network for all the SQL Server instances it can “see” by using EnumAvailableSqlServers and returns the resulting DataTable. Our code then takes that DataTable and binds it to a DataGridView.

private void button1_Click(object sender, EventArgs e)
  {
    DataTable dtList = SMOUtilities.ListAllKnownInstances();
    dataGridView1.DataSource = dtList;
  }

Listing 2 makes a call to the EnumAvailableSqlServers method and displays the results in a DataGrid.

Listing 2. Enumerating available servers

public static DataTable ListAllKnownInstances() { //this method will return a DataTable //based on all of the servers available //this method can take some time depending on your network //will connect to SQL Server 2000 machines too DataTable dtServers = new DataTable(); try { //Obtain a list of all SQL Server available //this method returns a DataTable dtServers = SmoApplication.EnumAvailableSqlServers(false); } catch (SmoException exSMO) { MessageBox.Show(exSMO.ToString()); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } return dtServers; }

Figure 4 shows the results of running the code in Listing 2.

Figure 4. Simple enumeration of available servers