Silverlight 4 with (HTTP) Duplex and SQL Notifications

• Download source code - 2.82 MB

Introduction

If you have ever tried to create a push mechanism, originating from the database to the services and then to the browser, it can be complicated enough, but having a Silverlight embedded browser application makes it a little more difficult.

If you do an online search for that type of application, you will find numerous articles on Silverlight and Duplex or Silverlight and SQL Notifications, but Silverlight with Duplex and SQL Notations - there is so little content to be found.

Background

Initially, this arose out of the frustration of not being able to find an article online on just this scenario. Some came very close, but just not the complete solution. Silverlight will be around for a while (or until HTML5 is fully accepted and matured), and such a push mechanism will only aid other developers to use such technologies.

Using the code

Project structure layout

From Fig. 1, you can see the structure of the application. There is the Silverlight project "SLDuplexDependency" (that will be housed in the test page within the project "SLDuplexDependency.Web"). This Silverlight project has a service reference to the service called "BrowserMaintenanceService". This service is used for keeping track of the browsers that are connected to the server. The second service in the Silverlight project called "SqlServiceReference" will maintain the callback (notification) from SQL Server and process any new results that will be sent back to the clients. When an update, insert, or delete is performed on the database, this service will be notified (a small XML notification is sent to the callback method), which in turn will use the static class\methods to notify the browsers (in a JSON format as Silverlight does not have an XML parser). The callback method in the browsers (Silverlight C#) will process the JSON and rebind the grid.

One of the services in the main project "SLDuplexDependency.Web" called "DBNotificationService" is used to handle the events when a browser connects to the server and displays interest in the SQL notifications from the server. The other service "BrowserDuplex" is used initially by the Silverlight application to make the initial connection and binding of the notification to the server - after this initial binding has been used (a SqlDependency has to be recreated after each notification), the main service "SqlEventsService" will then look after all notification and rebinding of the SqlDependency object.

There is a static class called SilverlightClientsList, this will maintain a record of the browser session IDs, and its static methods can be called from the service to push data back to the clients.

Silverlight

This method makes the initial call to the "DBNotificationService" service to register this browser's interest in SQL Server table notifications.

/// Subscribes this instance of the browser,
void Subscribe()
{            
    client.SubscribeToNotificationsCompleted += (sender, e) => { };
    client.SubscribeToNotificationsAsync();
}

This is the method that will register the browsers and store their session IDs in a static list. Each session will have its own closure events associated with it.

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            
    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);
        }
    }
}

Duplex

This is the method that is used as the callback for when there is a database change:

private void dependency_OnDataChangedDelegate(object sender, SqlNotificationEventArgs e)
{
    if (e.Type != SqlNotificationType.Change) return;

    ObservableCollection<Employee> myAuthors = 
      this.GetEmployeeList(DateTime.Now.AddYears(-20), DateTime.Now.AddYears(1));

    string c = SerializeObject(myAuthors);
    this.SendTriggerAuditData(c);

    //the notification is now used up - so now we remove
    //the event handler from the dependency object
    //(another one will have been bound to it again)
    SqlDependency dependency = sender as SqlDependency;
    dependency.OnChange -= 
      new OnChangeEventHandler(dependency_OnDataChangedDelegate);
}

This is the method that will initiate the callback to the browsers with the new data:

public void SendTriggerAuditData(string data)        
{
    // loop through channels (browsers) and make a call to their callback method
    if (SilverlightClientsList.GetCallbackChannels().Count() > 0)
    {
        lock (syncRoot)
        {
            IEnumerable<IDBNotificationCallbackContract> channels = 
                   SilverlightClientsList.GetCallbackChannels();
            channels.ToList().ForEach(c => c.SendNotificationToClients(data));
        }
    }
}

SQL Notifications \ SqlDependency

The following method is the general purpose method used to query the database and create the SQL dependency:

