Building Custom Hosted Event Providers

If you need an event provider that runs within the SQL-NS engine, but none of the builtin event providers are suitable, you need to build a custom hosted event provider. Custom hosted event providers plug into the framework provided by the SQL-NS engine in the same way that the built-in event providers do.

The previous section covered the APIs that any custom event provider (hosted or not) can use to submit events. This section is about building a custom hosted event provider using some of those APIs.

Why Build a Custom Hosted Event Provider?

Building a custom event provider allows you to interface with event sources that are not well suited to either of the built-in event providers. It's certainly easy to imagine such event sources: Think about getting events from a web service or from a text feed. Neither of the built-in event providers would be of much help. The need to work with custom event sources is the most common reason for building a custom event provider.

However, there is also another important reason. If you build a custom event provider, you can perform custom processing or filtering on the data read from the event source, before submitting it to the SQL-NS application. In cases where the event source may provide duplicate or erroneous events, preprocessing the data in the event provider may allow your SQL-NS application to be more efficient and avoid errors. Filtering is also important when the event source provides an overwhelming amount of event data, only a small portion of which is relevant to your application. In a custom event provider, you can weed out irrelevant data. Some custom event providers even look at the subscriptions in the SQL-NS application to determine which events to submit. For more information on prefiltering event data in a custom event provider, see the "Prefiltering Events" section (p. 422) in Chapter 12, "Performance Tuning."

The reasons just described apply to all custom event providers, whether hosted or standalone. Let's consider some specific reasons for building a hosted custom event provider:

• Hosted event providers run within the SQL-NS engine and therefore do not require an additional process in which to execute.

• Because they run in the SQL-NS engine, hosted event providers run within a well-established security context; you do not have to invent a new security model just for your event provider.

• Using a hosted event provider reduces the complexity of administering your application in deployment. For example, when you start the SQL-NS engine, the event provider starts with it. Your operations staff do not need to perform additional steps to start and stop the event provider, and they can monitor and administer it using the standard mechanisms for components in the SQL-NS engine.

Although these advantages are compelling, you shouldn't get the impression that hosted event providers are necessarily a better choice than standalone event providers. There are certain scenarios to which standalone event providers are better suited. These are discussed in the "Building Standalone Event Providers" section (p. 291).

Choosing an Event Provider Type

The "Event Providers and Event Sources" section (p. 234) describes two kinds of event sources: active and passive. Active event sources push event data out to listeners; passive event sources make data available and wait for interested parties to ask for it.

There are also two types of hosted event providers: continuous and scheduled. Continuous event providers are started and then run until they are stopped; scheduled event providers are invoked periodically, on a configurable schedule. The FileSystemWatcherProvider is an example of a continuous event provider. It starts and then continuously monitors a directory for new event files. The SQLProvider is an example of a scheduled event provider. It is invoked on a periodic schedule to execute the events query.

Although it isn't a firm rule, in general, continuous event providers are well suited to active event sources, and scheduled event providers are well suited to passive event sources. A continuous event provider typically establishes itself as a listener with the active event source and then waits to receive events. In contrast, a scheduled event provider periodically polls the event source, checking whether new event data is available.

The type of event provider type you should build depends on the particular needs of your application. This section shows examples of both continuous and scheduled event providers for the music store application. However, only the scheduled event provider really makes sense because the music store application's event source (the music store database) is passive. To test the continuous event provider, we will simulate an active event source. Admittedly, this is somewhat of an artificial scenario, but, for the purpose of explanation, it is sufficient to illustrate continuous event provider operation.

Classes, Interfaces, and Assemblies

Implementing a custom hosted event provider involves building a class that implements a SQL-NS interface. The code for the event provider class can be written in any of the .NET languages, although the examples in this book are in C#. This book assumes familiarity with object-oriented programming, but this section provides a brief recap of classes and interfaces.

Think of a class as a definition of a type of object. The class defines the data that the object encapsulates and the implementation of the methods and properties it exposes.

An interface is a description of a set of methods and properties. An interface does not provide the implementation of the methods and properties, but declares their signatures (names, arguments, and return value types).

A class can implement an interface by implementing the methods and properties that the interface describes. Many different classes can implement the same interface. You can think of an interface as a contract between a class and its callers. If a class implements a particular interface, callers know that it provides the methods and properties that the interface describes.

The interface concept is the basis for all custom components in the SQL-NS engine. For each type of custom component the SQL-NS engine supports, it defines an interface. The engine uses the methods described by those interfaces to interact with the components. Any class that implements the interface for a particular component type can plug into the SQL-NS engine as a custom component. The engine just needs to know how to load and instantiate the class, and then it can interact with it through the methods described in the interface.

In the case of hosted event providers, SQL-NS defines two interfaces: one for continuous event providers and one for scheduled event providers. These interfaces are presented in the next section. These interfaces describe the methods that the engine calls to interact with event providers. To build a custom event provider, you need to implement these methods in a class of your own.

After you write the code for your event provider class, you build it using the .NET compiler for the language you're using. This produces an assembly (a .dll file) that contains the compiled, binary representation of the class. To use the event provider in a SQL-NS application, you declare it in a <HostedProvider> element in the ADF, much like you did for the built-in event providers. In the <HostedProvider> declaration, you specify the name of the class that implements the event provider interface and the name of the assembly that contains the compiled class. At runtime, the SQL-NS engine loads the assembly and creates an object of the event provider class. In the <HostedProvider> declaration, you can also supply a set of arguments that the SQL-NS engine passes to the event provider on startup.

