SQL Server Notifications on Record Change with SignalR and SQLTableDependency

• Download source - 132.1 KB
• Download source code from Github

Introduction

SqlTableDependency is a class used to receive notification when the result set of a specified query changes due to any insert, update or delete operation performed on a database table.

However, this class does not send back the values for the record changed.

So, supposing we want to show stocks values in a web page, for every notification received, we have to execute a new full query to refresh our cache and in turn refresh the browser.

But if we want that, as soon as one stock value changes, the browser immediately shows new values, without the need for refresh? Ideally, what we want is to receive notifications directly from the Web server, without any polling system from the browser and also without pulling on the database table.

The solution is to use SignalR in combination with SqlTableDependency: SqlTableDependency gets notification from the table and in turn SignalR sends a message to the web page.

Enhancement

SqlTableDependency is a generic C# component used to send events when the content of a specified table changes. This event reports the operation type (INSERT/UPDATE/DELETE) as well as the deleted, inserted or modified value. Its implementation for this component is:

• SqlTableDependency for SQL Server
• OracleTableDependency for Oracle

How It Works

When instantiated, this component generates on the fly all database objects used to monitor a table content. In case of SqlTableDependency, we have:

• Message Types
• Message Contract
• Queue
• Service Broker
• Table Trigger
• Stored Procedure

All those objects are dropper once SqlTableDependency is disposed.

Watch Dog

SqlTableDependency has a watchDogTimeOut that takes care of removing those objects in case the application disconnects abruptly. This time out is set to 3 minutes but it can be increased in case of deployment stage.

With all these objects in place, SqlTableDependency gets notification of table content changes and transforms this notification in C# event containing the record values.

The Code

Let's assume a SQL Server database table containing stocks value modified constantly:

CREATE TABLE [dbo].[Stocks](
    [Code] [nvarchar](50) NULL,
    [Name] [nvarchar](50) NULL,
    [Price] [decimal](18, 0) NULL
) ON [PRIMARY]

We are going to map those table columns with the following model:

public class Stock
{
    public decimal Price { get; set; }
    public string Symbol { get; set; }
    public string Name { get; set; }        
}

Next, we install the NuGet package:

PM> Install-Package SqlTableDependency 

The next step is to create a custom hub class, used from the SignalR infrastructure:

[HubName("stockTicker")]
public class StockTickerHub : Hub
{
    private readonly StockTicker _stockTicker;

    public StockTickerHub() :
        this(StockTicker.Instance)
    {

    }

    public StockTickerHub(StockTicker stockTicker)
    {
        _stockTicker = stockTicker;
    }

    public IEnumerable<Stock> GetAllStocks()
    {
        return _stockTicker.GetAllStocks();
    }
}

We'll use the SignalR Hub API to handle server-to-client interaction. A StockTickerHub class that derives from the SignalR Hub class will handle receiving connections and method calls from clients. We can't put these functions in a Hub class, because Hub instances are transient. A Hub class instance is created for each operation on the hub, such as connections and calls from the client to the server. So the mechanism that keeps stock data, updates prices, and broadcasts the price updates that have to run in a separate class, which you'll name StockTicker:

public class StockTicker
{
    // Singleton instance
    private readonly static Lazy<StockTicker> _instance = new Lazy<StockTicker>(
        () => new StockTicker
        (GlobalHost.ConnectionManager.GetHubContext<StockTickerHub>().Clients));

    private static SqlTableDependency<Stock> _tableDependency;

    private StockTicker(IHubConnectionContext<dynamic> clients)
    {
        Clients = clients;

        var mapper = new ModelToTableMapper<Stock>();
        mapper.AddMapping(s => s.Symbol, "Code");

        _tableDependency = new SqlTableDependency<Stock>(
            ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString,
            "Stocks",
            mapper);

        _tableDependency.OnChanged += SqlTableDependency_Changed;
        _tableDependency.OnError += SqlTableDependency_OnError;
        _tableDependency.Start();
    }

    public static StockTicker Instance
    {
        get
        {
            return _instance.Value;
        }
    }

    private IHubConnectionContext<dynamic> Clients
    {
        get;
        set;
    }

    public IEnumerable<Stock> GetAllStocks()
    {
        var stockModel = new List<Stock>();

        var connectionString = ConfigurationManager.ConnectionStrings
				["connectionString"].ConnectionString;
        using (var sqlConnection = new SqlConnection(connectionString))
        {
            sqlConnection.Open();
            using (var sqlCommand = sqlConnection.CreateCommand())
            {
                sqlCommand.CommandText = "SELECT * FROM [Stocks]";

                using (var sqlDataReader = sqlCommand.ExecuteReader())
                {
                    while (sqlDataReader.Read())
                    {
                        var code = sqlDataReader.GetString(sqlDataReader.GetOrdinal("Code"));
                        var name = sqlDataReader.GetString(sqlDataReader.GetOrdinal("Name"));
                        var price = 
                            sqlDataReader.GetDecimal(sqlDataReader.GetOrdinal("Price"));

                        stockModel.Add
                           (new Stock { Symbol = code, Name = name, Price = price });
                    }
                }
            }
        }

        return stockModel;
    }

