Notification of Silverlight Applications about Database State Changes

Introduction

There is a big class of software applications that should provide the ability for users to be notified about other user's changes. The software for stock exchanges, medical practices, and clinics have to have the above mentioned feature. Silverlight applications are suitable for such areas because they have a rich interface and they are thin clients simultaneously.

To implement the notification feature for Silverlight applications, we should implement the following architecture:

Fig. 1. Architecture of the application.

If a client updates a particular data, then all other clients should be notified about the occurred changes. This means, between a middle and the client tiers should be established a two-way (duplex) connection.

The middle tier should save data after receiving it from the client tier. A data tier is a layer that can say that data was saved successfully, and the last one should notify the middle tier about the changes.

The database does not have direct access to the middle tier, so we should use one of the following solutions to notify it about the changes:

• There should be a special table in the database which will be used for storing information about database changes. The middle tier should read it periodically for changes and notify the client tier about those.
• CLR SQL triggers can be implemented to notify the middle tier via a Web Service.
• Microsoft Notification Service can be used to notify the middle tier about database changes.

I do not think the first variant is a good one. The middle tier is monitoring the database, sending requests all the time, and it's a waste of resources.

The third variant requires using additional software and I am leaving it for another article.

I'm going to describe a second variant and unlock the secrets of building duplex Silverlight applications, CLR triggers, and using WCF services from CLR triggers.

Background

To implement such an application, I am going to use the following technologies:

• Silverlight for the client tier
• WCF services (Polling Duplex Binding/WS HTTP Binding)
• CLR triggers for SQL server

Technical requirements

The following software were used:

• Windows XP SP3/IIS 5.1
• VS 2008 SP1
• .NET 3.5 SP1
• Microsoft Silverlight Projects 2008 Version 9.0.30730.126
• Silverlight 3.0.40818.0
• Silverlight Toolkit July 2009
• Silverlight 3 Developer Package
• Silverlight 3 SDK
• .NET RIA Services (July 2009 Preview)
• MS SQL Server 2005

Getting started

I am going to implement a solution that consists of several parts:

• A Web application. It hosts the Silverlight application and the Web Services (see below).
• Silverlight application. This application will receive notifications about the database changes.
• CLR trigger. It will contain some logic for sending data from the trigger via a WCF service.
• SQL Server. Hosts CLR triggers.

Web application - DuplexSample.Web

The Web application is a middle tier for the Silverlight application and is based on a standard template Silverlight Navigation Application (New Project/Silverlight/Silverlight Navigation Application).

I am going to implement two WCF services. The first one (DBNotificationService) is intended for doing callbacks to the Silverlight application. The second one (DBAuditService) is intended for receiving data from CLR triggers.

Why should I use such a complex schema and cannot use the client tier service (DBNotificationService) to notify the Silverlight applications directly? The answer is simple - Polling Duplex Binding mode is not implemented for non-Silverlight applications, and any other binding mode can't be used for implementing a duplex channel. So, I decided to implement one more service.

DBNotificationService

DBNotificationService is based on the Polling Duplex Binding protocol. This protocol allows to exchange data between the client tier (the Silverlight application) and the middle tier (the web application).

This service includes four files:

• DBNotificationService.svc. Contains the service metadata and is used for generating the source code (proxy classes/interfaces) for the client part of the WCF service.
• DBNotificationService.svc.cs. Contains the implementation of the WCF service.
• IDBNotificationCallbackContract.cs. Contains an interface for the callback contract. The method (voidSendNotificationToClients(DBTriggerAuditData data)) will be used to send data from the middle tier to the clients.
• IDBNotificationService.cs. Contains an interface for the service contract that describes the methods that the client can call to subscribe (voidSubscribeToNotifications()) or unsubscribe (void UnsubscribeToNotifications()) to notifications.

This article is not a guide for WCF services, but I am going to describe the details of the implementation of the mentioned service.

Implementation of the SubscribeToNotifications() method.

 Collapse

public void SubscribeToNotifications()
{
  IDBNotificationCallbackContract ch = 
    OperationContext.Current.GetCallbackChannel<IDBNotificationCallbackContract>();
  string sessionId = OperationContext.Current.Channel.SessionId;

  //Any message from a client we haven't seen before 
  //causes the new client to be added to our list
  //(Basically, treated as a "Connect" message)
  lock (syncRoot)
  {
    if (!SilverlightClientsList.IsClientConnected(sessionId))
    {
      SilverlightClientsList.AddCallbackChannel(sessionId, ch);
      OperationContext.Current.Channel.Closing += new EventHandler(Channel_Closing);
      OperationContext.Current.Channel.Faulted += new EventHandler(Channel_Faulted);
    }
  }
}

