DotNetCurry Logo

Broadcast Real-Time Notifications using SignalR, KnockoutJS and SqlTableDependency

Posted by: Christian Del Bianco , on 10/3/2016, in Category ASP.NET
Views: 8274
Abstract: Create a Flight Availability web application that uses ASP.NET SignalR to provide server broadcast functionality

ASP.NET SignalR is an open source library that adds real-time notifications to our application by establishing a two-way communication channel between server and client, in order to send asynchronous messages in both directions.

HTML5 specifications about WebSocket have been introduced specifically to meet this need. Although natively supported by ASP.NET 4.5, as well as in the latest versions of popular browsers, it does require the presence of the IIS 8 web server. This poses a challenge especially when we are building a site intended for diverse audiences, since we cannot be absolutely sure that all our visitors will use browsers that are compliant with this technology.

This article is published from the DNC Magazine for Developers and Architects. Download this magazine from here [PDF] or Subscribe to this magazine for FREE and download all previous and current editions

SignalR connection strategy

However, there are other techniques to establish a full-duplex communication channel and luckily ASP.NET SignalR can help us with that. During the initial stages of the communication and based on the characteristics of the server and the client, SignalR automatically makes a decision about the most suitable connection strategy to use, choosing between:

· WebSocket: HTML5-related protocol that provides full duplex communication channels over a single TCP connection available in modern web browsers and web servers

· Server-Sent Events: Strategy based on the EventSource HTML5 support that allows a server to stream messages to the connected clients

· Forever Frame: Strategy based on the use of a hidden iframe element, where the chunked encoding HTTP feature is used to send stream of bytes

· Long Polling: Basic technique that involves the opening of an HTTP connection and keeping it artificially alive to create the illusion of a persistent connection.

By default, SignalR will try to use WebSocket whenever possible and if it's not available, it will gracefully fall back on the remaining ones until it picks the best option for the current environment. So with SignalR, we can establish real-time communication from server to clients: our application can push contents to connected clients instantly, rather than having the server wait for a client requesting new data.

But what about pushing content from SQL Server to ASP.NET web app?

Let’s assume we are developing a Web application to book flight tickets. Let us assume the tickets availability is stored in a database used by different booking terminals. These terminals have no idea about availability, and the only way for us to be aware of any change is to continuously poll the database for changes, and refresh the display. This approach has some disadvantages, such as a constant performance hit on the server even when there is no change in data.

For such applications, we need the server to take the initiative and be capable of sending information to the client when a relevant event occurs, instead of waiting for the client to request it.

But what about the database? How can we keep our server code aware of database changes? An easy solution is writing code that periodically re-executes a query to maintain and update the server cache, and then use SignalR to send back a message to all connected clients whenever something has changed. In software terminology, this kind of continuous checking of other programs or devices to see what state they are in, is called polling.


Figure 1: Server application polling database for changes and notifying client

Of course, this is not the best solution. We need something that automatically notifies our server about any record change, and thereby reduces round trips to the database. Some possible options are:

  • SQL Server Service Broker.
  • SqlNotificationRequest.
  • SqlDependency.

Let’s take a look at them.

SQL Service Broker and Queue

This is a feature of SQL Server where external or internal processes can send and receive asynchronous messages reliably by using extensions of T-SQL Data Manipulation Language (DML).

SQL Server Service Broker is used to create conversations for exchanging messages. Messages are exchanged between two parties, the destination and the initiator, and allows to transmit data and trigger processing when receiving a message.

This solution, of course, requires a good knowledge of SQL Sever needed for implementing the entire database infrastructure - that is a SQL Server Queue and a Service Broker. A server side object to listen for notifications is also needed.


With SqlNotificationRequest, we are required to create our own Service Broker service and queue in SQL Server, as well as our own listener to process the sent notifications accordingly. We may choose to use this lower-level class for more granular control over the notification architecture. Following steps are involved:

  • Set up the Service Broker service and queue.
  • Create an instance of the SqlNotificationRequest class and attach it to the SqlCommand.Notification property.
  • Write a listener that retrieves and reacts to the received query notifications message.

This solution has the advantage to deal with a .NET class that receives notification about record changes, but still requires us to create a SQL Server Queue as well as a Service Broker.


If we want to use query notifications without paying attention to the underlying Service Broker infrastructure, the SqlDependency .NET class in System.Data is our choice. The SqlDependency class represents a query notification dependency between an application and an instance of SQL Server. When we use query notifications, SQL Server provides the queue and the service object, as they are created automatically.