public ObservableCollection<WcfSqlNotifications.DuplexServiceReference.Employee> 
       GetEmployeeList(DateTime startDate, DateTime endDate)
{
    employees = 
      new ObservableCollection<WcfSqlNotifications.DuplexServiceReference.Employee>();

    //the connection string to your database
    string connString = @"Data Source=YouDatabaseServer;Initial" + 
      @" Catalog=dependencyDB;Persist Security Info=True;" + 
      @"User ID=NotificationsUser;Password=password";

    string proc = "SelectCredentialsWithinDOBRange";
    //the name of our stored procedure

    if (!CheckUserPermissions()) return null;
    //first we need to check that the current user
    //has the proper permissions, otherwise display the error

    this.employees = new ObservableCollection<
       WcfSqlNotifications.DuplexServiceReference.Employee>();
    bool success = SqlDependency.Start(connString);
    // create the dependency reference on the database

    using (SqlConnection sqlConn = new SqlConnection(connString))
    {
        using (SqlCommand sqlCmd = new SqlCommand())
        {

            SqlParameter myParm1 = sqlCmd.Parameters.Add(
                         "@StartDate", SqlDbType.DateTime, 20);
            myParm1.Value = startDate;
            SqlParameter myParm2 = sqlCmd.Parameters.Add(
                           "@EndDate", SqlDbType.DateTime, 20);
            myParm2.Value = endDate; 

            sqlCmd.Connection = sqlConn;
            sqlCmd.Connection.Open();

            //tell our command object what to execute
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = proc;
            sqlCmd.Notification = null;

            SqlDependency dependency = new SqlDependency(sqlCmd);
            dependency.OnChange += 
              new OnChangeEventHandler(dependency_OnDataChangedDelegate);

            if (sqlConn.State != ConnectionState.Open) sqlConn.Open();

            using (SqlDataReader reader = sqlCmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    WcfSqlNotifications.DuplexServiceReference.Employee author = 
                       new WcfSqlNotifications.DuplexServiceReference.Employee();
                    author.ID = reader.GetInt32(0);
                    author.FirstName = reader.GetString(1);
                    author.SecondName = reader.GetString(2);
                    author.Address = reader.GetString(3);
                    author.DOB = reader.GetDateTime(4).ToString();
                    this.employees.Add(author);
                }
            }
        }
        return this.employees;
    }
}

A method is needed to make sure that the user is able to initiate a query on the server:

private bool CheckUserPermissions()
{
    try
    {
        SqlClientPermission permissions = 
                 new SqlClientPermission(PermissionState.Unrestricted);
        //if we cannot Demand() it will throw an exception
        //if the current user doesnt have the proper permissions
        permissions.Demand();
        return true;
    }
    catch { return false; }
}

SQL Server

Attach the following database files to your SQL Server (2008) database.

• Download database - 1.61 MB

Run the following script against your newly attached database:

USE dependencyDB;
GO
CREATE QUEUE dependencyDBQueue;
CREATE SERVICE dependencyDBService ON QUEUE dependencyDBQueue (
  [http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]);
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO GeneralUser;
ALTER DATABASE dependencyDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE dependencyDB SET ENABLE_BROKER
ALTER DATABASE dependencyDB SET MULTI_USER
GO

Note: If the above code hangs, stop the SQL service and then try again.

1. Gotchas to remember when creating your Stored Procedures (with an enabled broker database) - http://msdn.microsoft.com/en-us/library/ms181122(SQL.100).aspx
2. How to enable your database as a service broker - http://www.dreamincode.net/forums/topic/156991-using-sqldependency-to-monitor-sql-database-changes/
3. What is a "Service Broker" - http://msdn.microsoft.com/en-us/library/bb522889(SQL.100).aspx

Testing the application

1. Make sure the (dependencyDB) database has been enabled as a broker.
2. Make sure you have created the user (in the connection string) within the database (with enough permissions) - should already be created if just attaching the database to the server.
3. Run the Silverlight application within VS2010 (with the modified connection string) - a grid should appear with details.
4. Go to the database and open the credentials table for editing.
5. Edit one of the table rows.
6. Result: The edited row's details should be reflected in the browser's grid.
7. Further testing: Open multiple browsers and do the same - changes will be pushed to all browsers.

Pros and Cons

Upside

1. It is a cross database platform - there is an OracleDependency object (http://download.oracle.com/docs/ cd/B19306_01/win.102/b14307/OracleDependencyClass.htm).
2. High scalability - using a background thread to call WCF and using a Win2008 server with IIS7 - connections can run into 1000's.
3. The code can be tailored to be more efficient in that only the updated records are passed back to the clients, not all the records and rebound.
4. Can greatly aid an application's caching mechanism - WCF can be notified when an update has occurred and refresh its cache.

Downside

1. Mono WCF is in .NET 2 - SqlDependency is in .NET 3.5+, so can not port this solution to Linux environment.
2. There are limitations in the SQL that can be used within a Stored Procedure (http://msdn.microsoft.com/en-us/library/ms181122(SQL.100).aspx).