Click here to Skip to main content
Click here to Skip to main content
Go to top

Query Notification using SqlDependency and SqlCacheDependency

, 3 Nov 2012
Rate this:
Please Sign up or sign in to vote.
The article demonstrates how you can use SQL Server Query Notification feature to get notification from database whenever the result of a command gets changed. Sample application demonstrating SqlDependency and SqlCacheDependency is also included.

Table of Contents

SqlDependency/r4.JPG

Well, it's been a nice time since I last posted in CodeProject. Meanwhile, I got CodeProject MVP for the year 2011, it's my second time; and also got Microsoft MVP for this year too. It's always nice to receive this award and without your support, I wouldn't be what I am now. Please read this.

Today I am going to discuss about a somewhat old but yet very useful feature introduced with SQL Server 2005. We will talk about the Query Notification feature of SQL Server and later introduce a practical approach to get you through with the concept.

Introduction

Query Notification is a feature that lets you subscribe for a query such that when the result of the query changes, a message is passed to the subscriber. SqlDependency is a special .NET class which lets you subscribe for an event and later on when the subscribed command gets notification from SQLServer, the OnChange event of the class gets called. On the other hand for ASP.NET application, there is SqlCacheDependency class which uses the same technique to invalidate the cache entry for your application when the underlying data changes its result. Hence, in both ways, your application gets benefited from this new feature and you don't need to poll the database every now and then to get updates. Once the notification is received, you can poll for once to the database to get the update and re-register for the next notification. The Query Notification feature uses Service Broker architecture to send messages to an external process. Let's get into it quick and build an application for you.

What is Service Broker?

Service Broker architecture allows you to build loosely coupled SQL Server instances so that the instances talk with each other using normal form of messaging. Service Broker uses TCP/IP to transmit messages form the network and hence allows encrypted messaging. It is both for applications which use SQL Server instance or for applications that distribute the work into more than one SQL server instance. Service Broker allows to use Queue to hold messages and hence the messages are processed one by one without the caller to wait to receive the message.

What is Query Notification?

Query Notification is a new feature which lets the application wait for the notification when the underlying query changes its result. The Database Engine uses Service Broker to deliver notification messages. So once you run a command that has notification associated with it, the Database Engine registers the notification request to it and maps with the existing command. Hence the notification gets executed once the result is changed.

Prerequisites to Create SQL Notification Requests

There are a number of database prerequisites you need to follow before you run your application so that your application receives the desired notification. Let's discuss them one by one:

  1. Enabling Service Broker: The first thing that you need for notification service is to enable Service Broker for the database. The Service Broker feature can be enabled in SQL server database using the following command:
    ALTER DATABASE MyDatabase SET ENABLE_BROKER

    Once you enable Service Broker, the application can get the Notification messages from SQL Server.

  2. Permission Set for the Database: Query Notification requires few permissions for the database. It requires Subscribe Query Notification to be enabled for the database.
    GRANT SUBSCRIBE QUERY NOTIFICATIONS TO database_principal

    In addition to this, it also needs Create Service, Create Queue and Create Procedure granted on the database where you want the subscription to run. On the other hand, to receive notification, the subscriber must have SEND/RECEIVE notification permission as well.

    GRANT RECEIVE ON Table TO login
    GRANT SEND ON SERVICE:://the service to login

    User must also have granted SELECT on Table to have notification work.

  3. Identify the Query: The final step is to identify the query for which you need to register the Notification. Query Notification is generally subscribed on a Query, but you might also put this on a Stored Procedure, for which the basic statements like IF, Try, Loop, etc. are allowed. In spite of this, there are few restrictions that you need to know before using this subscription.
    1. All the columnnames must be explicitly stated in the query. So use Select Col1, Col2 from Table rather than select * from Table. Hence selection cannot include * and TableName.* in the query.
    2. Table must use two part name, use dbo.TableName rather than TableName
    3. Unnamed or duplicate columns are not allowed
    4. Reference to a table with ComputedColumns are not allowed
    5. When you need aggregate column subscription, you must use a GROUPBY. Cube, Rollup or Having is not allowed
    6. Statement must not contain Pivot or Unpivot operators
    7. Union, Intersect and except is not allowed
    8. Statement should not contain a reference of a View
    9. It should not contain Distinct, Compute or Into
    10. NText, Text, Image Type in the query for notification is not allowed
    11. Rowset functions like OpenRowset or OpenQuery are not allowed
    12. Statement must not refer to a service broker Queue
    13. Top expression is also not allowed in the query.
    14. Set NoCount ON will invalidate the usage of Query Notification in a stored procedure
    15. Reference to server global variables (@@variableName) must also be excluded from the queries

    For full restriction sets, refer here.