I call the method above on the client tier to subscribe to notifications from the middle tier. The implementation of this method gets an incoming callback channel and stores it in the list; also, this one initializes other events (OnFault, OnDisconnect; see below) of the incoming channel.

The stored channel will be used to send the notification to all clients.

Implementation of the UnsubscribeToNotifications() method.

 Collapse

public void UnsubscribeToNotifications()
{
    ClientDisconnect(OperationContext.Current.Channel.SessionId);
}

I call the method above on the client tier to unsubscribe to notifications from the middle tier. The implementation of this method just deletes the callback channel from the list by its identification number.

Initialization of the callback channel events; implementation of the ClientDisconnect method.

 Collapse

private void Channel_Closing(object sender, EventArgs e)
{
    IContextChannel channel = (IContextChannel)sender;
    ClientDisconnect(channel.SessionId);
}

private void Channel_Faulted(object sender, EventArgs e)
{
    IContextChannel channel = (IContextChannel)sender;
    ClientDisconnect(channel.SessionId);
}

private void ClientDisconnect(string sessionId)
{
    lock (syncRoot)
    {
      if (SilverlightClientsList.IsClientConnected(sessionId))
        SilverlightClientsList.DeleteClient(sessionId);
    }
}

These methods are executed when a client disconnects/is disconnected.

The class DBNotificationService is marked with the following attribute:

 Collapse

[ServiceBehavior(ConcurrencyMode = ConcurrencyMode.Multiple, 
 InstanceContextMode = InstanceContextMode.Single)]

ConcurrencyMode = ConcurrencyMode.Multiple means the service instance is multi-threaded and a developer should care about synchronization. So, I lock the list of Silverlight channels before reading/writing, to prevent inconsistency.

InstanceContextMode = InstanceContextMode.Single means only one InstanceContext object is used for all incoming calls.

DBAuditService

DBAuditService is based on the WSHttpBinding protocol (BasicHttpBinding can be used as well). I am going to use this protocol to set a channel between the data tier (SQL Server database) and the middle tier.

So, any changes in the database will be intercepted in the appropriate DB trigger, and the last one should send a notification about the changes to the middle tier using the stored channels (callbacks).

This service consists of four files:

• DBAuditService.svc. Contains the service metadata and is used for generating the source code (proxy classes/interfaces) for the client part of the WCF service.
• DBAuditService.svc.cs. Contains the implementation of the WCF service.
• DBTriggerAuditData.cs. Contains a data contract (data transfer object) that will be used for exchanging data between the data/middle tiers and the middle/client tiers. This class contains two string properties. The first one contains the name of the table for which the trigger fired, and the second one contains the audit data received from the trigger.
• IDBAuditService.cs. Contains an interface for the service contract that describes the method that can be called to send audit data.

The service implements only one method: SendTriggerAuditData. It enumerates all the client channels and executes theSendNotificationToClients method for each channel. The parameter for this method is data we received from the trigger.

Implementation of the SendTriggerAuditData(DBTriggerAuditData data) method.

 Collapse

public void SendTriggerAuditData(DBTriggerAuditData data)
{
    Guard.ArgumentNotNull(data, "data");

    if (SilverlightClientsList.GetCallbackChannels().Count() > 0)
    {
      lock (syncRoot)
      {
        IEnumerable<IDBNotificationCallbackContract> channels = 
            SilverlightClientsList.GetCallbackChannels();
        channels.ToList().ForEach(c => c.SendNotificationToClients(data));
      }
    }
}

Adding non-Silverlight clients

Of course, it is not only Silverlight clients that can get notifications. One more service should be implemented to notify other clients about database changes.

This service should use WSDualHttpBinding, and it should be implemented similar to the DBNotificationService service. In this case,DBAuditService should send notification using channels of non-Silverlight clients as well.

Silverlight application - DuplexSample

This Silverlight application is a client tier based on the standard template 'Silverlight Navigation Application' (New Project/Silverlight/Silverlight Navigation).

I just added two controls to HomePage.xaml:

 Collapse

