Using a Dependency to Detect Changes in the Server
You can execute an SQL statement and use a SqlDependency object to detect when the query results will be different from those originally retrieved.
If your application maintains state, such as a Windows Form application, then you can create a SqlDependency object and maintain a reference to it. You can also assign a delegate to the OnChange event. OnChange will fire when the results change for an associated command. You must associate the SqlDependency with the command before you execute the command. If you are not using OnChange, you can check the HasChanges property of the SqlDependency to determine if the query results have changed.
To declare a dependency, execute a command, and receiving a notification when the result set changes
Initiate a SqlDependency connection to the server.
Create SqlConnection and SqlCommand objects to connect to the server and define a Transact-SQL statement.
Create a new SqlDependency object, or use an existing one, and bind it to the SqlCommand object. Internally, this creates a SqlNotificationRequest object and binds it to the command object as needed. This notification request contains a GUID that uniquely identifies this SqlDependency object. This also starts the client listener if it is not already active.
Subscribe an event handler to the OnChange event of the SqlDependency object.
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.
Stop the SqlDependency connection to the server.
If the same or another 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 a dispatching mechanism. The information necessary to contact the client (such as client address, and the protocol that the client can understand) is embedded in the notification request. The client listener receives the invalidation message. Based on the GUID (from step 2 above), the client listener then locates the associated SqlDependency object, and fires the OnChange event.
The application is notified in a form similar to that shown in the following C# code.
Sub Initialization()
' Create a dependency connection
SqlDependency.Start(cmd)
End Sub ' Initialization
Sub SomeMethod()
' Assume c is an open SqlConnection.
' Create a new SqlCommand object.
Dim cmd As New SqlCommand("SELECT * FROM Authors", c)
' Create a dependency and associate it with the SqlCommand.
Dim dep As New SqlDependency(cmd)
' Maintain the refence in a class member.
' Subscribe to the SqlDependency event.
AddHandler dep.OnChange, AddressOf OnDependencyChange
' Execute the command.
cmd.ExecuteReader()
End Sub 'SomeMethod
' Process the DataReader.
' Handler method
Sub OnDependencyChange(ByVal sender As Object, ByVal e As SqlNotificationsEventArgs)
End Sub 'OnDependencyChange
' Handle the event (for example, invalidate this cache entry).
Sub Termination()
' Release the dependency
SqlDependency.Stop(cmd)
End Sub 'Termination
void Initialization()
{
// Create a dependency connection
SqlDependency.Start(cmd);
}
void SomeMethod()
{
// Assume c is an open SqlConnection.
// Create a new SqlCommand object.
SqlCommand cmd=new SqlCommand( "SELECT * FROM Authors", c );
// Create a dependency and associate it with the SqlCommand.
SqlDependency dep=new SqlDependency( cmd );
// Maintain the refence in a class member.
// Subscribe to the SqlDependency event.
dep.OnChange+=new OnChangeEventHandler( OnDependencyChange );
// Execute the command.
cmd.ExecuteReader();
// Process the DataReader.
}
// Handler method
void OnDependencyChange( object sender,
SqlNotificationsEventArgs e )
{
// Handle the event (for example, invalidate this cache entry).
}
void Termination()
{
// Release the dependency
SqlDependency.Stop(cmd);
}
Security
The dependency infrastructure includes a client-side listener that is contacted by the server to send notifications. This listener is protected by Code Access Security attributes and by authentication. For more information, see SqlClientPermission.
Code Access Security Assertions
The listener infrastructure enables the listener to listen on TCP ports or HTTP ports depending on which system is running. To use the listener, which is necessary to use SqlDependency, the caller must have SqlNotificationPermission. The use of the SqlNotification request is independent of the dependencies and listener infrastructure on the client. Therefore listener permission is not required.