Hosted Event Provider Interfaces

SQL-NS defines two interfaces for hosted event providers:

• IEventProvider for continuous event providers

• IScheduledEventProvider for scheduled event providers

Both interfaces are defined in the Microsoft.SqlServer.NotificationServices namespace and are compiled into the SQL-NS assembly. To use these interfaces in a Visual Studio project, you need to add a reference to the SQL-NS assembly. This section describes these interfaces and the methods they define. The subsequent sections show examples of classes that implement them.

The IEventProvider Interface

Listing 8.7 shows the definition of the IEventProvider interface. The interface defines three methods, Initialize(), Run(), and Terminate(). All continuous event provider classes must implement these methods.

Listing 8.7. The IEventProvider Interface

 interface IEventProvider {     void Initialize(         NSApplication application,         string providerName,         StringDictionary args,         StopHandler stopDelegate);     bool Run();     void Terminate(); } 

To start an event provider, the SQL-NS engine creates an instance of the event provider class and then calls the Initialize() method. The engine passes four arguments to the Initialize() method:

• An NSApplication object representing a connection to the SQL-NS application to which the event provider submits events

• An event provider name, as configured in the <ProviderName> element of the provider's declaration in the ADF

• A dictionary of arguments as specified in the <Arguments> element of the event provider's declaration in the ADF

• A delegate that the event provider can invoke to tell the engine that it needs to stop

Tip

The Initialize() method on IEventProvider uses a StringDictionary object to pass arguments to the event provider. StringDictionary is a class in the .NET Frameworks, defined in the System.Collections.Specialized namespace. It is used in several of the SQL-NS interfaces, including IEventProvider. Whenever you write the code for a class that implements any of these interfaces, you need to put a using declaration for System.Collections.Specialized at the top of your source file.

The implementation of the Initialize() method typically stores the NSApplication object, provider name, and stop delegate in private member variables for use later. It also usually validates and stores the arguments passed in from the ADF. SQL-NS requires that the Initialize() method of every event provider implementation must return within 5 minutes or else the engine will consider it to have timed out. If this occurs, it will log an error and immediately call the provider's Terminate() method.

The SQL-NS engine calls the Run() method to tell the event provider to begin processing. In this method, the continuous event provider typically registers itself as a listener with the event source and then waits to receive events. The Run() method must also return within 5 minutes, so continuous event providers will usually create a new thread to continue processing after the Run() method returns. Run() must return true if the event provider is running successfully, false if it encountered an error and cannot run.

The Terminate() method is called by the SQL-NS engine to tell the event provider to stop. This typically occurs at the time the SQL-NS engine is shut down but can also occur if an administrator disables the event provider. Terminate() has a 1-minute window in which to return; otherwise, the event provider is terminated unconditionally. Within that 1-minute window, the event provider must stop submitting events and clean up any resources it has in use.

All the event provider methods are expected to catch and handle exceptions internally. They should not throw any exceptions out to the caller (the SQL-NS engine), unless the exception indicates a fatal error. If any event provider method does throw an exception, the SQL-NS engine logs an error and terminates the event provider immediately.

The IScheduledEventProvider Interface

Listing 8.8 shows the definition of the IScheduledEventProvider interface that scheduled hosted event providers need to implement. Notice that the methods and their signatures are the same as those defined in the IEventProvider interface.

Listing 8.8. The IScheduledEventProvider Interface

 public interface IScheduledEventProvider {     public void Initialize(         NSApplication application,         string providerName,         StringDictionary args,         StopHandler stopDelegate);     public bool Run();     public void Terminate(); } 

Although the methods and their signatures are the same, the way in which the Run() method is called differs from IEventProvider. In a class that implements IEventProvider, the Run() method is called only once, at the time that the event provider starts. In a class that implements IScheduledEventProvider, the Run() method is called repeatedly, according to the schedule defined in the event provider's ADF declaration. Each time the Run() method is called, the event provider can do the processing it needs to obtain and submit events, as long as it returns within a 5-minute time limit. As is the case in IEventProvider(), the Run() method must return a Boolean value indicating whether the event provider is running successfully.

The calling pattern for the Run() method is the only difference between IScheduledEventProvider and IEventProvider. The Initialize() and Terminate() methods have the same semantics across the two interfaces.

Building a Continuous Custom Hosted Event Provider

To build a continuous custom hosted event provider for the music store application, we need to create a class that implements IEventProvider. As mentioned earlier, a continuous hosted event provider doesn't make much sense in the context of the music store application, but this example is included here to illustrate the concepts behind continuous event providers in general. As you read this section, focus on the general principles, not on the specific example.

The continuous event provider we're going to build is based on Message Queuing. The "Installing Message Queuing" section (p. 245) earlier in this chapter provides instructions on how to install Message Queuing on your system. The sample described in this section will not work if you do not have Message Queuing installed.

A Brief Introduction to Message Queuing

Message Queuing is a technology that facilitates reliable transactional communication between applications. An application can establish one or more message queues on which it can exchange messages with other applications. The underlying message queuing framework provides the applications with reliability, transactional, and ordering guarantees about the queuing and retrieval of messages.

