Click here to Skip to main content
Click here to Skip to main content

Using SqlDependency for data change events

By , 28 Jun 2006
Rate this:
Please Sign up or sign in to vote.

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)

About the Author


Comments and Discussions

 
GeneralMy vote of 5 PinmemberPatrick-Wayfarer5-Jun-13 15:12 
QuestionMy vote of 5 - Excellent article and very helpful!!!! But...there is a small detail... [modified] PinmemberBrian C Hart9-Apr-13 12:33 
QuestionWhy it invoke all times? PinmemberAshuTetra18-Jan-13 18:52 
AnswerRe: Why it invoke all times? Pinmemberalekcarlsen1-Oct-13 0:39 
GeneralMy vote of 5 Pinmemberabdusalam.benhaj16-Jan-13 2:04 
GeneralMy vote of 5 PinmemberEdo Tzumer27-Nov-12 19:18 
GeneralMy vote of 5 PinmvpKanasz Robert24-Sep-12 6:12 
Questionwill this application work with SQLite ? PinmemberBhimraj Ghadge19-Apr-12 20:40 
AnswerRe: will this application work with SQLite ? PinmvpMark Nischalke20-Apr-12 1:11 
GeneralMy vote of 5 PinmemberSandeep Ramani13-Mar-12 21:50 
GeneralRe: My vote of 5 PinmvpMark Nischalke26-Mar-12 15:13 
QuestionWhat are the other ways PinmemberMayank Bhardwaj119-Nov-11 2:05 
GeneralNice Article... I need a clarification Pinmemberraghavendran.s@target.com12-Jun-11 23:47 
Questionreal world usage? Pinmemberwalex481127-Apr-11 22:17 
AnswerRe: real world usage? PinmvpMark Nischalke28-Apr-11 2:27 
GeneralWindows Form VS WPF Pinmemberenergie4-Mar-11 12:52 
QuestionClarification PinmemberElfman_NE9-Dec-10 10:21 
AnswerRe: Clarification PinmvpMark Nischalke9-Dec-10 10:40 
NewsSQL Server 2008 64/vista 64/vs2008 PinmemberPandele Florin2-Nov-10 8:27 
GeneralMy vote of 5 PinmemberAlejandro Miralles27-Sep-10 8:52 
Generalexcellent, just one question PinmemberleollI18-Jul-10 18:34 
GeneralRe: excellent, just one question PinmvpMark Nischalke19-Jul-10 9:21 
GeneralSqlDependency Article PinmemberMaurice7526-Feb-10 9:18 
GeneralRe: SqlDependency Article PinmvpMark Nischalke26-Feb-10 12:11 
GeneralRe: SqlDependency Article PinmemberMaurice7528-Feb-10 11:00 

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 | Mobile
Web04 | 2.8.140415.2 | Last Updated 28 Jun 2006
Article Copyright 2005 by Not Active
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid