当数据库更新时如何通知我的程序?

问题描述

我有一个 C# 程序可以查询 SQL Server 数据库的某些值.

I have a C# program that queries the SQL Server database for some values.

目前，应用程序每分钟查询一次数据库，以确保表是最新的.

Currently the application queries the database every minutes to make sure that the table is up to date.

我希望能够做的是查询仅在数据库已更改/更新时完成.当数据库中的某些内容更新时，我如何通知我的程序?

What I would like to be able to do is that the query is only done when the database has been changed / updated. How do I notify my program when something has been updated in the database?

谢谢

推荐答案

轮询数据库不是很优雅的解决方案.

Polling database is not very elegant solution.

SqlDependency 对您的情况很有用.它不使用轮询而是使用通知机制.通知由 Service Broker 在您的数据库中提供，因此需要在您的数据库中启用此服务.OnChange 事件将在指定的表更改(更新、删除、插入...)时引发

SqlDependency from ADO.NET will be useful in your case. It does not use polling but notification mechanism. The notifications are provided by Service Broker in your database, so will need to enable this service in your databse. The OnChange event will raise when specified table changes(update, delete, insert..)

这是一个如何使用SqlDependency的例子:

Here is an example how to use SqlDependency:

void Initialization()
{
    // Create a dependency connection.
    SqlDependency.Start(connectionString, queueName);
}

void SomeMethod()
{
    // Assume connection is an open SqlConnection.

    // Create a new SqlCommand object.
    using (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.
        using (SqlDataReader reader = command.ExecuteReader())
        {
            // Process the DataReader.
        }
    }
}

// Handler method
void OnDependencyChange(object sender, 
   SqlNotificationEventArgs e )
{
  // Handle the event (for example, invalidate this cache entry).
}

void Termination()
{
    // Release the dependency.
    SqlDependency.Stop(connectionString, queueName);
}

来自 http://msdn.microsoft.com/en-us/图书馆/62xk7953.aspx

以下是如何启用 Service Broker(请注意，您将拥有数据库的独占性来执行此操作 - 最好在重新启动 sql server 后执行此操作):http://blogs.sftsrc.com/stuart/archive/2007/06/13/42.aspx(链接失效)

Here is how to enable Service Broker(note that you will have exclusiveness on the database to do that - best do it after restart of the sql server): http://blogs.sftsrc.com/stuart/archive/2007/06/13/42.aspx(Broken link)

可能的替代链接:http://technet.microsoft.com/en-us/library/ms166086(v=sql.105).aspx