Using Advantage Events with Visual Studio

Thursday, August 14, 2008

Advantage Events(Notifications) are a powerful mechanism for allowing clients to react to changes which occur to the data. When an event is signaled, on a table update for example, all clients who are waiting for the event will be notified. This tech-tip demonstrates the use of events from a Visual Studio application which uses a separate thread to wait for event signals.

A Word about Threading with Visual Studio

The simplest way to create threads with Visual Studio is to use a background worker object, which is specifically designed to create a separate thread which can notify the main (UI) thread to take specific actions. Individual threads can also be created using the System.Threading library or using the System.ThreadPool. 

It is important to note that creating threads is very easy, managing the interaction between the threads is where things get complicated. You can read more about threading with Visual studio in the following blog posts.

• Part I – using the Thread class
• Part II – using the ThreadPool class
• Part III – using the BackgroundWorker class

The example application is designed as a simple patient tracking system. Part of the application would keep track of the status of each room in the office. For instance if the patient is waiting on a nurse or doctor. This would allow the people at the front desk to know when rooms are available. To keep the room status up to date an event is used so all the users know when the room status changes. 

The example database consists of three tables; Patient, RoomStatus and Room. For this example we will be concentrating on the RoomStatus and Room tables. 

The RoomStatus table provides the descriptions and color code information for the various categories the rooms can be in. The Room table reflects the current state of the room including the status and which patient is in the room. 

The interface is built as an MDI application with a panel which displays room status. This is a listview control which shows each room with a color code to the side. The listview is updated whenever a notification is received from the server. The application is pictured below.

To configure a notification with Advantage three distinct steps must be taken. First the event must be created by the client using the sp_CreateEvent system procedure. Second a dedicated connection must wait for the event using either sp_WaitForEvent or sp_WaitForAnyEvent. Finally the event must be signaled on the server using the sp_SignalEvent. 

The following code opens a new connection to Advantage, creates an event and waits for the event. This code is run for a BackgroundWorker DoWork event which creates a separate thread and runs in the background. When the event is signaled the BackgroundWorker notifies the main thread by reporting progress. The main thread can then update the room status. The background worker then waits for the event again.

   1: static void bwRoomStatus_DoWork(object sender, DoWorkEventArgs e)

   2: {

   3:     AdsConnection cn;

   4:     AdsCommand cmd;

   5:     AdsDataReader dr;

   6:  

   7:     cn = new AdsConnection(sConnection);

   8:     cmd = cn.CreateCommand();

   9:  

  10:     try

  11:     {

  12:         // Connect using the same path as the application

  13:         cn.Open();

  14:  

  15:         // Create the RoomStatus event for this connection

  16:         cmd.CommandText = "EXECUTE PROCEDURE sp_CreateEvent('RoomStatus', 0)";

  17:         cmd.ExecuteNonQuery();

  18:     }

  19:     catch (AdsException aex)

  20:     {

  21:         if (aex.Number == 5051)

  22:         {

  23:             // Event already exists so continue

  24:         }

  25:         else

  26:         {

  27:             e.Result = aex;

  28:             return;

  29:         }

  30:     }

  31:

  32:     while (!e.Cancel)

  33:     {

  34:         // Wait for the RoomStatus event for up to 5 seconds

  35:         cmd.CommandText = "EXECUTE PROCEDURE sp_WaitForEvent('RoomStatus', 5000, 0, 0)";

  36:  

  37:         dr = cmd.ExecuteReader();

  38:  

  39:         // check to see if the room status was updated

  40:         dr.Read();

  41:         if (dr.GetInt32(1) > 0)

  42:         {

  43:             bwRoomStatus.ReportProgress(100);

  44:         }

  45:         dr.Close();

  46:  

  47:         // Check to see if the worker has been canceled

  48:         if (bwRoomStatus.CancellationPending)

  49:             e.Cancel = true;

  50:     }

  51:

  52:     // Close the connection

  53:     cn.Close();

  54: }

To complete the implementation a trigger is created on the Rooms table which signals the event. In this case we are only concerned with updates since the number of records in this table should not change. The Update Trigger just needs to signal the event which was created by the client. This is accomplished with a call to sp_SignalEvent.

The example application along with all the source code can be downloaded from Code Central on the DevZone. The project is filed under Applications for C#/VB.Net and is called Event Demo. You may also use this direct link.

Advantage Events are a powerful mechanism for alerting clients of changes made in the database. They are manipulated through the use of system procedures and require a dedicated connection. For best results clients should wait for events using a background thread which alerts the main thread when notifications are received. 

Visual Studio includes a powerful class called the BackgroundWorker which provides a mechanism for creating background threads. BackgroundWorkers also include methods for alerting the main thread which makes them a useful tool for Advantage Events. For more details about how Notifications work refer to this tech-tip.