DevForce Community Forum : Anyone using SQL Server Query Notification? http://www.ideablade.com/forum/ This is an XML content feed of; DevForce Community Forum : DevForce Classic : Anyone using SQL Server Query Notification? Sun, 19 Apr 2026 11:30:49 -700 Fri, 21 Dec 2007 11:14:55 -700 http://blogs.law.harvard.edu/tech/rss Web Wiz Forums 9.69 360 www.ideablade.com/forum/RSS_post_feed.asp?TID=609 DevForce Community Forum http://www.ideablade.com/forum/forum_images/IdeaBlade_logo_tm.png http://www.ideablade.com/forum/ Anyone using SQL Server Query Notification? : Yup. I've wondered about... http://www.ideablade.com/forum/forum_posts.asp?TID=609&PID=2280#2280 Author: IdeaBlade
Subject: 609
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.

]]> Fri, 21 Dec 2007 11:14:55 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=609&PID=2280#2280 Anyone using SQL Server Query Notification? : We implemented a cache-refresh... http://www.ideablade.com/forum/forum_posts.asp?TID=609&PID=2279#2279 Author: Sigi
Subject: 609
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.
]]> Fri, 21 Dec 2007 03:34:07 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=609&PID=2279#2279 Anyone using SQL Server Query Notification? : I think I can explain this in... http://www.ideablade.com/forum/forum_posts.asp?TID=609&PID=2254#2254 Author: pnschofield
Subject: 609
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. 
]]> Tue, 18 Dec 2007 11:14:25 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=609&PID=2254#2254 Anyone using SQL Server Query Notification? : I found the tutorial on using... http://www.ideablade.com/forum/forum_posts.asp?TID=609&PID=2204#2204 Author: pnschofield
Subject: 609
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 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

]]> Thu, 13 Dec 2007 13:36:02 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=609&PID=2204#2204