SignalR duplicate messages on sqlDependency change

Question

I have developed a webpage that connects to the signalR hub hooking into the jquery code with angularjs. The clients are sent a message when a sqldependency.onchange event occurs, however, for every client connected the message is duplicated to each. So, if two clients are connected, each client receives two messages and so on.

Here are the steps:

• connect two clients to hub
• Make DB change
• sqlDependency.onchange fires
• Call hub function clients.all.renewProducts()
• Recreate data respository resetting dependency
• Client Console: "Database SQL Dependency change detected: Update" app.js:44:12 (Twice)

Hub.cs

public static void SignalRGetData(string data)
{
      IHubContext context = GlobalHost.ConnectionManager.GetHubContext<SignalRGetData>();
      context.Clients.All.renewData(data);

      // Recereate data and sql dependency
      new DataRespository().GetData();
}

DataRespository.cs _dependency_OnChange

public void _dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
    if(e.Info == SqlNotificationInfo.Update)
    {
        ProductHub.GetProducts("Database SQL Dependency change detected: " + e.Info);
    }

}

GetData

public IEnumerable<ProductInventoryDetail> GetData()
{
     using(var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DynamicPricing"].ConnectionString))
     {
          conn.Open();
          var reposQuery =
          "SELECT [ID], [Program] FROM [DBO].[Detail]";
          using(SqlCommand cmd =  new SqlCommand(reposQuery, conn))
          {
                // remove any command object notifications
                cmd.Notification = null;
                // create dependency
                SqlDependency dependency = new SqlDependency(cmd);
                dependency.OnChange += new OnChangeEventHandler(_dependency_OnChange);

                if (conn.State == System.Data.ConnectionState.Closed)
                    conn.Open();

                // Execute Sql Command
                using(var reader = cmd.ExecuteReader())
                {
                    return reader.Cast<IDataRecord>().Select(x => new ProductInventoryDetail(){

                         ID = x.GetInt32(0),
                         Program = x.GetInt32(1)
                     }
                 }
           }
      }
}

Angular JavaScript

// Apply jQuery SignalR operations to Angular
app.value('$', $);

app.factory('signalRService', ['$', '$rootScope', function ($, $rootScope) {

    var proxy = null;
    var initialise = function () {
        // Get Connection to SignalR Hub
        var connection = $.hubConnection();

        // Create a Proxy
        proxy = connection.createHubProxy('SignalRData');

        // Publish the event when server has a push notification
        proxy.on('renewProducts', function (message) {
            console.log("Database SQL Dependency change detectedgnalRGetData: " + message);
            $rootScope.$emit('renewProducts', message);
        });

        // Start Connection
        connection.start().done(function () {
            console.log("Conenction Ready - invoke proxy");
            proxy.invoke('SignalRGetData');
        });

    };

    return {
        initialise: initialise
    }
}]);

Answer 1

As far as I can see, your code has a couple of problems:

• Every time when your client connects to the server it invokes SignalRGetData. According to your code SigalRGetData creates a new SqlDependency entity. Thus, if you have two clients you will have two SqlDependency entities or two database subscriptions which means you will have two notifications on the side of each client. If you want to send one notification for each client when a change in the database occurs you should have just only one SqlDependency entity for your application.
• According to your code you will have a notification just only for the first database change (I know it is strange, but it is true). If you want to receive notifications continuously you should make resubscribtion:

The MSDN contains a sample of how to use the SqlDependency here. Note how, similarly to the SqlNotification usage, the client is expected to subscribe again if it whishes to be further notified

• SqlDependency has the problems with memory leaks. Hovewer, you can use an open source realization of the SqlDependency class - SqlDependencyEx. It uses a database trigger and native Service Broker notification to receive events about the table changes. With SqlDependecyEx you are able to monitor INSERT, DELETE, UPDATE separately and receive actual changed data (xml) in the event args object. This is an usage example:

int changesReceived = 0;
using (SqlDependencyEx sqlDependency = new SqlDependencyEx(
          TEST_CONNECTION_STRING, TEST_DATABASE_NAME, TEST_TABLE_NAME)) 
{
    sqlDependency.TableChanged += (o, e) => changesReceived++;
    sqlDependency.Start();

    // Make table changes.
    MakeTableInsertDeleteChanges(changesCount);

    // Wait a little bit to receive all changes.
    Thread.Sleep(1000);
}

Assert.AreEqual(changesCount, changesReceived);

Suggestion:

To avoid duplicated client-side notifications it is better to use one notification exemplar for your controller/application. A code example from my last project:

public class HomeController : Controller
{
    // One global subscription for all the controller.
    static HomeController()
    {
        // ITableRowRepository incapsulates SqlDependencyEx usage.
        var repo = (ITableRowRepository)DependencyResolver.Current
                           .GetService(typeof(ITableRowRepository));
        // One global subscription.
        repo.TableChanged += RepoTableChanged;
    }

    // Actions here.

    private static void RepoTableChanged(object sender, TableChangedEventArgs e)
    {
        // Clients notification here.
    }
} 

Hope this helps.