Understanding SQL Server 2005 Notification Services with ASP.NET 2.0: ASP Alliance

Print Add To Favorites Email To Friend Rate This Article
Understanding SQL Server 2005 Notification Services with ASP.NET 2.0

page 2 of 6

by Sudeep G
Feedback

Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 30909/ 39

Article Contents:

Notification based configuration

A notification based dependency can be directly configured using the OutputCache directive of an ASPX page with the CommandNotification attribute. This indicates to ASP.NET runtime that a notification based dependency should be created for the page or the DataSource control. Let us first look at setting up Notification services.

1. Run SQL Server 2005 Setup and then select Notification Services along with other features that you wish to use.

2. Grant login and query notification rights to the appropriate Windows account on your Sql Server 2005 machine. To allow login for NETWORK SERVICE, open SQL Server management studio, expand Security, and add NETWORK SERVICE to the list of allowed login. Not only do you need to add this account to the list of allowed logins, but also add db_datareader and db_datawriter permissions to your database.

NOTE: The username will be NT AUTHORITY\NETWORK SERVICE if SQL Server is installed on the same machine as the web server. If SQL server is on a remote computer and you are using NETWORK SERVICE as the application pool identity, then add NT AUTHORITY\<computername>$.

The security identity running the query must have rights to register queries for notification in Sql Server 2005. This right can be granted with the following T-SQL command from SQL Server Query analyzer.

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO username.

The security identity running the query must also have rights to send query notifications from Sql Server 2005. This right can be granted with the following T-SQL command:

GRANT SEND ON SERVICE::SqlQueryNotificationService TO username.

NOTE: SQLExpress does not support Query notifications and therefore will not work. For NETWORK SERVICE account, enclose the username in double quotes.

ASP.NET 2.0

1. Create your web page.

2. Enable cache notification for SqlCacheDependency.

3. Configure your page output caching. Set the "SqlDependency" attribute on the page to "CommandNotification." Any commands issued against a SQL Server 2005 back-end will automatically get wired up to use SQL Server 2005 query notifications. You can do the same thing on a SqlDataSource control.

Listing 1: On an ASPX page

<%@ OutputCache Duration="999999" SqlDependency="CommandNotification" 
VaryByParam="none" %>

Listing 2: On a datasource control

<asp:SqlDataSource EnableCaching="true" SqlCacheDependency="CommandNotification" 
CacheDuration="Infinite" />

Call System.Data.SqlClient.SqlDependency.Start() & System.Data.SqlClient.SqlDependency.Stop() methods somewhere in the application before the first SQL query is executed. You could ideally add these methods in Application_Start() & Application_End() events in global.asax file. These methods require a connection string as its parameter. SqlDependency.Start() starts a listener for receiving dependency change notifications from the instance of SQL Server specified by the connection string and SqlDependency.Stop() stops listening for change notifications. You can read the connection string from a web.config or code it per your requirement. The line of code below reads the second connection string from the local web.config.

Listing 3

System.Data.SqlClient.SqlDependency.Start(
ConfigurationManager.ConnectionStrings[1].ConnectionString.ToString()); 
System.Data.SqlClient.SqlDependency.Stop(
ConfigurationManager.ConnectionStrings[1].ConnectionString.ToString());

Note: ConfigurationManager.ConnectionStrings[n] is zero based and allows reading the desired connection string. You could also use the connection string name, which is recommended to make the code more readable. Eg: ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString

Whenever a command is issued to SQL Server, ASP.NET and ADO.NET will automatically create a cache dependency that listens to change notifications sent from the SQL Server. As data is changed in SQL Server, these notifications will cause the cached queries to be invalidated on the web server. The next time a page or data source control associated with the dependency is requested, the page or data source control will be executed again as opposed to serving cached information.

Example 1: In the first example I used I have a simple ASPX page with a SqlDataSource control and a GridView control that is bound to the data source. I am caching the page using the output cache parameter as described above and then also adding Response.Write("Last Updated: " + DateTime.Now.ToString()) in the Page_Load event so that I can see the time when the data was last updated. I also have a Global.asax page that has the SqlDependency.Start and SqlDependency.Stop methods.

It is pretty easy to see this in action. When you run the page, you should see the Last Update value remain static as long as the underlying data changes. If you manually use SQL Server manager to change the underlying data, then the page should be updated. You could also use SQLProfiler to see what SQL Server is doing.

