Click here to Skip to main content
15,868,016 members
Articles / Desktop Programming / Windows Forms
Article

Using SqlDependency for data change events

Rate me:
Please Sign up or sign in to vote.
4.71/5 (74 votes)
28 Jun 2006CDDL4 min read 647.3K   18.1K   190   102
Using SQL Server 2005 and SqlDependency to keep your application's data updated.

Image 1

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.

SQL
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.

C#
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.

Image 2

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

C#
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:

SQL
SELECT * FROM Message

This will work:

SQL
SELECT ID, Message FROM dbo.Message

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

C#
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.

C#
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.

C#
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)



Comments and Discussions

 
QuestionBRAVO Pin
ecodeprojetb8-Sep-20 23:39
ecodeprojetb8-Sep-20 23:39 
QuestionOnNewMessage not being Invoked Pin
Member 142067561-Apr-19 18:15
Member 142067561-Apr-19 18:15 
GeneralMy vote of 5 Pin
damianrodriguez88@gmail.com6-Jul-18 5:14
damianrodriguez88@gmail.com6-Jul-18 5:14 
QuestionNiiiiice :) Pin
edwarmartinez2-Mar-16 5:40
edwarmartinez2-Mar-16 5:40 
QuestionScript to create sample database Pin
bsws015-Jan-16 15:36
bsws015-Jan-16 15:36 
QuestionSqlDependency Pin
amir farashah11-Mar-15 0:04
amir farashah11-Mar-15 0:04 
AnswerRe: SqlDependency Pin
M Saqib Ali22-Jun-15 7:53
M Saqib Ali22-Jun-15 7:53 
GeneralMy vote of 4 Pin
neil04195-Feb-15 4:50
neil04195-Feb-15 4:50 
Questionuseful Pin
lqht3-Dec-14 3:43
lqht3-Dec-14 3:43 
QuestionGet store procedure Pin
mert Aslı22-Sep-14 22:17
mert Aslı22-Sep-14 22:17 
GeneralMy vote of 5 Pin
Patrick-Wayfarer5-Jun-13 15:12
professionalPatrick-Wayfarer5-Jun-13 15:12 
QuestionMy vote of 5 - Excellent article and very helpful!!!! But...there is a small detail... Pin
Brian C Hart9-Apr-13 12:33
professionalBrian C Hart9-Apr-13 12:33 
QuestionWhy it invoke all times? Pin
Ashu87118-Jan-13 18:52
Ashu87118-Jan-13 18:52 
AnswerRe: Why it invoke all times? Pin
alekcarlsen1-Oct-13 0:39
alekcarlsen1-Oct-13 0:39 
GeneralMy vote of 5 Pin
abdusalam.benhaj16-Jan-13 2:04
abdusalam.benhaj16-Jan-13 2:04 
GeneralMy vote of 5 Pin
Joezer BH27-Nov-12 19:18
professionalJoezer BH27-Nov-12 19:18 
GeneralMy vote of 5 Pin
Kanasz Robert24-Sep-12 6:12
professionalKanasz Robert24-Sep-12 6:12 
Questionwill this application work with SQLite ? Pin
Bhimraj Ghadge19-Apr-12 20:40
Bhimraj Ghadge19-Apr-12 20:40 
AnswerRe: will this application work with SQLite ? Pin
Not Active20-Apr-12 1:11
mentorNot Active20-Apr-12 1:11 
GeneralMy vote of 5 Pin
Sandeepkumar Ramani13-Mar-12 21:50
Sandeepkumar Ramani13-Mar-12 21:50 
GeneralRe: My vote of 5 Pin
Not Active26-Mar-12 15:13
mentorNot Active26-Mar-12 15:13 
QuestionWhat are the other ways Pin
Mayank Bhardwaj119-Nov-11 2:05
Mayank Bhardwaj119-Nov-11 2:05 
in the beginning of your article you said

Quote:
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.


What are other good ways to achieve this, can you direct me to some good reference material for those ?
GeneralNice Article... I need a clarification Pin
raghavendran.s@target.com12-Jun-11 23:47
raghavendran.s@target.com12-Jun-11 23:47 
Questionreal world usage? Pin
walex481127-Apr-11 22:17
walex481127-Apr-11 22:17 
AnswerRe: real world usage? Pin
Not Active28-Apr-11 2:27
mentorNot Active28-Apr-11 2:27 

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.