Export (0) Print
Expand All

Adding a Subscription

SQL Server 2005

The Subscription object exposes properties that allow you to set subscription information such as the subscriber ID, an indexer to the fields that define the subscription data, and whether the subscription is enabled to generate notifications.

The Add method of this object writes that data to the application database, and returns the system-generated SubscriptionId for the subscription. The SubscriptionId is stored as a 64-bit integer in the database, but is returned to the application as a string.

The following code example shows how to use the Item method of the Subscription class to set the value of the application-specific subscription fields:

// Create the NSInstance object.
NSInstance testInstance = new NSInstance("Tutorial");

// Create the NSApplication object.
NSApplication testApplication =
    new NSApplication(testInstance, "Weather");

// Create the Subscription object.
Subscription testSubscription =
    new Subscription(testApplication, "WeatherCity");


// Set the properties that describe the subscription record.
testSubscription.Enabled = true;
testSubscription.SubscriberId = "TestUser1";

// Set the subscription data fields (as defined in the ADF),
// using the indexer to set fields by field name.
testSubscription["DeviceName"] = "Work e-mail";
testSubscription["SubscriberLocale"] = "en-US";
testSubscription["City"] = "Shoreline";

// Add the subscription to the database.
testSubscription.Add();

Some subscription classes support scheduled subscriptions. For scheduled subscriptions, you must provide two additional properties to provide the schedule recurrence and start date.

Add the following code to the sample above, before the Add method, to create a scheduled subscription. The ScheduleRecurrence property configures the subscription to be processed daily. The ScheduleStart property configure the subscription to be processed at the current time in the Pacific time zone. For the list of supported time zone codes, see Time Zone Codes.

ms171385.note(en-US,SQL.90).gifNote:
The values for the ScheduleStart and ScheduleRecurrence properties of the Subscription class must conform to the Notification Services subset of the ICalendar interface specification. For more information, see the property topics.

// Set the recurrence of the subscription.
testSubscription.ScheduleRecurrence = "FREQ=DAILY";

// Set the start date and time of the subscription.
StringBuilder scheduleBuilder = new StringBuilder();
scheduleBuilder.AppendFormat("TZID={0}:{1}{2}{3}T{4}{5}{6}",
    "4",
    DateTime.Now.Year.ToString("D4"),
    DateTime.Now.Month.ToString("D2"),
    DateTime.Now.Day.ToString("D2"),
    DateTime.Now.Hour.ToString("D2"),
    DateTime.Now.Minute.ToString("D2"),
    DateTime.Now.Second.ToString("D2"));
testSubscription.ScheduleStart = scheduleBuilder.ToString();

The following code shows how to create a subscription for a subscription class that uses condition actions. The RuleName and Condition properties are from the Subscription class. The objects used to define the condition, such as the OrCondition class, are from the Microsoft.SqlServer.NotificationServices.Rules namespace.

This example shows how to create a subscription and condition using hard-coded text values. Your subscription management interface can provide drop-down lists and text boxes to allow users to select and enter values.

// Create the NSInstance object.
NSInstance testInstance =
    new NSInstance("InventoryTrackerInstance");

// Create the NSApplication object.
NSApplication testApplication =
    new NSApplication(testInstance, "InventoryTracker");

// Define subscription properties
Subscription s = new Subscription(testApplication, "InventoryTrackerSubscriptions");
s.SubscriberId = "TestUser1";
s.Enabled = true;
s.RuleName = "InventoryTrackerRule";
s["DeviceName"] = "Work e-mail";
s["SubscriberLocale"] = "en-US";

// Define OrCondition
s.Condition = new OrCondition(
    new SimpleLeafCondition(new FieldValue("Quantity"),
        SimpleOperator.GreaterThanOrEqualTo,
        500),
    new SimpleLeafCondition(new FieldValue("Quantity"),
        SimpleOperator.LessThanOrEqualTo,
        35)
);

// Add subscription
s.Add();

The subscription condition can be a rich object graph combining many AND, OR, and NOT conditions, as well as various argument types and even other rules, via LinkLeafCondition objects.

The following code example shows how to use the SetFieldValue method of the Subscription class to set the value of the application-specific subscription fields:

ms171385.note(en-US,SQL.90).gifNote:
COM Interop is not supported for condition-based subscriptions.


Dim testInstance, testApplication, testSubscription, subscriptionId

const instanceName = "Tutorial"
const applicationName = "Weather"
const subscriptionClassName = "WeatherCity"

' Create the NSInstance object.
set testInstance = WScript.CreateObject( _ 
    "Microsoft.SqlServer.NotificationServices.NSInstance")
testInstance.Initialize instanceName

' Create the NSApplication object.
set testApplication = WScript.CreateObject( _ 
    "Microsoft.SqlServer.NotificationServices.NSApplication")
testApplication.Initialize (testInstance), applicationName

' Create the Subscription object.
set testSubscription = WScript.CreateObject( _
    "Microsoft.SqlServer.NotificationServices.Subscription")
testSubscription.Initialize (testApplication), subscriptionClassName


' Set the properties that describe the subscription record.
testSubscription.SubscriberId = "TestUser2"
testSubscription.Enabled = true

' Set the subscription data fields 
testSubscription.SetFieldValue "DeviceName", "Work e-mail"
testSubscription.SetFieldValue "SubscriberLocale", "en-US"
testSubscription.SetFieldValue "City", "Anaheim"

' Add the subscription to the database.
subscriptionId = testSubscription.Add

wscript.echo "Subscription added."

Community Additions

ADD
Show:
© 2015 Microsoft