Click here to Skip to main content
15,867,686 members
Articles / Database Development / SQL Server
Article

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

Rate me:
Please Sign up or sign in to vote.
4.62/5 (25 votes)
1 Feb 20062 min read 94.7K   80   9
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.

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.

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

The dependency has to be started using:

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

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

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

    SQL
    ALTER DATABASE databasename SET ENABLE_BROKER
  4. Grant Permissions

    Grant permission to the user using the following statement:

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

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:

C#
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";
}

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralWorks (sort-of) in SQL Server Express 2005 (aka SSE) Pin
wwalseth7-Dec-06 14:57
wwalseth7-Dec-06 14:57 
Generalhelp please Pin
csmba15-Mar-06 13:10
csmba15-Mar-06 13:10 
GeneralRe: help please Pin
rajiv40415-Mar-06 19:39
rajiv40415-Mar-06 19:39 
GeneralRe: help please Pin
csmba15-Mar-06 20:13
csmba15-Mar-06 20:13 
GeneralRe: help please Pin
CoderQue13-Aug-06 7:19
CoderQue13-Aug-06 7:19 
GeneralRe: help please Pin
grumpy-wan6-May-07 16:12
grumpy-wan6-May-07 16:12 
GeneralRe: help please Pin
shimesrle19-May-07 11:41
shimesrle19-May-07 11:41 
AnswerSome mistakes Pin
Marcel 876549-Feb-06 2:53
Marcel 876549-Feb-06 2:53 
GeneralRe: Some mistakes Pin
rajiv40415-Mar-06 19:42
rajiv40415-Mar-06 19:42 

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.