Silverlight – Writing a unified code to access online and offline Database

By: Oren Beeri I got a request to build an internet application that will also be available offline; The user “checks out” part of the database to the client, works offline and checks it in when going back online. To implement this request I decided to use Silverlight built-in out-of-browser application, RIA services for accessing the remote database and the MSDN sample that enables serializing RIA DomainContext objects to an isolated storage, so I can save database tables locally(part of the “Authentication quick-start” example). The challenge with the above is that every query against the database requires checking if the application is online or offline and writing a duplicate Data access code for each scenario. The solution I used was to write a helper code that will enable writing one data access code regardless of the application status (online/offline). For example you can write in your view model the following code:

private AdventureWorksLTDomainContext domainContext =
                              new AdventureWorksLTDomainContext();
this.Customers = await this.domainContext.OCLoadAsync(

Things to notice:

  1. The method OCLoadAsync will check the application connectivity status. If it is online it will execute the RIA service against the remote server and if it is offline it will load the information from the isolated storage.
  2. I am using the Async framework (still in CTP) to load the query. For more detail on using Async framework with RIA service please refer to this article.
  3. The query can be customized on the fly, for example the following line is also valid:
    this.Customers = await this.domainContext.OCLoadAsync(
      from customer in this.domainContext.GetCustomersQuery()
      where customer.FirstName.StartsWith(this.Prefix)
      orderby customer.FirstName, customer.LastName
      select customer );
  4. Given the way RIA is implemented, only the final complete query is executed against the Database.
  5. The implementation of the offline support will not include any code rules in the domain service methods. The assumption is that your domain service methods will basically return all entities for a specific type. You can add code rules to the domain service, for example security trimming;  The offline version will not include these code rules, however the local storage will only contain the records that the user had access to last time they were online..

Before you will be able to access offline storage you will need to create one. This is also an easy task with the OCSaveAsOfflineContext method. Just execute the following code when the application is online:

await this.domainContext.LoadAsync(

Things to notice:

  1. The method LoadAsync returns the result of the query but also populates the DomainContext with the result, so by saving the domaincontext to the Isolate Storage we can access the information later when the application is offline.
  2. You will need to load all the queries you like to save with the same domainContext and then use the OCSaveAsOfflineDomainContext method to save local version of your database in the Isolated Storage.

To use this solution follow these steps:

  1. Create normal Silverlight project with Entity framework model and RIA services (if you’re not familiar with RIA you can check this link).
  2. Install the Async Framework and add reference to the AsyncCtpLibrary_Silverlight DLL.
  3. Add reference to the SilverlightOccasionallyConnected Dll  download available at the bottom of this article.
  4. Add using-statement to ‘SilverlightOccasionallyConnected” to have access to the extension methods for the DomainContext class.
  5. Start writing your class.

In the previous part I showed how to easily write a unified code that automatically checks the application connectivity status and executes a query against the database, or an offline storage, whether the application is online/offline respectively. In this part I will describe in more detail the implementation of the DomainContext extension methods that enables this. The DomainContextExtension class includes the method OCLoadAsync that receives as a parameter an RIA services query that can be customized on the fly and returns a Task of type IEnumerable of the requested entity type. The method contains 3 main steps:

  1. If the application is online, the method just loads the data from the RIA service.
  2. Else, will create an IQueryable of the requested entity from the offline DomainContext.
  3. Transfers the query from the current DomainContext to the offline DomainContext.

I will explain in details each step:

  1. The class SilverlightOccasionallyConnected.OfflineHelper has a static Boolean property IsApplicationOnline. The property is initiated with the built-in NetworkInterface.GetIsNetworkAvailable() value. The idea is to set the status at the application loading and to enable control of this property to the application. If the application is online, the method use the Async framework to load the query from the server as I described in detail in this article.
  2. If the application is offline we need to execute the query on the offlineDomainContext that is stored on the application Isolated Storage. I used the example from the MSDN- Code Gallery to load the DomainContext from the Isolated Storage. Next we need to create IQueryable for the requested Entity. If the entity framework’s model used in the domain service does not have any inherit relation we can just write:


    If however we have inherit relation we need to do an extra step and find the base type of the Entity (the parent entity that is inherit directly from the System.ServiceModel.DomainServices.Client.Entity object) and then use the following syntax:


    Where T is the requestEntityType. We also need to convert the IEnumerable to IQueryable so the next step of changing the query source will be available.

  3. This step is the trickiest. We need to transfer the query from the current DomainContext to the offline DomainContext. The queries on the DomainContext are of type EntityQuery<T>. This type has a Query property of type IQueryable that contains the specific requested query (for example “where-clauses” that narrow the result). The IQueryable contains an Expression property that exposes the expression tree that builds the requested query. By debugging I realized that the expression tree contains nesting of MethodCallExpression where the first argument can be the next MethodCallExpression or the ConstantExpression that represents the original IQueryable object (the object we’d like to replace) and the second argument is a UnaryExpression that represents the lambda expression. In light of the above tree structure, I wrote recursive method that goes through the expression tree and creates new expressions with a different source for the query (the offline DomainContext IQueryable that we created in step 2).
private static Expression ChangeQueryableExpressionSource(
                                             Expression expression,
                                             IQueryable newSource)
    MethodCallExpression methodCallExpression =
    Expression expressionLeftArgument =
    Expression expressionRightArgument =

    if (expressionLeftArgument is ConstantExpression)
        return Expression.Call(
       return Expression.Call(
                                      expressionLeftArgument, newSource),

In the last two parts of this session I have shown how to develop Silverlight application that shows information from a database in occasion connected environment. In this last part I will show you how to manage updates (including inserting and deleting records) and synchronizing them back into the database.

The first step is to make sure we are working with the relevant DomainContextobject and to apply all the updates to the offline/online DomainContext, depending on the application’s status. To accomplish this I have created a factory method that checks the application’s status and returns the relevant DomainContext. This method should be used whenever we need a DomainContext object in Silverlight projects.

public static NorthwindDomainContext GenerateNewNorthwindDomainContext()
    NorthwindDomainContext northwindDomainContext;
    if (OfflineHelper.IsApplicationOnline)
        northwindDomainContext = new NorthwindDomainContext();
        northwindDomainContext =

    return northwindDomainContext;

The second step is submitting the changes. To accomplish that, we will need to check the application status; if it is online we should use the out of the boxSubmitChanges method and if it is offline, we just call theOCSaveAsOfflineDomainContext that we implemented in the previous parts. Since the Entity object is tracing all the changes, we do not need to do anything except serialize the updated DomainContext to the offline storage. To simplify the operation I created this extension method:

public static Task OCSubmitChangesAsync(this DomainContext source)
    if (OfflineHelper.IsApplicationOnline)
        return source.SubmitChangesAsync();
        return TaskEx.Run(() =>

The third and last step is synchronizing with the database when the application becomes online.  All we need is to call the SubmitChanges on the offlineDomainContext. Although the MSDN sample that enables serializing RIADomainContext objects fit the job, it still required fixing some bugs to make it work properly (I attached the final code at the button of the post). Again, I warp it up with an extension method:

public static Task OCSyncWithOnlineServiceAsync(this DomainContext source)
    Task result = null;
    if (OfflineHelper.IsApplicationOnline)
        DomainContext offlineDomainContext =
            result = offlineDomainContext.SubmitChangesAsync();
        catch (Exception ex)

    return result;

An example of using the above methods for doing offline updating, would look something like this:

NorthwindDomainContext domainContext =

await this.domainContext.LoadAsync(this.domainContext.GetCustomersQuery());

OfflineHelper.IsApplicationOnline = false;

domainContext = DomainContextFactory.GenerateNewNorthwindDomainContext();
// Do some update on domainContext objects...

OfflineHelper.IsApplicationOnline = true;
await this.domainContext.OCSyncWithOnlineServiceAsync();

In addition, I have also attached a simple application that demonstrates the use of this framework. Notice that you can take the application offline, make changes, close and open the application and the changes will still be there. The application is also able to run out of the browser so you will be able to run this application even after the web server and the SQL server are unavailable.

Summary: To convert your application to support offline scenarios you will need to follow these steps:

  1. Download and install the Microsoft Visual Studio Asynchronous Framework.
  2. Add reference to the SilverlightOccasionallyConnected DLL.(attached to this post)
  3.  Create a factory method as demonstrated, to create DomainContextand use only this method throughout the Silverlight projects.
  4. Use the OCLoadAsync and OCSubmitChangesAsync to query and submit your changes respectively.
  5. Make sure that the filter and order-by logic are located in the Silverlight projects and not on the Server-side project.
  6. Add to your application this functionalities :
    1. An offline/online indication
    2. An option to generate offline copy of the database using the OCSaveAsOfflineDomainContext function. Load all the data that your application will need access to in offline mode before calling this method.
    3. An option to synchronize using theOCSyncWithOnlineServiceAsync function.

To the above method I just added some basic functionality that loads and saves DomainContext to a fixed-name file on the Isolated Storage. In the next part I will add submit update functionality that will enable making updates in occasionally connected application. Feel free to use this code in your applications and let me know what you think about it. Source Code for this article

Referenced by:

The Definite Guide to Notification Window in Silverlight 4.0

By: pluginbaby

Note: This article was first published in Web Central Station, but code format has problem there, so I republish here.
When this article was written the latest version of Silverlight was Silverlight 4.

Notification Window is a Silverlight Out-of-Browser feature (among WebBrowser control, Elevated Trust and offline DRM) that allows your application to display a notification as a little pop-out window that disappear automatically. This is sometimes called a “toast”, and is similar to the Outlook live notification when new emails get in your inbox.


The Notification Window is only available in Out-of-Browser (OOB) mode. You can take advantage of this feature to display confirmation of user’s actions (“Product saved”), application status (“Application updated, please restart”) or warn the user that data were refreshed while he is working on another application.

Silverlight Notification Window
Notification Window (”Now Playing”) used in a Silverlight Media Player app.

How to use the NotificationWindow?

  1. Check if the app is running Out-of-Browser (with App.Current.IsRunningOutOfBrowser)
  2. Instantiate a NotificationWindow object
  3. Set its Height and Width
  4. Set the Content property to something of type FrameworkElement (UserControl, TextBlock, …)
  5. Use the Show() method

As mentioned on MSDN: “You must set Height and Width on a notification window before Show is called and the notification displays. Attempting to set the height/width of a displayed notification throws an exception.”

Notification layout

The typical usage for the NotificationWindow visual is to create a UserControl to serve as the NotificationWindow.Content, so you can design the UI of the notification in Blend. But it could be something as simple as a TextBlock, or you could use a custom control which comes with its whole XAML graph (Canvas, Grid, Button, …) and code.

Show me the code!

Code to create a simple Notification:

Assuming you have a NotificationWindow nw; defined in your class, you could add this code in the click event of a Button:

// If a notification is already opened, close it
if (nw != null)

// Create a new nofitication window
nw = new NotificationWindow();
nw.Width = 400;
nw.Height = 100;

// Create a new TextBlock
TextBlock text = new TextBlock();
text.Text = "Simple Notification";
text.FontSize = 26;
text.HorizontalAlignment = HorizontalAlignment.Center;
text.VerticalAlignment = VerticalAlignment.Center;

// Set the content of the notification with the TextBlock
nw.Content = text;

// Show the notification for 3 seconds


Code to create a custom Notification:

if (nw != null)

// Create a new nofitication window
nw = new NotificationWindow();
nw.Height = 100;
nw.Width = 400;

// Create a custom control for the Notification UI
CustomNotification customContent = new CustomNotification();
customContent.Header = "Custom Notification Header";
customContent.Text = "This is a custom Notification text.";

// Set the content of the notification with the control
nw.Content = customContent;

// Show the notification for 4 seconds

The code for the CustomNotification custom control is available with the source code linked below.

Things you need to know

Maximum size for a Notification Window is: 400x100px.

Currently (SL4) a Notification Window does not support transparency (for security reasons), no rounded corners as well.
You can only style inside the content of the Window with your own control. The notification default to a blank background.

Multiple notifications: 
You can’t have more than 1 notification at the same time (or you’ll get an InvalidOperationException), and the API does not provide a built-in queue mechanism. But nothing prevent you from building your own system to queue several notifications, and it’s fairly easy, see Tim Heuer post.

No fade in/fade out like in Outlook right now. No transition/animation possible for the Notification Window itself.

Silverlight always display the notification in the common location of your OS, on a Windows system it will appear in the lower right corner of the screen, on a Mac it is upper right.

You can interact with a Notification Window but only with the mouse, no keyboard events. Also you can’t run actions like navigating from a HyperlinkButton, using SaveFileDialog or OpenFileDialog, accessing the Clipboard or switch to full-screen mode.

The maximum duration for a notification is 30 seconds so that is NotificationWindow.Show(30000).

Download the code!5407&parid=E564ED4426A5ECC!5370&authkey=!

Referenced by:

Notification of Silverlight Applications about Database State Changes

By: Max Paulousky


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.


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 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.
 Collapse | Copy Code
public void SubscribeToNotifications()
  IDBNotificationCallbackContract ch = 
  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 (OnFaultOnDisconnect; 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 | Copy Code
public void UnsubscribeToNotifications()

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 | Copy Code
private void Channel_Closing(object sender, EventArgs e)
    IContextChannel channel = (IContextChannel)sender;

private void Channel_Faulted(object sender, EventArgs e)
    IContextChannel channel = (IContextChannel)sender;

private void ClientDisconnect(string sessionId)
    lock (syncRoot)
      if (SilverlightClientsList.IsClientConnected(sessionId))

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

The class DBNotificationService is marked with the following attribute:

 Collapse | Copy Code
[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 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.
 Collapse | Copy Code
public void SendTriggerAuditData(DBTriggerAuditData data)
    Guard.ArgumentNotNull(data, "data");

    if (SilverlightClientsList.GetCallbackChannels().Count() > 0)
      lock (syncRoot)
        IEnumerable<IDBNotificationCallbackContract> channels = 
        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 theDBNotificationService 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 | Copy Code
<Button Content="Connect" Click="ButtonConnect_Click" 
   x:Name="ButtonConnect" Margin="10"></Button>
<ListBox Grid.Row="1" ScrollViewer.VerticalScrollBarVisibility="Visible" 

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

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

 Collapse | Copy Code
private DBNotificationClient client;
private ObservableCollection<string> liveDataMessages = 
                          new ObservableCollection<string>();

public Home()

    ListBox1.ItemsSource = liveDataMessages;

    client = new DBNotificationClient(new PollingDuplexHttpBinding(), 
       new EndpointAddress("http://localhost:2877/" + 
    client.SendNotificationToClientsReceived += (sender, e) =>
        DBTriggerAuditData 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 | Copy Code
private void Subscribe()
    ButtonSubscribe.Content = "Subscribing...";
    client.SubscribeToNotificationsCompleted += (sender, e) =>
      ButtonSubscribe.Content = "Subscribed (click to unsubscribe)";
      subscribed = true;

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

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 | Copy Code
EndpointAddress endpoint = 
  new EndpointAddress(new Uri("http://localhost:2877/" + 
DBAuditClient client = 
  new DBAuditClient(new WSHttpBinding(SecurityMode.None), endpoint);

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

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


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 | Copy Code
create ASSEMBLY [DuplexSample.SqlTriggers] FROM 

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 | Copy Code
  ON AppUser
  FOR Insert,Update,Delete 
  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 | Copy Code
EXEC sp_configure 'show advanced options' , '1';


EXEC sp_configure 'clr enabled' , '1'


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

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 | Copy Code
ALTER DATABASE [Silverlight.DuplexNotification]

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 | Copy Code
create ASSEMBLY [Microsoft.VisualStudio.Diagnostics.ServiceModelSink] FROM 

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 | Copy Code
drop trigger AppUserAudit
drop assembly [DuplexSample.SqlTriggers]

The debugging of CLR triggers is a very simple process. You need to just set a breakpoint, attach to thesqlserver.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:

 Collapse | Copy Code
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.

Referenced by: