Developing Notification Services Applications

In the first part of this chapter, you got an overview of the new SQL Server 2005 Notification Services. In this next section, we’ll dig in a little deeper so that you learn about the actual steps involved in developing SQL Server 2005 Notification Services applications. First, you’ll get an overview of the development process, and next we’ll dive in and build a very simple Notification Services application.

Development Stages

The process for developing Notification Services applications begins with defining the schema and rules that govern how the application works. Next, you must compile the application. Then, you need to construct an interface that allows the user to add subscriptions to the application. Finally, you’ll need to add any custom components that may be needed by the application. Let’s look at each of these steps in more detail.

Define the Schema and Rules

The Notification Services developer uses a combination of XML and T-SQL to define the application’s schema and rules. When you define the schema and the rules for a Notification Services application, you are essentially describing the events that the application will monitor as well as the application’s subscriptions, its notifications, and the logic that will be used to match the events to the subscriptions. The Notification Services application’s schema and rules are primarily defined in two files: adf.xml and config.xml. You can create these files using a standard text editor or an XML-aware editor such as Visual Studio 2005 or XMLSpy. More detailed information about the specific contents of the adf.xml and config.xml files is presented a little later in this chapter.

Compile the Application

After the schema and the rules have been created, the next step in building a Notification Services application is to compile all of the code and register a service that will run the Notification Services applications. To compile the application, SQL Server 2005 provides the nscontrol command line utility, which is used to create, register, and also update the Notification Services application. In addition, the SQL Server Management Studio provides a dialog that enables you to interactively create Notification Services applications.

Build the Notification Subscription Management Application

The first two steps build the core engine of the Notification Services application. However, users still need a way of adding their own subscription information to the Notification Services application. To enable users to enter their subscription information, the Notification Services application needs a subscription management interface, which is typically a Web or GUI application built using ASP.NET, VB.NET, or C# technologies in conjunction with a development platform like Visual Studio 2005.

Add Custom Components

Finally, the last step in building your Notification Services application is to optionally add any custom components that might be needed by the application. Custom components would include any required event providers, content formats, or notification delivery protocols that are not included in the base SQL Server 2005 Notification Services product.

Example Notification Services Application

The example Notification Services application that is presented in the next part of this chapter monitors the value of a column in a SQL Server table. When that column value exceeds a certain threshold, the sample Notification Services application will send an e-mail notification to the end user. This sample is a simplified version of the Stock example that was included with the SQL Server 2000 Notification Services sample programs. To make all of this work, the application starts by using the SQL Server 2005 provider to inspect a table in the database. An event will need to be created that checks the value of a column in that table, the user must enter a subscription for that event, and a rule must be added to allow SQL Server to match the events to the subscriptions. When an event matches the event rule, the e-mail distribution provider will send an XML-formatted notification to the end user. Now that you’ve got an overview of the sample Notification Services application, let’s see how it’s built.

Creating the Application Definition and Application Configuration Files

Notification Services applications consist of two primary files: an application definition file (ADF) and an application configuration file (ACF)—both XML files that must be built in accordance with their xsd schemas. The XSD schemas serve to make sure that both documents possess the required elements and attributes. The ACF file defines the name of the Notification Services application as well as its instance name and the application’s directory path. The instance name is essentially the name of a Windows service that runs the Notification Services application.

The ADF file is the core file for the Notification Services; the different sections of the ADF describe the event, subscription, rules, and notification structure that will be employed by the Notification Services application. For the SQL Server 2000 release, you needed to do all of the XML coding by hand to create these two files. This was also true for the early betas of SQL Server 2005. However, the final SQL Server 2005 release will have graphical tools that enable you to create the ACF and ADF files without manually coding XML in a text editor.

In addition to these two primary files that define the Notification Services application and structure, a Notification Services application also needs a subscription management application that is responsible for adding subscription information to the Notification Services application’s database. While the ADF and the ACF are built primarily using XML and T-SQL, the subscription management application is typically built using a high-level language with a graphical user interface.