<Button Content="Connect" Click="ButtonConnect_Click" 
   x:Name="ButtonConnect" Margin="10"></Button>
<ListBox Grid.Row="1" ScrollViewer.VerticalScrollBarVisibility="Visible" 
  x:Name="ListBox1"></ListBox>

The first one is a Button, and the user can use it to connect/disconnect from a server. The second one is a ListBox, and it displays a text of incoming messages.

In the constructor of the page, I just initialize the DBNotification service.

 Collapse

private DBNotificationClient client;
private ObservableCollection<string> liveDataMessages = 
                          new ObservableCollection<string>();

public Home()
{
    InitializeComponent();

    ListBox1.ItemsSource = liveDataMessages;

    client = new DBNotificationClient(new PollingDuplexHttpBinding(), 
       new EndpointAddress("http://localhost:2877/" + 
                           "DBNotificationService/DBNotificationService.svc"));
    client.SendNotificationToClientsReceived += (sender, e) =>
      {
        DBTriggerAuditData data = e.data;
        liveDataMessages.Add(data.TableName + ": " + data.Data);
      };
}

SendNotificationToClientsReceived is an anonymous delegate that is executed when a message from the middle tier is received.

The methods Subscribe/Unsubscribe just execute the corresponding method of the WCF service (SubscribeToNotificationsAsync/UnsubscribeToNotificationsAsync) and define the anonymous delegates that will be executed after finishing connecting/disconnecting.

 Collapse

private void Subscribe()
{
    ButtonSubscribe.Content = "Subscribing...";
    client.SubscribeToNotificationsCompleted += (sender, e) =>
    {
      ButtonSubscribe.Content = "Subscribed (click to unsubscribe)";
      subscribed = true;
    };
    client.SubscribeToNotificationsAsync();
}

private void Unsubscribe()
{
    ButtonSubscribe.Content = "Unsubscribing...";
    client.UnsubscribeToNotificationsCompleted += (sender, e) =>
    {
      ButtonConnect.Content = "Unsubscribed (click to subscribe)";
      subscribed = false;
    };
    client.UnsubscribeToNotificationsAsync();
}

CLR trigger - DuplexSample.SqlTriggers

This project is a class library, and it contains just one class AppUser and a static method AppUserAudit. This method creates a message according to the changed rows and fields and sends it via the DBAudit service. The code for building a log was taken from the SQL Server documentation, and you can find a lot of information about this feature there. I just added into this method to send audit data via the service.

 Collapse

EndpointAddress endpoint = 
  new EndpointAddress(new Uri("http://localhost:2877/" + 
                      "DBAuditService/DBAuditService.svc"));
DBAuditClient client = 
  new DBAuditClient(new WSHttpBinding(SecurityMode.None), endpoint);

DBTriggerAuditData data = new DBTriggerAuditData();
data.TableName = "[dbo].[AppUser]";
data.Data = sb.ToString();

try
{
    client.SendTriggerAuditDataCompleted += (sender, e) =>
    {
      if (e.Error != null)
        throw new ApplicationException("There was an error occured", e.Error);
    };
    client.SendTriggerAuditDataAsync(data);
}
catch (Exception ex)
{

    throw;
}

Please pay attention that the address of the service is hard-coded. I am going to explain below why I did it.

Adding a CLR trigger

To add a CLR trigger to a SQL Server, I have to do the following actions:

• Create an assembly in the database that corresponds to the assembly with the CLR trigger (DuplexSample.SqlTriggers);
• Create a trigger that is based on the CLR trigger.

The following SQL commands correspond to the actions above:

 Collapse

create ASSEMBLY [DuplexSample.SqlTriggers] FROM 
    'C:\Projects\Sandbox\DuplexSample\DuplexSample.SqlTriggers
     \bin\Debug\DuplexSample.SqlTriggers.dll' 
    WITH PERMISSION_SET = UNSAFE

where [DuplexSample.SqlTriggers] is the name of the assembly within the database, and PERMISSION_SET = UNSAFE is the level of permissions for that library (see below for details).

 Collapse

CREATE TRIGGER AppUserAudit
  ON AppUser
  FOR Insert,Update,Delete 
  AS
  EXTERNAL NAME [DuplexSample.SqlTriggers].AppUser.AppUserAudit

That's all! Your trigger is ready to be fired. You can go to the appropriate table and try to change the data - the trigger will be executed, and the data will be sent via the WCF service.