While SqlNotificationRequest is a lower level API, SqlDependency is a high-level implementation to access query notifications. It allows us to detect changes on the database table. In most cases, this is the simplest and most effective way to leverage the SQL Server notifications capability by managed client applications (using the .NET Framework data provider for SQL Server). In short, SqlDependency provides capabilities to our application to monitor our database table for data changes without the hassles of continuously querying the database using timers or loops.

However, the received notification doesn’t tell us which record is changed, so it is necessary to execute another database query to fetch the data. This is an example showing the event handler triggered by SqlDependency due to a record change:

private void SqlDependencyOnChange(object sender, SqlNotificationEventArgs eventArgs)
    if (eventArgs.Info != SqlNotificationInfo.Invalid)
        Console.WriteLine("Notification Info: " + eventArgs.Info);
        Console.WriteLine("Notification source: " + eventArgs.Source);
        Console.WriteLine("Notification type: " + eventArgs.Type);

As we can see, there is no information about the record that has been modified. With SqlDependency, infact the application issues a command that contains a query, and a request for notification. The application caches the results of the query or dynamic content generated from the query results. When the application receives the query notification, the application clears the cached content. The application then re-issues the query and the notification request, when the application needs the updated query results.

Wouldn't it be better if this database notification returned us updated, inserted or deleted records, avoiding us to execute a new SELECT to refresh our server cache?

SqlTableDependency for instant notifications from Database to Server

SqlTableDependency is an open source component that can create a series of database objects used to receive notifications on table record change. When any insert/update/delete operation is detected on a table, a change notification containing the record’s status is sent to SqlTableDependency, thereby eliminating the need of an additional SELECT to update application’s data.


Figure 2: Notification from Database to SqlTableDependency after table record changes.

The SqlTableDependency class represents a notification dependency between an application and a SQL Server table. To get notifications, this component provides an on the fly low-level implementation of an infrastructure composed of a table trigger, contracts, messages, queue, service broker and a clean-up stored procedure. SqlTableDependency class provides access to notifications without knowing anything about the underlying database infrastructure. When a record change happens, this infrastructure notifies SqlTableDependency, which in turn raises a .NET event to subscribers providing the updated record values. You can read more about SqlTableDependency at

Generic implementation

This event that gets invoked implements the generic <T> pattern; a subscriber receives a C# object based on our needs. Let’s take a very simple example. Let’s take a database table as shown in Figure 3, and define a model that maps only to table columns we are interested in:


Figure 3: Database table and relative C# model

After this step we can establish our dependency, by simply creating a new instance of SqlTableDependency. Here we specify the C# model used to map the column’s table; and if needed, the table name as the second optional parameter.

var conStr =

var mapper = new ModelToTableMapper<SeatsAvailability>();
mapper.AddMapping(c => c.Seats, "SeatsAvailability");

