SqlDependency Class

Definition

Namespace:

Microsoft.Data.SqlClient

Assembly:

Microsoft.Data.SqlClient.dll

Package:

Microsoft.Data.SqlClient v5.2.0

The SqlDependency object represents a query notification dependency between an application and an instance of SQL Server. An application can create a SqlDependency object and register to receive notifications via the OnChangeEventHandler event handler.

public ref class SqlDependency sealed

public sealed class SqlDependency

type SqlDependency = class

Public NotInheritable Class SqlDependency

Inheritance

Object

SqlDependency

Remarks

SqlDependency is ideal for caching scenarios, where your ASP.NET application or middle-tier service needs to keep certain information cached in memory. SqlDependency allows you to receive notifications when the original data in the database changes so that the cache can be refreshed.

To set up a dependency, you need to associate a SqlDependency object to one or more SqlCommand objects. To receive notifications, you need to subscribe to the OnChange event. For more information about the requirements for creating queries for notifications, see Working with Query Notifications.

Note

SqlDependency was designed to be used in ASP.NET or middle-tier services where there is a relatively small number of servers having dependencies active against the database. It was not designed for use in client applications, where hundreds or thousands of client computers would have SqlDependency objects set up for a single database server. If you are developing an application where you need reliable sub-second notifications when data changes, review the sections Planning an Efficient Query Notifications Strategy and Alternatives to Query Notifications in the Planning for Notifications article.

For more information, see Query Notifications in SQL Server and Building Notification Solutions.

Note

The OnChange event may be generated on a different thread from the thread that initiated command execution.

Query notifications are supported only for SELECT statements that meet a list of specific requirements.

Constructors

SqlDependency()	Creates a new instance of the SqlDependency class with the default settings.
SqlDependency(SqlCommand, String, Int32)	Creates a new instance of the SqlDependency class, associates it with the SqlCommand parameter, and specifies notification options and a time-out value.
SqlDependency(SqlCommand)	Creates a new instance of the SqlDependency class and associates it with the SqlCommand parameter.

Properties

HasChanges	Gets a value that indicates whether one of the result sets associated with the dependency has changed.
Id	Gets a value that uniquely identifies this instance of the SqlDependency class.

Methods

AddCommandDependency(SqlCommand)	Associates a SqlCommand object with this SqlDependency instance.
Start(String, String)	Starts the listener for receiving dependency change notifications from the instance of SQL Server specified by the connection string using the specified SQL Server Service Broker queue.
Start(String)	Starts the listener for receiving dependency change notifications from the instance of SQL Server specified by the connection string.
Stop(String, String)	Stops a listener for a connection specified in a previous Start(String, String) call.
Stop(String)	Stops a listener for a connection specified in a previous Start(String) call.

Events

OnChange

Occurs when a notification is received for any of the commands associated with this SqlDependency object.