数据库更新后，如何通知我的程序？ [英] How can I notify my program when the database has been updated?

问题描述

我有一个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/library/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/zh-cn/library/ms166086（v = sql.105）.aspx

这篇关于数据库更新后，如何通知我的程序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！