Query Notification using SqlDependency and SqlCacheDependency

Well, its been a nice time since I last posted in codeproject. Meanwhile, I got Codeproject MVP for the year 2011, its my second time; and also got Microsoft MVP for this year too. Its always nice to receive this award and without your support I wouldnt 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 iss a speacial .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 benifitted from this new feature and you dont need to poll the data base 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 next notification. The Query Notification feature uses Service Broker architecture to send messages to external process. Lets 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 talks 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 application 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 whthout 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 have 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. Lets discuss them one by one :

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.
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:://theservice to login
```
User must also have granted SELECT on Table to have notification work.
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 explicitely 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 is 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 an 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 subscription using All keyword in place of subscription id.

Implementation of Windows Client

As you already know the basics of Query notification, lets 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 notfication.

Steps to create notification:

Use SQLDependency.Start(connectionString) to start listening notification for a particular database instance. The ConnectionString argument provided with the method idetifies the actual database instance.
Create a SQLCommand object with a CommandText (StoredProcedure is also supported) and an SQLConnection pointing to the same database where the start is executed.
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.
Subscribe the OnChange event for the SQLDependency object to get query notification
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.
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 kind 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.

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 and 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 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 releives 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 drawback 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 notifices 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

Create a blank Web site and add a Default page and Global.asax.
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.
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.
Data is loaded either from Cache.Get or from database depending upon the data in cache.

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 priety 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 wth 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 perfomance of the query been affected whenever we subscribe for notification. Lets see the Query Execution plan from SQLServer Management Studio to get an idea about the same.

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.

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 dont use * instead of column names and also used dbo.TableName instead of TableName.
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 lots of post over the internet on the same topic, but there is no single post which clears the entire thing. So I thought of posting this myself. I hope this article will come handy to you. Please put your feedback to enrich the article for future. Thank you for reading.

关键字 query,notification,using,sqldependency,and,sqlcachedependency

推荐.NET配套的通用数据层ORM框架：CYQ.Data 通用数据层框架

IT技术博客

Table of Contents

Introduction

What is Service Broker ?

What is Query Notification?

Prerequisites to create SQL Notification Requests

What does a notification message contain?

Check Notification Subscription at an instant

Implementation of Windows Client

Steps to create notification:

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

Steps to create ASP.NET web client application taking help of notification service

Does Query Notification have performance costs ?

Troubleshoot

History

Conclusion

发表评论

公告信息

文章搜索

文章分类

文章档案

最新文章

最新评论

阅读排行榜

评论排行榜

友情链接