Table of Contents
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.
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.
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.
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.
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:
- 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:://the service 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.
- 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.
- Table must use two part name, use
dbo.TableName rather than TableName
- Unnamed or duplicate columns are not allowed
- Reference to a table with
ComputedColumns are not allowed
- When you need aggregate column subscription, you must use a
GROUPBY. Cube, Rollup or Having is not allowed
- Statement must not contain
Pivot or Unpivot operators
Union, Intersect and except is not allowed - Statement should not contain a reference of a View
- It should not contain
Distinct, Compute or Into
NText, Text, Image Type in the query for notification is not allowed Rowset functions like OpenRowset or OpenQuery are not allowed - Statement must not refer to a service broker Queue
- Top expression is also not allowed in the query.
- Set
NoCount ON will invalidate the usage of Query Notification in a stored procedure
- Reference to server global variables (
@@variableName) must also be excluded from the queries
For full restriction sets, refer here.
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.
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.
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.
- 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.
- 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 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.
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.
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.
- 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 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.
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.
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.
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 don't 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.
- Initial draft: 10th January, 2011
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.