Tip 

Visual Studio.NET’s multiple project solution structure enables you to easily group both the XML application definition files and the subscription management code as two projects that are part of a common solution.

The following listing shows the ACF file, config.xml, that’s used in this chapter’s example Notification Services application:

<?xml version="1.0" encoding="utf-8"?> <NotificationServicesInstance xmlns:xsd="http://www.w3.org/2001/XMLSchema"   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"   xmlns="http://www.microsoft.com/MicrosoftNotificationServices     /ConfigurationFileSchema">     <InstanceName>NSSampleInstance</InstanceName>       <SqlServerSystem>tecayukon</SqlServerSystem>     <Applications>         <Application>             <ApplicationName>NSSample</ApplicationName>             <BaseDirectoryPath>C:\NSSample</BaseDirectoryPath>                 <ApplicationDefinitionFilePath>ADF.xml                 </ApplicationDefinitionFilePath>         </Application>     </Applications>     <DeliveryChannels>         <DeliveryChannel>             <DeliveryChannelName>EmailChannel                 </DeliveryChannelName>                 <ProtocolName>SMTP</ProtocolName>         </DeliveryChannel>     </DeliveryChannels> </NotificationServicesInstance>

You can see that the ACF is a relatively simple document. This file can be created using any text or XML-aware editor. The most important points to notice are the SqlServerSystem, InstanceName, ApplicationName, BaseDirectoryPath, and ApplicationDefinitionFilePath tags. As you might guess, the SqlServerSystem name tag contains the name of the SQL Server system that will host the Notification Services databases, the InstanceName tag defines the instance name for the application, and the ApplicationName tag defines the name of the Notification Services application. The BaseDirectoryPath tells the compiler where to find the ADF file, and the ApplicationDefinitionFilePath tag supplies the name of the XML document that contains the ADF code. In addition to these basic items, the ACF also uses the DeliveryChannel tag to define how notifications will be delivered. In this example, the DeliveryChannel tag uses the SMTP protocol to deliver e-mail notifications.

Defining the ADF Event Schema

The core definitions that control how a Notification Services application works are found in the ADF. The first thing that needs to be done to build the example application is to build the schema for the events. In the ADF file the EventClasses element contains the XML code that’s used to define the Notification Services events. The EventClasses element can contain multiple event definitions. Each event definition is described in a separate EventClass subelement. The following code snippet from the example adf.xml file illustrates the XML code used to create the sample:

<?xml version="1.0" encoding="utf-8" ?> <Application xmlns:xsd="http://www.w3.org/2001/XMLSchema"   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"   xmlns="http://www.microsoft.com/MicrosoftNotificationServices/   ApplicationDefinitionFileSchema">     <!-- Describe the Events  -->     <EventClasses>         <EventClass>             <EventClassName>DataEvents</EventClassName>             <Schema>                 <Field>                     <FieldName>DataID</FieldName>                     <FieldType>int</FieldType>                     <FieldTypeMods>not null</FieldTypeMods>                 </Field>                 <Field>                     <FieldName>DataValue</FieldName>                     <FieldType>int</FieldType>                     <FieldTypeMods>null</FieldTypeMods>                 </Field>             </Schema>             <IndexSqlSchema>                 <SqlStatement>CREATE INDEX DataEventsIndex                    ON DataEvents ( DataID )</SqlStatement>             </IndexSqlSchema>             <Chronicles>                 <Chronicle>                     <ChronicleName>DataEventsTable</ChronicleName>                     <SqlSchema>                         <SqlStatement>                           IF EXISTS(SELECT name FROM dbo.sysobjects                           WHERE name = 'DataEventsTable')                           DROP TABLE dbo.DataEventsTable                           CREATE TABLE DataEventsTable                           (                             [DataID]    int,                             [DataValue]    int                           )                         </SqlStatement>                     </SqlSchema>                 </Chronicle>             </Chronicles>         </EventClass>     </EventClasses>