Example 2: In the second example, I have an ASPX page without the OutputCache directive. I then set the caching options for the SqlDataSource control to cache only the underlying data and not the entire page. I have then configured the following properties for the SqlDataSource control and then bind the GridView to this data source control.

· SelectCommand

· CacheDuration

· EnableCaching

· SqlCacheDependency

Listing 4

<asp:SqlDataSource  ID="SqlDataSource1" runat="server" 
ConnectionString="<%$ ConnectionStrings:ASPNETDemoConnectionString %>" 
SelectCommand="SELECT ID, Name FROM dbo.Customers" 
CacheDuration="Infinite" EnableCaching="True" 
SqlCacheDependency="CommandNotification"> 
</asp:SqlDataSource>

In both examples I also have the Global.asax page that has the SqlDependency.Start and SqlDependency.Stop methods. You may use the SQL Profiler with a custom tracing template (Just some basic logging enabled to illustrate the point) to see the notification that is sent to the application which then invalidates the DataSet cache. I have used the ClientProcessID column in SQL profiler to map it to the web application that is setting up the subscription. The w3wp.exe (in task manager) process hosts the web application. Also, check the account that is making the request. Depending on how your application is configured (impersonation enabled/disabled or using web service calls with specific credentials), you will need to use the appropriate account in the GRANT statements discussed earlier to allow subscription and notification for queries.

Common issues that prevent queries from working with SQL Server 2005 query notifications

If it appears that data is not being cached, and instead is being executed on every page request, it is likely that either the query does not follow the constraints required by SQL Server for notification or that SQL Server generated an error when attempting to setup notifications for that query. As far as I know, if an error condition occurs when attempting to setup a cache dependency in ASP.NET, it will silently fail with the end result being that the cache dependency is always invalid and any associated queries are always executed on each page request.

There are several restrictions on the syntax of queries that can be used in query notifications. It is documented in SQL Server books online and also included below:

· Queries must explicitly include column names in the SELECT statement. Using "SELECT *" results in a query that will not be registered with SQL Server query notifications.

· Table names in queries must include the ownername. Example: A query definition against the authors table in the pubs database, the query must reference the table as "dbo.authors."

· All tables referenced in the statement must be in the same database.

· The statement may not use unnamed columns or duplicate column names.

· The statement must not reference tables with computed columns.

· The projected columns in the SELECT statement may not contain aggregate expressions unless the statement uses a GROUP BY expression. When a GROUP BY expression is provided, the select list may contain the aggregate functions COUNT_BIG() or SUM(). However, SUM() may not be specified for a nullable column. The statement may not specify HAVING, CUBE, or ROLLUP.

· A projected column in the SELECT statement that is used as a simple expression must not appear more than once.

· The statement must not include PIVOT or UNPIVOT operators.

· The statement must not include the INTERSECT or EXCEPT operators.

· The statement must not reference a view.

· The statement must not contain any of the following: DISTINCT, COMPUTE or COMPUTE BY, or INTO.

· The statement must not reference server global variables (@@variable_name).

· The statement must not reference derived tables, temporary tables, or table variables.

· The statement must not reference tables or views from other databases or servers.

· The statement must not contain subqueries, outer joins, or self-joins.

· The statement must not reference the large object types: text, ntext, and image.

· The statement must not use the CONTAINS or FREETEXT full-text predicates.

· The statement must not use rowset functions, including OPENROWSET and OPENQUERY.

· The statement must not use any of the following aggregate functions: AVG, COUNT(*), MAX, MIN, STDEV, STDEVP, VAR, or VARP.

· The statement must not use any nondeterministic functions, including ranking and windowing functions.

· The statement must not contain user-defined aggregates.

· The statement must not reference system tables or views, including catalog views and dynamic management views.

· The statement must not include FOR BROWSE information.

· The statement must not reference a queue.

· The statement must not contain conditional statements that cannot change and cannot return results (for example, WHERE 1=0).

· The statement can not specify READPAST locking hint.

· The statement must not reference any Service Broker QUEUE.

· The statement must not reference synonyms.

· The statement must not have comparison or expression based on double/real data types.

There is a bug within the .net Framework that sometimes causes notifications to be lost. It is described in the following knowledge base article:

Some notifications might not be received when an application subscribes to query notifications by using ADO.NET 2.0