SignalR signaling messages when sqlDependency changes

Question

I developed a webpage that connects to the signalR hacker in jquery code with angular. Clients are sent messages when the sqldependency.onchange event sqldependency.onchange , however, for each client connected to the message, each is duplicated. Thus, if two clients are connected, each client receives two messages, etc.

Here are the steps:

• connect two clients to a hub
• Make a database change
• sqlDependency.onchange fires
• Call concentrator function clients.all.renewProducts()
• Restore data repository reset 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 STRONG>

 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 several problems:

• Each time your client connects to the server, it calls SignalRGetData . According to your SigalRGetData code, a new SqlDependency object is created. Thus, if you have two clients, you will have two SqlDependency objects or two database subscriptions, which means that you will have two notifications on the side of each client. If you want to send one notification for each client when the database changes, you should have only one SqlDependency object for your application.
• According to your code, you will only have a notification for the first change to the database (I know this is strange, but true ). If you want to receive notifications continuously, you must re-subscribe:

MSDN contains an example of using SqlDependency here . Please note that, like using SqlNotification, it is expected that the client will sign up again if he is notified of further notification

• SqlDependency has problems with memory leaks. Hovewer, you can use the open source implementation of the SqlDependency class - SqlDependencyEx . It uses a database trigger and its own Service Broker notification to receive table change events. Using SqlDependecyEx you can separately track INSERT , DELETE , UPDATE and get the actual changed data ( xml ) in the args event object. This is a 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); 

Sentence:

To avoid duplication of notifications on the client side, it is better to use one notification example for your controller / application. Sample code 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.

Answer 2

I have a solution 1. Just create a class with a static bool attribute.

 public class OutilContext { public static bool first = true; } 

2. In your Global.asax.cs file you must control the emptiness of Session_Start

    if (OutilContext.first == true) { OutilContext.first = false; NotificationComponent NC = new NotificationComponent(); var currentTime = DateTime.Now; NC.RegisterNotification(currentTime); } 

This is the management of multiple SqlDependency, because when a client accesses the application, the global class creates a SqlDependency for each client.