Detecting changes with SqlDependency

Download ADO.NET

A SqlDependency object can be associated with a SqlCommand to detect when query results differ from the results originally retrieved. You can also assign a delegate to the OnChange event, which will fire when the results change for an associated command. Associate the SqlDependency with the command before you execute the command. 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

The dependency infrastructure relies on a SqlConnection that is opened when Start is called to receive notifications that the underlying data has changed for a given command. The ability for a client to begin the call to SqlDependency.Start is controlled by using SqlClientPermission and code access security attributes. For more information, see Enabling query notifications.

Example

The following steps illustrate how to declare a dependency, execute a command, and receive a notification when the result set changes:

1. Initiate a SqlDependency connection to the server.

2. Create SqlConnection and SqlCommand objects to connect to the server and define a Transact-SQL statement.

3. Create a new SqlDependency object, or use an existing one, and bind it to the SqlCommand object. Internally, this association creates a SqlNotificationRequest object and binds it to the command object as needed. This notification request contains an internal identifier that uniquely identifies this SqlDependency object. It also starts the client listener if it isn't already active.

4. Subscribe an event handler to the OnChange event of the SqlDependency object.

5. 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. Stop the SqlDependency connection to the server.

If any user then changes the underlying data, Microsoft SQL Server detects that there's 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. 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.

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);
}

Next steps

• Query notifications in SQL Server