What Does a Notification Message Contain?

A notification message is basically a Soap based XML message which a client consumes by receiving from the Service Broker Queue. The query notification message is of type http://schemas.microsoft.com/SQL/Notifications/QueryNotification. This message is a part of http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification contract. Hence, a service which registers the notification service should be bound to this contract. Hence, after the client receives the message, the client is supposed to close the conversation using End Conversation.

A sample query notification message can be:

<qn:QueryNotification
  xmlns:qn="http://schemas.microsoft.com/SQL/Notifications/QueryNotification"
  Type="change" Source="data" Info="insert">
    <qn:Message>http://mysite.microsoft.com/catalog.aspx?Category=Cars</qn:Message>
</qn:QueryNotification>

You can read more about Query Notification Message from here.

Check Notification Subscription at an instant

There is a special system Table that list all the subscriptions that are currently stored into the database. To check the notifications, try the following query:

select * from sys.dm_qn_subscriptions

You can also kill a subscription using:

KILL QUERY NOTIFICATION SUBSCRIPTION 3

where 3 is the subscription id. You can kill all subscriptions using All keyword in place of subscription id.

Implementation of Windows Client

As you already know the basics of Query notification, let's build an application that takes the benefit of this feature. ADO.NET exposes a class called SqlDependency which takes an SqlCommand into its constructor and automatically builds up the SqlNotificationRequest for you. On receipt of the notification from the SQLServer, the class automatically invokes OnChange event and hence the application gets the actual notification.

Steps to Create Notification

  1. Use SqlDependency.Start(connectionString) to start listening notification for a particular database instance. The ConnectionString argument provided with the method identifies the actual database instance.
  2. Create a SqlCommand object with a CommandText (StoredProcedure is also supported) and an SqlConnection pointing to the same database where the start is executed.
  3. Configure SqlCommand.Notification with an object of SqlNotificationRequest or rather use SqlDependency to create this for you. To use SqlDependency create an instance of SqlDependency and pass the command object within it.
  4. Subscribe the OnChange event for the SqlDependency object to get query notification
  5. Upon receipt of a notification the OnChange event will be raised. The event receives the SqlDependency object as sender and SqlNotificationEventArgs as event argument. Inside the EventHandler, you need to deregister the event as notifications are one shot deal. The SqlNotificationEventArgs on the other hand receives the information about the data, its source, etc. into it.
  6. SqlDependency.Stop(connectionString) will let you unregister the notification subscription for the current instance.
    public class SQLNotifier : IDisposable
    {
        public SqlCommand CurrentCommand { get; set; }
        private SqlConnection connection;
        public SqlConnection CurrentConnection
        {
            get
            {
                this.connection = this.connection ?? 
			new SqlConnection(this.ConnectionString);
                return this.connection;
            }
        }
        public string ConnectionString
        {
            get
            {
                return @"Data Source=VALUE-699460DF8\SQLEXPRESS;
                		Initial Catalog=Northwind;Integrated Security=True";
            }
        }

        public SQLNotifier()
        {
            SqlDependency.Start(this.ConnectionString);

        }
        private event EventHandler<SqlNotificationEventArgs> _newMessage;

        public event EventHandler<SqlNotificationEventArgs> NewMessage
        {
            add
            {
                this._newMessage += value;
            }
            remove
            {
                this._newMessage -= value;
            }
        }

        public virtual void OnNewMessage(SqlNotificationEventArgs notification)
        {
            if (this._newMessage != null)
                this._newMessage(this, notification);
        }
        public DataTable RegisterDependency()
        {

            this.CurrentCommand = new SqlCommand("Select [MID],[MsgString],
             			[MsgDesc] from dbo.Message", this.CurrentConnection);
            this.CurrentCommand.Notification = null;

            SqlDependency dependency = new SqlDependency(this.CurrentCommand);
            dependency.OnChange += this.dependency_OnChange;

            if (this.CurrentConnection.State == ConnectionState.Closed)
                this.CurrentConnection.Open();
            try
            {

                DataTable dt = new DataTable();
                dt.Load(this.CurrentCommand.ExecuteReader
			(CommandBehavior.CloseConnection));
                return dt;
            }
            catch { return null; }
        }

        void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            SqlDependency dependency = sender as SqlDependency;

            dependency.OnChange -= new OnChangeEventHandler(dependency_OnChange);

            this.OnNewMessage(e);
        }
        public void Insert(string msgTitle, string description)
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand("usp_CreateMessage", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.AddWithValue("@title", msgTitle);
                    cmd.Parameters.AddWithValue("@description", description);

                    con.Open();

                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    finally
                    {
                        con.Close();
                    }
                }
            }
        }

        #region IDisposable Members

        public void Dispose()
        {
            SqlDependency.Stop(this.ConnectionString);
        }

        #endregion
    }

