Click here to Skip to main content
15,867,686 members
Articles / Web Development / IIS

Notification of Silverlight Applications about Database State Changes

Rate me:
Please Sign up or sign in to vote.
5.00/5 (26 votes)
27 Nov 2009CPOL12 min read 119K   2.3K   122   29
This article presents the techniques and caveats of building Silverlight applications that can be notified about database state changes via polling duplex. WSHttp binding and CLR triggers are used to implement the solution as well.

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.

The architecture of the solution that allows to notify Silverlight applications about DB chabges.

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 (void SendNotificationToClients(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 (void SubscribeToNotifications()) 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.
C#
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.
C#
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.
C#
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:

C#
[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 the SendNotificationToClients method for each channel. The parameter for this method is data we received from the trigger.

Implementation of the SendTriggerAuditData(DBTriggerAuditData data) method.
C#
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:

XML
<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.

C#
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.

C#
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.

C#
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:

SQL
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).

SQL
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:

SQL
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:

SQL
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:

SQL
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:

SQL
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.

This dialog allows to attach to the process of SQL server and start debugging CLR triggers.

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.

Clients are ready to subscribe to notifications

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

Clients receive information about insert operation

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

Clients receive information about update operation

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

One client is disconnected, Another one receives information about delete operation

The following SQL scripts were used for database updating:

SQL
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 to DBAuditService and sends test data. These data come to the web server and the last one sends them to each subscribed client.

Wrapping up

This article is oriented for developers and architects who work on business applications. It describes:

  • How to implement a WCF service based on the Polling Duplex Binding mode;
  • How to implement a WCF service based on the WS HTTP Binding mode;
  • How to implement the CLR trigger;
  • How to add the CLR trigger to the database;
  • Problems that can appear during developing and installing CLR triggers and ways out.

History

  • Version 1.0 (2009-11-27) - Initial release.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Team Leader www.maxpaulousky.com
Belarus Belarus
Max currently is a senior developer at software company.

He lives with his wife Tatiana and son Zakhar (4 yrs) in Minsk, Belarus, but they dream to live in New Zealand.

Comments and Discussions

 
Question64-Bits Create Assembly List Pin
Member 944336710-Oct-12 1:27
Member 944336710-Oct-12 1:27 
GeneralMy vote of 5 Pin
Christian Amado27-Jul-12 12:04
professionalChristian Amado27-Jul-12 12:04 
QuestionWhy 2 services for Silverlight and Non-silverlight apps Pin
Member 85446396-Jan-12 10:02
Member 85446396-Jan-12 10:02 
QuestionWhere to set the receiveTimeout? Pin
archerleonard18-Oct-10 21:46
archerleonard18-Oct-10 21:46 
GeneralNotification of Silverlight Applications about Database State Changes Pin
Fregate1-Sep-10 16:03
Fregate1-Sep-10 16:03 
Generalweldone Pin
Mubi | www.mrmubi.com30-Jul-10 12:59
professionalMubi | www.mrmubi.com30-Jul-10 12:59 
QuestionHow to make it work on 64-bit? Pin
rpin2230-Mar-10 15:24
rpin2230-Mar-10 15:24 
i tested your app on 32-bit VS2008, it worked perfectly. But then i formatted my PC to 64-bit Windows 7, problems occured. All CREATE ASSEMBLY commands were issued correctly (i changed some to use C:\Windows\Microsoft.NET\Framework64 folder instead of C:\Windows\Microsoft.NET\Framework), but when i insert a row to database, error occured:

Msg 6522, Level 16, State 1, Procedure GSMAudit, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "GSMAudit":
System.IO.FileLoadException: Could not load file or assembly 'System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC. (Exception from HRESULT: 0x80131050) See Microsoft Knowledge Base article 949080 for more information.
System.IO.FileLoadException:
at GSMAuditClass.GSMAudit()


Is there any solution to this?
AnswerRe: How to make it work on 64-bit? Pin
Max Paulousky30-Mar-10 20:30
Max Paulousky30-Mar-10 20:30 
GeneralWorks only on localhost and from within VS hosted IIS Pin
zhi-lee.com7-Mar-10 8:55
zhi-lee.com7-Mar-10 8:55 
GeneralRe: Works only on localhost and from within VS hosted IIS Pin
Max Paulousky8-Mar-10 20:41
Max Paulousky8-Mar-10 20:41 
GeneralRe: Works only on localhost and from within VS hosted IIS Pin
zhi-lee.com10-Mar-10 15:40
zhi-lee.com10-Mar-10 15:40 
GeneralHidden problem - no more notification after 1st run Pin
rpin2224-Feb-10 21:13
rpin2224-Feb-10 21:13 
GeneralRe: Hidden problem - no more notification after 1st run Pin
Max Paulousky8-Mar-10 20:42
Max Paulousky8-Mar-10 20:42 
GeneralSometimes it doesnt work Pin
khandujaniket4-Jan-10 18:15
khandujaniket4-Jan-10 18:15 
GeneralRe: Sometimes it doesnt work Pin
Max Paulousky4-Jan-10 20:40
Max Paulousky4-Jan-10 20:40 
GeneralRe: Sometimes it doesnt work Pin
khandujaniket4-Jan-10 21:38
khandujaniket4-Jan-10 21:38 
GeneralRe: Sometimes it doesnt work Pin
Max Paulousky4-Jan-10 21:48
Max Paulousky4-Jan-10 21:48 
GeneralRe: Sometimes it doesnt work Pin
khandujaniket4-Jan-10 21:48
khandujaniket4-Jan-10 21:48 
GeneralRe: Sometimes it doesnt work Pin
Max Paulousky4-Jan-10 21:58
Max Paulousky4-Jan-10 21:58 
GeneralThanks! Very good job but.... [modified] Pin
Carmelo Campione30-Dec-09 4:18
Carmelo Campione30-Dec-09 4:18 
GeneralWell done Pin
Daniel Vaughan27-Nov-09 23:32
Daniel Vaughan27-Nov-09 23:32 
GeneralRe: Well done Pin
Max Paulousky28-Nov-09 13:28
Max Paulousky28-Nov-09 13:28 
GeneralNice but just one criticism Pin
Sacha Barber27-Nov-09 2:59
Sacha Barber27-Nov-09 2:59 
GeneralRe: Nice but just one criticism Pin
Max Paulousky27-Nov-09 6:55
Max Paulousky27-Nov-09 6:55 
GeneralRe: Nice but just one criticism Pin
Sacha Barber27-Nov-09 21:06
Sacha Barber27-Nov-09 21:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.