How do I create a row specific sql cache dependency?

Question

I want to use data caching on my .net C# application. So far I added data caching and added sql cache dependencies on specific tables. But thats not good enough. These tables will be updated too frequently but not relevant to a lot of the cached objects. This will make the data caching almost useless because it will be flushed to frequently. I want to implement sql cache dependency on specific rows for each object. How can I do that?

Answer 1

You need to understand how SqlDependency works. You subscribe a result set and get notified when that result set has changed. You can subscribe any kind of result set, that means any kind of query, as long as it conforms to the restrictions of the supported statements. It really makes no difference if is a table or a view.

So technically you can subscribe for specific notifications by submitting a query specific for that row, ie. with a hard coded WHERE clause. You would have to change your code to retrieve and cache only the needed data on a row-by-row basis as opposed to retrieving entire tables and caching them in memory. Heck, you'd have to do that anyway if you're at least concerned about the size of those tables. Caching entire tables should only be done for catalog and reference data that change infrequently or not at all.

You can also choose to retrieve and cache partitions of the data, ie. individual ranges of keys (say between 'A' and 'D', 'E' and 'H' etc and subscribe to be notified for that specific data partition.

If you want to understand how SqlDependency works my blog has some articles covering it, including common programming pitfalls of SqlDependency and deployment problems with SqlDependency.