問題描述

我有一個 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

這篇關于當數據庫更新時如何通知我的程序?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！