SQLNotifier is a custom class created in the sample application to demonstrate the feature. In this class, we have two kinds of database activity. One, using RegisterDependency, which lets you register for the query notification service and also invokes the select statement to get the DataTable, and another is Insert which calls usp_CreateMessage to create an entry in the table. You might have noticed that I have used SqlDependency.Start in the constructor as stated above and Stop in the Dispose method. The class will generate a NewMessage event to the outside environment whenever the notification is received.

SqlDependency/r1.JPG

The application is very simple. It is a WPF client which shows a ListView to list all the data from the table Message. The SQLNotify is used to notify the changes. So when the Insert is called, even though there is no call of DataLoad from the code, the data will be automatically updated from the notification service. The XAML for the above UI looks like:

<Grid>
       <Grid.RowDefinitions>
           <RowDefinition Height="Auto"/>
           <RowDefinition Height="*" />
       </Grid.RowDefinitions>
       <StackPanel Grid.Row="0" Orientation="Horizontal" 
       VerticalAlignment="Center">
           <TextBlock Text="Title:" VerticalAlignment="Center" 
           	Margin="10,0,0,0"/>
           <TextBox Text="{Binding Title}"  Width="50"/>
           <TextBlock Text="Description:" VerticalAlignment="Center" 
           	Margin="10,0,0,0"/>
           <TextBox Text="{Binding Description}" Width="100"/>
           <Button Command="{Binding InsertMessage}"  Content="Execute Insert"/>
       </StackPanel>
       <ListView ItemsSource="{Binding Messages}" Grid.Row="1">
           <ListView.View>
               <GridView>
                   <GridViewColumn Header="Id" DisplayMemberBinding="{Binding Id}" />
                   <GridViewColumn Header="Title" 
			DisplayMemberBinding="{Binding Title}" />
                   <GridViewColumn Header="Description" 
			DisplayMemberBinding="{Binding Description}"  />
               </GridView>
           </ListView.View>
       </ListView>
   </Grid>

From the ViewModel, I have created an object of SQLNotifier which handles my notification service. The class generates OnNewMessage event whenever a QueryNotification is received. Thus the ViewModel gets notified from the registered eventhandler for the SQLNotifier class and updates the data from the database.

public Dispatcher UIDispatcher { get; set; }

public SQLNotifier Notifier { get; set; }
public MessageModel(Dispatcher uidispatcher)
{
    this.UIDispatcher = uidispatcher;
    this.Notifier = new SQLNotifier();

    this.Notifier.NewMessage += new EventHandler<SqlNotificationEventArgs>
				(notifier_NewMessage);
    DataTable dt = this.Notifier.RegisterDependency();

    this.LoadMessage(dt);
}

The constructor for the ViewModel receives Dispatcher, to ensure that data is updated from the UI thread. The call to RegisterDependency loads the data and passes it as a DataTable. The LoadMessage loads the data to the UI controls.

void notifier_NewMessage(object sender, SqlNotificationEventArgs e)
{
   this.LoadMessage(this.Notifier.RegisterDependency());
}

Whenever a new message is received, the RegisterDependency is called again and the data is loaded on the UI again. As I have already told you, notification is one shot, hence when one notification is received, you need to reregister the notification again for the next notification.

