@trydis

Ramblings on coding related topics

© 2016. All rights reserved.

Getting started with query notifications

08 Mar 2010

The SqlDependency class was introduced in .NET 2.0, but still there are many developers not aware of this functionality, or many not using it because it can be somewhat cumbersome to make it work properly.

This class gives you the ability to create a dependency between your application and SQL server 2005/2008, which in turn will notify your application whenever the result set of your query changes. This is ideal for scenarios that involve caching or where you normally would use some sort of polling mechanism.

It’s worth that the applications that will benefit the most from query notifications are those that mostly read data and where updates are infrequent. This is because query notification introduces an overhead, as it has to check for active subscribers, and raise notifications, whenever the table data is altered.

Database permissions

This is the part that causes most of the trouble for people trying to get query notifications working. It can be very hard to troubleshoot what’s wrong, because quite often you get no error message at all, stating what the possible cause is. The SQL statements below should make sure you’re good to go.

Open up your query window and run the following command, to make sure you’re working against the correct database:

USE DatabaseName

The first thing you need to check is that the service broker on your SQL server is running. This is is done by running the command below, which should return “1”.

SELECT is_broker_enabled FROM sys.databases where name = 'DatabaseName'

If the command above returned “0” you need to run the command below, then rerun the command above to make sure it now returns “1”:

ALTER DATABASE DatabaseName SET ENABLE_BROKER

Then you need to give the database user you’re going to use in your application some permissions, to allow it to create the needed procedure, queue and service:

GRANT CREATE PROCEDURE TO DatabaseUser
GRANT CREATE QUEUE TO DatabaseUser
GRANT CREATE SERVICE TO DatabaseUser

You then need to allow your user to be able to subscribe to query notifications, by running the following command. Make sure that the database user is the same as you’re specifying in your connection string.

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO DatabaseUser

To successfully register a notification, the database user needs SELECT privileges on the table that it’s trying to query. You only need to do this if your login isn’t sysadmin or db_owner. The schema value below will for the majority of people be “dbo”.

GRANT SELECT ON OBJECT::Schema.TableName TO DatabaseUser

The last permission you will have to give the database user is to allow it to receive notifications sent to the message queue by SQL server:

GRANT RECEIVE ON QueryNotificationErrorsQueue TO DatabaseUser

To make sure that all the permissions have been granted, you can run the following command in the query window:

exec sp_helprotect NULL, 'DatabaseUser'

Some code please!

In this example I’m going to create a simple console application that will receive query notifications. Exception handling is not implemented in this example, to keep it simple.

The first thing we’re doing is checking that we’re allowed to request notifications. If we’re allowed to do that we start listening for notifications from the SQL server specified in the connection string. We then register our dependency, which is done by specifying an SQL query and associating an instance of the SqlDependency class with our SqlCommand instance. The code is shown below…

static void Main()
{
    if (CanRequestNotifications())
    {
        SqlDependency.Start(ConnectionString);

        Console.WriteLine("Started the listener...");

        RegisterDependency();

        Console.WriteLine("Press enter to quit...");
        Console.ReadLine();

        Console.WriteLine("Stopping the listener...");

        SqlDependency.Stop(ConnectionString);
    }
    else
        Console.WriteLine("Not allowed to request notifications!");
}

This method will return true if all the correct permissions are set.

private static bool CanRequestNotifications()
{
    SqlClientPermission permission = new SqlClientPermission(PermissionState.Unrestricted);
    try
    {
        permission.Demand();
        return true;
    }
    catch (Exception)
    {
        return false;
    }
}

This method will register the dependency with the SQL server and hook up an event handler, which will be invoked whenever the result set of the SQL query you’ve specified has changed. There as some restrictions on what type of queries you can specify, read more about the limitations here.

private static void RegisterDependency()
{
    using (SqlConnection connection = new SqlConnection(ConnectionString))
    {
        SqlCommand command = new SqlCommand("select Id from dbo.table", connection) { Notification = null };

        SqlDependency dependency = new SqlDependency(command);
        dependency.OnChange += DependencyOnChange;

        connection.Open();

        SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);

        if (dr != null)
        {
            // Cache or display the data if you need to

            dr.Close();
        }
    }
}

This is the method that will be invoked whenever the result set of your query has changed. As you can see we need to unhook the event handler and then register a new one.

A query notification will be sent if any of the following events occur:

private static void DependencyOnChange(object sender, SqlNotificationEventArgs e)
{
    SqlDependency dependency = (SqlDependency)sender;
    dependency.OnChange -= DependencyOnChange;

    Console.WriteLine(DateTime.Now.ToLongTimeString() +
        " Info:" + e.Info +
        " Type:" + e.Type +
        " Source:" + e.Source);

    RegisterDependency();
}

That’s it! You should now be able to enjoy the benefits of query notification in your own applications!

Related Posts