CLR trigger tricks

I have spent much time before the trigger started working well. I found out several issues, and now I am going to describe them to simplify the life of my fellows.

First of all, a CLR trigger is loaded within the SQL Server process, and neither App.config nor other parameters (Assembly.Location, for example) of the assembly are available. So, I can't even get the path to the assembly; therefore, all parameters of the WCF service should be hardcoded or indicated in a different way.

By default, SQL Server does not support calls to CLR methods, so it should be turned on manually:

 Collapse

EXEC sp_configure 'show advanced options' , '1';
go

reconfigure;
go

EXEC sp_configure 'clr enabled' , '1'
go

reconfigure;

EXEC sp_configure 'show advanced options' , '0';
go

SQL Server does not allow to add unsafe assemblies (if your trigger executes the WCF service, the assembly will be unsafe definitely). To allow adding unsafe assemblies, the following command should be executed:

 Collapse

ALTER DATABASE [Silverlight.DuplexNotification]
SET TRUSTWORTHY ON

By default, SQL Server does not allow to add unsafe assemblies even when TRUSTWORTHY is on, and I should use the special parameter (PERMISSION_SET = UNSAFE) to add the assembly to the database (see above).

Note: Setting the database to trustworthy can cause problems with security, so it would be better to use the certificate/asymmetric key scenario (see here for details).

The CLR trigger assembly has a lot of related assemblies, and they should be installed into SQL Server as well. SQL Server can install them automatically only if they are placed in the same folder as the CLR trigger assembly. I added all such assemblies to the CLR trigger project as references, and set the property Copy Local to true for each added assembly. See the project file DuplexSample.SqlTriggers.csproj for the list of added assemblies.

There is one assembly that should be added to the database manually, because SQL Server does not do it:

 Collapse

create ASSEMBLY [Microsoft.VisualStudio.Diagnostics.ServiceModelSink] FROM 
'C:\Projects\sandbox\DuplexSample\DuplexSample.SqlTriggers\bin\
  Debug\Microsoft.VisualStudio.Diagnostics.ServiceModelSink.dll' 
WITH PERMISSION_SET = UNSAFE

If you change your CLR trigger, just rebuilding is not enough. You have to rebuild it, delete the trigger and the assembly from the database, and add them again.

If you are going to play with CLR triggers, you need the following code to delete the trigger/assembly per saltum:

 Collapse

drop trigger AppUserAudit
GO
drop assembly [DuplexSample.SqlTriggers]
GO

The debugging of CLR triggers is a very simple process. You need to just set a breakpoint, attach to the sqlserver.exe process (main menu - Debug - Attach to process), and try to change the table. The most amazing thing is, the web application (the server part of WCF services) can be debugged at the same time when you are debugging the CLR trigger.

Fig. 2. Attaching to the SQL Server process to debug the CLR trigger.

Sometimes, notifications do not come up to the Silverlight application - just restart the built-in web server. Sometimes, the Silverlight application crashes Internet Explorer (I do not know the reason) - use Firefox instead.

Demo application

The source code of the application contains all the above techniques, and is ready to be compiled and deployed. There are some things the developer should change/update before running:

• Change the port number/create the IIS folder for the web project (if you want to use another port or IIS instead of the built-in web server).
• Create a database from the given scripts.

To start a demo, I should compile all the libraries, add the CLR trigger to the database, run one or several Silverlight applications and subscribe to notifications, and do any change for the table for which the CLR trigger was created.

The following figures display how the application works:

Fig. 3. Two Silverlight applications are started, connected to the service and ready to subscribe to notifications.

Fig. 4. Two Silverlight applications are subscribed to notifications and receive information about the insert operation.

Fig. 5. Two Silverlight applications receive information about the update operation.

Fig. 6. One Silverlight application is disconnected, a second one is connected and receives information about the delete operation.

The following SQL scripts were used for database updating:

 Collapse

insert into AppUser Values ('Test User 1', '123456', 'test1@test.test')
insert into AppUser Values ('Test User 2', '654321', '')
-------
update AppUser set email = 'test2@test.test' where Name = 'Test User 2'
-------
delete from AppUser

Console application

The application DuplexSample.ConsoleApp was added to the solutions just to have the ability to emulate the CLR trigger. This application connects toDBAuditService and sends test data. These data come to the web server and the last one sends them to each subscribed client.