Notification Services - Creating a Notification Services Application and Service (Page 4 of 4 ) 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 created—one that is notified by entries in a text file and one that is notified using email. Each of the two users monitors a single stock—ticker symbolsABC andDEF. This section shows the application that creates theStockWatchNotification 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 theStockWatch Notification Services application with two subscribers and a subscription for each, start the service, and generate notifications: - Create a Visual Studio 2005 C# console application in the C:\PSS2005 directory. Name the projectNotificationServices.
- Add the following references to the project:
- Microsoft.SqlServer.ConnectionInfo
- Microsoft.SqlServer.NotificationServices
- Microsoft.SqlServer.Smo
- 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);
}
}
- 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 theNS$StockWatchservice runs
under.
NSPassword
The password for theNSUserName account.
- Compile and execute the code. The results are shown in Figure 18-3.
Figure 18-3. Results for Notification Services example
- Two databases are created when the NMO application is run—StockWatchNSMainandStockWatchStockWatchApp. Ensure that the service login account specified in Step 4 has at least theNSRunServicedatabase role membership for both of these databases.
- 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
- Right-click theStockWatch service and select Start from the context menu to start the service.
- Right-click theStockWatch 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
- Select the Windows Services page to display the service status, as shown in Figure 18-6. The service should be running.
- Ensure that the SMTP service—a component of Internet Information Services (IIS)—is installed and started. For more information, see Microsoft SQL Server 2005 Books Online.
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- 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>
- 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
<fixml version="1.0" encoding="UTF-8" fi>
<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-template/>
<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>
- 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 symbolsABCandDEFhave subscriptions and generate notifications. The event for symbolGHI 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>
- 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 applicationStockWatchApp, 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.
- 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 userTonyHamiltonin theCreateSubscriber()method of Example 18-1 to a valid email address by changing theDeviceAddressproperty of theSubscriberDeviceobject 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 theStockWatchservice, subscribers, and subscriptions. Please check back next week for the continuation of this article. | DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|  This article is excerpted from chapter 18 of the book Programming SQL Server 2005, written by Bill Hamilton (O'Reilly, 2006; ISBN: 0596004796). Check it out today at your favorite bookstore. Buy this book now.
|
|
|
/ 4 
