Menu
I was developing this application on VB.net 2010 and SQL 2008.
I wanted the clients to be notified for updates on the db, and the application used to check the db for changes in the specified minute using a timer, which really is not efficient. I read about query notification, sqldependency, service broker, but then I read something that said they might not be efficient if I have 100 clients and I'm using query notifications to push notifications to my application.
Would someone help out on what I should do, and how I can do it (would be really helpful if examples are available). Thanks in advance!
558
There are two simple methods within the NotificationsHub class, one to push the newly edited database table data to the clients (all the connected clients in this case), and a method to retrieve cached data when a device connects for the first time (performance gain).
Built upon the Service Broker infrastructure, query notifications allow applications to be notified when data has changed. This feature is particularly useful for applications that provide a cache of information from a database, such as a Web application, and need to be notified when the source data is changed.
SqlDependency allows you to receive notifications when the original data in the database changes so that the cache can be refreshed. To set up a dependency, you need to associate a SqlDependency object to one or more SqlCommand objects. To receive notifications, you need to subscribe to the OnChange event.
To get the last updated record in SQL Server: We can write trigger (which automatically fires) i.e. whenever there is a change (update) that occurs on a row, the “lastupdatedby” column value should get updated by the current timestamp.
Query Notification will push to a Service Broker service, not directly to your application. See The Mysterious Notification to understand how it works. Your application is waiting for notifications by posting a WAITFOR(RECEIVE) statement on the database. Which implies that each of the 100 clients is occupying one SQL Server worker thread (which are limited, see max worker threads option). I've seen this working in production with +1000 clients (after bumping up the max worker threads option) but I would advise against it.
My recommendation would be to have one service monitoring for change, using SqlDependency/QueryNotifications. This service would then push notifications, using WCF for instance, to all your running apps. You would subscribe to generic changes (the table Foo was changed), not to specific ones (the row x in table Foo was inserted).
As a general rule SqlDependency/Query Notifications can only inform you that data has changed, but it won't push the new data. The application must refresh its local datasets by running the queries again, once notified.
82
Be careful using SqlDependency class - it 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. 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);
With SqlDependecyEx you are able to monitor just UPDATE, avoiding DELETE and INSERT. Hope this helps.
4
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
