eople receive many notifications daily: weather updates, news updates, updates about stock prices, or a current account balance via a SMS message. But how can you build such notification applications on the Microsoft platform? You have two options: the first and less palatable is to build everything from scratch. The second and more attractive option is to use the SQL Server 2005 Notification Services (SQLNS), which is a new API intended to help you build notification applications.
SQLNS?can generate and deliver notifications to thousands of end users of your application in several different ways and without any regard for the local time of the machine receiving the notification. One of the best known delivery protocols for notifications is e-mail, because people are used to working with emails constantly. But with SQLNS you can not only use e-mail notifications, but also provide notifications via SMS, pagers, or the Windows Messenger client. If none of those options seem palatable, you can also create your own delivery channel.
SQLNS is based on four components that form the heart of an SQLNS application. SQLNS applications are always hosted inside the SQL Server or inside a Windows Service. Table 1 describes these four main components.
Table 1. The table lists the four main components required for an SQLNS application and provides a description for each.
|A subscriber is a person or an application who wants to receive notifications.
|A subscription is a request for a notification and can be generated from an application through the SQLNS API. A typical example is the change of a stock price.
|An event is an information, in which a subscriber is interested in. As soon as a stock price has changed an event can be generated that’s processed within SQLNS.
|The notification component is a notification which is send to the registered subscriber through a defined delivery channel.
|Figure 1. The core components of a SQLNS application.
Figure 1 shows how you combine these components in an SQLNS application.
SQLNS executes the following steps whenever it generates a new notification and sends it to registered subscribers:
- Subscribers create subscriptions with a subscription management application, such as an ASP.NET or Windows Forms front end. Subscription management is the only part of an SQLNS application where users or applications are actively involved in the process. You generate subscriptions using a managed assembly shipped with SQLNS.
- The Notification Application collects events, which are processed within the SQLNS application.
- SQLNS uses T-SQL statements to assign events to the subscriptions registered in the first step.
- SQLNS generates new notifications whenever it finds an assignment.
- A Formatter formatsthe new notification, and SQLNS delivers it through a delivery channel to the registered target device of the subscriber.
As you can see from the preceding list of actions, SQLNS applications run completely within SQL Server except for the subscription registration process itself.
SQLNS Configuration Files
To tie all these things and components together everything must be configured through a pair of XML-based configuration files: the Instance Configuration file and the Application Definition file.
To host a SQLNS application you need an instance of SQLNS. An SQLNS instance can host one or more SQLNS applications. The instance also stores subscriber information, which can be shared among all SQLNS applications hosted on an instance. The Instance Configuration File registers each SQLNS application for that instance. Here’s an example of an Instance Configuration file.
DM Localhost StockPrice C:MyPath appADF.xml FileChannel
Table 2 shows the most important XML elements used in this configuration file.
Table 2. The table shows a description of each main element in an Instance Configuration file.
|The name of this SQLNS instance.
|The name of the SQL Server that hosts the SQLNS instance database.
|Describes the several delivery channels supported by this SQLNS instance.
Table 3. The table shows the XML elements needed within each
|Name of the SQLNS application hosted on this SQLNS instance.
|Base directory of the SQLNS application. Here are stored all application relevant information like the Application Definition File.
|Name and path to the Application Definition File that configures the SQLNS application.
The Application Definition File
The Application Definition File is more interesting. You use this file to configure the whole SQLNS application structure along with all necessary metadata. Listing 1 shows a minimal Application Definition File.
As you can see from Listing 1, the sample SQLNS application supports only one subscription, named StockPriceSubscription. This subscription contains only one relevant bit information?the StockSymbol?stored in a table column, which gives users of the front-end application the capability of entering the stock codes that they’re interested in. SQLNS uses this configuration file to create all the tables required for the SQLNS application.. You’ll see more about this feature later.
Through the section
After configuring the SQLNS instance and application you must create all the necessary databases and tables specified in the configuration files. SQLNS provides a command utility tool called nscontrol.exe to simplify the process. Table 4 describes the nscontrol.exe options.
Table 4. The table lists the command-line options available for nscontrol.exe.
|Creates a new instance of SQLNS.
|Deletes an existing instance of SQLNS.
|Disables a SQLNS instance.
|Enables a SQLNS instance.
|Registers a new instance of SQLNS.
|Unregisters a instance of SQLNS.
|Updates an existing instance of SQLNS.
For example, to create a new instance of SQLNS you call nscontrol.exe with the Create option and supply the name of the Instance Configuration file as a parameter to the call as follows:
nscontrol.exe -Create -In Instance.xml
The In parameter specifies the instance configuration file that nscontrol.exe should process. When you make this call, nscontrol.exe creates a new SQLNS instance and registers the SQLNS application with that new instance. In addition, it creates the instance database DMNSMain and the application database DMStockPrice with the structure specified in the Application Definition File. These database names are created automatically by nscontrol.exe and can’t be customized in any configuration file.
To make use of SQLNS you need to define events that SQLNS will process to produce notifications. SQLNS processes events from an EventProvider, which writes the new events to the event table. You need to define Event classes that process new incoming events. You define the Event classes in the Application Definition file in the
Listing 2 defines only defining one event, called StockEvt. This event consists of the properties StockCode, ExchangeCode and Price. When you create or update the application using nscontrol.exe, it creates a new event table for use by the SQLNS application. To process events you need one additional component?an Event Provider. Event Providers pass new events to SQLNS for processing. You configure Event Providers through the
StockEP FileSystemWatcherProvider localhost WatchDirectory C:Events SchemaFile Schema.xsd EventClassName StockEvt
nscontrol.exe --Update --in appADF.xml
Because the FileSystemWatcherProvider is a so-called Hosted Provider (the other option is a Non-hosted Provider), you must activate the Service Mode on the SQLNS instance. A Hosted Provider is hosted within a SQLNS instance and a Non-hosted Provider can be hosted in your own application. When you do that, the SQLNS instance runs as a normal Windows Service and monitors the specified directory for new incoming events in the form of XML files.
nscontrol.exe --Register name DM --server localhost --service
When you execute this command nscontrol.exe creates and starts a new Windows Service named NS$DM. SQLNS always adds the prefix NS$, which indicates that this is a Windows Service used for SQLNS. You can’t change the service name prefix using configuration. Because the service is a normal Windows Service you can administer it through the standard MMC Services snap-in.
According to that schema, an XML event file can have the following structure:
MSFT NYSE 60.54
SQLNS processes those event files and writes a new record in the application’s event table, NSStockEvtEvents.
To send out notifications SQLNS must know:
- Who should receive notifications
- What information to send
- The target device to which the notification should be sent
You set up this information using the Subscription Management Objects from a .NET application. The Subscription Management Objects are a set of .NET classes implemented in an assembly shipped with SQLNS. SQLNS stores subscription information in the instance database (not in the application database). This design decision makes it possible to use subscriber information in more than one SQLNS application.
Before you can manage subscriptions you must define a Subscription class in the Application Definition file. You also need to configure a Delivery Channel in the Instance Configuration file. The Subscription class stores information about the event, the target device, and the language in which the notification should be delivered. Listing 3 shows the Subscription class for the sample application.
Using this Subscription class, nscontrol.exe will create a new table when you use it to update the SQLNS instance. The column DeviceName stores the name of the device to which the SQLNS will publish the subscription. The column SubLocale defines the language in which SQLNS sends the notification. Using the StockCode, ExchangeCode, and TriggerVal columns, users can decide which stocks they want to be notified about when a change occurs. For example, a typical notification request might be: “I want to be informed about the stock MSFT (column StockCode) that trades at NYSE (column ExchangeCode) whenever as the stock price exceeds $7.00 (column TriggerVal).”
A Subscription Management application must be able to perform these three tasks:
- Create a new subscriber
- Specify the target device for the new subscriber
- Create a subscription for the new subscriber
To communicate with an existing SQLNS instance you need a reference to it. The sample application accomplishes this using the NSInstance object. This object’s constructor receives a parameter containing the instance name of the SQLNS instance to manage. The SubscriberDevice class represents the subscriber’s target device, and the Subscription class represents a subscription. With all this information in hand, it’s very easy to manage all three tasks. Listing 4 shows the required code in C#. You can use this same code in either an ASP.NET or Windows Forms application.
The most interesting method is AddSubscription. That method creates a new Subscription instance, then uses its SetFieldValue method to set the various properties of the Subscription. SQLNS uses this information to generate notifications from newly received events.
After adding subscriptions to the database you can begin generating notifications from new incoming events. Remember that SQLNS generates notifications by comparing new events to the stored subscription information. More accurately, it just performs a simple T-SQL JOIN statement (the Notification Rule) between the Events and the Subscriptions table. The results of this JOIN are the notifications that SQLNS must send to the subscribers.
You define the structures of the resulting notifications through Notification Classes in the Application Definition file, in the section
After adding the Notification class, and execute nscontrol.exe to update the SQLNS instance it creates the new notification table within the application database. The last thing you need to add is a Notification Rule. The following example shows where to place the Notification Rule in the Application Definition file.
For clarity, I’ve included the Notification Rule in a separate code fragment below:
INSERT INTO StockPriceNotifications (SubscriberId, DeviceName, SubscriberLocale, StockCode, ExchangeCode, Price) SELECT s.SubscriberId, s.DeviceName, s.SubLocale, e.StockCoce, e.ExchangeCode, e.Price FROM StockEvt e, StockPriceSubscriptions s WHERE e.ExchangeCode = s.ExchangeCode AND e.StockCode = s.StockCode AND e.Price > s.TriggerVal
Be sure to escape all special characters (such as “>”) because you’re writing the Notification Rule in an XML file! Alternatively, you could put the Notification Rule in a stored procedure.
The preceding T-SQL statement inserts a new record into the notification table whenever SQLNS receives a matching event, then it hands all generated notifications over to the Formatter. The Formatter handles notification formatting. SQLNS ships with the XsltFormatter used in the sample application, but you’re free to write your own Formatter for special cases. Using the XsltFormatter you specify an XSLT stylesheet that formats the notification. You define the Formatter in the
XsltFormatter XsltBaseDirectoryPath C:Stylesheets XsltFileName Tansformation.xslt
After the notifications are formatted the last thing to do is send them to the subscribers. To do that, you specify a Delivery Protocol. SQLNS provides two built-in protocols, SMTP and File, which send notifications through e-mail or write them into a file. Developers often use the File protocol for testing purposes. You configure Delivery Protocols in the Instance Configuration File as follows.
FileChannel File FileName C:Notifications.txt
After adding the DeliveryChannel, run nscontrol.exe one last time, and the whole notification application should work. When you put a new event file in the watch directory SQLNS should write the resulting notification in the file C:Notifications.txt.
For further information about SQLNS you should read Shyam Pather’s book Microsoft SQL Server Notification Services. I hope this example has given you an overall introduction to SQLNS. And I urge you to experiment with and modify the sample configuration files to build your own applications.