Using SqlDependency for data change events

• Download source files - 21.5 Kb

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.