    void SqlTableDependency_OnError(object sender, ErrorEventArgs e)
    {
        throw e.Error;
    }

    /// <summary>
    /// Broadcast New Stock Price
    /// </summary>
    void SqlTableDependency_Changed(object sender, RecordChangedEventArgs<Stock> e)
    {
        if (e.ChangeType != ChangeType.None)
        {
            BroadcastStockPrice(e.Entity);
        }
    }

    private void BroadcastStockPrice(Stock stock)
    {
        Clients.All.updateStockPrice(stock);
    }

    #region IDisposable Support
    private bool disposedValue = false; // To detect redundant calls

    protected virtual void Dispose(bool disposing)
    {
        if (!disposedValue)
        {
            if (disposing)
            {
                _tableDependency.Stop();
            }

            disposedValue = true;
        }
    }

    ~StockTicker()
    {
        Dispose(false);
    }

    // This code added to correctly implement the disposable pattern.
    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }

    #endregion
}

Now it's time to see the HTML page:

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title>SqlTableDependencly with SignalR</title>
</head>
<body>
    <h1>SqlTableDependencly with SignalR</h1>

    <div id="stockTable">
        <table border="1">
            <thead style="background-color:silver">
                <tr><th>Symbol</th><th>Name</th><th>Price</th></tr>
            </thead>
            <tbody>
                <tr class="loading"><td colspan="3">loading...</td></tr>
            </tbody>
        </table>
    </div>

    <script src="jquery-1.10.2.min.js"></script>
    <script src="jquery.color-2.1.2.min.js"></script>
    <script src="../Scripts/jquery.signalR-2.2.0.js"></script>
    <script src="../signalr/hubs"></script>
    <script src="SignalR.StockTicker.js"></script>
</body>
</html>

and how we manage data returned from SignalR in our JavaScript code:

// Crockford's supplant method
if (!String.prototype.supplant) {
    String.prototype.supplant = function (o) {
        return this.replace(/{([^{}]*)}/g,
            function (a, b) {
                var r = o[b];
                return typeof r === 'string' || typeof r === 'number' ? r : a;
            }
        );
    };
}

$(function () {
    var ticker = $.connection.stockTicker; // the generated client-side hub proxy
    var $stockTable = $('#stockTable');
    var $stockTableBody = $stockTable.find('tbody');
    var rowTemplate = '<tr data-symbol="{Symbol}"><td>
    {Symbol}</td><td>{Name}</td><td>{Price}</td></tr>';

    function formatStock(stock) {
        return $.extend(stock, {
            Price: stock.Price.toFixed(2)
        });
    }

    function init() {
        return ticker.server.getAllStocks().done(function (stocks) {
            $stockTableBody.empty();

            $.each(stocks, function () {
                var stock = formatStock(this);
                $stockTableBody.append(rowTemplate.supplant(stock));
            });
        });
    }

    // Add client-side hub methods that the server will call
    $.extend(ticker.client, {
        updateStockPrice: function (stock) {
            var displayStock = formatStock(stock);
            $row = $(rowTemplate.supplant(displayStock)),
            $stockTableBody.find('tr[data-symbol=' + stock.Symbol + ']').replaceWith($row);
        }
    });

    // Start the connection
    $.connection.hub.start().then(init);
});

In the end, we do not have to forget to register the SignalR route:

[assembly: OwinStartup(typeof(Stocks.Startup))]
namespace Stocks
{
    public static class Startup
    {
        public static void Configuration(IAppBuilder app)
        {
            // For more information on how to configure your application using OWIN startup, 
            // visit http://go.microsoft.com/fwlink/?LinkID=316888

             app.MapSignalR();
        }
    }
}

How to Test

In the attachment, there is a simple web application, containing an HTML page that report stocks value in a table.

To test, follow these steps:

• Create a table as:
  SQL

  CREATE TABLE [dbo].[Stocks]([Code] [nvarchar](50) NOT NULL, _
  	[Name] [nvarchar](50) NOT NULL, [Price] [decimal](18, 0) NOT NULL)

• Populate the table with some data.
• Run the web application and browse the /SignalR.Sample/StockTicker.html page.
• Modify any data in the table to get an immediate notification on the HTML page.

References

• SignalR: http://www.asp.net/signalr/overview/getting-started/tutorial-server-broadcast-with-signalr
• SqlTableDependency: https://github.com/christiandelbianco/monitor-table-change-with-sqltabledependency

History

• 3^rd July, 2017: Initial version