In typical message queuing scenarios, one application creates a message queue and assigns it a name. Other applications then reference this message queue by that name to send and receive messages on it. Message Queuing can be used to communicate between different parts of one application in a single process, between applications on different processes, and even between applications distributed across different machines.

The .NET Framework offers a number of Message Queuing APIs in the System.Messaging namespace. Using these APIs, applications can exchange objects as messages on queues. The sample event provider in this chapter uses these APIs.

A Message Queuing Event Provider

The event provider we build in this section establishes a message queue and then listens for messages on it. Messages sent to the queue contain event data that the event provider reads and then submits to the music store application.

We'll use a simple client program to test the event provider. This program writes some hard-coded event data to the queue, which the event provider then receives.

Message queues are identified by name; therefore, the event provider needs to assign a name to the queue it establishes. Rather than use a fixed name, hard-coded in the implementation, the event provider allows you to specify the queue name via an argument in the ADF (in the <Arguments> element of its <HostedProvider> declaration). This allows the event provider to be easily reused with different queue names. You could even declare two instances of the event provider in the ADF, each using a different queue name.

The Continuous Event Provider Class

Use the following instructions to open the Visual Studio solution containing the code for the continuous event provider:

1.	Navigate to the C:\SQL-NS\Samples\MusicStore\SongAlerts\CustomComponents\MQHostedEventProvider directory.
2.	Open the solution file, MQHostedEventProvider.sln, in Visual Studio.

From within the solution, open MQHostedEventProvider.cs and browse through the event provider code. Near the beginning of this file, you'll see a class called SongAddedEvent. Ignore this for now and focus on the MQHostedEventProvider class, which contains the actual event provider implementation. Listing 8.9 shows an outline of this class.

