Caching with SqlDependency

Question

I'm using SqlDependency to monitor for changes in a database. Let's say, these are stock quotes, to make the example easy to understand. The application also uses (another) SqlDependeny-Object to monitor changes to static data, which is cached in the application.

What I do, is start the Dependency by SqlDependency.Start() when launching the application, and calling SqlDependeny.Stop when closing the application. So far everything is working well.

Now when the user selects a stock symbol, another SqlDependency is set up and notifies me of changes, on which I can take actions upon. When the user changes the symbol he wants to track, I have to set up a new SqlDependency and would have to clear out the old SqlDependency.

My question is: Is that true? And how am I able to clear out specific Dependencies? SqlDependency.Stop() is a static method and stops all dependencies - and even there I am quite uncertain if the notitication subscriptions on the SQL server are really cleared out - or just waiting to run into a timeout.

This all raises the question, if it is the correct way using SqlDependency to do some caching, or if there are other good and easy practices accomplishing this?

Answer 1

Not sure why you need to call Stop here. Just create a new SqlDependency instance for the new symbol, and remove the reference to the instance for the old one, allowing garbage collection to clean it up in time. So long as you also remove your subscription to the old instance's OnChange event, this approach should work for you.

Not sure how it will scale if you want to support many user, though. In that case a centralized cache change detection and merge scheme might be better.