private void LoadMessage(DataTable dt)
{
    this.UIDispatcher.BeginInvoke((Action)delegate()
    {
        if (dt != null)
        {
            this.Messages.Clear();

            foreach (DataRow drow in dt.Rows)
            {
                Message msg = new Message
                {
                    Id = Convert.ToString(drow["MID"]),
                    Title = drow["MsgString"] as string,
                    Description = drow["MsgDesc"] as string
                };
                this.Messages.Add(msg);
            }
        }
    });
}

The LoadMessage loads the messages from the datatable to an ObservableCollection which is bound to the actual UI Control.

To test the application, run the application, insert data from the screen, you can see the data is updated. You can also run another instance of the same application or insert data directly in the database using Insert query and see the data is updated to the UI.

Implementation of an ASP.NET Web Client (Cache Dependency)

In case of ASP.NET application, you can also use this feature. There is a class SqlCacheDependency inside System.Web.Caching which lets you invalidate your cache whenever the underlying data is modified or SQL Server Query notification is received. Hence you can easily use this feature in ASP.NET to produce more sophisticated website.

Caching is very important for every ASP.NET site. Caching allows you to store frequently accessed data in memory available to all users and hence relieves the pressure to load data for each request. Thus whenever the website is accessed heavily, the caching lets the IIS to respond quickly. One of the major drawbacks is whenever the data is updated, how to load the new data from the database. Query Notification allows you to have Cache Dependency, such that whenever the SQL Server notifies that the data is modified, the Cache entry gets erased. Hence on next request based on this, you can reload the data from the database again.

Steps to Create ASP.NET Web Client Application Taking Help of Notification Service

  1. Create a blank Web site and add a Default page and Global.asax.
  2. Within Application_Start event of Global.asax, use SqlDependency.Start to register the SQL Server instance to get notification. The Application_Stop will deregister the SqlDependency.

  3. Create the UI for the webpage, for simplicity we have created the same web page as we saw for WPF application. The Save Button will Insert data into the database whilst a GridView will show the data.

  4. Data is loaded either from Cache.Get or from database depending upon the data in cache.

SqlDependency/r2.JPG

string connectionString = 
	ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;

void Application_Start(object sender, EventArgs e)
{
    System.Data.SqlClient.SqlDependency.Start(connectionString);
}

void Application_End(object sender, EventArgs e)
{
    System.Data.SqlClient.SqlDependency.Stop(connectionString);
}

Hence, first we retrieve the connectionString from the Web.config and register the SQL instance for Query Notification.

<div>
        Message Title:
        <asp:TextBox runat="server" ID="txtTitle" />
        <asp:RequiredFieldValidator ControlToValidate="txtTitle" 
        Display="Dynamic"
            runat="server" SetFocusOnError="true" 
            ErrorMessage="Title is left blank" />
        Message Description :
        <asp:TextBox runat="server" ID="txtDescription" />
        <asp:RequiredFieldValidator ControlToValidate="txtDescription" 
        runat="server" SetFocusOnError="true" 
        Display="Dynamic"
            ErrorMessage="Description is left blank" />
        <asp:Button ID="btnSave" runat="server" 
        OnClick="btnSave_Click" Text="Execute Insert" />
        <br />
        <asp:Label ID="lblDate" runat="server" /><br />
        <asp:GridView ID="grdMessages" runat="server">
        </asp:GridView>
    </div>

The above design is very simple. I have taken two textboxes, one button and one Label. The Label shows from where the data is retrieved. There is a Grid which shows the Data fetched from the database.