All ADF files must begin with the application elements, which as you might guess, represent the Notification Services application. The primary elements within the application element that define the application are the EventClasses, SubscriptionClasses, and NotificationClasses elements. The preceding code listing contains the EventClasses section of the ADF. Because this sample application uses only a single event, the EventClasses element contains only one EventClass element, named DataEvents. The Schema section within the EventClass element defines the event schema that the Notification Services application will monitor. In this case, two columns are defined: the DataID column, which is a simple identifier, and the DataValue column, which contains an associated value. The resemblance of these two definitions to SQL Server columns is no coincidence. Notification Services uses these definitions to create columns in the Notification Services database. To ensure good performance, the IndexSqlSchema tag is use to create an index over the DataID column.

The Chronicles section defines tables that store event data for use by scheduled subscriptions. The Chronicles section has two required tags: the ChronicleName tag and the SqlSchema tag. The ChronicleName tag supplies the name of the Chronicle table, and the SqlSchema tag contains the T-SQL statements that create the table. Because this code is used during both the Notification Services creation and update processes, it must first check for the existence of the table and drop it if it’s found. The next line of T-SQL code then creates the table.

Defining the ADF Providers

After defining the events that the application will monitor, the next step in creating the Notification Services application is defining the provider that will deliver those events to the application. In the following code snippet from the next section of the adf.xml file, you can see the definition for the SQL Server event provider that is used to connect the Notification Services application to a SQL Server:

<!-- Specify the SQL Server Event Provider --> <Providers>     <HostedProvider>         <ProviderName>SQLData</ProviderName>         <ClassName>SQLProvider</ClassName>         <SystemName>tecayukon</SystemName>         <Schedule>             <Interval>P0DT00H00M60S</Interval>         </Schedule>         <Arguments>             <Argument>                 <Name>EventsQuery</Name>                 <Value>SELECT DataID, DataValue FROM DataEventsTable</Value>             </Argument>             <Argument>                 <Name>EventClassName</Name>                 <Value>DataEvents</Value>             </Argument>         </Arguments>     </HostedProvider> </Providers> 

The Providers section of the ADF describes the event providers used by the Notification Services application. In this example, the HostedProvider element defines the SQL Server event provider. The ProviderName element is used to assign a name to the provider, and the SystemName element supplies the name of the SQL Server system that the provider will connect to.

The Schedule element defines how often the provider will connect to the system; this interval is governed by the value defined in the Interval element. The value in the Interval element uses the XML duration data type. The 0DT portion of this value represents a date interval with a value of 0. The 00HR portion represents an hourly interval with a value of 0. The 00M segment represents a minute interval with a value of 0. The 60S portion represents a seconds interval with a value of 60. In other words, this interval is set at 60 seconds.

The Arguments element supplies the query that will be used to extract data from the event source. In this example, the contents of the DataID and DataValue column in the DataEventsTable will be retrieved every 60 seconds for the event class named DataEvents that was defined in the preceding EventClass element.

Defining the ADF Subscription Schema

Once the events have been described, the next step in creating the ADF file is defining the subscriptions. The following code listing shows the next portion of the adf.xml file that describes the subscriptions used by the sample Notification Services application:

<!-- Describe the Subscription --> <SubscriptionClasses>     <SubscriptionClass>         <SubscriptionClassName>DataSubs</SubscriptionClassName>         <Schema>             <Field>                 <FieldName>DeviceName</FieldName>                 <FieldType>nvarchar(255)</FieldType>                 <FieldTypeMods>not null</FieldTypeMods>             </Field>             <Field>                 <FieldName>SubLocale</FieldName>                 <FieldType>nvarchar(10)</FieldType>                 <FieldTypeMods>not null</FieldTypeMods>             </Field>             <Field>                 <FieldName>DataID</FieldName>                 <FieldType>int</FieldType>                 <FieldTypeMods>not null</FieldTypeMods>             </Field>             <Field>                 <FieldName>DataTriggerValue</FieldName>                 <FieldType>int</FieldType>                 <FieldTypeMods>not null</FieldTypeMods>             </Field>         </Schema>         <IndexSqlSchema>             <SqlStatement>CREATE INDEX DataSubIndex ON DataSubs               ( DataID )</SqlStatement>         </IndexSqlSchema>         <EventRules>             <EventRule>                 <RuleName>DataSubEventRule</RuleName>                 <Action>                     SELECT dbo.DataNotificationsNotify                       (s.DeviceName, s.SubLocale, e.DataID, e.DataValue)                       FROM DataSubs s JOIN DataEvents e                       ON s.DataID = e.DataID                       LEFT OUTER JOIN DataEventsTable t                       ON s.DataID = t.DataID                       WHERE s.DataTriggerValue &lt;= e.DataValue                       AND (s.DataTriggerValue &gt; t.DataValue                       OR .DataValue IS NULL)                     INSERT INTO DataEventsTable (DataID, DataValue)                     SELECT e.DataID, e.DataValue                       FROM DataEvents e                       WHERE e.DataID NOT IN                         (SELECT DataID from DataEventsTable)                     UPDATE DataEventsTable                       SET DataValue = e.DataValue                       FROM DataEvents e, DataEventsTable t                       WHERE e.DataID = t.DataID                       AND e.DataValue &gt; t.DataValue                 </Action>                 <EventClassName>DataEvents</EventClassName>             </EventRule>         </EventRules>     </SubscriptionClass> </SubscriptionClasses> 

Like the EventClasses, the SubscriptionClasses section of the ADF document can describe multiple subscriptions, where each subscription is described in a separate SubscriptionClass element. This example uses a single SubscriptionClass named DataSubs. The Schema section describes the subscription. The DeviceName field identifies that target device type. The SubLocale is used to optionally change the language that the subscriber will use to receive the notification. The DataID and DataTriggerValue fields identify the event that will be subscribed to and the data value that will trigger a notification. Again, as with the EventClass, the IndexSqlSchema element is used to create an index on the DataID column for better performance. Notification Services uses these descriptions to create database columns on SQL Server.

After the subscriptions have been set up, the next section of code in the EventRules element defines the logic that the Notification Services application will use to match events to subscriptions. While the Event and Subscription information is defined using XML, the event rules are created using T-SQL code that’s stored in the EventRules Action element. In this example, the most important thing to notice is that the DataSubs table is joined to the DataEvents table where the DataValue from the DataEvents table is greater than the DataTriggerValue but less than a new value. When the join condition is met, a row for the subscriber will be created. The following INSERT and UPDATE statements are used to update the Chronicle table when a value isn’t found or when the value is greater than the existing chronicle value.

The ADF Notification Schema

The next part of defining the ADF file is setting up the NotificationClasses schema. The NotificationClasses describe how the notification information will be delivered. The following code listing from the final section of the adf.xml file contains the NotificationClass definition. The NotificationClasses element could describe multiple notification types where each type is described in its own NotificationClass element. Because this sample application uses only one type of notification, the NotificationClasses section contains a single NotificationClass element.

