5

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?

casperOne
  • 73,706
  • 19
  • 184
  • 253
Gregor Favre
  • 155
  • 1
  • 10

1 Answers1

2

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.

Steve Townsend
  • 53,498
  • 9
  • 91
  • 140
  • Hi steve, thank you for your answer! How would one usually start to build a centralized cache change detection engine? Probably by using a broker on the sql server, which sends out messages to the applications as soon as the observed data changes (notification subscriptions)? It would be great if you could point me to an example or an url, where I can find more on this practice. Thank you very much! – Gregor Favre May 03 '11 at 08:58
  • I've been doing some memory testing and haven't found any reason to remove the old OnChange event handler. – Jonathan Allen Jun 26 '14 at 20:26