问题描述
有谁知道的方法来监视在SQL Server（2005或2008）数据库从.NET应用程序表记录的变化？它需要能够支持多个客户端的时间。每个客户端将订阅启动时，和退订，当它退出。多个用户可以同时访问该系统，我想反映他们的其他用户的客户端上进行更改。然后，当客户端处理的变化情况下，它可以更新它的本地对象重新presenting该记录。一种类似于如何被修改的访问更新的记录，反映在每个表单中引用它。
Does anyone know of a way to monitor table record changes in a SQL Server (2005 or 2008) database from a .Net application? It needs to be able to support multiple clients at a time. Each client will "subscribe" when it starts, and "unsubscribe" when it exits. Multiple users could be accessing the system at once and I want to reflect their changes on the other users client. Then when the client handles the change event, it could update it's local object representing that record. Kind of similar to how Access updates records that are modified are reflected in each form referencing it.
我知道微软有自己的Microsoft.SqlServer库与SQL Server进行交互。但我不知道该概念适用于我想要做的（或什么可弯曲适用于我想做的事情）。这听起来像他们可能是有用的那些是管理那些或复制的。
I know microsoft has their Microsoft.SqlServer libraries for interacting with a SQL Server. But I'm not sure which concept applies to what I want to do (or what could be bent to apply to what I want to do). The ones that sound like they might be useful are the Management ones or the Replication one.
在期待有人问，对你为什么不只是重新查询表偶尔去寻找新的信息？我有一个大数目，我要监控，这将是在一个痛苦的对接表。另外，如果我在寻找的东西多一点优雅。
In anticipation of someone asking, "why don't you just requery the table occasionally to look for new information?" I have a large number of tables that I want to monitor and that would be a pain in the butt. Plus if I'm looking for something a bit more elegant.
我接受建议...
推荐答案
要监视更改SQL表或记录在SQL 2005 +，你可以利用的SqlDependency类。
To monitor for changes to a SQL table or record in SQL 2005+ you can utilize the SqlDependency class.
这是针对ASP.NET中使用或中间层服务，而一台服务器是针对数据库管理活动的订阅 - 而不是多个几百个客户管理订阅。根据您指的是你的客户端的最大数量可能要建立一个可管理对SQL的通知订阅您的客户端缓存池服务。
It is targeted for use in ASP.NET or a middle-tier service where a single server is managing active subscriptions against a database - not multiple hundreds of clients managing subscriptions. Depending on the maximum number of clients you are referring to you may want to build a cache pooling service that can manage the notification subscriptions against SQL to your clients.
里面它采用SqlNotificationRequest它使用服务中介，不通知服务。因此，这应该前进的SQL 2008，等等。
Inside it uses SqlNotificationRequest which uses Service Broker, not Notification Services. Thus, this should work going forward on SQL 2008, etc.
void Initialization()
{
// Create a dependency connection.
SqlDependency.Start(connectionString, queueName);
}
void SomeMethod()
{
// Assume connection is an open SqlConnection.
// Create a new SqlCommand object.
SqlCommand command=new SqlCommand(
"SELECT ShipperID, CompanyName, Phone FROM dbo.Shippers",
connection);
// Create a dependency and associate it with the SqlCommand.
SqlDependency dependency=new SqlDependency(command);
// Maintain the refence in a class member.
// Subscribe to the SqlDependency event.
dependency.OnChange+=new OnChangeEventHandler(OnDependencyChange);
// Execute the command.
command.ExecuteReader();
// Process the DataReader.
}
// Handler method
void OnDependencyChange(object sender,
SqlNotificationsEventArgs e )
{
// Handle the event (for example, invalidate this cache entry).
}
void Termination()
{
// Release the dependency.
SqlDependency.Stop(connectionString, queueName);
}
Using和监控SQL 2005查询通知的文章，讨论您完成步骤来设置它在你的code（使用一个Web应用程序为例）以及所需要的订阅服务代理适当的SQL权限等。
"Using and Monitoring SQL 2005 Query Notifications" article that talks you through the steps to set it up in your code (using a web app as example) along with the appropriate SQL permissions that are required to subscribe to Service Broker and so on.
这是 ASP.NET（网页缓存）类也是可用于Web方案。
An ASP.NET (web caching) class is also available for web scenarios.
这篇关于净SQL Server数据库监测 - 插入，更新，删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！