<!-- Describes the Notifications --> <NotificationClasses>     <NotificationClass>         <NotificationClassName>DataNotifications</NotificationClassName>         <Schema>             <Fields>                 <Field>                     <FieldName>DataID</FieldName>                     <FieldType>int</FieldType>                 </Field>                 <Field>                     <FieldName>DataValue</FieldName>                     <FieldType>int</FieldType>                 </Field>             </Fields>         </Schema>         <!-- Specify the Content Format XSLT -->         <ContentFormatter>             <ClassName>XsltFormatter</ClassName>             <Arguments>                 <Argument>                     <Name>XsltBaseDirectoryPath</Name>                     <Value>C:\NSSample</Value>                 </Argument>                 <Argument>                     <Name>XsltFileName</Name>                     <Value>NSSample.xslt</Value>                 </Argument>             </Arguments>         </ContentFormatter>     </NotificationClass> </NotificationClasses> <Generator>     <SystemName>tecayukon</SystemName> </Generator> <Distributors>     <Distributor>         <SystemName>tecayukon</SystemName>     </Distributor> </Distributors> <ApplicationExecutionSettings>     <PerformanceQueryInterval>PT5S</PerformanceQueryInterval> </ApplicationExecutionSettings> </Application>

In this listing, you can see that the notification class is named DataNotifications. The DataNotification classes’ Schema element defines information that will be sent to the subscriber. Here you can see that the value of the DataID and DataValue fields will be sent as part of the notification.

The ContentFormatter element defines how the notification will be formatted when it is sent to the subscriber. This example illustrates using the built-in XSLTFormatter. The Arguments element describes the directory where the XSLT file is found as well as the name of the file. In this listing, you can see that the XSLT file is found in the C:\NSSample directory and is named NSSample.xslt.

The Generator, Distributor, and ApplicationExecutionSettings elements specify the SQL Server system that will be used to generate notifications, the system that will be used to distribute notifications, and the interval at which system performance counters will be updated, respectively.

Formatting the Notification Output

In the previous listing, you saw that the notification was formatted using the NSSample.xslt style sheet. You can see what that example style sheet looks like in the following listing:

<?xml version="1.0" encoding="UTF-8" ?> <xsl:stylesheet version="1.0"   xmlns:xsl="http://www.w3.org/1999/XSL/Transform">   <xsl:template match="notifications">     <HTML>     <BODY>       <xsl:apply-templates />       <I>This message was generated using       <BR/>Microsoft SQL Server Notification Services</I><BR/><BR/>     </BODY>     </HTML>   </xsl:template>   <xsl:template match="notification">     <P>The data value of <B><xsl:value-of select="DataID"/></B>     <BR/>reached the value <B><xsl:value-of select="DataValue"/></B>     </P>   </xsl:template> </xsl:stylesheet>

The style sheet used to format the Notification Services application’s output is a standard HTML style sheet. In the template section, you can see where the DataID and DataValue fields from the NotificationClass are displayed in the notification.

Creating the Notification Services Application

After the required XML and T-SQL application code has been created, you’re ready to build the Notification Services application. Notification Services applications can be created interactively using the SQL Server Management Studio, or they can be created using the nscontrol utility.

Creating Notification Services Applications Using SQL Server Management Studio

After the config.xml and adf.xml files that define the Notification Services have been created, you can use them to build your Notification Service application from the SQL Server Management Studio by first opening the Object Browser and right-clicking the Notification Services node. Then, you can select the New Notification Services Instance option from the context menu to display a screen like the one in Figure 5-3.

Figure 5-3: The New Notification Services Instance dialog

To create a new Notification Services application using the New Notification Services Instance dialog, you click Browse and navigate to the directory that contains your application’s config.xml file. Next, you select the config.xml file and click OK. If you want the application to be immediately enabled after it is created, you need to check the Enable Instance After It Is Created check box.

Creating Notification Services Applications Using Nscontrol

Nscontrol is a command-line tool that’s used to create and administer Notification Services applications. Nscontrol understands a number of different action commands that you can use to work with Notification Services applications. Table 5-1 lists the available nscontrol action commands.

Table 5-1: Nscontrol Commands

Nscontrol Command	Description
nscontrol create	Creates a Notification Services application and its databases.
nscontrol delete	Deletes a Notification Services application and its databases.
nscontrol disable	Disables a Notification Services application.
nscontrol displayargumentkey	Displays the key used to encrypt event data.
nscontrol enable	Enables a Notification Services application.
nscontrol listversions	Displays the version of Notification Services and any registered applications.
nscontrol register	Registers a Notification Services application.
nscontrol status	Displays the status of a Notification Services application.
nscontrol unregister	Unregisters a Notification Services application.
nscontrol update	Updates a Notification Services application.

