Section 18.3. Programming Notification Services


18.3. Programming Notification Services

You can build a Notification Services solution by configuring the Notification Services instance in the IDF file and defining an ADF for the application, as described in the preceding section. Alternatively, you can use NMO classes to configure the instance and define the instance. Programming with NMO classes is the focus of this section.

NMO contains classes that are used to programmatically create and administer Notification Services instances and applications. Figure 18-2 shows the relationship between NMO classes.

The NMO namespace Microsoft.SqlServer.Management.Nmo contains classes used to develop Notification Services instances and applications. The Microsoft.SqlServer.NotificationServices namespace contains classes and interfaces for developing custom event providers, content formatters, and delivery protocols. It also contains the subscription management interfaces.

The remainder of this chapter describes the NMO classes and provides examples that show how to use them. You need a reference to the following assemblies to compile and run the examples:

  • Microsoft.SqlServer.ConnectionInfo

  • Microsoft.SqlServer.NotificationServices

  • Microsoft.SqlServer.Smo

NotificationServices is the top-level class in the NMO class hierarchy and represents a Notification Services server.

Figure 18-2. NMO class hierarchy


18.3.1. Creating a Notification Services Application and Service

This section creates a complete Notification Services application that creates file and email notifications based on changes in a stock price. For the purpose of this example, the stock price information is limited to a stock ticker symbol and price. Two subscribers are createdone that is notified by entries in a text file and one that is notified using email. Each of the two users monitors a single stockticker symbols ABC and DEF.

This section shows the application that creates the StockWatch Notification Services application and adds two subscribers and a subscription for each. Subsequent sections discuss the different parts of the application in detail.