using (var tableDependency = new SqlTableDependency<SeatsAvailability>(
   tableDependency.OnChanged += TableDependency_Changed;

   Console.WriteLine(@"Waiting for receiving notifications...");
   Console.WriteLine(@"Press a key to stop");


The ModelToTableMapper in this case is necessary because our C# model has a property whose name differs from the column’s name. The same holds true for the second parameter passed to SqlTableDependency constructor: C# model name differs from table name. If we had adopted identical names, this configuration wouldn't be necessary. Finally we define a handler that subscribes to record changes:

private static void TableDependency_Changed(object sender, RecordChangedEventArgs<SeatsAvailability> e)
   var changedEntity = e.Entity;

   Console.WriteLine(@"DML operation: " + e.ChangeType);
   Console.WriteLine(@"From: " + changedEntity.From);
   Console.WriteLine(@"To: " + changedEntity.To);
   Console.WriteLine(@"Seats free: " + changedEntity.Seats);

For every inserted, deleted or updated record, SqlTableDependency instantiates an object with properties whose values are set based on the modifications carried on by a SQL operation. To achieve this, SqlTableDependency creates the following database objects:


Fig.4: Database infrastructure generated from SqlTableDependency.

The most important objects to note are:

  • Queue: used to store messages related to record changes
  • Service broker: used to exchange info with the queue
  • Table trigger: used to catch record changes on the monitored table, and prepare a message to put in the queue using the Service Broker.
  • Conversation timer: This behaves as a “watch dog”. When the SqlTableDependency time-out expires, Service Broker puts a message to DialogTimer on the queue. Every message inserted in the queue triggers the activated stored procedure that detects the DialogTimer message, and executes the teardown of all SQL object generated previously in order to remove unused database objects.
  • Activated stored procedure: used to clean up all SqlTableDependency objects in case the application closed down without disposing SqlTableDependency.

To use SqlTableDependency, we must have SQL Server Service Broker enabled on our database.

A practical example using Knockout JS

We are going to create a web application simulating a Flight Booking system. This web application serves different client terminals that book tickets for a flight. These terminals need to stay up to date constantly to avoid overbooking. The idea is to refresh all connected terminals every time a reservation is done, by submitting a message from the server containing real time availability.

Using the described components, we can obtain a chain of notifications that starting from our database, goes to the server, and in turn goes to all terminal clients. All this without any sort of polling.

Initial settings

Create an ASP.NET MVC Web application and install the following packages:

PM> Install-Package Microsoft.AspNet.SignalR 
PM> Install-Package Knockoutjs
PM> Install-Package Knockout.Mapping
PM> Install-Package SqlTableDependency  

Then initialize SignalR:

using Microsoft.Owin;
using Owin;

[assembly: OwinStartup(typeof(FlightBooking.Startup))]
namespace FlightBooking
    public class Startup
        public void Configuration(IAppBuilder app)

The following are the most important points to note during this process:

  • SignalR uses OWIN (Open Web Interface) for .NET as the standard interface between .NET web servers and web applications, enabling a level of indirection and abstraction that keeps our project from directly tying up to any specific hosting platform. This enables SignalR to be hosted from web applications.
  • Every OWIN application must have a Startup class that follows specific conventions: a Configuration() method with the signature shown in the preceding code must be defined.
  • The assembly-level attribute OwinStartup is used to declare that our Startup class will be used to bootstrap every OWIN-based asset contained in all the loaded assemblies.
  • Inside the Configuration() method, we make a call to the MapSignalR() method in order to expose an endpoint called /signalr, which the clients will use to connect to the server.

Now we need a client. So, we customize the layout in order to include the required JavaScript libraries:

<!DOCTYPE html>
    <title>SignalR, Knockout JS and SqlTableDependency</title>
http://~/Scripts/jquery-1.10.2.js http://~/Scripts/jquery.signalR-2.2.0.js http://~/signalr/hubs http://~/Scripts/knockout-3.4.0.js http://~/Scripts/knockout.mapping-latest.js @RenderSection("scripts", required: false) </body> </html>

The first relevant portions of code are the first two blocks, where we reference jquery and jquery.signalR as JavaScript libraries. jQuery is necessary because the SignalR JavaScript client is actually a jQuery plugin.

Then we refer to a dynamic endpoint (/signalr/hubs) exposed by the server because of the MapSignalR call from the Startup class. It actually generates JavaScript code on the fly according to the available Hub. In practice, the JavaScript proxies for our Hub is built on the fly by the server-side portion of SignalR as soon as this endpoint is hit, and it is sent to the client as JavaScript source code.

Hub implementation

SignalR's main goal is to deliver a real-time message over HTTP. In order to do so, SignalR comes with two distinct APIs: one called Persistent connection, which we can consider as the low-level API; and one called Hub, which built on top of the former, represents a high-level API that allows client and server to call methods on each other directly. Hubs also allow us to pass strongly typed parameters to methods, enabling model binding.

To recap our target, what we want to achieve is a notification chain that will forward this information to all connected clients, whenever any change is done to the FlightBookings table. So assuming an update DML operation on this table is executed, we want to broadcast messages from database, up to the clients.

So we start defining our Hub class. We are going to use it to retrieve the first set of available flight seats, and then push seats availability change from the server. This class establishes a communication channel between the server and clients:

public class FlightBookingHub : Hub
   private readonly FlightBookingService _flightBookingService;

   public FlightBookingHub() : this(FlightBookingService.Instance) { }

   public FlightBookingHub(FlightBookingService flightBookingHub)
      _flightBookingService = flightBookingHub;

   // used to get the first result set concerning seats availability
   public FlightsAvailability GetAll()
      return _flightBookingService.GetAll();

Note: The class is marked with the HubName attribute, which allows us to give the Hub a friendly name to be used by the clients. If we don't use the HubName attribute, the Hub name will be the same as the class name.

Hubs are instantiated through calls. Each time a client sends a message to the server, an object of this type will be created to process the request: it means that we cannot use instance members on the hub to maintain the state. After the request is processed, the object will be eliminated, so this information would be lost. That is why we need a singleton service class to constitute the channel between the database and web application; to be able to be the listener for record modifications. For this we are going to use SqlTableDependency.

Let's create our server side "record change" listener. In its constructor, we instantiate our SqlTableDependency instance that will work as "record table change" listener, defining the connection string, and the table name to monitor:

public class FlightBookingService : IDisposable
   // singleton instance
   private readonly static Lazy _instance = 
      new Lazy(() => 
         new FlightBookingService(

   private SqlTableDependency SqlTableDependency { get; }
   private IHubConnectionContext Clients { get; }

   private static connectionString = 

   public static FlightBookingService Instance => _instance.Value;

   private FlightBookingService(IHubConnectionContext clients)
      this.Clients = clients;

      // because our C# model has a property not matching database table name, 
      // an explicit mapping is required just for this property
      var mapper = new ModelToTableMapper();
      mapper.AddMapping(x => x.Availability, "SeatsAvailability");

      // because our C# model name differs from table name we must specify table name
      this.SqlTableDependency = new SqlTableDependency(
      this.SqlTableDependency.OnChanged += this.TableDependency_OnChanged;

Unlike the Hub implementation, this service class is not disposed off when the communication with the client is ended. It has to monitor record changes, and then route this info to clients using an instance of our hub implementation. This is achieved by registering an event handler on SqlTableDependency:

private void TableDependency_OnChanged(object sender, 
  RecordChangedEventArgs e)
  switch (e.ChangeType)
     case ChangeType.Delete:                    

     case ChangeType.Insert:

     case ChangeType.Update:

This event gives us an object populated with current table values. In case of update or insert operation, we receive the latest value; in case of delete, we receive the deleted record values. Within our event handler, we can notify a list of connected clients (IHubConnectionContext Clients property) about the change that happened on the database table. The JavaScript proxy will get this new availability, and using KnockoutJS, will update the UI.

Our hub also exposes a method used to retrieve the seats availability. This method will be called from our client code only once - to populate the view containing the free seats:

public FlightsAvailability GetAll()
  var flightsAvailability = new List();

  using (var sqlConnection = new SqlConnection(connectionString))
     using (var sqlCommand = sqlConnection.CreateCommand())
        sqlCommand.CommandText = "SELECT * FROM [FlightBookings]";

        using (var sqlDataReader = sqlCommand.ExecuteReader())
           while (sqlDataReader.Read())
              var flightId = sqlDataReader.GetInt32(0);
              var from = sqlDataReader.GetString(1);
              var to = sqlDataReader.GetString(2);
              var seats = sqlDataReader.GetInt32(2);

              flightsAvailability.Add(new FlightAvailability { 
                 FlightId = flightId, 
                 From = from, 
                 To = to, 
                 Availability = seats 

  return new FlightsAvailability() { 
     FlightCompanyId = "field not used", 
     FlightAvailability = flightsAvailability 

To complete our hub, we need to implement the IDisposable interface in order to destroy all database objects generated from SqlTableDependency.

public void Dispose()
  // invoke Stop() to remove all DB objects generated from SqlTableDependency
Seats Availability View

Now it is time to create a simple controller just to render our view:

public class FlightBookingController : Controller
    public ActionResult Index()
        return View();

This view takes advantage of KnockoutJS to display free seats, as well as update them every time the availability changes on the database table. Knockout is a Model-View-ViewModel (MVVM) JavaScript library that helps us to create rich, responsive user interfaces with a clean underlying data model. Any time we have sections of UI that update dynamically (e.g., changes depending on the user’s actions or when an external data source changes), knockout will refresh our UI. This is achieved using the data-bind attribute added to DOM elements, that automatically update their values.

Flight Id From To Seats Availability
@section Scripts { http://~/Scripts/flightBookingTicker.js }
Knockout ViewModel and SignalR client code

What remains to do is to define our knockout ViewModel, used to bind HTML elements with our server data. In this example, we take advantage of the Knockout Mapping plug-in. This is an alternative to manually writing your own JavaScript code that constructs a ViewModel based on data we are fetching from the server. The mapping plug-in will convert JavaScript objects or JSON strings into Knockout observable properties that will be automatically added to our ViewModel. In the end, this step results in having four properties (flightId, from, to, freeSeats) created in our ViewModel.

// flight ViewModel definition
function FlightBookingViewModel(flight) {
    var self = this;

    var mappingOptions = {
        key: function (data) {
            return ko.utils.unwrapObservable(data.flightId);

    ko.mapping.fromJS(flight, mappingOptions, self);

// flights view model definition
function FlightsBookingViewModel(flights) {
    var self = this;

    var flightsBookingMappingOptions = {
        flights: {
            create: function (options) {
                return new FlightBookingViewModel(;

    self.addFlightAvailability = function (flight) { FlightBookingViewModel(flight));

    self.updateFlightAvailability = function (flight) {
        var flightMappingOptions = {
            update: function (options) {
                ko.utils.arrayForEach(, function (item) {
                    if (item.flightId() === {

        ko.mapping.fromJS(flight, flightMappingOptions,;

    self.removeFlightAvailability = function (flight) { {
             return item.flightId() === flight.flightId;

    ko.mapping.fromJS(flights, flightsBookingMappingOptions, self);

The main ViewModel - FlightsBookingViewModel - has a constructor parameter used to initialize itself with initial seats availability. Also, it exposes three methods used to update itself, and consequently the UI:

  • removeFlightAvailability
  • updateFlightAvailability
  • addFlightAvailability

These methods will be called from the client-side hub proxy every time it is notified from our server side code using SignalR:

$(function () {
    var viewModel = null;

    // generate client-side hub proxy and then 
    // add client-side hub methods that the server will call
    var ticker = $.connection.flightBookingTicker;

    // Add a client-side hub method that the server will call
    ticker.client.updateFlightAvailability = function (flight) {

    ticker.client.addFlightAvailability = function (flight) {

    ticker.client.removeFlightAvailability = function (flight) {

    // start the connection, load seats availability and set the knockout ViewModel
    $.connection.hub.start().done(function() {
        ticker.server.getAll().done(function (flightsBooking) {
            viewModel = new FlightsBookingViewModel(flightsBooking);

Our code is written inside a classic jQuery $(...); call, which actually ensures that it is called when the page is fully loaded. We first take a reference to our Hub, which is exposed by the $.connection.flightBookingTicker property generated by the dynamic endpoint.

Then we add three callback methods on flightBookingTicker hub proxy: updateFlightAvailability, addFlightAvailability and removeFlightAvailability, whose name and signature are matching the method that the server Hub is trying to call back. These functions will update the knockout ViewModel with the received server message every time a record is changed.

After that we call the start() method exposed by the $.connection.hub member, which performs the actual connection to our server. The start() call is asynchronous, and we have to make sure it has actually been completed before using any hub. That's easy because start() returns a promise object containing a done() method to which we can pass a callback function, where we put our hub-related code. Here we get the initial list of seats availability using server member (getAll) defined in Hub instance. Using this, we are able to call any method exposed by the Hub, creating the knockout ViewModel and execution the applyBindings call. When this function is executed, Knockout processes both the view and the ViewModel. All data bindings in the view are executed and dynamically replaced with the data contained in the ViewModel, that in our case, is FlightBookingViewModel.

Wrapping Up

So far, instead of executing a request from client to the web application, and then the web application to the database; we are doing the reverse: sending a request from database to web application, and in turn from web application to clients. Figure 5 shows an example of notification workflow when a table record changes:


Figure 5: Notification chain when a record changes in the monitored database table

How to test

You can download the source code at; then create a database table with the following SQL script:

CREATE TABLE [dbo].[FlightBookings](
[FlightId] [int] PRIMARY KEY NOT NULL,
[From] [nvarchar](50),
[To] [nvarchar](50),
[SeatsAvailability] [int])

After setting the connection string, run the web application. Initially, if the database table has no data, an empty grid will be shown, otherwise its records will be displayed. Open SQL Server Manager and add, modify or delete a record in the table: as a record is committed we will get an immediate notification from the database to web server and, thanks to SignalR, from the web application to HTML page. Here, with the help of Knockout JS, the single row in the grid will be modified, based on what we have received from the database.

Was this article worth reading? Share it with fellow developers too. Thanks!
Share on Google+
Further Reading - Articles You May Like!

Page copy protected against web site content infringement 	by Copyscape

Feedback - Leave us some adulation, criticism and everything in between!