使用 SqlDependency 偵測變更Detecting Changes with SqlDependency

您可以將 SqlDependency 物件與 SqlCommand 產生關聯,以便偵測查詢結果與原始擷取結果不同的時間。A SqlDependency object can be associated with a SqlCommand in order to detect when query results differ from those originally retrieved. 此外,您也可以指派委派 (Delegate) 給 OnChange 事件,而此事件將在相關聯命令的結果變更時引發。You can also assign a delegate to the OnChange event, which will fire when the results change for an associated command. 您必須先將 SqlDependency 與此命令產生關聯,然後再執行此命令。You must associate the SqlDependency with the command before you execute the command. HasChangesSqlDependency 屬性也可用來判斷自從上次擷取資料以來,查詢結果是否已經變更。The HasChanges property of the SqlDependency can also be used to determine if the query results have changed since the data was first retrieved.

安全性考量Security Considerations

相依性基礎結構會仰賴呼叫 SqlConnection 時所開啟的 Start,以便接收指定命令之基礎資料已經變更的通知。The dependency infrastructure relies on a SqlConnection that is opened when Start is called in order to receive notifications that the underlying data has changed for a given command. 讓用戶端啟始呼叫 SqlDependency.Start 的功能是透過使用 SqlClientPermission 和程式碼存取安全性屬性來控制的。The ability for a client to initiate the call to SqlDependency.Start is controlled through the use of SqlClientPermission and code access security attributes. 如需詳細資訊,請參閱啟用查詢通知代碼啟用安全性和 ADO.NETFor more information, see Enabling Query Notifications and Code Access Security and ADO.NET.

範例Example

下列步驟將說明如何宣告相依性、執行命令,並在結果集變更時接收通知:The following steps illustrate how to declare a dependency, execute a command, and receive a notification when the result set changes:

  1. 啟始伺服器的 SqlDependency 連接。Initiate a SqlDependency connection to the server.

  2. 建立要連接至伺服器的 SqlConnectionSqlCommand 物件,並定義 Transact-SQL 陳述式。Create SqlConnection and SqlCommand objects to connect to the server and define a Transact-SQL statement.

  3. 建立新的 SqlDependency 物件,或使用現有的物件,並將它繫結至 SqlCommand 物件。Create a new SqlDependency object, or use an existing one, and bind it to the SqlCommand object. 如此便可在內部建立 SqlNotificationRequest 物件,並視需要將其繫結至命令物件。Internally, this creates a SqlNotificationRequest object and binds it to the command object as needed. 此通知要求包含可唯一識別此 SqlDependency 物件的內部識別項。This notification request contains an internal identifier that uniquely identifies this SqlDependency object. 此外,它也會啟動用戶端接聽程式 (Listener) (如果尚未作用中的話)。It also starts the client listener if it is not already active.

  4. 訂閱 OnChange 物件之 SqlDependency 事件的事件處理常式。Subscribe an event handler to the OnChange event of the SqlDependency object.

  5. 使用 Execute 物件的任何 SqlCommand 方法來執行命令。Execute the command using any of the Execute methods of the SqlCommand object. 因為命令已繫結至通知物件,所以伺服器知道它必須產生通知,並且佇列資訊將指向相依性佇列。Because the command is bound to the notification object, the server recognizes that it must generate a notification, and the queue information will point to the dependencies queue.

  6. 停止伺服器的 SqlDependency 連接。Stop the SqlDependency connection to the server.

如果任何使用者隨後變更基礎資料,Microsoft SQL Server 就會偵測到針對此類變更暫止的通知存在,並發佈通知,而該通知可透過呼叫 SqlConnection 所建立的基礎 SqlDependency.Start 來進行處理並轉送給用戶端。If any user subsequently changes the underlying data, Microsoft SQL Server detects that there is a notification pending for such a change, and posts a notification that is processed and forwarded to the client through the underlying SqlConnection that was created by calling SqlDependency.Start. 用戶端接聽程式會接收到無效訊息。The client listener receives the invalidation message. 然後,用戶端接聽程式會找出相關聯的 SqlDependency 物件並引發 OnChange 事件。The client listener then locates the associated SqlDependency object and fires the OnChange event.

下列程式碼片段會顯示您應該用來建立範例應用程式的設計模式。The following code fragment shows the design pattern you would use to create a sample application.

Sub Initialization()
    ' Create a dependency connection.
    SqlDependency.Start(connectionString, queueName)
End Sub

Sub SomeMethod()
    ' Assume connection is an open SqlConnection.
    ' Create a new SqlCommand object.
    Using command As New SqlCommand( _
      "SELECT ShipperID, CompanyName, Phone FROM dbo.Shippers", _
      connection)

        ' Create a dependency and associate it with the SqlCommand.
        Dim dependency As New SqlDependency(command)
        ' Maintain the refernce in a class member.
        ' Subscribe to the SqlDependency event.
        AddHandler dependency.OnChange, AddressOf OnDependencyChange

        ' Execute the command.
        Using reader = command.ExecuteReader()
            ' Process the DataReader.
        End Using
    End Using
End Sub

' Handler method
Sub OnDependencyChange(ByVal sender As Object, _
    ByVal e As SqlNotificationEventArgs)
    ' Handle the event (for example, invalidate this cache entry).
End Sub

Sub Termination()
    ' Release the dependency
    SqlDependency.Stop(connectionString, queueName)
End Sub
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 reference 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);
}

另請參閱See also