Building Standalone Event Providers

In this section, we add three new event providers to the application. These are standalone event providers, so they don't run in the SQL-NS engine. The examples in this section illustrate three ways that standalone event providers can be structured. In addition to the Event Object API, the examples in this section employ the event submission APIs that have not yet been used in this chapter.

Why Build a Standalone Event Provider?

Standalone event providers are not subject to the constraints imposed by the runtime environment of the SQL-NS engine, so they offer greater flexibility than hosted event providers. When designing a standalone event provider, you have the freedom to choose where it runs, how it is started, and what security and process model it follows.

Another good reason for building a standalone event provider is that you can embed it within another program. If you already have a program that acts as an event source, you can put the event provider functionality into this program instead of relying on an external event provider. For example, the AddSongs program we used to add song data to the music store application can actually be an event provider. When it adds new song records, it can also submit the corresponding events to the SQL-NS application directly. This simplifies the whole application because it removes the need for a separate event provider component (like the SQLProvider or the custom scheduled hosted event provider we built in the previous section) to read the song records and submit the corresponding event data. This section examines the code required to embed event provider functionality in the AddSongs program as well as build other types of standalone event providers.

Standalone Event Provider Configuration in the ADF

To use a standalone event provider in an application, it must be configured in the ADF. For each standalone event provider, you add a <NonHostedProvider> element to the <Providers> element. The <Providers> element may contain both hosted and standalone event provider declarations, but the <NonHostedProvider> declarations must appear after all the <HostedProvider> declarations.

Standalone event provider declarations are simple: The only element inside <NonHostedProvider> is <ProviderName>, which specifies a name for the standalone event provider. This name isn't really used by SQL-NS internally, except to validate the provider when submitting events; it mainly serves as an identification mechanism for tracking which batches the event provider submitted. SQL-NS does not need any other configuration information about standalone event providers because it does not host and invoke them in its engine.

You've already seen one example of a <NonHostedEventProvider> element: the declaration the test event provider used during initial testing of the music store prototype. Listing 8.18 shows the declaration of the three standalone event providers described in this section.

Listing 8.18. Declaration of the Standalone Event Providers in the ADF

 <Application>   <Providers>     ...     <NonHostedProvider>       <ProviderName>SongAddedXMLProvider</ProviderName>     </NonHostedProvider>     <NonHostedProvider>       <ProviderName>SongAddedEmbeddedProvider</ProviderName>     </NonHostedProvider>     <NonHostedProvider>       <ProviderName>SongAddedSPProvider</ProviderName>     </NonHostedProvider>   </Providers>   ... </Application> 

Each declaration specifies the provider name:

• SongAddedXMLProvider is the XML-based event provider described in the section "A Dedicated Program: The XML Event Provider" (p. 292).

• SongAddedEmbeddedEventProvider is the event provider embedded in the AddSongs program, described in the section "Submitting Events from an Application" (p. 296).

• SongAddedSPProvider is the event provider that uses SQL stored procedures to submit events, described in the section "Submitting Events with SQL Stored Procedures" (p. 302).

Because these declarations are so simple, we'll add them all at once rather than one at a time as we look at each example. The code in Listing 8.18 can be found in the supplemental ADF, ApplicationDefinition-11.xml. Add this code to your ADF and then update your instance to compile the changes. You must do this before running any of the code in the remainder of this section.

A Dedicated Program: The XML Event Provider

In this section, we build a standalone event provider that uses the XML Bulkloader event submission API. This is an example of an event provider as a dedicated program: It's only purpose is to submit events.

This event provider can be invoked directly from the command line to manually submit events. As such, it makes a handy tool to get events into the application for testing.

Design of the XML Event Provider

The XML event provider is a command-line program that requires two arguments:

• The name of an XML file containing event data for the music store application

• The name of a schema file describing the contents of the XML event file

The XML event provider is invoked at the command line as follows:

 XMLEventProvider -in <XmlDataFile> -schema <SchemaFile> 

<XmlDataFile> is the name of the XML file containing event data, and <SchemaFile> is the name of the schema file. The XML event provider loads the XML file and submits the data in it as events, using the schema file to map the XML data to fields in the event class.

