如何创建行特定的 sql 缓存依赖项？
我想在我的 .net C# 应用程序上使用数据缓存。 到目前为止，我添加了数据缓存并添加了对特定表的 sql 缓存依赖项。 但这还不够好。 这些表更新过于频繁，但与许多缓存对象无关。 这将使数据缓存几乎毫无用处，因为它会被频繁刷新。 我想为每个对象实现对特定行的 sql 缓存依赖。 我怎样才能做到这一点？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您需要了解 SqlDependency 的工作原理。 您订阅结果集并在该结果集发生更改时收到通知 。 您可以订阅任何类型的结果集，这意味着任何类型的查询，只要它符合 支持的语句的限制。 是表还是视图确实没有什么区别。
因此从技术上讲，您可以通过提交特定于该行的查询来订阅特定通知，即。 使用硬编码的 WHERE 子句。 您必须更改代码才能逐行检索和缓存所需的数据，而不是检索整个表并将它们缓存在内存中。 哎呀，如果您至少担心这些表的大小，那么无论如何您都必须这样做。 仅应对不经常更改或根本不更改的目录和参考数据缓存整个表。
您还可以选择检索和缓存数据分区，即。 各个键范围（例如“A”和“D”、“E”和“H”等之间）并订阅以获取有关该特定数据分区的通知。
如果您想要 了解 SqlDependency 如何工作 我的博客有一些文章介绍它，包括 SqlDependency 的常见编程陷阱 和 SqlDependency 的部署问题。
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.