使用 Sql Server 2008 更改通知 [英] Change Notification with Sql Server 2008

问题描述

我有一个由一个数据库和几个服务组成的应用程序.这些服务之一将信息添加到数据库(由用户触发).

I have an application that consists of a database and several services. One of these services adds information to the database (triggered by a user).

另一项服务会定期查询数据库中的更改，并将新数据用作处理输入.

Another service periodically queries the databases for changes and uses the new data as input for processing.

到目前为止，我使用了一个可配置的计时器，每 30 秒左右查询一次数据库.我阅读了有关 Sql 2005 的更改通知功能.但是，在 Sql 2008 中，此功能已弃用.

Until now I used a configurable timer that queries the database every 30 seconds or so. I read about Sql 2005 featuring Notification of changes. However, in Sql 2008 this feature is deprecated.

直接在代码中获取数据库中发生的更改通知的最佳方式是什么?最佳做法是什么?

What is the best way of getting notified of changes that occurred in the database directly in code? What are the best practices?

推荐答案

Notification Services 已弃用，但您无论如何都不想使用它.

Notification Services was deprecated, but you don't want to use that anyway.

在某些情况下，您可能会考虑 Service Broker 消息；详情取决于您的应用.

You might consider Service Broker messages in some scenarios; the details depend on your app.

在大多数情况下，您可能可以使用 SqlDependency 或 SqlCacheDependency.它们的工作方式是在发出查询时将 SqlDependency 对象包含在查询中.查询可以是单个 SELECT 或存储过程中的一组复杂命令.

In most cases, you can probably use SqlDependency or SqlCacheDependency. The way they work is that you include a SqlDependency object with your query when you issue it. The query can be a single SELECT or a complex group of commands in a stored procedure.

稍后，如果另一个 Web 服务器或用户或网页对 DB 进行更改，这可能会导致先前查询的结果发生更改，则 SQL Server 将向所有已注册 SqlDependency 对象的服务器发送通知.您可以注册代码以在这些事件到达时运行，也可以简单地清除缓存中的条目.

Sometime later, if another web server or user or web page makes a change to the DB that might cause the results of the previous query to change, then SQL Server will send a notification to all servers that have registered SqlDependency objects. You can either register code to run when those events arrive, or the event can simply clear an entry in the Cache.

虽然需要启用Service Broker才能使用SqlDependency，但不需要显式与之交互.但是，您也可以将其用作替代机制；将其更多地视为一种持久性消息传递系统，可保证消息顺序和一次性传递.

Although you need to enable Service Broker to use SqlDependency, you don't need to interact with it explicitly. However, you can also use it as an alternative mechanism; think of it more as a persistent messaging system that guarantees message order and once-only delivery.

关于如何使用这些系统的细节对于论坛帖子来说有点长.你可以谷歌他们，或者我也在我的书中提供例子(超快 ASP.NET).

The details of how to use these systems are a bit long for a forum post. You can either Google for them, or I also provide examples in my book (Ultra-Fast ASP.NET).

这篇关于使用 Sql Server 2008 更改通知的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！