Click here to Skip to main content
15,867,488 members
Articles / Web Development / HTML

SQL Server Notifications on Record Change with SignalR and SQLTableDependency

Rate me:
Please Sign up or sign in to vote.
4.73/5 (42 votes)
3 Jul 2017CPOL3 min read 128.4K   1.4K   85   39
SqlTableDependency is a component used to receive database notifications containing the record values inserted, deleted or update on a database table.

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:

SQL
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:

C#
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:

C#
[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:

SQL
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:

HTML
<!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:

JavaScript
// 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:

C#
[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

History

  • 3rd July, 2017: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Comments and Discussions

 
Questionupdates for NET 6? Pin
kiquenet.com18-Sep-22 6:11
professionalkiquenet.com18-Sep-22 6:11 
QuestionFantastic piece of software - my vote of 5! Pin
Martin Hart Turner4-May-19 22:02
Martin Hart Turner4-May-19 22:02 
QuestionHow to display inserted record? Pin
Ponsir4731-Jul-17 4:01
Ponsir4731-Jul-17 4:01 
GeneralMy vote of 4 Pin
Muhammad Shahid Farooq13-Mar-17 20:38
professionalMuhammad Shahid Farooq13-Mar-17 20:38 
QuestionStop notification for a particular client Pin
petsworld201122-Jan-17 21:14
petsworld201122-Jan-17 21:14 
SuggestionGreat article 5 from me Pin
IanL15-Apr-16 1:29
IanL15-Apr-16 1:29 
GeneralMy vote of 5 Pin
D V L22-Dec-15 20:10
professionalD V L22-Dec-15 20:10 
GeneralRe: My vote of 5 Pin
Christian Del Bianco22-Dec-15 20:37
Christian Del Bianco22-Dec-15 20:37 
QuestionRe: My vote of 5 Pin
D V L23-Dec-15 2:20
professionalD V L23-Dec-15 2:20 
AnswerRe: My vote of 5 Pin
Christian Del Bianco23-Dec-15 2:23
Christian Del Bianco23-Dec-15 2:23 
QuestionRe: My vote of 5 Pin
D V L23-Dec-15 2:29
professionalD V L23-Dec-15 2:29 
AnswerRe: My vote of 5 Pin
Christian Del Bianco23-Dec-15 4:16
Christian Del Bianco23-Dec-15 4:16 
Questioncan tabledependency work on views? Pin
Member 92798916-Sep-15 19:39
Member 92798916-Sep-15 19:39 
AnswerRe: can tabledependency work on views? Pin
Christian Del Bianco16-Sep-15 20:50
Christian Del Bianco16-Sep-15 20:50 
QuestionNeed help to understand few line of code Pin
Tridip Bhattacharjee15-Sep-15 22:03
professionalTridip Bhattacharjee15-Sep-15 22:03 
AnswerRe: Need help to understand few line of code Pin
Christian Del Bianco15-Sep-15 22:14
Christian Del Bianco15-Sep-15 22:14 
QuestionCan this work on sql server 2008 R2? Pin
Member 92798915-Sep-15 18:59
Member 92798915-Sep-15 18:59 
AnswerRe: Can this work on sql server 2008 R2? Pin
Christian Del Bianco15-Sep-15 21:12
Christian Del Bianco15-Sep-15 21:12 
QuestionTrying to find the attachment Pin
Member 92798915-Sep-15 16:26
Member 92798915-Sep-15 16:26 
AnswerRe: Trying to find the attachment Pin
Christian Del Bianco15-Sep-15 21:04
Christian Del Bianco15-Sep-15 21:04 
GeneralMy vote of 5 Pin
Humayun Kabir Mamun15-Sep-15 1:45
Humayun Kabir Mamun15-Sep-15 1:45 
QuestionWhere is the code for ModelToTableMapper Pin
Tridip Bhattacharjee14-Sep-15 21:34
professionalTridip Bhattacharjee14-Sep-15 21:34 
AnswerRe: Where is the code for ModelToTableMapper Pin
Christian Del Bianco15-Sep-15 1:49
Christian Del Bianco15-Sep-15 1:49 
GeneralRe: Where is the code for ModelToTableMapper Pin
Tridip Bhattacharjee15-Sep-15 21:47
professionalTridip Bhattacharjee15-Sep-15 21:47 
GeneralRe: Where is the code for ModelToTableMapper Pin
Christian Del Bianco15-Sep-15 21:53
Christian Del Bianco15-Sep-15 21:53 
Here: TableDependency project

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.