Share via

Lesson 5: Adding a Subscription Class

  • Article

In this lesson, you will add a subscription class to the application definition file (ADF) and then update the instance of Notification Services to apply the changes to the application database. You can then review the changes made to the application database.

Subscription Class Primer

When you define a subscription class, you are defining storage for subscription data and queries that generate notifications from event and subscription data.

When defining storage for subscription data, you define fields for the subscription data you collect. For example, if part of the subscription data is a city of interest, you can add a City subscription field. Then, you provide an interface to let subscribers create subscriptions in which they specify a city.

If your application supports multiple subscriber locales and multiple devices, such as e-mail and cellular phones, you also can add DeviceName and SubscriberLocale fields to the subscription fields. These fields let you customize notifications for different devices, time zones, and languages.

Notification Services adds a SubscriberId field to the underlying table for each subscription class.

Besides defining subscription fields, you must also define a notification generation rule. This rule is a Transact-SQL query that matches event and subscription data and inserts the results into the notification class view. These rules can be event driven or scheduled. Event-driven rules run whenever events arrive. Scheduled rules evaluate individual subscriptions on a schedule provided in the subscription.

A subscription class can define other information, such as indexes on the subscription data and supplemental tables. For more information about subscription classes, see Defining Subscription Classes.

When you create the application, Notification Services uses the subscription class definition to create tables, views, indexes, and stored procedures for the subscription class.

The WeatherCity Subscription Class

For this application, you will add a WeatherCity subscription class to the ADF. This subscription class has three fields, DeviceName, SubscriberLocale, and City. The City field is the important field for this application. When subscribers create weather subscriptions, they must specify the name of a city.

The DeviceName and SubscriberLocale subscription fields are defined so that you can add support for multiple devices and locales.

This subscription class also defines a notification generation query. To write this query, you must know what event data is available, what notification data you want to produce, and what subscription data is available:

  • As shown in Lesson 3, the WeatherData event class has five fields: City, Date, Low, High, and Forecast.
  • The subscription class has a City field.
  • As shown in Lesson 4, the WeatherAlerts notification class has the same fields as the event class (City, Date, Low, High, and Forecast).
  • Also, remember that the underlying notification table has the following fields: SubscriberId, DeviceName, and SubscriberLocale.

These fields become columns in views. Knowing these fields, you can join the event and subscription views and insert data into the notification view:

INSERT INTO WeatherAlerts(SubscriberId, 
   DeviceName, SubscriberLocale, City, Date, Low, High, 
   Forecast)
SELECT s.SubscriberId, s.DeviceName, s.SubscriberLocale,
    e.City, e.Date, e.Low, e.High, e.Forecast
FROM WeatherData e, WeatherCity s
WHERE e.City = s.City;

When Notification Services runs this query, it joins the current set of events in the event class view with the subscriptions in the subscription class view based on matches between the event and subscription City values. Notification Services then inserts the results into the notification view.

Add the Subscription Class to the ADF

The XML in this section defines the subscription class. Examine the XML and then follow the instructions to copy the XML to the ADF.

To add the subscription class to the ADF

  1. Click Copy Code to copy the XML to the Windows Clipboard.

    <!-- Subscription Classes -->
<SubscriptionClasses>
  <SubscriptionClass>
    <SubscriptionClassName>WeatherCity</SubscriptionClassName>
    <Schema>
      <Field>
        <FieldName>DeviceName</FieldName>
        <FieldType>nvarchar(255)</FieldType>
        <FieldTypeMods>not null</FieldTypeMods>
      </Field>
      <Field>
        <FieldName>SubscriberLocale</FieldName>
        <FieldType>nvarchar(10)</FieldType>
        <FieldTypeMods>not null</FieldTypeMods>
      </Field>
      <Field>
        <FieldName>City</FieldName>
        <FieldType>nvarchar(40)</FieldType>
        <FieldTypeMods>not null</FieldTypeMods>
      </Field>
    </Schema>
    <EventRules>
      <EventRule>
        <RuleName>WeatherEventRule</RuleName>
        <EventClassName>WeatherData</EventClassName>
        <Action>
          INSERT INTO WeatherAlerts(SubscriberId, 
             DeviceName, SubscriberLocale, City, Date, Low, High, 
             Forecast)
          SELECT s.SubscriberId, s.DeviceName, s.SubscriberLocale,
              e.City, e.Date, e.Low, e.High, e.Forecast
          FROM WeatherData e, WeatherCity s
          WHERE e.City = s.City;
        </Action>
      </EventRule>
    </EventRules>
  </SubscriptionClass>
</SubscriptionClasses>

  2. In Solution Explorer, open WeatherADF.xml.

  3. Replace the following XML with the code you just copied.

    <!-- Subscription Classes -->

    <SubscriptionClasses></SubscriptionClasses>

  4. On the File menu, select Save WeatherADF.xml.

Update the Instance of Notification Services

After you modify the ADF, you update the instance of Notification Services to add subscription class objects to the application database.

To update the instance of Notification Services

  1. In Object Explorer, expand Notification Services.

  2. Right-click Tutorial, point to Tasks, and then select Update.

  3. In the Update Instance dialog box, click Browse, locate the TutorialICF.xml file, and then click Open.

  4. In the Parameters pane, review the parameter values.

  5. Click OK.

  6. Review the Notification Services - Update Summary dialog box, and then click Update.

  7. When the update actions complete, click Close.

Review the Changes in the Application Database

When you update the instance, Notification Services adds tables, views, and stored procedures for the subscription class in the TutorialWeather application database:

  • The dbo.WeatherCityView view provides a view of the subscription records for the associated subscription class.

  • The dbo.WeatherCity view contains the current set of subscriptions being evaluated by the application. In your notification generation query, you select subscription data from this view.

  • The dbo.NSWeatherCitySubscriptions table is the underlying table that contains subscription data for the subscription class.

    Warning

    Do not directly modify the data in the subscription tables and views. Use the subscription management objects. For more information, see Developing Subscription Management Interfaces.

You can use Object Explorer to examine these objects in the TutorialWeather database. Tables and views for internal use only contain data used for internal tracking and data lookup.

You may have to refresh Object Explorer to see the new objects.

Next Lesson

Lesson 6: Adding an Event Provider

See Also

Concepts

Notification Services Tutorial

Other Resources

Notification Services Views
Notification Services Tables
Defining Subscription Classes
Building Notification Solutions
Introducing SQL Server Notification Services

Help and Information

Getting SQL Server 2005 Assistance