.Net SQL Server数据库监视 - 插入，更新，删除 [英] .Net SQL Server Database Monitoring - Insert, Update, Delete
问题描述
有没有人知道从.Net应用程序监视SQL Server（2005或2008）数据库中的表记录更改的方法？它需要能够一次支持多个客户端。每个客户端在启动时将订阅，并在退出时取消订阅。多个用户可以同时访问系统，我想在其他用户客户端上反映他们的更改。然后当客户端处理change事件时，它可以更新它的代表该记录的本地对象。
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有他们的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.
我可以接受建议...
I'm open to suggestions...
推荐答案
要监视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，它使用Service Broker，而不是Notification Services。因此，这应该在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);
}
使用和监控SQL 2005查询通知在您的代码（使用Web应用程序作为示例）中设置它以及订阅Service Broker等所需的适当的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.
a href =http://msdn.microsoft.com/en-us/library/system.web.caching.sqlcachedependency.aspx =noreferrer> ASP.NET（网络缓存）类也是可用于Web方案。
An ASP.NET (web caching) class is also available for web scenarios.
这篇关于.Net SQL Server数据库监视 - 插入，更新，删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
