Using NMO to Reflect the Contents of an Instance

In the preceding sections, you saw the NMO API used to create and administer a new SQL-NS instance. The NMO API can also be used to reflect over an existing instance. Reflection allows you to programmatically interrogate an existing SQL-NS instance to discover its contents. Think of reflection as the inverse operation of creating an instance: rather than defining a new instance by creating new NMO objects, your code can examine an existing instance by obtaining the corresponding NMO Instance object and then reading its properties and the properties of its child objects.

The reflection capabilities of the NMO API are useful in building dynamic management tools for SQL-NS instances. A good example is the SubscriptionLoader tool we used in Chapter 6, "Completing the Application Prototype: Scheduled Subscriptions and Application State," to enter subscriptions into the music store sample application. The SubscriptionLoader tool presents a user interface by which all the properties of a subscription can be specified, including the values of the subscription fields. When you used it with the music store sample application, you used controls in the SubscriptionLoader user interface to select among the application's two subscription classes. The subscription fields grid was populated with a line for each of the chosen subscription class's fields.

Despite the appearance of being tailored to the music store instance, the SubscriptionLoader user interface is in fact generic. The names of the music store instance's application, its subscription classes, and their subscription fields are not coded into the interface. SubscriptionLoader can work against any SQL-NS instance because it uses the NMO API to dynamically discover the list of applications and subscription classes, and the fields in those subscription classes. It then uses this information to populate its user interface dynamically.

This section explains the use of the NMO API for reflecting SQL-NS instances. As a practical example, we examine the actual SubscriptionLoader code.

The SubscriptionLoader Code

A Visual Studio solution containing the SubscriptionLoader source code is located in the C:\SQL-NS\Tools\Source\SubscriptionLoader directory. The solution file is called SubscriptionLoader.sln. Open this solution in Visual Studio and refer to it as you read through this section.

Most of the SubscriptionLoader code deals with creating and maintaining the program's user interface. In this section, we look only at the parts of this code that use the NMO API.

Open the file MainForm.cs from the Solution Explorer. All the code covered in this section is in this file. By default, Visual Studio may open MainForm.cs in design view, showing the layout of the visual controls in the program's user interface. As you read through this section, you'll need to look at the code, not the user interface design, so you should switch from design view to code view. To do this, right-click MainForm.cs in the Solution Explorer and choose View Code.

Caution

Opening MainForm.cs in design view before the solution is built for the first time may result in the following error message from the designer: Could not find type 'SubscriptionLoader.SubscriptionFieldData'. If you build the solution at least once, close the file, and open it again, you should no longer see this error message. However, because you don't need to look at the file in design view for the purposes of this chapter anyway, you can ignore this message if you see it. Open the file in code view, as described in the preceding paragraph.

Preparing to Reflect: Connecting to a Server

Reflection begins with an SMO Server object representing a SQL Server. From the Server object, the SQL-NS instances on the SQL Server can be enumerated and their properties discovered.

In the SubscriptionLoader program, the connection to the SQL Server is established in the method MainForm_Load() in MainForm.cs. The MainForm_Load() method is called when SubscriptionLoader is started. Its purpose is to initialize the state of the application, including its user interface controls. The relevant parts of the MainForm_Load() implementation are shown in Listing 16.9.

