Using Query Notifications in .NET 2.0 to handle ad-hoc data refreshes

Introduction

Query Notification in SQL Server 2005 solves the problem of having to maintain a polling database to get the updated data. The new notification service of SQL Server 2005 can perform this amazing task of notifying a .NET code when any DML operations are performed to a specified table. The notification cycle starts from the .NET 2.0 application through the object named SqlDependency in the System.Data.SqlClient namespace, the object just takes the SQLCommand object which has the query.

SqlCommand cmd = new SqlCommand("SELECT ID, Name, " + 
   "Address FROM dbo.employee", SQLConnectionObject));
SqlDependency depend = new SqlDependency(cmd);

The dependency has to be started using:

SqlDependency.Start(connstring);
//It takes the connection string as a parameter.

Now, the question is how and who would get notified?

The answers lies in the SQLDependency instance you make. You can delegate an event handler on the OnChange event of the SqlDependency object.

SqlDependency depend = new SqlDependency(cmd);
SqlDependency.Start(connstring);
depend.OnChange += new OnChangeEventHandler(MyOnChanged);

So when the notification arrives, it goes to the specified handler.

Preparing SQL Server 2005 for Notifications

1. Install the Notification Service

   You will have to install the SQL Server Notification Service from the SQL Server 2005 installation package. If it is already installed, you will surely see a ‘Notification Services’ folder in the SQL Server Management Studio.

2. Enable the .NET CLR

   You will have to enable the .NET CLR for SQL Server 2005, because the service broker uses it.

   To enable .NET CLR, execute this command:

   EXEC sp_configure 'show advanced options' , '1';
   go
   reconfigure;
   go
   EXEC sp_configure 'clr enabled' , '1'
   go
   reconfigure;
   go

3. Enable Broker

   Query Notifications use the Service Broker SERVICEs. A SERVICE in this case is a destination for asynchronous messages; the messages can be required to follow a specific set of rules known as a CONTRACT. A Service Broker SERVICE is always associated with a QUEUE that is the physical message destination.

   The broker has to be enabled for each database using the following statement:

   ALTER DATABASE databasename SET ENABLE_BROKER

4. Grant Permissions

   Grant permission to the user using the following statement:

   GRANT SUBSCRIBE QUERY NOTIFICATIONS TO username

About Notifications

The created notification is always executed once. So when a notification arrives, its job is done, and to continue receiving notification, you will have to recreate the SqlDependency on the handler.

The best usage of the query notification is in the case of caching. You don’t have to poll the database from time to time to find out any data changes. You can invalidate the cache in the handler and get the data table again, and along with that re-create the SqlDependency with a fresh notification.

Please note, since the SQL Server 2005 Express does not support the notification service, you will not be able to perform Query Notification in SQL Server 2005 Express.

Links

For more information, you can go through this article:

• Query Notifications in ADO.NET 2.0

This article covers a custom notification query using SqlNotificationRequest, Service Broker, and Queues.

Sample

The following sample code snippet uses the SQL Server notification feature:

using System.Data.SqlClient;
public void button1_Click(object sender, EventArgs e)
{
    string connstring = @"Data Source=SQLServer2005;" + 
       @"Initial Catalog=TestDB;Persist Security Info" + 
       @"=True;User ID=username;Password=password;" + 
       @"Pooling=True;Min Pool Size=5;Max Pool Size=100;" + 
       @"Asynchronous Processing=True;" + 
       @"MultipleActiveResultSets=True;Connect Timeout=15";
    List<string> list = new List<string>();
    using (SqlConnection conn = new SqlConnection(connstring))
        using (SqlCommand cmd = new SqlCommand("SELECT" + 
               " SELECT ID, Name, Address" + 
               " FROM dbo.employee", conn))
        {
            try
            {
                SqlDependency depend = new SqlDependency(cmd);
                SqlDependency.Start(connstring);
                depend.OnChange += new 
                  OnChangeEventHandler(MyOnChanged);
                conn.Open();
                SqlDataReader rdr = cmd.ExecuteReader();
                //perform any operation here
                while (rdr.Read())
                    list.Add(rdr[0].ToString());
                rdr.Close();
            }
            catch (Exception ee)
            {
                    string msg = ee.Message;
            }
        }
}

static void MyOnChanged(object caller, SqlNotificationEventArgs e)
{
    //to test place a break point here 
    //and change any data in the specified table
    string msg = "Notified";
}