Now if you see the code for the UI, it looks like:

    private string ConnectionString = 
    	ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (IsPostBack)
            return;

        this.BindGrid();

    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        if (!string.IsNullOrWhiteSpace(this.txtTitle.Text) &&
        			!string.IsNullOrWhiteSpace(this.txtDescription.Text))
            this.Insert(this.txtTitle.Text, this.txtDescription.Text);
        this.BindGrid();

    }

    private void BindGrid()
    {
        DataTable dtMessages = (DataTable)Cache.Get("Messages");

        if (dtMessages == null)
        {
            dtMessages = this.LoadMessages();
            lblDate.Text = string.Format("Last retrieved DateTime : {0}", 
				System.DateTime.Now);
        }
        else
        {
            lblDate.Text = "Data Retrieved from Cache";
        }
        grdMessages.DataSource = dtMessages;
        grdMessages.DataBind();
    }
    private DataTable LoadMessages()
    {
        DataTable dtMessages = new DataTable();

        using (SqlConnection connection = new SqlConnection(this.ConnectionString))
        {
            SqlCommand command = new SqlCommand("Select [MID],
            			[MsgString],[MsgDesc] from dbo.Message", connection);

            SqlCacheDependency dependency = new SqlCacheDependency(command);

            if (connection.State == ConnectionState.Closed)
                connection.Open();

            dtMessages.Load(command.ExecuteReader(CommandBehavior.CloseConnection));

            Cache.Insert("Messages", dtMessages, dependency);
        }

        return dtMessages;
    }
    public void Insert(string msgTitle, string description)
    {
        using (SqlConnection con = new SqlConnection(this.ConnectionString))
        {
            using (SqlCommand cmd = new SqlCommand("usp_CreateMessage", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@title", msgTitle);
                cmd.Parameters.AddWithValue("@description", description);

                con.Open();

                try
                {
                    cmd.ExecuteNonQuery();
                }
                finally
                {
                    con.Close();
                }
            }
        }
    }

The code looks pretty much the same. The Insert inserts the data into database and LoadMessage loads messages to the Grid. Now the basic difference is the call to Cache.Get("Messages"), this checks whether the data is available in Cache (only when the underlying data is not modified) or the data needed to be reloaded. Based on this, the Label Text is set. While Loading messages, we put the entire datatable inside Cache with SqlCacheDependency as its dependency parameter. And as I have already told you, SqlCacheDependency will invalidate the cache entry based on the SQL server Query notification.

Does Query Notification have Performance Costs?

As this thought in mind, I tried to see how the performance of the query been affected whenever we subscribe for notification. Let's see the Query Execution plan from SQLServer Management Studio to get an idea about the same.

r3.JPG - Click to enlarge image

The execution plan shows that 43% of the cost of a single insert is estimated for notification assertion. Hence there is definitely a cost associated with the Query Notification in terms of SQL Server.

Troubleshoot

Troubleshooting is an important part for this kind of application. Here I will keep an update of all the Troubleshoot information that I find. If you find any while running the sample, please let me know also.

  1. Infinite Loop for notification: There may be certain scenarios where your application gets notification as soon as you register. The main cause of this is either you register to a query that does not meet the criteria specified for the Query notification or your application regenerates the notification from inside. Please make sure you don't use * instead of column names and also used dbo.TableName instead of TableName.
  2. Could not obtain information about Windows NT user/group: If you see this exception, that means the user which accesses the notification service is not authorized to get it. Try running ALTER AUTHORIZATION ON DATABASE::Northwind TO sa to solve the issue.

History

  • Initial draft: 10th January, 2011

Conclusion

Even though I find a lot of posts over the internet on the same topic, but there is no single post which clarifies the entire thing. So I thought of posting this myself. I hope this article will come in handy for you. Please put in your feedback to enrich the article for the future. Thank you for reading.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Abhishek Sur
Architect
India India
Did you like his post?
 
Oh, lets go a bit further to know him better.
Visit his Website : www.abhisheksur.com to know more about Abhishek.
 
Abhishek also authored a book on .NET 4.5 Features and recommends you to read it, you will learn a lot from it.
http://bit.ly/EXPERTCookBook
 
Basically he is from India, who loves to explore the .NET world. He loves to code and in his leisure you always find him talking about technical stuffs.
 
Presently he is working in WPF, a new foundation to UI development, but mostly he likes to work on architecture and business classes. ASP.NET is one of his strength as well.
Have any problem? Write to him in his Forum.
 
You can also mail him directly to abhi2434@yahoo.com
 
Want a Coder like him for your project?
Drop him a mail to contact@abhisheksur.com
 
Visit His Blog

Dotnet Tricks and Tips



Dont forget to vote or share your comments about his Writing
Follow on   Twitter   Google+

Comments and Discussions

 
SuggestionGood article but one small mistake PinmemberPareshDehadray13-Nov-12 4:26 
GeneralRe: Good article but one small mistake PinmemberAbhishek Sur3-Nov-12 6:08 

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 | Mobile
Web01 | 2.8.140916.1 | Last Updated 3 Nov 2012
Article Copyright 2011 by Abhishek Sur
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid