Print Page | Close Window

Anyone using SQL Server Query Notification?

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce Classic
Forum Discription: For .NET 2.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=609
Printed Date: 19-Apr-2026 at 12:50pm


Topic: Anyone using SQL Server Query Notification?
Posted By: pnschofield
Subject: Anyone using SQL Server Query Notification?
Date Posted: 13-Dec-2007 at 1:36pm
I found the tutorial on using Push functionality with SQL Server Notification Services.  I was wondering if anyone had tried using the SQL Server Query Notification with DevForce.  I think our client app could bypass quite a few DataSourceOnly queries if we could have the BOS request Query Notifications for some queries, and then use the Push feature to notify clients when they need to refresh their EntityCache.

I found http://dunnry.com/blog/UsingSQLDependencyObjectsWithLINQ.aspx - this article , which describes a method for getting any SqlCommands in  a CallContext to participate in a Query Notification.

If the Fetching event is raised on the PM instance running in the BOS prior to performing the actual database query, I believe I can use the sample code to have the ADO.NET create a SqlDependency and add it to the SqlCommand used by the BOS to perform the actual query.  When SQL Server performs the callback to the BOS, the BOS can perform a Push call back to the client app that performed the original query, notifying it that it should re-fetch the query using DataSourceOnly, to keep the client cache up-to-date.

Has anyone tried this type of approach?

Paul

http://dunnry.com/blog/UsingSQLDependencyObjectsWithLINQ.aspx -


Replies:
Posted By: pnschofield
Date Posted: 18-Dec-2007 at 11:14am
I think I can explain this in another way that would be more interesting.  Imagine if your client app could execute every single query as CacheOnly, even if you absolutely required up-to-date data.  You could enjoy the performance of cached queries, with a guarantee that you'd be only hit the database if the data had changed in the meantime.

Just have the BOS ask for query notifications for each query.  When it receives a notification, the BOS calls a method on the client that originally made the query, to tells it to remove that query from the QueryCache.  The next time that client performs the query (as CacheOnly), it will find that the query doesn't exist in the QueryCache, and will only then execute it against the BOS.  Until that time, the client will be retrieving the query from the local cache, every single time. 

Basically, your client app would never again query the database unless it was necessary to do so. 

Posted By: Sigi
Date Posted: 21-Dec-2007 at 3:34am
We implemented a cache-refresh mechanism based on SqlDependency almost as you described it. We had an  SqlDependency  running on a Custom-Module with  the BOS which was notifying clients about DB-changes.
This works fine. There was/is one problem: SqlDependency does not tell you what exactly changed, even the Column which changed is not reported. This makes it useless for monitoring changes of tables with lots of data.
If all your data access to the Db goes through DevForce, you may wait for a new feature of DevForce providing this functionality in a way you can use it for any size of tables.

Posted By: IdeaBlade
Date Posted: 21-Dec-2007 at 11:14am
Yup. I've wondered about this too. It seems to be a limitation of SQL Server 2005. I'm not even sure if it tells you what row in a table changed (I confess I haven't really looked at it).
 
So if you want to be notified, you should be notified via an AuditTrail table that tells you ... and then either use table triggers or code to add rows to the AuditTrail table.


Print Page | Close Window