Follow these steps to create the StockWatch Notification Services application with two subscribers and a subscription for each, start the service, and generate notifications:

  1. Create a Visual Studio 2005 C# console application in the C:\PSS2005 directory. Name the project NotificationServices.

  2. Add the following references to the project:

    • Microsoft.SqlServer.ConnectionInfo

    • Microsoft.SqlServer.NotificationServices

    • Microsoft.SqlServer.Smo

  3. Replace the code in Program.cs with the code in Example 18-1. This code is explained in detail throughout the rest of this chapter.

    Example 18-1. Notification Services example

     using System; using Microsoft.SqlServer.Management.Smo; using Microsoft.SqlServer.Management.Nmo; using ns = Microsoft.SqlServer.NotificationServices; class Program {     private static Instance nsi;     private static Application a;     private const string baseDirectoryPath = @"C:\PSS2005\NotificationServices";     private const string nsServer = "NSServerName";     private const string serviceUserName = "NSUSerName";     private const string servicePassword = "NSPassword";     static void Main(string[] args)     {         Server server = new Server("(local)");         // create a new instance         NotificationServices ns = server.NotificationServices;         nsi = new Instance(ns, "StockWatch");         CreateDeliveryChannel(  );         // create a new application in the StockWatch instance         a = new Application(nsi, "StockWatchApp");         a.BaseDirectoryPath = baseDirectoryPath;         CreateEventClass(  );         CreateSubscriptionClass(  );         CreateNotificationClass(  );         CreateHostedEventProvider(  );         CreateGenerator(  );         CreateDistributor(  );         CreateVacuumSchedule(  );         a.QuantumDuration = new TimeSpan(0, 0, 15);         a.PerformanceQueryInterval = new TimeSpan(0, 0, 5);         a.SubscriptionQuantumLimit = 1;         a.ChronicleQuantumLimit = 1;         a.VacuumRetentionAge = new TimeSpan(0, 0, 1);         nsi.Applications.Add(a);         Console.WriteLine("Added application.");         nsi.Create(  );         nsi.RegisterLocal(serviceUserName, servicePassword);         nsi.Enable(  );         Console.WriteLine("Application enabled." + Environment.NewLine);         CreateSubscriber(  );         CreateSubscription(  );         Console.WriteLine(Environment.NewLine + "Press any key to continue.");         Console.ReadKey(  );     }     private static void CreateDeliveryChannel(  )     {         DeliveryChannelArgument dca;         // add file delivery channel         DeliveryChannel dcFile =             new DeliveryChannel(nsi, "StockWatchFileDeliveryChannel");         dcFile.ProtocolName = "File";         dca = new DeliveryChannelArgument(dcFile, "FileName");         dca.Value = baseDirectoryPath + @"\Notifications\FileNotifications.txt";         dcFile.DeliveryChannelArguments.Add(dca);         nsi.DeliveryChannels.Add(dcFile);         Console.WriteLine("Added delivery channel: " + dcFile.Name);         // add email delivery channel         DeliveryChannel dcEmail =             new DeliveryChannel(nsi, "StockWatchEmailDeliveryChannel");         dcEmail.ProtocolName = "SMTP";         nsi.DeliveryChannels.Add(dcEmail);         Console.WriteLine("Added delivery channel: " + dcEmail.Name);     }     private static void CreateEventClass(  )     {         EventClass ec = new EventClass(a, "StockWatchEvents");         EventField ef;         ef = new EventField(ec, "Symbol");         ef.Type = "nvarchar(6)";         ec.EventFields.Add(ef);         ef = new EventField(ec, "Price");         ef.Type = "float";         ec.EventFields.Add(ef);         a.EventClasses.Add(ec);         Console.WriteLine("Added event class: " + ec.Name);     }     private static void CreateSubscriptionClass(  )     {         SubscriptionClass sc = new SubscriptionClass(a, "StockWatchSubscriptions");         SubscriptionField sf;         sf = new SubscriptionField(sc, "DeviceName");         sf.Type = "nvarchar(255)";         sc.SubscriptionFields.Add(sf);         sf = new SubscriptionField(sc, "SubscriberLocale");         sf.Type = "nvarchar(10)";         sc.SubscriptionFields.Add(sf);         sf = new SubscriptionField(sc, "Symbol");         sf.Type = "nvarchar(6)";         sc.SubscriptionFields.Add(sf);         sf = new SubscriptionField(sc, "Price");         sf.Type = "float";         sc.SubscriptionFields.Add(sf);         SubscriptionEventRule ser =             new SubscriptionEventRule(sc, "StockWatchSubscriptionsEventRule");         ser.Action = @"INSERT INTO StockWatchNotifications (" +             "SubscriberId, DeviceName, SubscriberLocale, Symbol, Price) " +             "SELECT s.SubscriberId, s.DeviceName, s.SubscriberLocale, " +             "e.Symbol, e.Price " +             "FROM StockWatchEvents e, StockWatchSubscriptions s " +             "WHERE e.Symbol = s.Symbol";         ser.EventClassName = "StockWatchEvents";         sc.SubscriptionEventRules.Add(ser);         a.SubscriptionClasses.Add(sc);         Console.WriteLine("Added subscription class: " + sc.Name);     }     private static void CreateNotificationClass(  )     {         NotificationClass nc = new NotificationClass(a, "StockWatchNotifications");         NotificationField nf;         nf = new NotificationField(nc, "Symbol");         nf.Type = "nvarchar(6)";         nc.NotificationFields.Add(nf);         nf = new NotificationField(nc, "Price");         nf.Type = "float";         nc.NotificationFields.Add(nf);         ContentFormatter cf = new ContentFormatter(nc, "XsltFormatter");         ContentFormatterArgument cfa;         cfa = new ContentFormatterArgument(cf, "XsltBaseDirectoryPath");         cfa.Value = a.BaseDirectoryPath + @"\AppDefinition";         cf.ContentFormatterArguments.Add(cfa);         cfa = new ContentFormatterArgument(cf, "XsltFileName");         cfa.Value = "StockWatch.xslt";         cf.ContentFormatterArguments.Add(cfa);         nc.ContentFormatter = cf;         nc.DigestDelivery = true;         ProtocolField pf;         // add file notification class protocol         NotificationClassProtocol ncpFile =             new NotificationClassProtocol(nc, "File");         pf = new ProtocolField(ncpFile, "Symbol");         pf.FieldReference = "Symbol";         ncpFile.ProtocolFields.Add(pf);         pf = new ProtocolField(ncpFile, "Price");         pf.FieldReference = "Price";         ncpFile.ProtocolFields.Add(pf);         nc.NotificationClassProtocols.Add(ncpFile);         // add email notification class protocol         NotificationClassProtocol ncpEmail =             new NotificationClassProtocol(nc, "SMTP");         pf = new ProtocolField(ncpEmail, "Subject");         pf.SqlExpression = "'Stock watch: ' + CONVERT(nvarchar(30), GETDATE(  ))";         ncpEmail.ProtocolFields.Add(pf);         pf = new ProtocolField(ncpEmail, "BodyFormat");         pf.SqlExpression = "'html'";         ncpEmail.ProtocolFields.Add(pf);         pf = new ProtocolField(ncpEmail, "From");         pf.SqlExpression = "'notification@StockWatchService.com'";         ncpEmail.ProtocolFields.Add(pf);         pf = new ProtocolField(ncpEmail, "Priority");         pf.SqlExpression = "'Normal'";         ncpEmail.ProtocolFields.Add(pf);         pf = new ProtocolField(ncpEmail, "To");         pf.SqlExpression = "DeviceAddress";         ncpEmail.ProtocolFields.Add(pf);         nc.NotificationClassProtocols.Add(ncpEmail);         nc.ExpirationAge = new TimeSpan(1, 0, 0);         a.NotificationClasses.Add(nc);         Console.WriteLine("Added notification class: " + nc.Name);     }     private static void CreateHostedEventProvider(  )     {         HostedEventProvider hep = new HostedEventProvider(a, "StockWatchHEP");         hep.ClassName = "FileSystemWatcherProvider";         hep.SystemName = nsServer;         HostedEventProviderArgument hepa;         hepa = new HostedEventProviderArgument(hep, "WatchDirectory");         hepa.Value = baseDirectoryPath + @"\Events";         hep.HostedEventProviderArguments.Add(hepa);         hepa = new HostedEventProviderArgument(hep, "SchemaFile");         hepa.Value = baseDirectoryPath + @"\AppDefinition\EventsSchema.xsd";         hep.HostedEventProviderArguments.Add(hepa);         hepa = new HostedEventProviderArgument(hep, "EventClassName");         hepa.Value = "StockWatchEvents";         hep.HostedEventProviderArguments.Add(hepa);         a.HostedEventProviders.Add(hep);     }     private static void CreateGenerator(  )     {         // create a new generator for the application         Generator g = new Generator(a, "StockWatchGenerator");         g.SystemName = nsServer;         a.Generator = g;         Console.WriteLine("Created generator: " + g.Name);     }     private static void CreateDistributor(  )     {         Distributor d = new Distributor(a, "StockWatchDistributor");         d.SystemName = nsServer;         d.QuantumDuration = new TimeSpan(0, 0, 15);         a.Distributors.Add(d);         Console.WriteLine("Added distributor: " + d.Name);     }     private static void CreateVacuumSchedule(  )     {         VacuumSchedule vs = new VacuumSchedule(a, "StockWatchVacuumSchedule");         vs.StartTime = new TimeSpan(0, 0, 0);         a.VacuumSchedules.Add(vs);         Console.WriteLine("Added vacuum schedule: " + vs.Name);     }     private static void CreateSubscriber(  )     {         ns.NSInstance swnsi = new ns.NSInstance("StockWatch");         ns.Subscriber s;         ns.SubscriberDevice sd;         // create a subscriber         s = new ns.Subscriber(swnsi);         s.SubscriberId = @"KristinHamilton";         s.Add(  );         Console.WriteLine("Added subscriber: " + s.SubscriberId);         // create a file subscriber device         sd = new ns.SubscriberDevice(  );         sd.Initialize(swnsi);         sd.DeviceName = "StockWatchSubscriberDevice";         sd.SubscriberId = "KristinHamilton";         sd.DeviceTypeName = "File";         sd.DeviceAddress = "KristinH@StockWatch.ns";         sd.DeliveryChannelName = "StockWatchFileDeliveryChannel";         sd.Add(  );         Console.WriteLine("Added subscriber file device.");         // create a subscriber         s = new ns.Subscriber(swnsi);         s.SubscriberId = @"TonyHamilton";         s.Add(  );         Console.WriteLine("Added subscriber: " + s.SubscriberId);         // create an email subscriber device         sd = new ns.SubscriberDevice(  );         sd.Initialize(swnsi);         sd.DeviceName = "StockWatchSubscriberDevice";         sd.SubscriberId = "TonyHamilton";         sd.DeviceTypeName = "Email";         sd.DeviceAddress = "TonyH@StockWatchNS.ns";         sd.DeliveryChannelName = "StockWatchEmailDeliveryChannel";         sd.Add(  );         Console.WriteLine("Added subscriber email device.");     }     private static void CreateSubscription(  )     {         ns.NSInstance swnsi = new ns.NSInstance("StockWatch");         ns.NSApplication a = new ns.NSApplication(swnsi, "StockWatchApp");         ns.Subscription s;         // add subscriptions         s = new ns.Subscription(  );         s.Initialize(a, "StockWatchSubscriptions");         s.SetFieldValue("DeviceName", "StockWatchSubscriberDevice");         s.SetFieldValue("SubscriberLocale", "en-us");         s.SubscriberId = "KristinHamilton";         s.SetFieldValue("Symbol", "ABC");         s.SetFieldValue("Price", "0.00");         s.Add(  );         Console.WriteLine("Added subscription: " + s.SubscriberId);         s = new ns.Subscription(  );         s.Initialize(a, "StockWatchSubscriptions");         s.SetFieldValue("DeviceName", "StockWatchSubscriberDevice");         s.SetFieldValue("SubscriberLocale", "en-us");         s.SubscriberId = "TonyHamilton";         s.SetFieldValue("Symbol", "DEF");         s.SetFieldValue("Price", "0.00");         s.Add(  );         Console.WriteLine("Added subscription: " + s.SubscriberId);     } } 

  4. Replace the following string constant values in lines 13 to 15:


    NSServerName

    The server that runs the Notification Services engine components. Use the name of the local computer for this example.


    NSUserName

    The account the NS$StockWatch service runs under.


    NSPassword

    The password for the NSUserName account.

  5. Compile and execute the code. The results are shown in Figure 18-3.

    Figure 18-3. Results for Notification Services example

  6. Two databases are created when the NMO application is runStockWatchNSMain and StockWatchStockWatchApp. Ensure that the service login account specified in Step 4 has at least the NSRunService database role membership for both of these databases.

  7. Refresh the Notification Services node in Object Explorer in SQL Server Management Studio to view the new Notification Services service, as shown in Figure 18-4.

    Figure 18-4. Results for StockWatch service example

  8. Right-click the StockWatch service and select Start from the context menu to start the service.

  9. Right-click the StockWatch service and select Properties from the context menu to display the Instance Properties dialog box, shown in Figure 18-5.

    Figure 18-5. StockWatch application instance properties

  10. Select the Windows Services page to display the service status, as shown in Figure 18-6. The service should be running.

  11. Ensure that the SMTP servicea component of Internet Information Services (IIS)is installed and started. For more information, see Microsoft SQL Server 2005 Books Online.

  12. Create the following three folders in the C:\PSS2005\NotificationServices directory:


    AppDefinition

    The folder containing the XSLT transformation file used to format notifications (StockWatch.xslt in this example) and the schema for the event data (EventSchema.xsd in this example). These two files are discussed in Steps 13 and 14.


    Events

    The folder in which event data is placed as XML files (named EventData.xml in this example).


    Notifications

    The folder in which file notifications are created.

    Figure 18-6. StockWatch Windows Services instance properties

  13. Create a file named EventSchema.xsd, as shown in Example 18-2, in the C:\PSS2005\NotificationServices\AppDefinition folder. This file describes the schema of the event data in the EventData.xml file described in Step 15.

    Example 18-2. EventSchema.xsd

     <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"   xmlns:sql="urn:schemas-microsoft-com:mapping-schema">   <xsd:element name="event" sql:relation="FlightEvents">     <xsd:complexType>       <xsd:sequence>         <xsd:element name="Symbol" type="xsd:string" />         <xsd:element name="Price" type="xsd:float" />       </xsd:sequence>     </xsd:complexType>   </xsd:element> </xsd:schema> 

  14. Create a file named StockWatch.xslt, as shown in Example 18-3, in the C:\PSS2005\NotificationServices\AppDefinition folder. This file is used to format the notification data for both file and email notifications.

    Example 18-3. StockWatch.xslt

     <?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>             StockWatch Price Update<br/><br/>             <xsl:apply-templates/>             <br/><br/>             <i>SQL Server StockWatch Notification Services</i><br/><br/>     </body>     </html>     </xsl:template>     <xsl:template match="notification">         The price of <b><xsl:value-of select="Symbol" /></b> is now <b>         $<xsl:value-of select="Price" /></b>.<br/>     </xsl:template> </xsl:stylesheet> 

  15. Create a file named EventData.xml, as shown in Example 18-4, in the C:\PSS2005\NotificationServices folder. This XML file contains the event data. In this example, the events for symbols ABC and DEF have subscriptions and generate notifications. The event for symbol GHI has no subscriptions and does not generate a notification.

    Example 18-4. EventData.xml

     <eventData>     <event>         <Symbol>ABC</Symbol>         <Price>3.83</Price>     </event>     <event>         <Symbol>DEF</Symbol>         <Price>5.75</Price>     </event>     <event>         <Symbol>GHI</Symbol>         <Price>1.22</Price>     </event> </eventData> 

  16. Copy the EventData.xml file into the C:\PSS2005\NotificationServices\Events folder to submit events. The File System Watcher event provider reads data from the application, submits the data to the application StockWatchApp, and changes the extension of the event datafile to .done once the file is processed. If there is an error processing the file, the extension of the datafile is changed to .err.

  17. After less than a minute, a notification file named FileNotification.txt is created in the C:\PSS2005\NotificationServices\Notifications folder, as shown in Figure 18-7.

    Figure 18-7. FileNotification.txt

    The email message shown in Figure 18-8 is also generated. It appears in the C:\Inetpub\mailroot\Queue folder briefly as a file with an .eml extension and is then moved to the C:\Inetpub\mailroot\Badmail folder because the email address is not valid. To deliver the email, change the email address for the user TonyHamilton in the CreateSubscriber( ) method of Example 18-1 to a valid email address by changing the DeviceAddress property of the SubscriberDevice object for that user.

    Notice that both the file and email notifications are formatted using the XSLT transformation StockWatch.xslt, discussed in Step 14.

The remainder of this chapter discusses the code that creates the StockWatch service, subscribers, and subscriptions.

18.3.2. Creating a Notification Services Instance and Application

The Notification Services instance named StockWatch and a Notification Services application named StockWatchApp are created in the Main( ) method of Example 18-1. The code followsthe code that creates the Notification Services instance and application is highlighted:

         static void Main(string[] args)         {             Server server = new Server("(local)"); 

Figure 18-8. StockWatch notification email


             // create a new instance             NotificationServices ns = server.NotificationServices;             nsi = new Instance(ns, "StockWatch");             CreateDeliveryChannel(  );             // create a new application in the StockWatch instance             a = new Application(nsi, "StockWatchApp");             a.BaseDirectoryPath = baseDirectoryPath;             CreateEventClass(  );             CreateSubscriptionClass(  );             CreateNotificationClass(  );             CreateHostedEventProvider(  );             CreateGenerator(  );             CreateDistributor(  );             CreateVacuumSchedule(  );             a.QuantumDuration = new TimeSpan(0, 0, 15);             a.PerformanceQueryInterval = new TimeSpan(0, 0, 5);             a.SubscriptionQuantumLimit = 1;             a.ChronicleQuantumLimit = 1;             a.VacuumRetentionAge = new TimeSpan(0, 0, 1);             nsi.Applications.Add(a);             Console.WriteLine("Added application.");             nsi.Create(  );             nsi.RegisterLocal(serviceUserName, servicePassword);             nsi.Enable(  );             Console.WriteLine("Application enabled." + Environment.NewLine);             CreateSubscriber(  );             CreateSubscription(  );             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         } 

The NotificationServices object represents a Notification Services server. The Instances property of the NotificationServices class returns an InstanceCollection object containing a collection of Notification Services instances as Instance objects. At a minimum, you must define a DeliveryChannel object and an Application object to create an Instance objectin this example, this is done by the CreateDeliveryChannel( ) and Main( ) methods.

The RegisterLocal( ) method of the Instance class registers an instance of Notification Services on the local computer. This is the same as registering a Notification Services instance using SQL Server Management Studio by right-clicking the Notification Services instance and selecting Tasks Register from the context menu. You can also register a Notification Services instance by using the nscontrol register command. Registering an instance creates or updates registry entries for the instance, creates performance counters, and optionally creates a Windows Service to run the instance.

The Enable( ) method of the Instance class enables all instance and application components, allowing event collection, notification generation, notification distribution, and subscription management. A NotificationServices instance is disabled when you create it.

The NMO classes used to manage Notification Services instances are described in Table 18-1.

Table 18-1. NMO classes for managing Notification Services instances

Class

Description

Instance

Represents a Notification Services instance.

InstanceCollection

Represents a collection of instances as Instance objects. The Instances property of the NotificationServices class returns the Notification Services instances on the server.


The Application object represents a Notification Services application. At a minimum, you must define a Generator object and a Distributor object for an Application objectin this example, this is done by the CreateGenerator( ) and CreateDistributor( ) methods.

This example configures the Application object by setting the following properties:


BaseDirectoryPath

Specifies the base directory path for the ADF


QuantumDuration

Specifies how frequently the generator tries to process work


PerformanceQueryInterval

Specifies how frequently the application updates its performance counters


SubscriptionQuantumLimit

Specifies how far the logical clock can fall behind the real-time clock before skipping subscription rule firings


ChronicleQuantumLimit

Specifies how far the logical clock can fall behind the real-time clock before skipping event chronicle firings


VacuumRetentionAge

Specifies the minimum age at which event and notification data is considered obsolete and can be removed

The NMO classes used to manage Notification Services applications are described in Table 18-2.

Table 18-2. NMO classes for managing applications

Class

Description

Application

Represents a Notification Services application.

ApplicationCollection

Represents a collection of Notification Services applications as Application objects. The Applications property of the Instance class returns the Notification Services applications hosted on the Notification Services instance.


18.3.3. Creating a Delivery Channel

A file delivery channel named StockWatchFileDeliveryChannel and an email delivery channel named StockWatchEmailDeliveryChannel are created in the CreateDeliveryChannel( ) method of Example 18-1. The code follows:

     private static void CreateDeliveryChannel(  )     {         DeliveryChannelArgument dca;         // add file delivery channel         DeliveryChannel dcFile =             new DeliveryChannel(nsi, "StockWatchFileDeliveryChannel");         dcFile.ProtocolName = "File";         dca = new DeliveryChannelArgument(dcFile, "FileName");         dca.Value = baseDirectoryPath + @"\Notifications\FileNotifications.txt";         dcFile.DeliveryChannelArguments.Add(dca);         nsi.DeliveryChannels.Add(dcFile);         Console.WriteLine("Added delivery channel: " + dcFile.Name);         // add email delivery channel         DeliveryChannel dcEmail =             new DeliveryChannel(nsi, "StockWatchEmailDeliveryChannel");         dcEmail.ProtocolName   = "SMTP";         nsi.DeliveryChannels.Add(dcEmail);         Console.WriteLine("Added delivery channel: " + dcEmail.Name);     } 

You have to add at least one delivery channel to a Notification Services instance before creating it. The ProtocolName property of the DeliveryChannel object must be set to SMTP, File, or the name of a custom delivery protocol.

The NMO classes used to manage delivery channels are described in Table 18-3.

Table 18-3. NMO classes for managing delivery channels

Delivery channel

Description

DeliveryChannel

Represents a delivery channel.

DeliveryChannelArgument

Represents a name-value pair specifying delivery channel configuration and authentication information for the delivery service.

DeliveryChannelArgumentCollection

Represents a collection of delivery channel arguments as DeliveryChannelArgument objects. The DeliveryChannelArguments property of the DeliveryChannel class returns the delivery channel arguments for the delivery channel.

DeliveryChannelCollection

Represents a collection of delivery channels as DeliveryChannel objects. The DeliveryChannels property of the Instance class returns the delivery channels for the Notification Services instance.


18.3.4. Creating an Event Class

An event class named StockWatchEvents is created in the CreateEventClass( ) method of Example 18-1. The code follows:

     private static void CreateEventClass(  )     {         EventClass ec = new EventClass(a, "StockWatchEvents");         EventField ef;         ef = new EventField(ec, "Symbol");         ef.Type = "nvarchar(6)";         ec.EventFields.Add(ef);         ef = new EventField(ec, "Price");         ef.Type = "float";         ec.EventFields.Add(ef);         a.EventClasses.Add(ec);         Console.WriteLine("Added event class: " + ec.Name);     } 

An event class represents a type of event used by a Notification Services application. The event class has two fieldsSymbol of type nvarchar(6) and Price of type float.

The NMO classes for managing event classes, fields, and chronicles are described in Table 18-4.

Table 18-4. NMO classes for managing events

Class

Description

EventChronicle

Represents an event chronicle.

EventChronicleCollection

Represents a collection of event chronicles as EventChronicle objects. The EventChronicles property of the EventClass class returns the event chronicles for the event class.

EventChronicleRule

Represents an event chronicle maintenance query that the generator runs.

EventClass

Represents an event class.

EventClassCollection

Represents a collection of event classes as EventClass objects. The EventClasses property of the Application class returns the event classes for the Notification Services application.

EventField

Represents a field in an event class schema.

EventFieldCollection

Represents a collection of event class schema fields as EventField objects. The EventFields property of the EventClass class returns the event fields for the event class.


18.3.5. Creating a Subscription Class and Subscription Event Rule

A subscription class with a single subscription event rule is created in the CreateSubscriptionClassMethod( ) method of Example 18-1. The code follows:

     private static void CreateSubscriptionClass(  )     {         SubscriptionClass sc = new SubscriptionClass(a, "StockWatchSubscriptions");         SubscriptionField sf;         sf = new SubscriptionField(sc, "DeviceName");         sf.Type = "nvarchar(255)";         sc.SubscriptionFields.Add(sf);         sf = new SubscriptionField(sc, "SubscriberLocale");         sf.Type = "nvarchar(10)";         sc.SubscriptionFields.Add(sf);         sf = new SubscriptionField(sc, "Symbol");         sf.Type = "nvarchar(6)";         sc.SubscriptionFields.Add(sf);         sf = new SubscriptionField(sc, "Price");         sf.Type = "float";         sc.SubscriptionFields.Add(sf);         SubscriptionEventRule ser =             new SubscriptionEventRule(sc, "StockWatchSubscriptionsEventRule");         ser.Action = @"INSERT INTO StockWatchNotifications (" +             "SubscriberId, DeviceName, SubscriberLocale, Symbol, Price) " +             "SELECT s.SubscriberId, s.DeviceName, s.SubscriberLocale, " +             "e.Symbol, e.Price " +             "FROM StockWatchEvents e, StockWatchSubscriptions s " +             "WHERE e.Symbol = s.Symbol";         ser.EventClassName = "StockWatchEvents";         sc.SubscriptionEventRules.Add(ser);         a.SubscriptionClasses.Add(sc);         Console.WriteLine("Added subscription class: " + sc.Name);     } 

A SubscriptionClass object defines a type of subscription within a Notification Services application. The SubscriptionField objects added to the Subscription object represent fields in the subscription class schema. A SubscriptionChronicle object lets you store subscription information outside of tables used by the subscription classthis example does not use a subscription chronicle.

The NMO classes for managing, subscription chronicles subscription classes, and subscription fields are described in Table 18-5.

Table 18-5. NMO classes for managing subscription chronicles, classes, and fields

Class

Description

SubscriptionChronicle

Represents a subscription chronicle.

SubscriptionChronicleCollection

Represents a collection of subscription chronicles as SubscriptionChronicle objects. The SubscriptionChronicles property of the SubscriptionClass class returns the subscription chronicles for the subscription class.

SubscriptionClass

Represents a subscription class.

SubscriptionClassCollection

Represents a collection of subscription classes as SubscriptionClass objects. The SubscriptionClasses property of the Application class returns the subscription classes for the Notification Services application.

SubscriptionField

Represents a field in the subscription class schema.

SubscriptionFieldCollection

Represents a collection of fields as SubscriptionField objects. The SubscriptionFields property of the SubscriptionClass class returns the fields for the subscription class schema.


A SubscriptionEventRule object represents a rule that uses T-SQL queries to generate notifications when event batches arrive. The Action property represents the T-SQL query for the SubscriptionEventRule object. In this example, notifications are generated when the ticker symbol of an event matches the ticker symbol specified in a subscription.

The NMO classes for managing the different types of subscription rules are described in Table 18-6.

Table 18-6. NMO classes for managing subscription rules

Class

Description

SubscriptionConditionEventRule

Represents a subscription rule that the generator runs against subscriptions that use conditions to generate notifications.

SubscriptionConditionEventRuleCollection

Represents a collection of subscription condition event rules as SubscriptionConditionEventRule objects. The SubscriptionConditionEventRules property of the SubscriptionClass class returns the subscription condition event rules for the subscription class.

SubscriptionConditionScheduledRule

Represents a subscription rule that the generator runs against scheduled subscriptions that use conditions to generate notifications.

SubscriptionConditionScheduledRuleCollection

Represents a collection of subscription condition scheduled rules as SubscriptionConditionScheduledRule objects. The SubscriptionConditionScheduledRules property of the SubscriptionClass class returns the subscription condition scheduled rules for the subscription class.

SubscriptionEventRule

Represents an event rule that contains simple (not conditional) actions.

SubscriptionEventRuleCollection

Represents a collection of subscription event rules as SubscriptionEventRule objects. The SubscriptionEventRules property of the SubscriptionClass class returns the subscription event rules for the subscription class.

SubscriptionScheduledRule

Represents a scheduled rule that contains actions that do not use conditions to generate notifications.

SubscriptionScheduledRuleCollection

Represents a collection of scheduled rules as SubscriptionScheduledRule objects. The SubscriptionScheduledRules property of the SubscriptionClass class returns the scheduled rules for the subscription class.


18.3.6. Creating a Notification Class, Content Formatter, and Notification Class Protocol

A notification class named StockWatchNotifications, a content formatter named XsltFormatter, and two notification class protocols named File and SMTP are created in the CreateNotificationClass( ) method of Example 18-1. The code follows:

     private static void CreateNotificationClass(  )     {         NotificationClass nc = new NotificationClass(a, "StockWatchNotifications");         NotificationField nf;         nf = new NotificationField(nc, "Symbol");         nf.Type = "nvarchar(6)";         nc.NotificationFields.Add(nf);         nf = new NotificationField(nc, "Price");         nf.Type = "float";         nc.NotificationFields.Add(nf);         ContentFormatter cf = new ContentFormatter(nc, "XsltFormatter");         ContentFormatterArgument cfa;         cfa = new ContentFormatterArgument(cf, "XsltBaseDirectoryPath");         cfa.Value = a.BaseDirectoryPath + @"\AppDefinition";         cf.ContentFormatterArguments.Add(cfa);         cfa = new ContentFormatterArgument(cf, "XsltFileName");         cfa.Value = "StockWatch.xslt";         cf.ContentFormatterArguments.Add(cfa);         nc.ContentFormatter = cf;         nc.DigestDelivery = true;         ProtocolField pf;         // add file notification class protocol         NotificationClassProtocol ncpFile =             new NotificationClassProtocol(nc, "File");         pf = new ProtocolField(ncpFile, "Symbol");         pf.FieldReference = "Symbol";         ncpFile.ProtocolFields.Add(pf);         pf = new ProtocolField(ncpFile, "Price");         pf.FieldReference = "Price";         ncpFile.ProtocolFields.Add(pf);         nc.NotificationClassProtocols.Add(ncpFile);         // add email notification   class protocol         NotificationClassProtocol ncpEmail =             new NotificationClassProtocol(nc, "SMTP");         pf = new ProtocolField(ncpEmail, "Subject");         pf.SqlExpression = "'Stock watch: ' + CONVERT(nvarchar(30), GETDATE(  ))";         ncpEmail.ProtocolFields.Add(pf);         pf = new ProtocolField(ncpEmail, "BodyFormat");         pf.SqlExpression = "'html'";         ncpEmail.ProtocolFields.Add(pf);         pf = new ProtocolField(ncpEmail, "From");         pf.SqlExpression = "'notification@StockWatchService.com'";         ncpEmail.ProtocolFields.Add(pf);         pf = new ProtocolField(ncpEmail, "Priority");         pf.SqlExpression = "'Normal'";         ncpEmail.ProtocolFields.Add(pf);         pf = new ProtocolField(ncpEmail, "To");         pf.SqlExpression = "DeviceAddress";         ncpEmail.ProtocolFields.Add(pf);         nc.NotificationClassProtocols.Add(ncpEmail);         nc.ExpirationAge = new TimeSpan(1, 0, 0);         a.NotificationClasses.Add(nc);         Console.WriteLine("Added notification class: " + nc.Name);     } 

The NotificationClass object represents a type of notification supported by a Notification Services application. A NotificationField object represents a field in a notification class schema. The notification class in this example has two fieldsSymbol of type nvarchar(6) and Price of type float.

The NMO classes for managing notification classes and fields are described in Table 18-7.

Table 18-7. NMO classes for managing notification classes and fields

Class

Description

NotificationClass

Represents a notification class.

NotificationClassCollection

Represents a collection of notification classes as NotificationClass objects. The NotificationClasses property of the Application class returns the notification classes for the Notification Services application.

NotificationComputedField

Represents a computed field in a notification class schema.

NotificationComputedFieldCollection

Represents a collection of computed fields as NotificationComputedField objects. The NotificationComputedFields property of the NotificationClass class returns the computed fields for the notification class.

NotificationField

Represents a noncomputed field in a notification class schema.

NotificationFieldCollection

Represents a collection of fields as NotificationField objects. The NotificationFields property of the NotificationClass class returns the fields for the notification class.


A content formatter formats notifications for a notification class. Each notification class has one content formatter that can perform different formatting based on field values in the notification. The content formatter takes three arguments:


XsltBaseDirectoryPath

The root directory for all XSLT files.


XsltFileName

The name of the XSLT file used to transform raw notification data into formatted data for notification delivery.


DisableEscaping

An optional Boolean argument indicating that the event data contains either HTML or XML data preventing further transformation. The default value is false.

The NMO classes for managing content formatters are described in Table 18-8.

Table 18-8. NMO classes for managing content formatters

Class

Description

ContentFormatter

Represents a content formatter. The ContentFormatter property of the NotificationClass class returns the content formatter for the notification class.

ContentFormatterArgument

Represents a name-value pair for a content formatter initialization argument.

ContentFormatterArgumentCollection

Represents a collection of initialization arguments as ContentFormatterArgument objects. The ContentFormatterArguments property of the ContentFormatter class returns the initialization arguments for the content formatter.


A notification class protocol represents a delivery protocol for a notification class. A ProtocolField object represents a protocol header field used by some delivery protocols . Protocol field headers are different for file and email notificationexamine the code and examine the file and email notifications shown in Figure 18-7 and Figure 18-8 to see the result of setting the protocol fields. The value of the ProtocolField object is set using either the SqlExpression or FieldReference property. This lets you use either a T-SQL expression or a notification field to define a protocol field value.

The NMO classes for managing protocols are described in Table 18-9.

Table 18-9. NMO classes for managing protocols

Class

Description

NotificationClassProtocol

Represents a delivery protocol for a notification class.

NotificationClassProtocolCollection

Represents a collection of notification class protocols as NotificationClassProtocol objects. The NotificationClassProtocols property of the NotificationClass class returns the delivery classes for the notification class.

ProtocolDefinition

Represents a custom delivery protocol.

ProtocolDefinitionCollection

Represents a collection of custom delivery protocols as ProtocolDefinition objects. The ProtocolDefinitions property of the Instance class returns the custom delivery protocols for the Notification Services instance.

ProtocolField

Represents a protocol header field.

ProtocolFieldCollection

Represents a collection of protocol header fields as ProtocolField objects. The ProtocolFields property of the NotificationClassProtocol class returns the protocol header fields for the delivery protocol.

ProtocolRetrySchedule

Represents a retry schedule interval.

ProtocolRetryScheduleCollection

Represents a collection of retry schedule intervals as ProtocolRetrySchedule objects. The ProtocolRetrySchedules property of the NotificationClassProtocol class returns the retry schedules for notifications sent using the delivery protocol.


18.3.7. Creating an Event Provider

A hosted event provider is created in the CreateHostedEventProvider( ) method of Example 18-1. The code follows:

     private static void CreateHostedEventProvider(  )     {         HostedEventProvider   hep = new HostedEventProvider(a, "StockWatchHEP");         hep.ClassName = "FileSystemWatcherProvider";         hep.SystemName = nsServer;         HostedEventProviderArgument hepa;         hepa = new HostedEventProviderArgument(hep, "WatchDirectory");         hepa.Value = baseDirectoryPath + @"\Events";         hep.HostedEventProviderArguments.Add(hepa);         hepa = new HostedEventProviderArgument(hep, "SchemaFile");         hepa.Value = baseDirectoryPath + @"\AppDefinition\EventsSchema.xsd";         hep.HostedEventProviderArguments.Add(hepa);         hepa = new HostedEventProviderArgument(hep, "EventClassName");         hepa.Value = "StockWatchEvents";         hep.HostedEventProviderArguments.Add(hepa);         a.HostedEventProviders.Add(hep);     } 

Event providers collect event data and submit it to an event class. Hosted event providers are run by the Notification Services engine. Nonhosted event providers, on the other hand, run outside of the engine, and have no interaction with Notification Services.

The ClassName property of the HostedEventProvider class specifies the class that implements the event provider. Notification Services has three built-in event providers, described in the "Architecture" section earlier in this chapter. You can also use a custom event provider. This example uses the File System Watcher event provider (with a ClassName of FileSystemWatcherProvider), which gathers events of the StockWatchEvents event class from a file in the C:\PSS2005\NotificationServices\Events directory. The event file must conform to the schema defined in the C:\PSS2005\NotificationServices\AppDefinition\EventsSchema.xsd file.

The NMO classes for managing hosted and nonhosted event providers are described in Table 18-10.

Table 18-10. NMO classes for managing hosted and nonhosted event providers

Class

Description

HostedEventProvider

Represents a hosted event provider for a Notification Services application.

HostedEventProviderArgument

Represents a name-value pair specifying hosted event provider configuration.

HostedEventProviderArgumentCollection

Represents a collection of hosted event provider arguments as HostedEventProviderArgument objects. The HostedEventProviderArguments property of the HostedEventProvider class returns the hosted event provider arguments for the hosted event provider.

HostedEventProviderCollection

Represents a collection of hosted event providers as HostedEventProvider objects. The HostedEventProviders property of the Application class returns the hosted event providers for the Notification Services application.

NonHostedEventProvider

Represents a nonhosted event provider.

NonHostedEventProviderCollection

Represents a collection of nonhosted event providers as NonHostedEventProvider objects. The NonHostedEventProviders property of the Application class returns the nonhosted event providers for the Notification Services application.


18.3.8. Creating a Generator

A generator named StockWatchGenerator is created in the CreateGenerator( ) method of Example 18-1. The code follows:

     private static void CreateGenerator(  )     {         // create a new generator for the application         Generator g = new Generator(a, "StockWatchGenerator");         g.SystemName = nsServer;         a.Generator = g;         Console.WriteLine("Created generator: " + g.Name);     } 

The Generator object represents the generator for a Notification Services application. The Generator property of the Application class returns the generator for the application. You have to specify the Generator property of the Application object before adding the application to the Notification Services instance. An application has only one generator, which handles rule processing for the application.

The SystemName property of the Generator class must be specified, and cannot be any of the following: localhost, ., an IP address, or any string containing a backslash (\).

18.3.9. Creating a Distributor

A distributor named StockWatchDistributor is created in the CreateDistributor( ) method of Example 18-1. The code follows:

     private static void CreateDistributor(  )     {         Distributor d = new Distributor(a, "StockWatchDistributor");         d.SystemName = nsServer;         d.QuantumDuration = new TimeSpan(0, 0, 15);         a.Distributors.Add(d);         Console.WriteLine("Added distributor: " + d.Name);     } 

The Distributor object represents a distributor for a Notification Services application. Each application must have one distributor that controls formatting and distribution of notifications. If an application has multiple distributors, each one must be installed on a different server. The QuantumDuration property specifies the distributor work item polling interval, which is 15 seconds in this example.

The NMO classes for managing distributors are described in Table 18-11.

Table 18-11. NMO classes for managing distributors

Class

Description

Distributor

Represents a distributor for a Notification Services application.

DistributorCollection

Represents a collection of distributors as Distributor objects. The Distributors property of the Application class returns the distributors used to distribute notifications to delivery channels for the Notification Services application.


18.3.10. Creating a Vacuum Schedule

A vacuum schedule named StockWatchVacuumSchedule is created in the CreateVacuumSchedule( ) method of Example 18-1. The code follows:

     private static void CreateVacuumSchedule(  )     {         VacuumSchedule vs = new VacuumSchedule(a, "StockWatchVacuumSchedule");         vs.StartTime = new TimeSpan(0, 0, 0);         a.VacuumSchedules.Add(vs);         Console.WriteLine("Added vacuum schedule: " + vs.Name);     } 

Vacuuming removes old event and notification data from the application database on a daily schedule. The StartTime property specifies the daily time in Universal Coordinated Time (UTC) for the data removal process to start. Although specifying a vacuum schedule is optional, old data is not removed from the database if a vacuum schedule is not specified.

The NMO classes for managing vacuum schedules are described in Table 18-12.

Table 18-12. NMO classes for managing vacuum schedules

Class

Description

VacuumSchedule

Represents a data removal schedule.

VacuumScheduleCollection

Represents a collection of data removal schedules as VacuumSchedule objects. The VacuumSchedules property of the Application class returns the data removal schedules for the application.


18.3.11. Creating a Subscriber and Subscriber Device

Two subscribers, each with a single subscriber device, are created in the CreateSubscriber( ) method of Example 18-1. The code follows:

     private static void CreateSubscriber(  )     {         ns.NSInstance swnsi = new ns.NSInstance("StockWatch");         ns.Subscriber s;         ns.SubscriberDevice sd;         // create a subscriber         s = new ns.Subscriber(swnsi);         s.SubscriberId = @"KristinHamilton";         s.Add(  );         Console.WriteLine("Added subscriber: " + s.SubscriberId);         // create a file subscriber device         sd = new ns.SubscriberDevice(  );         sd.Initialize(swnsi);         sd.DeviceName = "StockWatchSubscriberDevice";         sd.SubscriberId = "KristinHamilton";         sd.DeviceTypeName = "File";         sd.DeviceAddress = "KristinH@StockWatch.ns";         sd.DeliveryChannelName = "StockWatchFileDeliveryChannel";         sd.Add(  );         Console.WriteLine("Added subscriber file device.");         // create a subscriber         s = new ns.Subscriber(swnsi);         s.SubscriberId = @"TonyHamilton";         s.Add(  );         Console.WriteLine("Added subscriber: " + s.SubscriberId);         // create an email subscriber device         sd = new ns.SubscriberDevice  (  );         sd.Initialize(swnsi);         sd.DeviceName   = "StockWatchSubscriberDevice";         sd.SubscriberId   = "TonyHamilton";         sd.DeviceTypeName   = "Email";         sd.DeviceAddress   = "TonyH@StockWatchNS.ns";         sd.DeliveryChannelName   = "StockWatchEmailDeliveryChannel";         sd.Add(  );         Console.WriteLine("Added subscriber email device.");     } 

The NSInstance class represents a Notification Services instance that is registered in the Windows registry.

The Subscriber class represents a subscriber in a Notification Services instance. The SubscriberEnumeration class represents a collection of subscribers as Subscriber objects in a Notification Services instance.

The SubscriberEnumeration class is used to retrieve a Subscriber object from the collection of subscribers to the Notification Services instance. The Delete( ) method removes a subscriber, the Add( ) method adds a subscriber, and the Update( ) method updates the information for an existing subscriber. The following code deletes the subscriber AnySubscriber from a Notification Service instance named HelloWorld:

     NSInstance nins = new NSInstance("HelloWorld");     SubscriberEnumeration se = new SubscriberEnumeration(nins);     se["AnySubscriber"].Delete(  ); 

The SubscriberDevice class represents a device that can receive notifications. A single subscriber device is added to each subscriber. Each subscriber device specifies the following properties:


DeviceName

The name of the subscriber device.


SubscriberId

The ID of the subscriber with which the subscriber device is associated.


DeviceTypeName

The name of the device type for the subscriber device.


DeviceAddress

The address used to contact the device.


DeliveryChannelName

The name of the delivery channel that the device uses. This example assigns the StockWatchFileDeliveryChannel delivery channel to one subscriber and the StockWatchEmailDeliveryChannel delivery channel to the otherthese delivery channels are created in the code described in the "Creating a Delivery Channel" section earlier in this chapter.

The SubscriberDeviceEnumeration class is used to retrieve a SubscriberDevice object from the collection of devices for a subscriber. The Delete( ) method removes a device, the Add( ) method adds a device, and the Update( ) method updates the information for an existing device.

18.3.12. Creating a Subscription

Two subscriptions are created in the CreateSubscription( ) method of Example 18-1. The code follows:

     private static void CreateSubscription(  )     {         ns.NSInstance swnsi = new ns.NSInstance("StockWatch");         ns.NSApplication a = new ns.NSApplication(swnsi, "StockWatchApp");         ns.Subscription s;         // add subscriptions         s = new ns.Subscription(  );         s.Initialize(a, "StockWatchSubscriptions");         s.SetFieldValue("DeviceName", "StockWatchSubscriberDevice");         s.SetFieldValue("SubscriberLocale", "en-us");         s.SubscriberId = "KristinHamilton";         s.SetFieldValue("Symbol", "ABC");         s.SetFieldValue("Price", "0.00");         s.Add(  );         Console.WriteLine("Added subscription: " + s.SubscriberId);         s = new ns.Subscription(  );         s.Initialize(a, "StockWatchSubscriptions");         s.SetFieldValue("DeviceName", "StockWatchSubscriberDevice");         s.SetFieldValue("SubscriberLocale", "en-us");         s.SubscriberId = "TonyHamilton";         s.SetFieldValue("Symbol", "DEF");         s.SetFieldValue("Price", "0.00");         s.Add(  );         Console.WriteLine("Added subscription: " + s.SubscriberId);     } 

The NSApplication class represents a Notification Services application. The Subscription class represents a subscription in a Notification Services application. Both subscriptions use the subscriber device named StockWatchSubscriberDevice created in the "Creating a Subscriber and Subscriber Device" section earlier in this chapter. The subscription for KristinHamilton is for events for ticker symbol ABC, while the subscription for subscriber TonyHamilton is for events for ticker symbol DEF. The results can be seen in the notifications shown in Figures 18-7 and 18-8.

The SubscriptionEnumeration class represents a collection of subscriptions as Subscription objects in a Notification Services application. Use the Subscrip-tionEnumeration class to iterate over a collection of subscriptions for an application and manage the individual subscriptions in the collection. These two classes are implemented in the Microsoft.SqlServer.NotificationServices namespace.

18.3.13. Enumerating a Notification Services Instance Database

This example enumerates the filegroup, database files, and logfiles for the StockWatch Notification Services instance created in Example 18-1:

     using System;     using Microsoft.SqlServer.Management.Smo;     using Microsoft.SqlServer.Management.Nmo;     class Program     {         static void Main(string[] args)         {             Server server = new Server("(local)");             NotificationServices ns = server.NotificationServices;             Instance ins = ns.Instances["StockWatch"];             InstanceDatabaseOptions ido = ins.InstanceDatabaseOptions;             Console.WriteLine("INSTANCE DATABASE OPTIONS:");             Console.WriteLine("  Name: " + ido.Name);             Console.WriteLine("  DefaultFileGroup: " + ido.DefaultFileGroup);             Console.WriteLine(Environment.NewLine + "FILE GROUPS:");             foreach (InstanceDatabaseFileGroup idfg in ido.InstanceDatabaseFileGroups)             {                 Console.WriteLine("  Name: " + idfg.Name);                 Console.WriteLine(Environment.NewLine + "DATABASE FILES");                 foreach (InstanceDatabaseFile idf in idfg.InstanceDatabaseFiles)                     Console.WriteLine("  Name: " + idf.Name);             }             Console.WriteLine(Environment.NewLine + "LOG FILES:");             foreach (InstanceDatabaseLogFile idlf in ido.InstanceDatabaseLogFiles)                 Console.WriteLine("  Name: " + idlf.Name);             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     } 

Results are shown in Figure 18-9.

Each Notification Services instance has one database that can optionally contain one or more filegroups. If you define filegroups, one must be called PRIMARY. Use SQL Server Management Studio if you need to alter the Notification Services instance database after creating the instance.

Figure 18-9. Results for enumerating a Notification Services instance database example


The NMO classes for managing the instance database, filegroups, files, and logfiles are described in Table 18-13.

Table 18-13. NMO classes for managing instance databases

Instance

Description

InstanceDatabaseFile

Represents an instance database file.

InstanceDatabaseFileCollection

Represents a collection of instance databases as InstanceDatabase objects. The InstanceDatabaseFiles property of the InstanceDatabaseFileGroup class returns the instance databases in the instance database filegroup.

InstanceDatabaseFileGroup

Represents an application database filegroup.

InstanceDatabaseFileGroupCollection

Represents a collection of instance database filegroups as InstanceDatabaseFileGroup objects. The InstanceDatabaseFileGroups property of the InstanceDatabaseOptions class returns the instance database filegroups in the application.

InstanceDatabaseLogFile

Represents an instance database logfile.

InstanceDatabaseLogFileCollection

Represents a collection of instance database logfiles as InstanceDatabaseLogFile objects. The InstanceDatabaseLogFiles property of the InstanceDatabaseOptions class returns the instance database logfiles in the application.

InstanceDatabaseOptions

Represents database options for the instance database. The InstanceDatabaseOptions property of the Instance class returns the database properties for the instance.


18.3.14. Enumerating a Notification Services Application Database

This example enumerates the filegroup, database files, and logfiles for the HelloWorldApp Notification Services application created in Example 18-1:

     using System;     using Microsoft.SqlServer.Management.Smo;     using Microsoft.SqlServer.Management.Nmo;     class Program     {         static void Main(string[] args)         {             Server server = new Server("(local)");             NotificationServices ns = server.NotificationServices;             Instance ins = ns.Instances["StockWatch"];             Application a = ins.Applications["StockWatchApp"];             ApplicationDatabaseOptions ado = a.ApplicationDatabaseOptions;             Console.WriteLine("APPLICATION DATABASE OPTIONS:");             Console.WriteLine("  Name: " + ado.Name);             Console.WriteLine("  DefaultFileGroup: " + ado.DefaultFileGroup);             Console.WriteLine(Environment.NewLine + "FILE GROUPS:");             foreach (ApplicationDatabaseFileGroup adfg in                 ado.ApplicationDatabaseFileGroups)             {                 Console.WriteLine("  Name: " + adfg.Name);                 Console.WriteLine(Environment.NewLine + "DATABASE FILES");                 foreach (ApplicationDatabaseFile adf in adfg.ApplicationDatabaseFiles)                     Console.WriteLine("  Name: " + adf.Name);             }             Console.WriteLine(Environment.NewLine + "LOG FILES:");             foreach (ApplicationDatabaseLogFile adlf in                 ado.ApplicationDatabaseLogFiles)                 Console.WriteLine("  Name: " + adlf.Name);             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     } 

Results are shown in Figure 18-10.

Figure 18-10. Results for enumerating a Notification Services application database example


Each Notification Services application has one database that can optionally contain one or more filegroups. If you define filegroups, one must be called PRIMARY. Use SQL Server Management Studio if you need to alter the application database after creating the instance.

The NMO classes for managing the application database, filegroups, files, and logfiles are described in Table 18-14.

Table 18-14. NMO classes for managing application databases

Class

Database

ApplicationDatabaseFile

Represents an application database file.

ApplicationDatabaseFileCollection

Represents a collection of application databases as ApplicationDatabaseFile objects. The ApplicationDatabaseFiles property of the ApplicationDatabaseFileGroup class returns the application databases in the application database filegroup.

ApplicationDatabaseFileGroup

Represents an application database filegroup.

ApplicationDatabaseFileGroupCollection

Represents a collection of application database filegroups as ApplicationDatabaseFileGroup objects. The ApplicationDatabaseFileGroups property of the ApplicationDatabaseOptions class returns the application database filegroups in the application.

ApplicationDatabaseLogFile

Represents an application database logfile.

ApplicationDatabaseLogFileCollection

Represents a collection of application database logfiles as ApplicationDatabaseLogFile objects. The ApplicationDatabaseLogFiles property of the ApplicationDatabaseOptions class returns the application database logfiles in the application.

ApplicationDatabaseOptions

Represents database options for the application database. The ApplicationDatabaseOptions property of the Application class returns the database properties for the application.




Programming SQL Server 2005
Programming SQL Server 2005
ISBN: 0596004796
EAN: 2147483647
Year: 2007
Pages: 147
Authors: Bill Hamilton

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net