Click here to Skip to main content
11,477,007 members (76,076 online)
Click here to Skip to main content

Using SqlDependency for data change events

, 28 Jun 2006 CDDL 353.2K 9.5K 172
Rate this:
Please Sign up or sign in to vote.
Using SQL Server 2005 and SqlDependency to keep your application's data updated.

Introduction

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.

Cautions

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.

Database

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.

ALTER DATABASE Chatter SET ENABLE_BROKER

SqlDependency

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.

SqlDependency.Start(m_ConnectionString);

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:

SELECT * FROM Message

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.

Notification

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)
  {
     OnNewMessage();
  }
}

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

  try
  {
    // 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)
        m_sqlConn.Open();

   // Get the messages
   dt.Load(cmd.ExecuteReader(
             CommandBehavior.CloseConnection));
  }
  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.

Conclusion

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.

License

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

Share

About the Author


Comments and Discussions

 
QuestionSqlDependency [modified] Pin
amir farashah11-Mar-15 1:04
memberamir farashah11-Mar-15 1:04 
GeneralMy vote of 4 Pin
neil04195-Feb-15 5:50
memberneil04195-Feb-15 5:50 
Questionuseful Pin
lqht3-Dec-14 4:43
memberlqht3-Dec-14 4:43 
QuestionGet store procedure Pin
mert Aslı22-Sep-14 23:17
membermert Aslı22-Sep-14 23:17 
GeneralMy vote of 5 Pin
Patrick-Wayfarer5-Jun-13 16:12
memberPatrick-Wayfarer5-Jun-13 16:12 
QuestionMy vote of 5 - Excellent article and very helpful!!!! But...there is a small detail... [modified] Pin
Brian C Hart9-Apr-13 13:33
memberBrian C Hart9-Apr-13 13:33 
QuestionWhy it invoke all times? Pin
AshuTetra18-Jan-13 19:52
memberAshuTetra18-Jan-13 19:52 
AnswerRe: Why it invoke all times? Pin
alekcarlsen1-Oct-13 1:39
memberalekcarlsen1-Oct-13 1:39 
GeneralMy vote of 5 Pin
abdusalam.benhaj16-Jan-13 3:04
memberabdusalam.benhaj16-Jan-13 3:04 
GeneralMy vote of 5 Pin
Edo Tzumer27-Nov-12 20:18
memberEdo Tzumer27-Nov-12 20:18 
GeneralMy vote of 5 Pin
Kanasz Robert24-Sep-12 7:12
mvpKanasz Robert24-Sep-12 7:12 
Questionwill this application work with SQLite ? Pin
Bhimraj Ghadge19-Apr-12 21:40
memberBhimraj Ghadge19-Apr-12 21:40 
AnswerRe: will this application work with SQLite ? Pin
Mark Nischalke20-Apr-12 2:11
mvpMark Nischalke20-Apr-12 2:11 
GeneralMy vote of 5 Pin
Sandeep Ramani13-Mar-12 22:50
memberSandeep Ramani13-Mar-12 22:50 
GeneralRe: My vote of 5 Pin
Mark Nischalke26-Mar-12 16:13
mvpMark Nischalke26-Mar-12 16:13 
QuestionWhat are the other ways Pin
Mayank Bhardwaj119-Nov-11 3:05
memberMayank Bhardwaj119-Nov-11 3:05 
GeneralNice Article... I need a clarification Pin
raghavendran.s@target.com13-Jun-11 0:47
memberraghavendran.s@target.com13-Jun-11 0:47 
Questionreal world usage? Pin
walex481127-Apr-11 23:17
memberwalex481127-Apr-11 23:17 
AnswerRe: real world usage? Pin
Mark Nischalke28-Apr-11 3:27
mvpMark Nischalke28-Apr-11 3:27 
GeneralWindows Form VS WPF Pin
energie4-Mar-11 13:52
memberenergie4-Mar-11 13:52 
QuestionClarification Pin
Elfman_NE9-Dec-10 11:21
memberElfman_NE9-Dec-10 11:21 
AnswerRe: Clarification Pin
Mark Nischalke9-Dec-10 11:40
mvpMark Nischalke9-Dec-10 11:40 
NewsSQL Server 2008 64/vista 64/vs2008 Pin
Pandele Florin2-Nov-10 9:27
memberPandele Florin2-Nov-10 9:27 
GeneralMy vote of 5 Pin
Alejandro Miralles27-Sep-10 9:52
memberAlejandro Miralles27-Sep-10 9:52 
Generalexcellent, just one question Pin
leollI18-Jul-10 19:34
memberleollI18-Jul-10 19:34 
GeneralRe: excellent, just one question Pin
Mark Nischalke19-Jul-10 10:21
mvpMark Nischalke19-Jul-10 10:21 
GeneralSqlDependency Article Pin
Maurice7526-Feb-10 10:18
memberMaurice7526-Feb-10 10:18 
GeneralRe: SqlDependency Article Pin
Mark Nischalke26-Feb-10 13:11
mvpMark Nischalke26-Feb-10 13:11 
GeneralRe: SqlDependency Article Pin
Maurice7528-Feb-10 12:00
memberMaurice7528-Feb-10 12:00 
GeneralI said just one question.... i found another :D Pin
Pheonyx4-Feb-10 7:49
memberPheonyx4-Feb-10 7:49 
GeneralRe: I said just one question.... i found another :D Pin
Mark Nischalke4-Feb-10 8:12
mvpMark Nischalke4-Feb-10 8:12 
GeneralRe: I said just one question.... i found another :D Pin
Pheonyx4-Feb-10 23:12
memberPheonyx4-Feb-10 23:12 
GeneralExcellent Program, just one question Pin
Pheonyx4-Feb-10 4:55
memberPheonyx4-Feb-10 4:55 
GeneralRe: Excellent Program, just one question Pin
Mark Nischalke4-Feb-10 5:15
mvpMark Nischalke4-Feb-10 5:15 
GeneralRe: Excellent Program, just one question Pin
Pheonyx4-Feb-10 5:19
memberPheonyx4-Feb-10 5:19 
GeneralRe: Excellent Program, just one question Pin
Mark Nischalke4-Feb-10 6:08
mvpMark Nischalke4-Feb-10 6:08 
QuestionHow can i know what operation is performed on table. Pin
jfdrgxdhsgh12-Jan-10 0:24
memberjfdrgxdhsgh12-Jan-10 0:24 
AnswerRe: How can i know what operation is performed on table. Pin
Mark Nischalke12-Jan-10 2:45
mvpMark Nischalke12-Jan-10 2:45 
GeneralLingering queue and service question [modified] Pin
westdw11-Jan-10 5:13
memberwestdw11-Jan-10 5:13 
GeneralRe: Lingering queue and service question Pin
Mark Nischalke11-Jan-10 12:11
mentorMark Nischalke11-Jan-10 12:11 
Generalexcellent article Pin
Member 38247778-Oct-09 6:02
memberMember 38247778-Oct-09 6:02 
GeneralRe: excellent article Pin
Mark Nischalke8-Oct-09 12:38
mentorMark Nischalke8-Oct-09 12:38 
QuestionRe: excellent article Pin
Member 38247778-Oct-09 22:26
memberMember 38247778-Oct-09 22:26 
AnswerRe: excellent article Pin
Mark Nischalke9-Oct-09 2:07
mentorMark Nischalke9-Oct-09 2:07 
GeneralRe: excellent article Pin
Sunil Munikar9-Oct-09 3:23
memberSunil Munikar9-Oct-09 3:23 
GeneralRe: excellent article Pin
Mark Nischalke9-Oct-09 5:20
mentorMark Nischalke9-Oct-09 5:20 
GeneralUsing a non-visual class instead of a form Pin
Kobus du Bruyn3-Sep-09 7:35
memberKobus du Bruyn3-Sep-09 7:35 
GeneralRe: Using a non-visual class instead of a form Pin
Mark Nischalke3-Sep-09 8:00
mentorMark Nischalke3-Sep-09 8:00 
GeneralRe: Using a non-visual class instead of a form Pin
Kobus du Bruyn3-Sep-09 8:34
memberKobus du Bruyn3-Sep-09 8:34 
GeneralGetting Infinite Pin
divyesh143227-May-09 2:19
memberdivyesh143227-May-09 2:19 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150520.1 | Last Updated 28 Jun 2006
Article Copyright 2005 by Not Active
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid