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