The XML event provider also takes two optional arguments, a SQL username and password to use to connect to the database for SQL Server Authentication. If you're using SQL Server Authentication, the XML event provider is invoked with the two optional arguments as follows:

[View full width]
 XMLEventProvider -in <XmlDataFile> -schema <SchemaFile> -sqlusername <SqlUserName>  -sqlpassword <SqlPassword> 

<SqlUserName> is the SQL username with which to connect, and <SqlPassword> is the corresponding password.

The XML event provider uses the XML Bulkload API, which employs the same XML processing facilities as the FileSystemWatcherProvider. The XML event provider is in fact similar to the FileSystemWatcherProvider: It uses the same format for the XML event files and the schema files that describe them. The main difference is that the FileSystemWatcherProvider monitors a directory and picks up new files as they are added, whereas the XML event provider is invoked manually to process a specific file.

XML Event Provider Implementation

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

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

From the Solution Explorer, open XMLEventProvider.cs, which contains the implementation of the event provider. Most of the code in this file deals with parsing and validating command-line arguments. You should take a brief look at that code to see how it works, but it isn't covered here. The Main() method, which is the entry point for the program and drives its execution, contains the code that submits events. Listing 8.19 shows the code for the Main() method.

Listing 8.19. Implementation of the Main() Method in the XML Event Provider

     class XMLEventProvider     {         private const string   INSTANCE_NAME = "MusicStore";         private const string   APPLICATION_NAME = "SongAlerts";         private const string   PROVIDER_NAME = "SongAddedXMLProvider";         private const string   EVENT_CLASS_NAME = "SongAdded";         static private string  xmlDataFile = null;         static private string  schemaFile = null;         static private string  sqlUserName = null;         static private string  sqlPassword = null;         [STAThread]         static int Main(string[] args)         {             int retCode = 0;             // Process the command-line arguments and display an             // error message if appropriate.             try             {                 ValidateArguments(args);             }             catch (ArgumentException ex)             {                 Console.WriteLine("Invalid command line arguments");                 Console.WriteLine(ex.Message);                 ShowUsage();                 retCode = 1;             }             // If the command-line arguments were valid,             // we can continue.             if (retCode == 0)             {                 try                 {                     // Create the SQL-NS Instance and                     // Application objects.                     NSInstance musicStoreInstance = null;                     if (string.IsNullOrEmpty(sqlUserName) &&                         string.IsNullOrEmpty(sqlPassword))                     {                         musicStoreInstance =                             new NSInstance(INSTANCE_NAME);                     }                     else                     {                         musicStoreInstance = new NSInstance(                             INSTANCE_NAME,                             sqlUserName,                             sqlPassword);                     }                        NSApplication songAlertsApp = new NSApplication(                         musicStoreInstance,                         APPLICATION_NAME);                     // Create the event loader object.                     EventLoader eventLoader = new EventLoader(                         songAlertsApp,                         PROVIDER_NAME,                         EVENT_CLASS_NAME,                         schemaFile);                     // Load the XML file.                     eventLoader.LoadXml(xmlDataFile);                     Console.WriteLine("Loaded events successfully.");                 }                 catch (Exception ex)                 {                     Console.WriteLine("An error occurred");                     Console.WriteLine(ex.Message);                     retCode = 1;                 }             }             return retCode;         }     ... } 

The first part of the code calls a helper method, ValidateArguments(), to parse the command-line arguments and ensure that they're valid. (You can explore the code for this function in the source file even though it isn't shown here.) If ValidateArguments() finds the required arguments, it stores their values in the xmlDataFile, schemaFile, sqlUserName, and sqlPassword member variables. If ValidateArguments() fails to find the correct arguments, it throws an exception, which Main() catches, and displays an error message.

If valid command-line arguments were obtained, the event provider constructs an NSInstance and NSApplication object to connect to the SQL-NS instance and application. Notice the two methods of constructing the NSInstance object, depending on whether Windows or SQL Server Authentication is used.

After creating the instance and application objects, the code creates a new EventLoader object. (Recall that EventLoader is a class in the XML Bulkload API.) The EventLoader constructor takes the application object, provider name (as configured in the ADF), event class name, and schema file.

Main() then calls the LoadXml() method on the EventLoader object, passing it the name of the XML data file. This method loads the XML file and submits its data as events. This is all it takes to submit events from XML files using the XML Bulkload API.

Testing the XML Event Provider

Build the XML event provider solution in Visual Studio. This produces the executable that you can invoke to submit events. In the XMLEventProvider project directory, a subdirectory called SampleFiles contains an event data file and schema file you can use to test the event provider. These files are copies of the files we used earlier with the FileSystemWatcherProvider. The following instructions describe how to perform the test:

1.

From a command prompt, navigate to the C:\SQL-NS\Samples\MusicStore\SongAlerts\CustomComponents\XMLEventProvider\bin\Debug directory.

2.

Invoke the XML event provider as follows if you're using Windows Authentication: [View full width] XMLEventProvider.exe -in ..\..\SampleFiles\EventData.xml -schema..\..\SampleFiles \SongAddedEventSchema.xsd If you're using SQL Server Authentication, include the two optional arguments as follows: [View full width] XMLEventProvider.exe -in ..\..\SampleFiles\EventData.xml -schema..\..\SampleFiles \SongAddedEventSchema.xsd -sqlusername <SqlUserName> -sqlpassword <SqlPassword> Replace <SqlUserName> and <SqlPassword> with the username and password of your SQL development account.

You can then check your events table to verify that the correct event data has been added.

Submitting Events from an Application

As described earlier, event provider functionality can be embedded in an existing application. In the case of the music store, the AddSongs program (which we've been using to add new song data to the music store) can act as an event provider by calling the event submission APIs to submit events for the song records it adds. This functionality has already been built in to the AddSongs program, but we have not used it so far. In this section, we'll take a look at the portion of the AddSongs code that implements the event provider operations.

Note

Note that most of the AddSongs code implements the user interface. Feel free to browse this code if you are curious about how it works, but it is not explained here because it isn't relevant to the event provider discussion.

Open the AddSongs Visual Studio solution from C:\SQL-NS\Samples\MusicStore\SongAlerts\AddSongs. From the Solution Explorer, open the AddSongs.cs file. By default, this file may open in Design View, which shows the layout of the visual controls. You should open the file in Code View, so that you can see the source code in it. To do this, right-click AddSongs.cs in the Solution Explorer and select View Code.

Recall that the AddSongs user interface has a check box labeled Submit Events for Songs Added. Checking this box turns on the event provider functionality. Earlier, we left this box unchecked because we were testing other event providers and did not want AddSongs to submit events.

When the state of the check box is changed, the code shown in Listing 8.20 runs.

Listing 8.20. Event Handler for the Check Box in the AddSongs Program

 public class AddSongs : System.Windows.Forms.Form {     ...     private const string   INSTANCE_NAME = "MusicStore";     private const string   APPLICATION_NAME = "SongAlerts";     private NSInstance     musicStoreInstance;     private NSApplication  songAlertsApp;     ...     private void checkBoxSubmitEvents_CheckedChanged(         object sender,         System.EventArgs e)     {         // Establish the connection to the NS instance         // and application, if they have not already         // been established.         if (this.checkBoxSubmitEvents.Checked == true)         {             if (this.musicStoreInstance == null)             {                 try                 {                    if (AuthenticationMode.WindowsAuthentication ==                        authMode)                    {                        musicStoreInstance =                            new NSInstance(INSTANCE_NAME);                    }                    else                    {                        musicStoreInstance = new NSInstance(                            INSTANCE_NAME,                            sqlUserName,                            sqlPassword);                    }                    songAlertsApp = new NSApplication(                        musicStoreInstance,                        APPLICATION_NAME);                 }                 catch (NSException ex)                 {                     MessageBox.Show(                         ex.Message,                         "Unable to connect to Music Store instance",                         MessageBoxButtons.OK,                         MessageBoxIcon.Error);                     // Set the checkbox back to unchecked.                     this.checkBoxSubmitEvents.Checked = false;                 }             }         }     }     ... } 

The AddSongs class has private member variables, musicStoreInstance and songAlertsApp, that store instance and application objects. The first time the box is checked, the code in Listing 8.20 initializes these objects. Even if the box is unchecked after that, these objects stay initialized and will be used again if the box is checked again later.

The code that submits the events appears in the event handler for the Add to Database button. Listing 8.21 shows this code.

Listing 8.21. The Event Handler for the Add to Database Button

 public class AddSongs : System.Windows.Forms.Form {     ...     private const string   PROVIDER_NAME = "SongAddedEmbeddedProvider";     private const string   EVENT_CLASS_NAME = "SongAdded";     ...     private void buttonAdd_Click(object sender, System.EventArgs e)     {         string  albumTitle;         string  artistName;         string  genre;         int     albumId;         SqlTransaction transaction;         bool    submitEvents;         EventCollector  eventCollector = null;         // Extract values from the controls.         albumTitle = this.textBoxAlbumTitle.Text;         artistName = this.textBoxArtistName.Text;         genre = (string) this.comboBoxGenre.SelectedItem;         // Create a SQL transaction that will wrap adding the         // album record and the individual song records.         transaction = this.dbConnection.BeginTransaction();         // Determine if we need to submit events based on the         // state of the checkbox.         submitEvents = this.checkBoxSubmitEvents.Checked;         // If we're supposed to submit events for each song,         // create an event collector.         if (submitEvents)         {             eventCollector = new EventCollector(                 this.songAlertsApp,                 PROVIDER_NAME);         }         try         {             // Add the album record.             albumId = AddAlbum(             albumTitle,             artistName,             genre,             transaction);         // For each song specified, add a song record.         foreach (string songTitle in Songs)         {             int songId = AddSong(                 songTitle,                 albumId,                 transaction);             // If we're supposed to submit events, then create             // an event record and submit it.             if (submitEvents)             {                 Event evt =                     new Event(songAlertsApp, EVENT_CLASS_NAME);                 // Set the SongId event class field.                 evt["SongId"] = songId;                 // Write the event.                 eventCollector.Write(evt);             }         }         // Commit the transaction.         transaction.Commit();         transaction = null;         if (submitEvents)         {             // Commit the event batch.             eventCollector.Commit();             eventCollector = null;         }         // Clear the form now that the data has been added.         ClearForm();         }         catch (Exception ex)         {             MessageBox.Show(                 ex.Message,                 "Unable to add songs",                 MessageBoxButtons.OK,                 MessageBoxIcon.Error);             // Roll back the transaction.             if (transaction != null)             {                 try                 {                     transaction.Rollback();                 }                 catch (Exception)                 {                     // The rollback may throw an error if the                     // transaction was already rolled back by                     // a SQL error. In this case, just absorb                     // the exception.                 }             }             if (submitEvents && eventCollector != null)             {                 // Abort the event batch.                 eventCollector.Abort();             }         }     }     ... } 

This code reads the contents of the text boxes in the AddSongs user interface and adds the appropriate album and song records by calling the AddAlbum() and AddSong() helper methods. It checks the state of the check box and sets the value of a local Boolean variable, submitEvents, accordingly. If the submitEvents variable is true, the code constructs an EventCollector and uses the Event Object API to submit an event for each song added. The EventCollector is initialized with the provider name, SongAddedEmbeddedProvider (via the string constant, PROVIDER_NAME, defined near the top of the class). Events submitted by AddSongs will be associated with this provider name.

The key point here is that the same event submission API we used to built hosted event providers can be used in a standalone program as well. None of the SQL-NS event submission APIs are limited to the hosted provider infrastructure provided by the SQL-NS engine. Although this example showed the Event Object API, you can also use any of the event submission APIs in standalone programs.

To test the event submission capabilities of the AddSongs program, run it again and check the Submit Events for Songs Added box. Now add some song data and click the Add to Database button. This adds the song records and submits the corresponding events.

Verify that the events have been submitted by checking the events table in your SQL-NS application database. You will actually see the events submitted three times: AddSongs will submit them once, but because song records were actually added to the music store database, events will also be submitted by the SQLProvider and the scheduled custom hosted event provider. The events submitted by AddSongs should appear immediately, but the events from the other event providers may be delayed because of their scheduling intervals. As before, you can distinguish the events submitted by the various event providers by querying the NSEventBatchView, as described in the sidebar, "Querying the NSEventBatchView," (p. 280), earlier in this chapter.

Submitting Events with SQL Stored Procedures

All the event submission APIs used in the examples so far have submitted events from managed code. This section looks at the SQL-NS stored procedures that you can use to submit events from SQL code. As described in the "SQL Stored Procedures for Event Submission" section (p. 262), the SQL-NS compiler creates a dedicated set of stored procedures for each event class when it compiles the application. The code in this section uses the stored procedures created for the SongAdded event class in the music store application.

The stored procedures offer two modes of event submission. In the first mode, you start an event batch, add events to it one at a time, and then close the event batch. In the second mode, you invoke a stored procedure with an events query (much like the one used with the SQLProvider). The stored procedure runs the query and submits the results as a batch of events.

Listing 8.22 shows the use of the stored procedures that do one-at-a-time event submission.

Listing 8.22. Using SQL Stored Procedures to Submit Events One at a Time

 USE MusicStore GO DECLARE @eventBatchId BIGINT DECLARE @providerName NVARCHAR(255) SELECT @providerName = N'SongAddedSPProvider' EXEC [SongAlerts].[NSEventBeginBatchSongAdded] @providerName, @eventBatchId OUTPUT EXEC [SongAlerts].[NSEventWriteSongAdded] @eventBatchId, 1 EXEC [SongAlerts].[NSEventWriteSongAdded] @eventBatchId, 2 EXEC [SongAlerts].[NSEventFlushBatchSongAdded] @eventBatchId, 2 

The NSEventBeginBatchSongAdded stored procedure starts a batch of SongAdded events. It takes the provider name as input and returns the new event batch ID as output. Notice that the stored procedure names are qualified with the application's schema name, SongAlerts. Like all other application database objects, the SQL-NS compiler creates the event submission stored procedures in the application schema.

Each call to NSEventWriteSongAdded adds a new SongAdded event to the event batch. The first argument this stored procedure takes is the event batch ID. This code uses the value obtained as output from the NSEventBeginBatchSongAdded stored procedure call that created the batch. The remaining arguments to NSEventWriteSongAdded represent the fields of the event class. Because the SongAdded event class has only one field, SongId, this stored procedure takes only one additional argument beyond the event batch ID. The calls to NSEventWriteSongAdded in Listing 8.22 provide two hard-coded SongId values.

The call to NSEventFlushBatchSongAdded closes the event batch. It takes the event batch ID and a count of events as arguments.

You can find the code shown in Listing 8.22 in the C:\SQL-NS\Samples\MusicStore\SongAlerts\CustomComponents\StoredProcedureEventProviders\EventSubmission1.sql file. Open this file in Management Studio and execute it to test these stored procedures.

Listing 8.23 shows the stored procedure that executes an events query to submit a whole batch of events at once.

Listing 8.23. Using the SQL Stored Procedure to Submit a Batch of Events with an Events Query

 USE MusicStore GO DECLARE @eventBatchId BIGINT DECLARE @providerName NVARCHAR(255) SELECT @providerName = N'SongAddedSPProvider' EXEC [SongAlerts].[NSEventSubmitBatchSongAdded]         @providerName,         N'SELECT TOP 5 SongId FROM [Catalog].[SongDetails]',         NULL 

The NSEventSubmitBatchSongAdded takes an event provider name and two queries: an events query that returns event data and a post query that runs after the events query. In the particular example shown in Listing 8.23, there is no post query, so a NULL value is given for the third argument.

The events query shown is trivial: It just selects the top five songs in the SongDetails view. Of course, in a real application, the events query could be much more elaborate; I've used this simple example for clarity. Whatever the form of the events query, it must return a set of data with columns that match the schema of the event class. Because the SongAdded event class only has a SongId field, this query selects only the SongId column from the view.

The file C:\SQL-NS\Samples\MusicStore\SongAlerts\CustomComponents\StoredProcedureEventProviders\EventSubmission2.sql contains the code shown in Listing 8.23. You can test this code by opening the file in Management Studio and executing it.

Tip

Although the examples here show the stored procedures invoked from simple scripts, they can also be used very effectively in triggers on database tables. By calling the event submission stored procedures in trigger code, you can easily attach event submission to database activities. For example, you could create an insert trigger on the Songs table in the music store catalog in which you call the event submission stored procedures to submit events for the inserted song records. This would cause an event to be submitted automatically for every song record, without an external event provider having to poll the table.