Click here to Skip to main content
13,048,584 members (90,952 online)
Click here to Skip to main content
Add your own
alternative version


79 bookmarked
Posted 1 Feb 2006

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

, 1 Feb 2006
Rate this:
Please Sign up or sign in to vote.
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.


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:

//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);
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';
    EXEC sp_configure 'clr enabled' , '1'
  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:

  4. Grant Permissions

    Grant permission to the user using the following statement:


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.


For more information, you can go through this article:

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


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))
                SqlDependency depend = new SqlDependency(cmd);
                depend.OnChange += new 
                SqlDataReader rdr = cmd.ExecuteReader();
                //perform any operation here
                while (rdr.Read())
            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";


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


About the Author

India India
No Biography provided

You may also be interested in...


Comments and Discussions

GeneralWorks (sort-of) in SQL Server Express 2005 (aka SSE) Pin
wwalseth7-Dec-06 14:57
memberwwalseth7-Dec-06 14:57 
Generalhelp please Pin
csmba15-Mar-06 13:10
membercsmba15-Mar-06 13:10 
GeneralRe: help please Pin
rajiv40415-Mar-06 19:39
memberrajiv40415-Mar-06 19:39 
GeneralRe: help please Pin
csmba15-Mar-06 20:13
membercsmba15-Mar-06 20:13 
nice try, but it will not be that simple...

I did my home work. it is not a trivial mistake like that.. well, it might be, but that this one. to be more detailed:
I can repeat this action many times:
1. change data in DB
2. watch log line "call back function called, reloading" being written
3. go back to step 1.

It works great! BUT, if I walk away from the computer for 6-10 minuts without repeating step 1-3, then after 10 minuts I do step 1 and...
nothing happens!

Where the H%^% is the notification? what happens to it?
GeneralRe: help please Pin
CoderQue13-Aug-06 7:19
memberCoderQue13-Aug-06 7:19 
GeneralRe: help please Pin
grumpy-wan6-May-07 16:12
membergrumpy-wan6-May-07 16:12 
GeneralRe: help please Pin
shimesrle19-May-07 11:41
membershimesrle19-May-07 11:41 
AnswerSome mistakes Pin
Marcel 876549-Feb-06 2:53
memberMarcel 876549-Feb-06 2:53 
GeneralRe: Some mistakes Pin
rajiv40415-Mar-06 19:42
memberrajiv40415-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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170713.1 | Last Updated 2 Feb 2006
Article Copyright 2006 by rajiv404
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid