Introduction

In one of our projects there was a need to make changes in data on the backend that were immediately visible to all connected users. In the past we used to implement some sort of polling system in which the connected user would repeatedly pol for data changes on the backend.

As this techique was a good one at the time, it generated much more traffic than needed.

SignalR

Now there is a much more suitable framework to do this job, namely SignalR.
SignalR is a library that :

  • makes developing real-time functionality very easy.
  • allows bi-directional communication between server and client.
  • allows the server to push content to connected clients instantly.
  • uses websockets whenever possible, but falls back on other compatible techniques for older browsers.

As we now know of a good technique to push data to clients,  another question rises: How can we discover when data is changed? This is where SQL Dependency comes in.

Sql Dependency

Sql Dependency can be used to discover when data has changed. When things have been put in place you can get notified of every change in data that is covered by a specific sql command. This is done by associating a sql dependency object with a sql command in order to detect when query results differ from those originally retrieved. Then you can assign a delegate to the onchange event, which will fire when the results change for an associated command.

Here is a code example:

Considerations

There are still some things to consider as using sql dependency has a great deal of limitations. Query notifications are supported for SELECT statements that meet the following requirements:

  1. The projected columns in the SELECT statement must be explicitly stated and table names must be qualified with two-part names. Notice that this means that all tables referenced in the statement must be in the same database.
  2. The statement may not use the asterisk (*) or table_name.* syntax to specify columns.
  3. The statement may not use unnamed columns or duplicate column names.
  4. The projected columns in the SELECT statement may not contain aggregate expressions unless the statement uses a GROUP BY expression. When a GROUP BY expression is provided, the select list may contain the aggregate functions COUNT_BIG() or SUM(). However, SUM() may not be specified for a nullable column. The statement may not specify HAVING, UBE, or ROLLUP
  5. The statement must not reference a view.
  6. The statement must not contain any of the following: DISTINCT, COMPUTE or COMPUTE BY, or INTO.
  7. The statement must not reference tables or views from other databases or servers.
  8. The statement must not contain subqueries, outer joins, or self-joins.
  9. The statement must not use any of the following aggregate functions: AVG, COUNT(*), MAX, MIN, STDEV, STDEVP, VAR, or VARP.
  10. The statement must not contain conditional statements that cannot change and cannot return results (for example, WHERE 1=0).

In my opinion, these are the main ones. But do read the complete list here. Overall, this is still a handy dandy piece of work. The combination of these two techniques  sounds like a nice marriage to make solid push driven data communication possible.

Demo Application

For the demo application we are building a retro looking flight information board. When flight info changes in the database, these changes should be pushed to all currently connected clients. Let’s first begin with a little diagram to map out the architecture:

As you can see on the image of the architecture, we have 3 main projects. Beginning at the far end (1), there is an admin to update the flight information and save this in the database. Next the console application, which is hooked up to the sql dependency, will be notified of these changes (2). Then the console application will send a singalR message to the webserver (3), telling him there are data changes.

When the webserver receives this signalR message, it will then notify all clients of these changes by sending the clients a message including the changed data. (4). To implement this, the first step is to hook up the sql dependency by starting this service on a certain connection.

 var connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DepartureDB"].ConnectionString; SqlDependency.Stop(connectionString); SqlDependency.Start(connectionString); 

Then you can start listening for changes:

using (var cn = new SqlConnection(ConfigurationManager.ConnectionStrings["DepartureDB"].ConnectionString))
{
     using (var cmd = cn.CreateCommand())
     {
          cmd.CommandType = CommandType.Text;
          cmd.CommandText = "SELECT [Time],[Destination],[FlightNo],[Gate],[Remarks],[Id] FROM [dbo].[Departures]";
          cmd.Notification = null;

          //creates a new dependency for the SqlCommand
          SqlDependency dep = new SqlDependency(cmd);
          //creates an event handler for the notification of data
          //changes in the database.
          dep.OnChange += Dep_OnChange;
          cn.Open();

          cmd.ExecuteReader();
     }
}

Console.WriteLine("Listening...");

When a change is detected, the delegate will fire and a message to the webserver can be send:

private static void Dep_OnChange(object sender, SqlNotificationEventArgs e)
{
    Console.WriteLine("Change caught! Triggering update to website...");
    var model = new FlightInfoModel();
    var updatedDepartures = model.Departures.Where(x => !string.IsNullOrEmpty(x.Action));
    Update(updatedDepartures.ToList());

    StartListening();
}

private static void Update(List departures)
{
     var connection = new HubConnection("http://localhost:62127");
     var hub = connection.CreateHubProxy("FlightInfoHub");
     connection.Start().Wait();
     hub.Invoke("UpdateFlightInfoBoard", departures);
}

In the web project we need to define a Hub class which derives from the Hub class of the signalR library.

In this class, create a method ‘UpdateFlightInfoBoard’ which can be called by the console application:

public class FlightInfoHub : Hub
{
     public void UpdateFlightInfoBoard(List updatedDepartures)
     {
          Clients.All.UpdateFlightBoardInfo(updatedDepartures.Select(x => new DepartureViewModel(x)).ToList());
     }
}

In this method, you can use a property Clients, which in turn exposes a property All to send the new updated flight info to.

On the client side, in javascript, we should first set up our connection with the websever using the generated javascript files:


$(document).ready(function () {
     var monitorHub = $.connection.flightInfoHub;
     monitorHub.client.updateFlightBoardInfo = function (departures) {
     $.each(departures, function (i, departure) {
               Animate(departure, true)
          });
     };

     $.connection.hub.logging = true;
     $.connection.hub.start();

});

As you can see, we first get a reference to the newly created hub. For this hub, the library generated the necessary javascript files. Then you can hook up a function to respond to the updateFlightBoardInfo method called by the sever, which can update the UI accordingly.

That’s it !
You now created your first bidirectional communcation using SignalR.

Source Code

You can download the working source code from our Narato Bitbucket