Listing 16.9. The MainForm_Load() Method in the SubscriptionLoader's MainForm Class

 ... using Microsoft.SqlServer.Management.Common; using Microsoft.SqlServer.Management.Smo; using Nmo=Microsoft.SqlServer.Management.Nmo; ... namespace SubscriptionLoader {     public partial class MainForm : Form     {         private ServerConnection connection = null;         ...         private Nmo.NotificationServices ns = null;         ...         private void MainForm_Load(object sender, EventArgs e)         {            // Show the database connection dialog.            DBConnectionDialog connectDlg = new DBConnectionDialog();            if (connectDlg.ShowDialog(this) == DialogResult.OK)            {                // Create a connection object from the information                // supplied in the connection dialog.                connection = new ServerConnection();                connection.ServerInstance = connectDlg.ServerName;                if (AuthenticationMode.SqlServerAuthentication ==                    connectDlg.AuthenticationMode)                {                    connection.LoginSecure = false;                    connection.Login = connectDlg.SqlLoginName;                    connection.Password = connectDlg.SqlPassword;                 }                 ...                 // Use the connection to obtain a Server object.                 try                 {                     Server server = new Server(connection);                     ns = server.NotificationServices;                  }                  catch (Exception ex)                  {                       ...                   }                   // We should have obtained a reference to the                   // NotificationServices object on the server                   // at this point.                   Debug.Assert(null != ns);                   PopulateInstances();                }                ...             }         }     } 

As Listing 16.9 shows, MainForm_Load() opens a database connection dialog box in which the user can specify a SQL Server, select an authentication mode, and provide credentials if SQL Server Authentication is selected. After this information is obtained from the connection dialog box, the code establishes a connection to the specified SQL Server by creating a ServerConnection object. It then uses this to instantiate a Server object. A reference to the Server object's NotificationServices property is stored in the private member variable ns for use in other methods.

Finally, the MainForm_Load() method calls the helper method PopulateInstances(). This method fills one of the drop-down lists in the program's user interface with the names of the instances that exist on the selected SQL Server. We look at the implementation of this method in the following section.

Note

In the file MainForm.cs, the NMO namespace, Microsoft.SqlServer.Management.Nmo, is assigned the alias Nmo. This is accomplished by the using directive near the top of the file:

 using Nmo=Microsoft.SqlServer.Management.Nmo; 

Notice the Nmo= in front of the namespace name. Because the namespace is declared in this way, the token Nmo can be used as shorthand for the entire namespace name. All NMO class names are referenced in the file with this namespace prefix. For example, the declaration of the private member variable that stores an Instance object is given as

 private Nmo.Instance selectedInstance = null; 

Because the earlier using directive establishes the Nmo alias, Nmo.Instance is equivalent to using the fully qualified class name Microsoft.SqlServer.Management.Nmo.Instance.

Enumerating SQL-NS Instances

The SQL-NS instances that exist on a particular SQL Server are represented by a collection of NMO Instance objects. This collection is accessed via the Instances property on the NotificationServices object attached to the corresponding Server object. By iterating over this collection, the SQL-NS instances can be enumerated. In this section, we look at the part of the SubscriptionLoader code that enumerates instances this way.

The SubscriptionLoader user interface provides drop-down lists (represented as combo box controls) with which users can select a SQL-NS instance, an application within the selected instance, and then a subscription class within that application. The combo box controls are populated by code that uses NMO classes to enumerate instances, applications, and subscription classes. Listing 16.10 shows the PopulateInstances() method, which populates the first combo box with instance names.

Listing 16.10. The PopulateInstances() Method in the SubscriptionLoader's MainForm Class

 public partial class MainForm : Form {    ...    private void PopulateInstances()    {        comboBoxInstanceName.Items.Clear();        foreach (Nmo.Instance instance in ns.Instances)        {           comboBoxInstanceName.Items.Add(instance.Name);        }        ...    }    ... } 

PopulateInstances() first clears any existing elements in the combo box before filling it with the names of the current SQL-NS instances on the SQL Server specified earlier. Recall from Listing 16.9 that the NotificationServices object associated with the selected server was saved in the private member variable, ns. This is used here to obtain the Instances collection. The foreach loop iterates over the Instance objects in this collection. The code in the loop's body adds the name of each instance (obtained from the Name property of the Instance object) to the combo box. Thus, by walking through the Instances collection on the NotificationServices object, all the instances are enumerated.

When a user selects an instance name in the instance name combo box, the handler, comboBoxInstanceName_SelectedIndexChanged(), is called. Some of the code in this handler is shown in Listing 16.11.

Listing 16.11. The comboBoxInstanceName_SelectedIndexChanged() Method in the SubscriptionLoader's MainForm Class

 public partial class MainForm : Form {    ...    private Nmo.Instance selectedInstance = null;    ...    void comboBoxInstanceName_SelectedIndexChanged(        object sender,        EventArgs e)    {        string selectedInstanceName =            (string)comboBoxInstanceName.SelectedItem;        selectedInstance = ns.Instances[selectedInstanceName];        ...        // Populate the selection controls.        PopulateApplications();        ...    }    ... } 

The name of the selected instance is read from the combo box and stored in the local string variable, selectedInstanceName. This is then used as an index into the Instances collection on the stored NotificationServices object, ns. The Instance object representing the selected instance is returned. This is stored in the selectedInstance private member variable, for use in other methods. After the Instance object for the selected instance is obtained and stored, the PopulateApplications() method is called to populate the user interface controls with the list of applications in the selected instance. The next section looks at the code that accomplishes this.

Enumerating the Contents of an Instance

When an Instance object that represents an existing instance is obtained, its properties are initialized with values that reflect the real attributes of the instance, as read from the configuration in the database. Child objects are also created to represent the existing components of the instance (for example, its delivery channels, protocols, and applications, as well as the event, subscription, and notification classes within those applications). These objects are linked in parent-child relationships and ultimately attached to the Instance object. This forms a hierarchy that correctly represents the structure of the existing instance. This is the same kind of hierarchy that the code in Listings 16.4 and 16.5 created to define a new instance with NMO objects; the child objects are of the same NMO classes you saw in those earlier listings.

Note

Conceptually, you can think of the entire hierarchy being populated when the Instance object is first obtained. In reality, some of the child objects are constructed only when they are first accessed. This is merely a performance optimization and does not affect the way the API is used.

To enumerate the entities within an instance, we start with an existing Instance object (obtained as shown in the previous section) and traverse its various child objects. Continuing down the parent-child hierarchy, we can navigate the properties and collections on these child objects to learn about their children. In this way, we can, for example, go through an Instance object's Applications collection to find a particular Application object, and then through the SubscriptionClass objects in that Application object's SubscriptionClasses collection. We can continue all the way down to the SubscriptionField objects in a SubscriptionClass object to learn the name and data type of each field. This is, in fact, what the SubscriptionLoader tool does. Listing 16.12 shows some of the related code.

Listing 16.12. Methods in the SubscriptionLoader's MainForm Class That Enumerate Applications and Subscription Classes

 public partial class MainForm : Form {     ...     private Nmo.Instance selectedInstance = null;     private Nmo.Application selectedApplication = null;     private Nmo.SubscriptionClass selectedSubscriptionClass = null;     ...     private void PopulateApplications()     {        comboBoxApplicationName.Items.Clear();        foreach (Nmo.Application application in            selectedInstance.Applications)        {            comboBoxApplicationName.Items.Add(application.Name);        }        ...      }      void comboBoxApplicationName_SelectedIndexChanged(           object sender,           EventArgs e)      {           // Establish a connection to the selected application.           string selectedApplicationName =           (string)comboBoxApplicationName.SelectedItem;       selectedApplication =            selectedInstance.Applications[selectedApplicationName];       ...       PopulateSubscriptionClasses();     }     private void PopulateSubscriptionClasses()     {         comboBoxSubscriptionClass.Items.Clear();         foreach (Nmo.SubscriptionClass subscriptionClass in             selectedApplication.SubscriptionClasses)       {             comboBoxSubscriptionClass.Items.Add(                 subscriptionClass.Name);       }       ...     }     void comboBoxSubscriptionClass_SelectedIndexChanged(         object sender,         EventArgs e)     {         string selectedSubscriptionClassName =              (string)comboBoxSubscriptionClass.SelectedItem;         selectedSubscriptionClass =               selectedApplication. SubscriptionClasses[selectedSubscriptionClassName];         ...         // Populate list of subscription fields.         PopulateSubscriptionData();     }     ...   } 

The PopulateApplications() method populates the applications combo box in the user interface with the list of applications within the selected instance. Its structure is much the same as the PopulateInstances() method we looked at in Listing 16.10. First, the applications combo box is cleared. Then, the code iterates over the Application objects in the Applications collection on the selectedInstance object. Recall from Listing 16.11 that selectedInstance is set to the Instance object representing the selected instance. The Applications collection on the Instance object contains one Application object for each application in the instance. By walking through the contents of this collection, the code enumerates all applications, adding the name of each one to the applications combo box.

When a particular application name is selected in the user interface, the comboBoxApplicationName_SelectedIndexChanged() handler is called. The code in this handler obtains the Application object corresponding to the selected application. It does this by using the selected application name (read from the combo box control) as an index into the Instance object's Applications collection. The Application object obtained from the collection is stored in the selectedApplication private member variable.

Following essentially the same patterns, the code in the PopulateSubscriptionClasses() method populates the list of subscription classes, and the comboBoxSubscriptionClass_SelectedIndexChanged() handler reacts to the selection of a particular subscription class by obtaining and storing the corresponding SubscriptionClass object. Notice that the subscription classes in the application are enumerated via the SubscriptionClasses collection on the Application object.

In code not shown in Listing 16.12, the application obtains the names of the fields in the selected subscription class by enumerating the SubscriptionField objects in the SubscriptionClass object's SubscriptionFields collection. Reading the name of each subscription field from the SubscriptionField object's Name property, the application populates the field name labels in the data grid used to enter field values.

Although only a few examples are shown here, the NMO API has the capability to enumerate all the entities within an instance and its applications. By traversing all the properties and child objects, starting from an Instance object, the complete instance definition can be obtained. Listings 16.4 and 16.5 show more of the NMO classes and properties in the context of defining a new instance, but these same classes and properties are used to reflect existing instances as well. The organization of the NMO classes is shown in Figure 16.1 (although the collection classes are not explicitly labeled). The SQL-NS Books Online provides complete reference documentation on each one.

Updating An Instance With Nmo

The NMO API can be used to update the definition of an existing SQL-NS instance. The same update functionality provided by the standard update tools, nscontrol update and the Update Instance command in Management Studio, is provided through NMO. This chapter's source code does not include an example of updating an instance, but this sidebar provides an overview of the process.

Updating an instance with NMO involves a combination of the techniques used to reflect over an existing instance and those used to create new instances. To begin, you obtain an Instance object that represents the instance you want to modify. As you saw in the reflection examples, you can then obtain information about the instance's subcomponents by navigating the NMO hierarchy. What the reflection examples didn't show is that, in addition to just reading the structure of the instance, you can also modify it.

You can add new components by creating new NMO objects and linking them into the existing object hierarchy (just like you did when defining a new instance). For example, to add a new notification class, you create a new NotificationClass object, set its parent to be an existing Application object, and then add it to the parent's NotificationClasses collection.

To modify existing components, you change the property values on the existing NMO objects. Some properties, such as the names of objects, cannot be changed. Attempting to change the values of these properties results in an exception.

You can remove existing components by extricating them from the existing object hierarchy. To remove a component, you remove the corresponding object from its parent object's collection. For example, to remove an event class, you locate its EventClass object and then pass it to the Remove() method on the parent Application object's EventClasses collection.

After making the necessary changes to the objects that represent the instance, you call the Instance object's Update() method to compile the changes and apply them to the database objects. Doing this invokes the SQL-NS compiler, which performs the same operations it does when invoked from nscontrol update or the Update Instance command in Management Studio. It compares the new instance definition (expressed in the modified NMO objects) with the existing compiled definition in the database and then updates the database objects according to the differences found. Until the Update() method is called, any changes made to the instance definition in the NMO objects in memory are not reflected in the database objects.

Before calling the Update() method on the Instance object, the instance must be disabled. You can disable the instance programmatically using the Disable() method on the Instance object, or you can use the standard SQL-NS tools to disable the instance from the command line or Management Studio.

As you've probably noticed from running the standard update tools, the SQL-NS compiler can take a significant amount of time to complete an update. Therefore, the Update() method can take a long time to return and will block the calling thread for the duration of the update process.