Creating a Notification Services application is a multistep process. First, the application needs to be created using the nscontrol create command. This creates the database used by the Notification Services application. Next, the application needs to be registered using the nscontrol register command. This creates the service that is used to run the application. Then, the application needs to be enabled using the nscontrol enable command. The following batch file illustrates the command sequence needed to create the example NSSample Notification Services application:

echo off cls set NSdir="C:\Program Files\Microsoft SQL Server\90\NotificationServices\9.0.242\bin" echo ======================================== echo Beginning NSSampleInstance Creation echo ======================================== echo . echo Create the application databases %NSdir%\nscontrol create -in config.xml echo Register the application %NSdir%\nscontrol register -name NSSampleInstance –service echo Enable the application %NSdir%\nscontrol enable -name NSSampleInstance echo start the NS app as a service net start NS$NSSampleInstance echo Display the status of the app %NSdir%\nscontrol status -name NSSampleInstance

The nscontrol create command –in argument specifies the name of the Notification Services ACF. In this example, the ACF is named config.xml. Running the nscontrol create command creates two databases on the server, NSSampleInstanceMain and NSSampleInstanceNSSample, which store the Notification Services application definition and data events.

The nscontrol register command uses the –name argument to identify the instance name of the Notification Services application to register. The –service switch directs it to register a service named NS$NSSampleInstance.

The nscontrol enable command uses the –name parameter to identify the instance name of the application that will be enabled.

Once the application is enabled, its service can be started using the net start command. For testing, you can also execute the NS$NSSampleInstance application from the command prompt or the Run dialog.

Adding Subscribers to the Application

Subscribers are added to Notification Services applications using the managed code APIs that Microsoft provides with SQL Server 2005 Notification Services. Microsoft’s .NET Framework APIs enable you to add, update, and delete subscribers, as well as subscriber devices and subscriptions. While the Notification Services API is provided via managed code classes, you can also access the API from unmanaged code by using Win32-based COM applications.

The Notification Services API is located in Microsoft.SqlServer.NotificationServices.dll, which must be added to your .NET project as a reference. Then, you can use the Notification Services classes to manage subscriptions to your notification services applications. The following code sample shows how you can add a subscription using the managed code API:

using Microsoft.SqlServer.NotificationServices; using System.Text; public class NSSubscriptions {     private string AddSubscription(string instanceName, string       applicationName, string subscriptionClassName, string subscriberId)     {         // Create the Instance object         NSInstance myNSInstance = new NSInstance(instanceName);         // Create the Application object         NSApplication myNSApplication = new NSApplication          (myNSInstance, applicationName);         // Create the Subscription object         Subscription myNSSubscription = new Subscription           (myNSApplication, subscriptionClassName);         myNSSubscription.Enabled = true;         myNSSubscription.SubscriberId = subscriberId;         // Set the subscription data fields         myNSSubscription["DeviceName"] = "MyDevice";         myNSSubscription["SubscriberLocale"] = "USA";         myNSSubscription["DataID"] = 1;         myNSSubscription["DataTriggerValue"] = 100;         // Add the subscription         string subscriptionId = myNSSubscription.Add();         return subscriptionId;     } }

At the top of this listing, you can see an import directive for the Microsoft.SqlServer.NotificationServices namespace. Using the import directive enables you to use the classes in the NotificationServces namespace without requiring you to fully qualify the names. Next, inside the AddSubscriptions method you can see the code that adds the subscription. First, a Notification Services instance object is created, followed by an Application object. Then, the Application object is used to create a subscription object named myNSSubscriptions. The Subscription object’s properties are assigned values, and then its Add method is called to actually add the subscription to the database.