RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


SQL Server Management Objects: Lessons from the Wild : Page 2

Harness the power of SQL Server Management Objects to create, document, and manage your SQL Server databases.

Retrieving Lists of Objects from SMO
In many cases, SMO provides two ways to fetch lists of items from SMO. For example, to build a list of Table objects contained in a database, you can either:

  1. Retrieve the group of objects via a property and receive a collection of objects.
  2. Call the EnumObjects method, which returns a DataTable containing the same information (though not the same functionality) in tabular format.
In general, calling the collection property yields more useful results, because you can query the individual objects further for more information. For example, if you retrieve a collection of Table objects, you can query each Table object in the collection for other properties, such as the column list, indexes, and triggers associated with the table.

In contrast, the EnumObjects method is often more convenient when you want to populate a grid or other data-aware object. The following code shows the process of retrieving a list of tables in a database using both methods:

   05 Imports Microsoft.SqlServer.Management
   10 Imports Microsoft.SqlServer.Management.Smo
   15 Imports Microsoft.SqlServer.Management.Common
   20 Dim objServer as Server
   25 Dim objDatabase As Database
   30 objServer = New Server()
   35 objDatabase = objServer.Databases("SMOSample")
      ' Use Tables property to get list of tables
   40 Debug.WriteLine("Tables in Database " & objDatabase.Name)
   45 For Each objTable As Table in objDatabase.Tables
   50    Debug.WriteLine("Table: " & strServer)
   55 Next
      ' Use EnumObjects method to get list of tables
   60 Debug.WriteLine("Tables in Database " & objDatabase.Name)
   65 For Each objRow As DataRow In _
         m_objDatabase.EnumObjects(DatabaseObjectTypes.Table, _
   70    Debug.WriteLine("Table " & CStr(objRow("Name")))
   75 Next
SMO Class Hierarchy
Most SMO objects will seem intuitive after you begin coding an SMO application. At the highest level are the connection methods. After establishing a connection, you'll find methods for managing service level attributes, such as retrieving a list of databases and logins, and for killing SQL processes. Each Server class contains a collection of Database classes, each of which contains collections of tables, stored procedures, views, etc. In most cases, the class hierarchy closely mimics the hierarchy models you're probably familiar with from SQL Server Management Studio and SQL Enterprise Manager.

Sample Application
Figure 1. Connection Methods: The SQL Connection dialog supports all the connection methods supported by SMO.
The downloadable sample application demonstrates how to navigate the SMO hierarchy programmatically to produce a tree-like structure similar to that displayed in SQL Server Management Studio. When you launch the sample application, you'll see an empty form. Click the Connect button to open the SQL Connection dialog shown in Figure 1. The sample application supports all the connection methods described above.

When the SQL Connection form first opens, it uses SMO's shared EnumAvailableSqlServers method to find instances of SQL Server running on the network. Next, it uses the resulting list to populate the Server combo box control on the form, using code similar to that which you saw earlier in the article.

Select a SQL instance and click the Connect button to create a Server object, connect to the selected server, and retrieve the list of databases from that server to populate the Database combo box (shown in Figure 2). Here's the code:

   05 For Each objDB As Database In Me.SMOServer.Databases
   10   Me.comboDatabase.Items.Add(objDB.Name)
   15 Next
Figure 2. Listing Databases: After connecting to a server, the SQL Connection dialog populates the databases available on the selected server.
Figure 3. Database TreeView: After connecting to a server, the application retrieves some basic information about the selected database and displays that in a tree view.
Select a database from the list, and then click the OK button. The SQL Connection dialog closes and the main form populates its tree view with tables, columns, keys, constraints, indexes, views, and stored procedures from the selected database (see Figure 3).

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date