Listing 8.9. Outline of the MQHostedEventProvider Class

 namespace SongAlertsCustomComponents {public class MQHostedEventProvider : IEventProvider     {         public MQHostedEventProvider()         {             // Do nothing.         }         public void Initialize(             NSApplication application,             string providerName,             StringDictionary args,             StopHandler stopDelegate)         {             ...         }         public bool Run()         {             ...         }         public void Terminate()         {             ...         }         private void DoEventSubmission()         {             ...         }     } } 

Notice that the class declaration (the first line) indicates that the class implements IEventProvider. The body of the class contains the implementations of the three IEventProvider methods, Initialize(), Run(), and Terminate(). There is also a helper method (DoEventSubmission()) and a constructor.

The constructor takes no arguments and does nothing. All the initialization happens in the Initialize() method. All classes that implement SQL-NS plug-in components must have a public, parameterless constructor. This is required because the class will be instantiated by the SQL-NS engine at runtime, using the information specified in the ADF. If the constructor took parameters, the engine would not be able to instantiate the class because it would not know what values to pass for those parameters.

Let's look at the event provider class's methods, starting with Initialize() in Listing 8.10.

Listing 8.10. Implementation of the Initialize() Method in MQHostedEventProvider

 public class MQHostedEventProvider : IEventProvider {     ...     private NSApplication   songAlertsApp;     private string          providerName;     private StopHandler     stopHandler;     private string          queueName;     private MessageQueue    queue;     private EventCollector  eventCollector;     private bool            terminating;     private Thread          submissionThread;     ...     public void Initialize(         NSApplication application,         string providerName,         StringDictionary args,         StopHandler stopDelegate)     {         // Initialize the private members.         this.songAlertsApp = application;         this.providerName = providerName;         this.stopHandler = stopDelegate;         // Get the queue name from the arguments passed in.         if (args.ContainsKey("QueueName"))         {             this.queueName = args["QueueName"];         }         else         {             throw new ArgumentException(                 "A required event provider argument was not specified.",                 "QueueName");         }         // Initialize the message queue object.         if (MessageQueue.Exists(queueName))         {             // The message queue exists, so just obtain a reference             // to it.             this.queue = new MessageQueue(queueName);         }         else         {             // The message queue does not exist, so create it.             this.queue = MessageQueue.Create(queueName);         }         // Set the formatter to indicate body contains         // a SongAddedEvent.         this.queue.Formatter = new XmlMessageFormatter(             new Type[] {typeof(SongAddedEvent)});         // Initialize the event collector used to submit events.         this.eventCollector = new EventCollector(             this.songAlertsApp,             this.providerName);         // Initialize the terminating flag.         this.terminating = false;         // Initialize the thread object to null - we'll create         // it in the Run() method.         this.submissionThread = null;     }     ... } 

Initialize() begins by storing the supplied parameters (passed by the SQL-NS engine) in private member variables so that they can be used later. One of the items passed in is a dictionary of event provider arguments, constructed from the <Arguments> element in the event provider's ADF declaration. The dictionary contains one entry for each argument name and value provided in the ADF. Initialize() looks in the dictionary for an argument called QueueName. If it doesn't find this argument, it throws an exception indicating that a required argument is missing. The SQL-NS engine will catch this exception and stop the event provider immediately. If the queue name argument is found, it is stored in a private member variable.

The code then initializes a MessageQueue object that will be used later to read from the queue. It checks whether a queue with the given queue name already exists and, if not, creates it. If the queue does exist, the code just creates a new MessageQueue object that refers to it.

The next line sets the Formatter property on the queue to indicate what type of messages will be sent and received on the queue. Because the queue is used to exchange events, the messages must represent event data. Recall that at the top of the MQHostedEventProvider.cs file there is the definition of a class called SongAddedEvent. This simple class provides an encapsulation of the data associated with a SongAdded event. The value set for the Formatter property indicates that the messages sent and received on the queue will be XML-serialized instances of the SongAddedEvent class.

The event provider uses the Event Object API, so after setting the Formatter property on the queue, Initialize() creates an EventCollector object that will be used later to submit events. It passes the NSApplication object and provider name that it obtained from the SQL-NS engine to the EventCollector constructor.

The final two statements of Initialize() provide initial values for two private member variables, terminating and submissionThread. The terminating variable is a Boolean flag used to signal that the event provider is shutting down. The submissionThread variable will hold a thread object that represents the running thread used to submit events. The use of both these variables will become clear when we look at the Run() and Terminate() methods.

Listing 8.11 shows the implementation of the event provider's Run() method.

Listing 8.11. Implementation of the Run() Method in MQHostedEventProvider

 public class MQHostedEventProvider : IEventProvider {     ...     public bool Run()     {         // Create a thread to do the event submission.         this.submissionThread =             new Thread(new ThreadStart(this.DoEventSubmission));         // Start the thread.         this.submissionThread.Start();         return true;     }     ... } 

The Run() method does not do any event submission itself. Rather, it creates a new thread that will do the event submission later. The new thread is needed because Run() must return within a 5-minute time window. The thread that calls Run() belongs to the SQL-NS engine, and the event provider cannot use it to listen for messages on the queue indefinitely. Run() just establishes a thread that will do the listening and event submission, and then returns.

To create the thread, Run() instantiates a new Thread object and passes it a delegate to the DoEventSubmission() helper method. When the thread starts up, it begins executing in this method. After creating the thread object, Run() calls its Start() method and returns TRue, indicating success.

Before looking at the DoEventSubmission() method, which does most of the work of the event provider, let's look at the final IEventProvider method, Terminate(). Listing 8.12 shows the code for Terminate().

Listing 8.12. Implementation of the Terminate() Method in MQHostedEventProvider

 public class MQHostedEventProvider : IEventProvider {     ...     public void Terminate()     {         // Set the terminating flag.         lock(this)         {             this.terminating = true;         }         // Now wait for the submission thread to exit.         this.submissionThread.Join();     }     ... } 

The Terminate() method sets the terminating flag to indicate to the event submission thread that the event provider needs to shut down (as you'll see later, the submission thread checks this flag periodically). Because the terminating flag can be accessed by multiple threads (the thread that calls Terminate sets it and the event submission thread reads it), the lock construct is used to synchronize access to it. After setting the terminating flag, Terminate() then calls the Join() method on the thread object, which blocks until the thread completes.

Let's examine DoEventSubmission(), the method that does the actual event submission. The event submission thread created by Run() begins in this method and stays in this method until it exits. Note that DoEventSubmission() is not a method defined by IEventProvider but rather a helper method implemented by the MQHostedEventProvider class. Listing 8.13 shows the code for DoEventSubmission().

Listing 8.13. Implementation of the DoEventSubmission() Method in MQHostedEventProvider

 public class MQHostedEventProvider : IEventProvider {     private const string    EVENT_CLASS_NAME = "SongAdded";     ...     private void DoEventSubmission()     {         TimeSpan    timeout = new TimeSpan(0, 0, 5); // 5 seconds         bool        shouldTerminate;         lock(this)         {             shouldTerminate = this.terminating;         }         while (!shouldTerminate)         {             try             {                 // Read a message from the queue. Specify a                 // timeout value so that we don't block forever                 // and can check the terminating flag.                 Message m = queue.Receive(timeout);                 // Extract the SongAddedEvent from the body.                 SongAddedEvent e = (SongAddedEvent)m.Body;                 // Create a SQL-NS event and submit it.                 Event evt =                     new Event(songAlertsApp, EVENT_CLASS_NAME);                 evt["SongId"] = e.SongId;                 eventCollector.Write(evt);                 eventCollector.Commit();             }             catch (MessageQueueException mex)             {                 // If the exception indicated that a timeout                 // occurred, then we absorb it and just let                 // the loop run again. Any other error means                 // something went wrong and we call the stop                 // delegate.                 if (mex.MessageQueueErrorCode !=                       MessageQueueErrorCode.IOTimeout)                 {                     this.stopHandler();                     break;                 }             }             catch (Exception)             {                 // For all other exceptions, call the stop handler.                 this.stopHandler();                 break;             }             lock(this)             {                 shouldTerminate = this.terminating;             }         }     }     ... } 

The method begins by declaring a TimeSpan object that represents a duration of 5 seconds. This will be used to specify a timeout value when reading from the queue. Before doing any actual work, DoEventSubmission() checks the terminating flag (inside a lock construct for thread safety) and stores its current value in a local variable that can be checked without locking.

It then enters a while loop. In this loop, it reads from the message queue, submits an event when a message arrives, and checks the terminating flag again. The loop continues until either the terminating flag is set or a fatal error occurs.

To read from the queue, DoEventSubmission() calls the Receive() method on the queue object, passing it the 5-second timeout value. This call blocks until either a message is received or 5 seconds pass and a timeout occurs.

When a message is received, its contents are stored in a Message object. This message object contains a serialized copy of a SongAddedEvent object. The code obtains the SongAddedEvent object by casting the Body method of the Message object. It then creates a new Event object (recall that Event is a class in the Event Object API) and initializes it with the NSApplication object representing the application and the event class name. It then sets the SongId field in the event object to the value stored in the SongAddedEvent object just read from the queue.

After the event object is properly initialized, the code passes it to the EventCollector's Write() method. It then immediately calls Commit() on the event collector to close the event batch. Thus, this event provider produces event batches containing single events.

If a timeout occurs while reading from the message queue, the Receive() method throws a MessageQueueException object, with an error code value of IOTimeout. The code that reads from the message queue appears within a try block and catches exceptions of type MessageQueueException. The code only treats a MessageQueueException as a real error condition if the error code is anything other than IOTimeout. In the case of exceptions generated for timeouts, the exceptions are just ignored, and the loop continues.

The timeout is needed to facilitate periodic checking of the terminating flag. If DoEventSubmission() just did a blocking read on the queue, without checking this flag, a request to terminate the event provider might go ignored until a message is actually received. This would obviously cause problems because the Terminate() method waits for the submission thread to complete, and this must happen within a 1-minute window. In general, continuous event providers must be written in such a way that they are responsive to terminate requests.

Building the Continuous Event Provider Assembly

Build the MQHostedEventProvider project in Visual Studio. This compiles the source code we just looked at and produces the event provider assembly.

You will find the event provider assembly in the bin\Debug output directory under the MQHostedEventProvider project directory. Navigate to C:\SQL-NS\Samples\MusicStore\SongAlerts\CustomComponents\MQHostedEventProvider\bin\Debug and verify that MQHostedEventProvider.dll has been created.

Declaring the Continuous Custom Hosted Event Provider in the ADF

To use the continuous custom hosted event provider, we need to declare it in the ADF in a <HostedProvider> element. Listing 8.14 shows this declaration.

Listing 8.14. Declaration of the Continuous Custom Hosted Event Provider in the ADF

[View full width]

 <Application>   ...   <Providers>     ...     <HostedProvider>       <ProviderName>SongAddedMQProvider</ProviderName>       <ClassName>SongAlertsCustomComponents.MQHostedEventProvider</ClassName>       <AssemblyName>%_ApplicationBaseDirectoryPath_%\CustomComponents \MQHostedEventProvider\bin\Debug\MQHostedEventProvider.dll</AssemblyName>       <SystemName>%_NSServer_%</SystemName>       <Arguments>         <Argument>           <Name>QueueName</Name>           <Value>.\Private$\SongAddedEventQueue</Value>         </Argument>       </Arguments>     </HostedProvider>     ...   </Providers>   ... </Application> 

As with other <HostedProvider> declarations you've seen, the <ProviderName> element assigns a name to the event provider. The <ClassName> element specifies the name of the event provider class. As shown in Listing 8.9, the event provider class name is MQHostedEventProvider and it is declared in the namespace, SongAlertsCustomComponents. Thus, the fully qualified name of the event provider class is SongAlertsCustomComponents.MQHostedEventProvider. This fully qualified name is given as the value for the <ClassName> element in Listing 8.14.

Caution

Whenever you refer to a class that implements a SQL-NS custom component in the ADF, you must specify its fully qualified name, including any namespace prefix.

The <AssemblyName> element provides the full path to the event provider assembly. In the example shown in Listing 8.14, this path is derived from the _ApplicationBaseDirectoryPath_ parameter and points to the event provider assembly you just built. Note that even though we declare both custom and built-in hosted event providers with the same <HostedProvider> element, the <AssemblyName> element is required only in the case of custom event providers.

As before, the <SystemName> element specifies the machine on which the event provider should run, and the <Arguments> element specifies the arguments that get passed to the event provider at startup (via the args parameter to the Initialize() method). Our event provider requires a single argument, QueueName, that specifies the name of the event queue that will be used to receive events.

Testing the Continuous Custom Hosted Event Provider

To test the custom hosted event provider, add the code shown in Listing 8.14 to the ADF. You can find this code in the supplemental ADF, ApplicationDefinition-9.xml. After you've added the code to the ADF, update your instance using the update instructions provided earlier in this chapter.

Caution

Before updating your instance, make sure that you have built the event provider assembly successfully, as described in the "Building the Continuous Event Provider Assembly" section (p. 278). When you start the SQL-NS service after the update, it will try to load the custom event provider from this assembly. If it can't find the assembly, the service will log an error to the Application Event Log. The message in the log will have the ID 2008 and its description will read:

   Notification Services failed to load the event provider assembly. 

When you start the SQL-NS service after updating your instance, the new custom hosted event provider starts. We now need to send a message to the message queue to see this event provider working. I've provided a simple test program that connects to the message queue and writes a single hard-coded event. Perform the following instructions to build and run this program:

1.	Navigate to the C:\SQL-NS\Samples\MusicStore\SongAlerts\CustomComponents\Test\MQClient directory.
2.	Open the solution file MQClient.sln in Visual Studio.
3.	Build the MQClient project.
4.	In a command prompt, navigate to the C:\SQL-NS\Samples\MusicStore\SongAlerts\CustomComponents\Test\MQClient\bin\Debug directory.
5.	Run the MQClient.exe program. This should produce the following output: Connected to message queue: .\Private$\SongAddedEventQueue Wrote SongAdded event with SongId=15

As the output indicates, the client program writes a single SongAdded event to the message queue, with the SongId field set to 15. Verify that the event provider submitted this event to the SQL-NS application by examining the contents of the events table in Management Studio as you did before.

Querying the Nseventbatchview

Our application now has three event providers: the FileSystemWatcherProvider, the SQLProvider, and the custom hosted event provider we just added. Looking at the events and event batches tables, it's not always clear which event provider submitted a particular event batch. To find out, you can query the NSEventBatchView that the SQL-NS compiler creates in every application schema. This view returns a row for every event batch submitted into the application. Columns in the view indicate the event class that the events in the batch belong to and the name of the event provider that submitted them.

To query the view in the music store application, you can issue the following commands in Management Studio:

   USE MusicStore   SELECT * FROM [SongAlerts].[NSEventBatchView] 

The rows returned by this query should list event batches from all three of the application's event providers. The provider names we configured in the ADF were SongAddedFSWProvider for the FileSystemWatcher, SongAddedSQLProvider for the SQLProvider, and SongAddedMQProvider for our continuous custom hosted event provider. You should see these provider names in the ProviderName column in the resultset. Event batches submitted by directly inserting rows into the events view (as we did in previous chapters) have NULL in the ProviderName column.

Building a Scheduled Custom Hosted Event Provider

In this section, we build a scheduled custom hosted event provider. Both the development process and the code for this event provider are similar to what you just saw in the previous section for the continuous hosted event provider.

To build the scheduled event provider, we create a class that implements IScheduledEventProvider. We then configure this event provider in the ADF in a <HostedProvider> element, which will specify an invocation schedule.

Operation of the Scheduled Event Provider

The scheduled event provider described in this section polls the music store database to find new songs that have been added. It remembers the last time it ran and looks for songs with timestamps between that last time and the current time. It then submits SongAdded events for the song IDs it finds.

This is similar to the way the SQLProvider works, but rather than running the events query and doing event submission in the database, this event provider reads the data from the event source into memory. When the data is in memory, it is then submitted using the Event Object API. Building an event provider this way provides an opportunity to process or filter the data from the event source in memory before submitting it as events to the SQL-NS application. However, this requires a trade-off with respect to efficiency, as described in the section "The SQLProvider" (p. 253). Transferring the data in and out of the database repeatedly can be inefficient.

The Scheduled Event Provider Class

Use the following instructions to open the Visual Studio solution containing the code for the scheduled event provider:

1.	Navigate to the C:\SQL-NS\Samples\MusicStore\SongAlerts\CustomComponents\ScheduledHostedEventProvider directory.
2.	Open the solution file ScheduledHostedEventProvider.sln in Visual Studio.

Within the solution, open ScheduledHostedEventProvider.cs and browse through the event provider code. The event provider class is called ScheduledHostedEventProvider and implements IScheduledEventProvider. Listing 8.15 shows the class declaration and the implementation of the Initialize() method of IScheduledEventProvider.

Listing 8.15. Implementation of the Initialize() Method in ScheduledHostedEventProvider

 namespace SongAlertsCustomComponents {     ...     class ScheduledHostedEventProvider : IScheduledEventProvider     {         ...         private const string    DATABASE_NAME = "MusicStore";         private NSApplication   songAlertsApp;         private string          providerName;         private StopHandler     stopHandler;         private EventCollector  eventCollector;         private string          serverName;         private string          sqlUserName;         private string          sqlPassword;         private string          connectionString;         private SqlCommand      command;         private DateTime        lastRunTime;         public void Initialize(             NSApplication application,             string providerName,             StringDictionary args,             StopHandler stopDelegate)         {             // Initialize the private members.             this.songAlertsApp = application;             this.providerName = providerName;             this.stopHandler = stopDelegate;             // Initialize the event collector used to submit events.             this.eventCollector = new EventCollector(                 this.songAlertsApp,                 this.providerName);             // Read the arguments passed in.             if (args.ContainsKey("DatabaseServerName"))             {                 this.serverName = args["DatabaseServerName"];             }             else             {                 throw new ArgumentException(                     "A required event provider argument was not specified.",                     "DatabaseServerName");             }             AuthenticationMode authMode                 = AuthenticationMode.WindowsAuthentication;                 // Treat a blank username as if no username was specified.                 if (args.ContainsKey("SqlUserName") &&                     args["SqlUserName"] != "")                 {                     this.sqlUserName = args["SqlUserName"];                     authMode = AuthenticationMode.SqlServerAuthentication;                 }                 else                 {                     // This argument is optional, so no need to throw                     // an error if it wasn't supplied.                     this.sqlUserName = null;                 }                 if (args.ContainsKey("SqlPassword")                     && args["SqlPassword"] != "")                 {                     this.sqlPassword = args["SqlPassword"];                 }                 else                 {                     // This argument is optional, so no need to throw                     // an error if it wasn't supplied.                     this.sqlPassword = null;                 }                                  // The SQL username and password must either be                 // supplied together, or neither of them must be                 // supplied. It's invalid to supply just one or                 // the other.                 if (this.sqlUserName == null ^ this.sqlPassword == null)                 {                     string message;                     string argument;                     if (this.sqlUserName == null)                     {                         message = "A SQL password was supplied without a username";                         argument = "SqlPassword";                     }                     else                     {                         message = "A SQL username was supplied without a password";                         argument = "SqlUserName";                     }                     throw new ArgumentException(message, argument);                 }                 // Build the database connection string.                 this.connectionString = BuildConnectionString(                     this.serverName,                     DATABASE_NAME,                     true,                     authMode,                     this.sqlUserName,                     this.sqlPassword,                     this.providerName);                 // Create the SQL command object that will read new songs                 // from the database.                 this.command = new SqlCommand();                 this.command.CommandText = @"     SELECT SongId FROM [Catalog].[SongDetails]     WHERE DateAdded > @LastRunTime AND DateAdded <= @CurrentRunTime";                 this.command.CommandType = CommandType.Text;                 this.command.Parameters.Add(                     "@LastRunTime",                     SqlDbType.DateTime);                 this.command.Parameters.Add(                     "@CurrentRunTime",                     SqlDbType.DateTime);                 // Initialize the last run time.                 this.lastRunTime = DateTime.UtcNow;         }         ...     } } 

The Initialize() method stores the arguments it receives from the SQL-NS engine in private member variables, creates an event collector for use later, and then validates the ADF arguments. Because this event provider needs to connect to the music store database, the ADF arguments must provide the database server name and a SQL username and password if SQL Authentication is used. Initialize() obtains values for these arguments from the dictionary passed in and uses them to build a connection string to the database.

Initialize() then creates a SqlCommand object that will be used to query the music store database for new songs. This command selects the song IDs from the music store catalog's SongDetails view, where the song timestamp falls between the last and current runtime of the event provider. The command text is specified using parameters for the last runtime and current runtime. Later, when the event provider executes the command, it provides values for these parameters.

Finally, Initialize() stores the current UTC time in the private member variable, lastRunTime. This variable is used to track the last time the event provider was run. Initializing this variable with the current UTC time on startup means that on the first run, the event provider looks for songs added since its start time.

Listing 8.16 shows the implementation of the Run() and Terminate() methods on the scheduled event provider class.

Listing 8.16. Implementation of the Run() and Terminate() Methods in ScheduledHostedEventProvider

 namespace SongAlertsCustomComponents {     ...     class ScheduledHostedEventProvider : IScheduledEventProvider     {         private const string    EVENT_CLASS_NAME = "SongAdded";         ...         public bool Run()         {             // Create a database connection.             SqlConnection connection =                 new SqlConnection(this.connectionString);             int eventsSubmitted = 0;             bool success = false;             // Get the current time.             DateTime currentRunTime = DateTime.UtcNow;             try             {                 // Open the connection.                 connection.Open();                 using (connection)               {                   // Set the command's Connection property to the                   // current connection.                   this.command.Connection = connection;                   // Set the command parameter values.                   command.Parameters["@LastRunTime"].Value =                       this.lastRunTime;                   command.Parameters["@CurrentRunTime"].Value =                       currentRunTime;                   // Execute the command.                   SqlDataReader reader = command.ExecuteReader();                      using (reader)                   {                       // Read rows from the resultset and                       // submit the Song Ids as events.                       while (reader.Read())                       {                           int songId = (int) reader["SongId"];                             Event evt = new Event(                               songAlertsApp,                               EVENT_CLASS_NAME);                           evt["SongId"] = songId;                           eventCollector.Write(evt);                           eventsSubmitted++;                       }                   }                   success = true;               }         }         catch (Exception)         {             // Don't call the stop delegate here because             // one error should not cause the event provider             // to shut down. Next time Run() is called, we'll             // just try again.             // Set the success flag to false so we know to roll             // back the event batch.             success = false;         }         finally         {             // If no events were submitted, then there's no             // work to do here.             if (eventsSubmitted > 0)             {                 // If the submission was successful, commit                 // the event batch, otherwise abort it.                 if (success)                 {                     this.eventCollector.Commit();                     // Only set the last run time to the current                     // time now that we've successfully committed                     // the event batch.                     this.lastRunTime = currentRunTime;                 }                 else                 {                     this.eventCollector.Abort();                 }              }           }                    return true;         }         public void Terminate()         {             // Nothing to do.         }         ...     } } 

Recall that the key difference between the Run() method on a scheduled event provider and the Run() method on a continuous event provider is the frequency at which it is invoked. A continuous event provider's Run() method is invoked once and must create a separate thread to do any long-running operations. In contrast, the Run() method on the scheduled event provider is called repeatedly (on the schedule specified in the ADF), so it can implement the periodic detection and submission of events directly.

The Run() method shown in Listing 8.16 begins by obtaining the current time and storing this in a local variable. It then opens a connection to the database, specifies values for the last and current time parameters in the SQL command that queries the music store database, and then executes the command. For each row of data returned by the query, Run() creates an event object, sets the value of the SongId field, and then submits it to the event collector.

The finally block, which executes before Run() returns, commits the event batch if everything went successfully or aborts it if a failure occurred. In the case of success, the lastRunTime private member variable is updated with the current time.

Caution

Whenever you use the Event Object API, you must write your error handling code so that any open event batches are either committed or rolled back if an error occurs. If you abandon an event batch without either committing or aborting it, the data associated with that event batch will never be processed or cleaned up properly.

Notice that the Run() method always returns true. This indicates to the SQL-NS engine that it should be called again, at the next time specified by the schedule. If Run() returns false, the SQL-NS engine terminates the event provider immediately. In the case of this event provider, Run() returns true even if a failure occurs, so that it can make another attempt at checking for events the next time it is called. In other scheduled event providers, a single failure may warrant termination, in which case Run() should return false.

The Terminate() method shown in Listing 8.16 does nothing. The scheduled event provider does not keep any long-lived state that it needs to clean up on shutdown, so there is no work for Terminate() to do. This is different from the Terminate() method on the continuous event provider, which had to coordinate the shutdown of the worker thread.

Building the Scheduled Event Provider Assembly

Build the ScheduledHostedEventProvider project in Visual Studio. This compiles the source files and produces the event provider assembly.

After building the project, you will find the event provider assembly in the C:\SQL-NS\Samples\MusicStore\SongAlerts\CustomComponents\ScheduledHostedEventProvider\bin\Debug directory. The assembly is called ScheduledHostedEventProvider.dll.

Declaring a Scheduled Custom Hosted Event Provider in the ADF

Listing 8.17 shows the <HostedProvider> declaration for the scheduled custom hosted event provider. This is similar to the declaration you saw for the continuous custom hosted event provider, except that it specifies a schedule.

Listing 8.17. Declaration of the Scheduled Custom Hosted Event Provider in the ADF

[View full width]

 <Application>   ...   <Providers>     ...     <HostedProvider>       <ProviderName>SongAddedScheduledProvider</ProviderName>       <ClassName>SongAlertsCustomComponents.ScheduledHostedEventProvider</ClassName>       <AssemblyName>%_ApplicationBaseDirectoryPath_%\CustomComponents \ScheduledHostedEventProvider\bin\Debug\ScheduledHostedEventProvider.dll</AssemblyName>       <SystemName>%_NSServer_%</SystemName>       <Schedule>         <Interval>P0DT00H00M10S</Interval>       </Schedule>       <Arguments>         <Argument>           <Name>DatabaseServerName</Name>           <Value>%_SQLServer_%</Value>         </Argument>         <Argument>           <Name>SqlUserName</Name>           <Value></Value>         </Argument>         <Argument>           <Name>SqlPassword</Name>           <Value></Value>         </Argument>       </Arguments>     </HostedProvider>     ...   </Providers>   ... </Application> 

The <ClassName> element specifies the name of the event provider class, qualified with its namespace. The <AssemblyName> element specifies the full path to the event provider assembly.

Because this is a scheduled event provider, the declaration includes a <Schedule> element. This is the same type of <Schedule> element that we used to configure the SQLProvider earlier in this chapter. It follows the syntax of the XML duration data type, which is explained in the sidebar "The XSD duration Data Type" (p. 157) in Chapter 5. The value given in Listing 8.17 specifies a schedule interval of 10 seconds.

The <Arguments> section of the event provider declaration specifies the name of the database server hosting the music store database and the SQL username and password with which the event provider should connect (for SQL Server Authentication). The event provider's Initialize() method (shown earlier in Listing 8.15) processes these arguments. If the SqlUserName and SqlPassword argument values are left blank, the event provider will connect using Windows Authentication.

The original description of argument encryption in the early part of this chapter stated that it's useful when the event provider arguments contain sensitive information. This is an example of such a case: The event provider arguments may contain a username and password that should not be readily accessible to anyone looking at your application database. Because we're using argument encryption in this chapter's instance, the values of these event provider arguments will be encrypted before they are stored in the database by the compiler.

Testing the Scheduled Custom Hosted Event Provider

Add the code shown in Listing 8.17 to the ADF. You will find this code in the ApplicationDefintion-10.xml supplemental ADF. If you're using SQL Server Authentication, make sure the values of the SqlUserName and SqlPassword event provider arguments are set to the username and password of the test account you created in Chapter 2. (If you're using Windows Authentication, these arguments should be left blank.) If you configured your development environment for SQL Server Authentication in Chapter 2, the SqlUserName and SqlPassword argument values in the supplementary ADF will be preset to the username and password of your SQL test account.

After you've added the code to your ADF, update your instance according to the instructions given earlier. Before you do this, make sure that you have built the scheduled event provider code so that the event provider assembly exists. When you start the service after doing the update, the event provider will start.

Because this event provider reads song data directly from the music store database, all you need to do to test it is add a few new songs. As you did earlier, you can use the AddSongs program to accomplish this. When you use AddSongs, make sure that you leave the Submit Events for Songs Added check box unchecked.

After adding songs to the database, wait at least 10 seconds (recall that 10 seconds is the period of the scheduled event provider) and then check the events table. You should see events submitted for the songs you added. You will eventually see two events submitted for each new song because both the built-in SQLProvider we configured earlier and the custom event provider we built here will detect the new songs and submit events. The second set of events may take a little longer to appear in the events table because the SQLProvider runs on a period of 30 seconds. You can distinguish the events submitted by each event provider by querying the NSEventBatchView, as described in the sidebar, "Querying the NSEventBatchView," (p. 280), earlier in this chapter.