如何做一个的SqlCacheDependency知道何时表中的更改回向任何侦听器时的数据？ [英] How does a SqlCacheDependency know when to communicate back to any listeners when data in a table changes?

问题描述

我一直在做一些研究，我可以看到大部分管道的这个系统但是我不知道服务器如何SQL知道何时触发一个消息给任何监听器（应用程序）时，表中数据的变化，实例。我将解释什么，我明白到这，我迷路点开始。

I have been doing some research and I can see most of the plumbing for this system however I'm not sure how sql server knows when to fire a message back to any listeners (applications) when data in a table changes, for instance. I'll start by explaining what I understand up to the point that I'm getting lost.

1）服务代理需要的数据库上启用和一些权限需要设置

1) Service Broker needs to be enabled on the database and some permissions need to be set.

2）的数据库模式应部署。

2) The database schema should be deployed.

3）使用aspnet_regsql.exe的，启用您需要为缓存数据的数据库和表的SQL缓存依赖（这一步创建一个表来跟踪变化，并触发对表捕捉变化，并增加在该表中的值）

3) Using aspnet_regsql.exe, enable sql cache dependency for the database and tables you need to cache data for (this step creates a table to track changes and triggers on the tables to capture changes and increment a value in that table).

4）建立在.NET应用程序中的SQL缓存依赖。例如，在一个Web应用程序，你需要为投票时间，连接字符串等添加配置值;启动/停止在你的global.asax.cs的依赖，然后添加SQL缓存依赖当你将项目添加到您的缓存。

4) Set up the sql cache dependency in the .net application. For instance, in a web app, you'll need to add config values for the poll time, connection string, etc; start/stop the dependency in your global.asax.cs, and then add sql cache dependencies when you add items to your cache.

4a）的启动依赖时发生的情况部分原因是基础设施建立了一个队列，服务，沟通和清理之后一个存储过程。使用SQL查询分析器，可以看到连接正在取得和通信信道被建立在服务应用程序从SQL Server接收消息。

4a) Part of what happens when starting the dependency is that the infrastructure is set up for a queue, a service, and a sproc for communication and cleanup afterwards. Using Sql Query Profiler, you can see connection being made and a communication channel being set up on the service for the application to receive messages from sql server.

5）这里是我感到困惑。在这一点上，我在一个参考SQL缓存依赖的基础表上我的应用程序缓存中的项目，使我的应用程序可以接收的情况下，该行的变化而变化。如果我手动运行该行的最新情况，我可以看到触发器被击中，并在跟踪表中的值由1递增不过，我没有看到SQL查询分析器任何通信将返回给应用程序，什么都没有，也没有是从缓存中删除的项目。我还没有看到在队列中的任何数据库上（无论是动态应用程序队列，也不是标准错误/传输队列）

5) Here is where I get confused. At this point, I've cached an item in my application cache with a reference to the sql cache dependency on the underlying table so that my app can receive changes in case that row changes. If I manually run an update on that row, I can see the trigger being hit and the value in the tracking table being incremented by 1. However, I do not see any communication going back to the application, nothing on sql query profiler, nor is the item removed from the cache. I also don't see anything in the queues on the database (neither the dynamic application queue nor the standard error/transmission queues)

我的问题是，什么是看着那跟踪数据库中的表，这样可以发送备份到SQL依赖关系是关心更改这些数据？

My question is, what is watching that tracking table in the database so that messages can be sent back up to the sql dependencies that are concerned about changes to this data?

任何帮助是极大AP preciated ......我已经经历了许多的在线参考煮找不到这个任何具体的解释。

Any help is greatly appreciated... I've been scouring through many online references and can't find any concrete explanation of this.

推荐答案

在数据库中保存您要检查在更新表，更改号码的名称创建一个新表。你有安装的SqlDependency每个表都有一个触发器设置了更新/插入递增在我刚才所描述的新表changeid。

A new table is created in your database that holds a name of the table you want to check for updates on, and a change number. Each table you have setup for sqldependency has a trigger set up for updates/inserts that increments the changeid in the new table I just described.

您的心理是如何工作的模式是倒退。您的应用程序检查日志以确定如果一个表已经改变了。

Your mental model of how this works is backwards. Your application checks the log to determine if a table has changed.

因此​​，如果更改日志表（这就是我所说的话）正在跟踪两个表在您的数据库（产品，用户），它看起来是这样的。

So if the changelog table (That's what I call it) is tracking two tables in your database (Product, User) It will look like this.

+Table Name + ChangeNumber +
| Product   | 1            |
+-----------+--------------+
| User      | 1            |
+-----------+--------------+

现在，如果你在任何这些表的任何修改，触发器将增加的changenumber ，现在我们知道他们改变了。

Now if you modify anything in either of these tables, the trigger will increment ChangeNumber and we now know they changed.

显然有更多的这一点，但这是一般的想法。

Obviously there is more to this, but this is the general idea.

注：应该指出的是，如果一个或多个表改变你可以无效页面，所以如果你的页面已经依赖设立这两个表，如果其中一个变化，它会失效缓存的页面并重新缓存的更新版本。

Note: It should be noted that you can invalidate a page if one or more tables change, so if your page has dependency set up for both of these tables, if one of them changes it will invalidate the cached page and re-cache an updated version.

这篇关于如何做一个的SqlCacheDependency知道何时表中的更改回向任何侦听器时的数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！