Click here to Skip to main content
Click here to Skip to main content

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.

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:

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

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

Share

About the Author

rajiv404

India India
No Biography provided

Comments and Discussions

 
GeneralWorks (sort-of) in SQL Server Express 2005 (aka SSE) Pinmemberwwalseth7-Dec-06 15:57 
Generalhelp please Pinmembercsmba15-Mar-06 14:10 
GeneralRe: help please Pinmemberrajiv40415-Mar-06 20:39 
GeneralRe: help please Pinmembercsmba15-Mar-06 21:13 
GeneralRe: help please PinmemberCoderQue13-Aug-06 8:19 
GeneralRe: help please Pinmembergrumpy-wan6-May-07 17:12 
GeneralRe: help please Pinmembershimesrle19-May-07 12:41 
AnswerSome mistakes PinmemberMarcel 876549-Feb-06 3:53 
GeneralRe: Some mistakes Pinmemberrajiv40415-Mar-06 20:42 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.1411023.1 | Last Updated 2 Feb 2006
Article Copyright 2006 by rajiv404
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid