Click here to Skip to main content
14,217,523 members

Using SqlDependency for data change events

Rate this:
4.70 (72 votes)
Please Sign up or sign in to vote.
4.70 (72 votes)
28 Jun 2006CDDL
Using SQL Server 2005 and SqlDependency to keep your application's data updated.


Some applications need to share data between instances, as in a chat program, or receive constant updates, as in a stock broker application. There are many ways to accomplish this, some good, some better. This sample will explore using ADO.NET 2.0 SqlDependency and SQL Server 2005 to provide data flow between separate instances of an application. This simple chat program will demonstrate the usage, and give a test for the potential of these new features.


This sample uses features of SQL Server 2005 and ADO.NET 2.0, and was created using Visual Studio 2005.

SQL Server 2005

Among the many new features for SQL Server 2005 are Service Broker and Query Notifications. The Service Broker is a queued, reliable messaging mechanism that is built into SQL Server 2005 and provides a robust asynchronous programming model. The details of SSB are not covered in this article, more information can be found in MSDN.

Query Notifications allow applications to receive a notice when the results of a query have been changed. This improves performance by not having to periodically query the database for changes. More information can be found in MSDN.


The database for this sample is not very elaborate, just enough to demonstrate the features for this article. There are two tables, Message and Person. Person stores the users for the application, and Message stores the messages that are sent. To use SqlDependecy, the database must support a Service Broker. If the database was not created with this option enabled, you will need to enable it.



SqlNotificationRequest can also be used to provide the same services, however, it requires a good deal of manual setup. SqlDependency sets up the plumbing for you. While it is simpler to implement, it obviously doesn't allow the degree of customization that may be necessary for some applications, and SqlNotificationRequest would be the best choice.

A dependency is created between the application and the database via a SqlCommand. Before that can be established, the SqlDependency must be started for this session.


After processing this command, SQL Server will automatically create a queue and a service in the database designated in the connection string that is passed in. In order to create this queue, the database user must have the SUBSCRIBE QUERY NOTIFICATIONS permission. As you can see, GUIDs are used for naming these objects. Each time the application is run, a new GUID will be generated, and a new queue and a service created. Although the documentation says these will be removed when the application exits, I have found this to not be the case.

As stated before, the dependency is created based on a SqlCommand.

SqlDependency dependency = new SqlDependency(cmd);

There are some restrictions, of course, as to what can be included in this command. The command must use two part names and not use *. It also must obviously not be an UPDATE or INSERT statement.

The following won't work:


This will work:

SELECT ID, Message FROM dbo.Message

If the query is not correct, an event will immediately be sent with:

SqlNotificationEventArgs.Info = Query
SqlNotificationEventArgs.Source = Statement
SqlNotificationEventArgs.Type = Subscribe

The very vague explanation for this is "The statement is not valid for notifications". Unfortunately, I have not been able to find a good source for what comprises a valid statement. In the testing process, I have used an in-line SQL statement which was shown as valid, and then a stored procedure with the very same statement and it was shown as invalid.

Update: Thanks to DylanTheDeveloper for finding this resource describing valid queries: Special Considerations When Using Query Notifications. Also, using SET NOCOUNT ON in your stored procedure will invalidate it for usage in Query Notifications.


Once the dependency is setup and the application is running, all you have to do is sit back and wait to receive the event.

In this sample application, when a new message is sent by a user, it will be inserted into the database which will cause a message to be sent to the Service Broker Queue and picked up by the Notification Service which will in turn fire the OnChange event to be handled by the application.

void OnChange(object sender, SqlNotificationEventArgs e)
  SqlDependency dependency = sender as SqlDependency;

  // Notices are only a one shot deal
  // so remove the existing one so a new 
  // one can be added

  dependency.OnChange -= OnChange;

  // Fire the event
  if (OnNewMessage != null)

The notification is a one shot deal, so after the event has been received, it must be connected again to continue receiving notifications. In the sample application, the OnChange event is removed and an event is fired to the client, which will reload the messages and cause the dependency to be re-established.

public DataTable GetMessages()
  DataTable dt = new DataTable();

    // Create command
    // Command must use two part names for tables
    // SELECT <field> FROM dbo.Table rather than 
    // SELECT <field> FROM Table
    // Query also can not use *, fields 
    // must be designated
    SqlCommand cmd = 
        new SqlCommand("usp_GetMessages", m_sqlConn);
    cmd.CommandType = CommandType.StoredProcedure;
    // Clear any existing notifications
    cmd.Notification = null;

    // Create the dependency for this command
    SqlDependency dependency = new SqlDependency(cmd);

    // Add the event handler
    dependency.OnChange += 
           new OnChangeEventHandler(OnChange);

   // Open the connection if necessary
   if(m_sqlConn.State == ConnectionState.Closed)

   // Get the messages
  catch (Exception ex)
    throw ex;

  return dt;

You'll notice that the connection to the database does not need to be kept open to receive the change event.


This article and sample are just a quick introduction to one of the new features of SQL Server 2005 and ADO.NET 2.0 and how it may be used in an application.

These features are, of course, very new, and information is still somewhat sketchy.

As well as the previously mentioned resources, this blog from Sushil Chordia has some good, though outdated, information about the necessary permissions: DataWorks WebLog.


This article, along with any associated source code and files, is licensed under The Common Development and Distribution License (CDDL)


About the Author

Comments and Discussions

QuestionClarification Pin
Elfman_NE9-Dec-10 10:21
memberElfman_NE9-Dec-10 10:21 
AnswerRe: Clarification Pin
Not Active9-Dec-10 10:40
mentorNot Active9-Dec-10 10:40 
NewsSQL Server 2008 64/vista 64/vs2008 Pin
Pandele Florin2-Nov-10 8:27
memberPandele Florin2-Nov-10 8:27 
GeneralMy vote of 5 Pin
Alejandro Miralles27-Sep-10 8:52
memberAlejandro Miralles27-Sep-10 8:52 
Generalexcellent, just one question Pin
leollI18-Jul-10 18:34
memberleollI18-Jul-10 18:34 
GeneralRe: excellent, just one question Pin
Not Active19-Jul-10 9:21
mentorNot Active19-Jul-10 9:21 
GeneralSqlDependency Article Pin
Maurice7526-Feb-10 9:18
memberMaurice7526-Feb-10 9:18 
GeneralRe: SqlDependency Article Pin
Not Active26-Feb-10 12:11
mentorNot Active26-Feb-10 12:11 
GeneralRe: SqlDependency Article Pin
Maurice7528-Feb-10 11:00
memberMaurice7528-Feb-10 11:00 
GeneralI said just one question.... i found another :D Pin
Pheonyx4-Feb-10 6:49
professionalPheonyx4-Feb-10 6:49 
GeneralRe: I said just one question.... i found another :D Pin
Not Active4-Feb-10 7:12
mentorNot Active4-Feb-10 7:12 
GeneralRe: I said just one question.... i found another :D Pin
Pheonyx4-Feb-10 22:12
professionalPheonyx4-Feb-10 22:12 
GeneralExcellent Program, just one question Pin
Pheonyx4-Feb-10 3:55
professionalPheonyx4-Feb-10 3:55 
GeneralRe: Excellent Program, just one question Pin
Not Active4-Feb-10 4:15
mentorNot Active4-Feb-10 4:15 
GeneralRe: Excellent Program, just one question Pin
Pheonyx4-Feb-10 4:19
professionalPheonyx4-Feb-10 4:19 
GeneralRe: Excellent Program, just one question Pin
Not Active4-Feb-10 5:08
mentorNot Active4-Feb-10 5:08 
QuestionHow can i know what operation is performed on table. Pin
jfdrgxdhsgh11-Jan-10 23:24
memberjfdrgxdhsgh11-Jan-10 23:24 
AnswerRe: How can i know what operation is performed on table. Pin
Not Active12-Jan-10 1:45
mentorNot Active12-Jan-10 1:45 
GeneralLingering queue and service question [modified] Pin
westdw11-Jan-10 4:13
memberwestdw11-Jan-10 4:13 
GeneralRe: Lingering queue and service question Pin
Not Active11-Jan-10 11:11
mentorNot Active11-Jan-10 11:11 
Generalexcellent article Pin
Sunil Munikar8-Oct-09 5:02
memberSunil Munikar8-Oct-09 5:02 
GeneralRe: excellent article Pin
Not Active8-Oct-09 11:38
mentorNot Active8-Oct-09 11:38 
QuestionRe: excellent article Pin
Sunil Munikar8-Oct-09 21:26
memberSunil Munikar8-Oct-09 21:26 
AnswerRe: excellent article Pin
Not Active9-Oct-09 1:07
mentorNot Active9-Oct-09 1:07 
GeneralRe: excellent article Pin
Sunil Munikar9-Oct-09 2:23
memberSunil Munikar9-Oct-09 2:23 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Posted 18 Nov 2005


190 bookmarked