The only configuration step you need to perform is to make sure your database has the ENABLE_BROKER flag set.
ALTER DATABASE Northwind SET ENABLE_BROKER
Notifications work with SELECT queries and stored procedures. However, some restrictions
exist for the SELECT syntax you can use. To properly support notifications, your command must
adhere to the following rules:
• You must fully qualify table names in the form [Owner].table, as in dbo.Employees (not just Employees).
• Your query cannot use an aggregate function, such as COUNT(), MAX(), MIN(), or AVERAGE().
• You cannot select all columns with the wildcard * (as in SELECT * FROM Employees). Instead,
you must specifically name each column so that SQL Server can properly track changes that
do and do not affect the results of your query.
Here’s an acceptable command: SELECT EmployeeID, FirstName, LastName, City FROM dbo.Employees
Creating the Cache Dependency:
You use a different syntax to use SQL cache dependencies with SQL Server 2005 (and 2008) than you do
with SQL Server 2000. That’s because it’s not enough to simply identify the database name and table—
instead, SQL Server needs to know the exact database command you’re using to retrieve your data.
If you use programmatic caching, you must create the SqlCacheDependency using the constructor
that accepts a SqlCommand object. Here’s an example:
// Create the ADO.NET objects.
string connectionString = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
SqlConnection con = new SqlConnection(connectionString);
string query ="SELECT EmployeeID, FirstName, LastName, City FROM dbo.Employees";
SqlCommand cmd = new SqlCommand(query, con);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
// Fill the DataSet.
DataSet ds = new DataSet();
// Create the dependency.
SqlCacheDependency empDependency = new SqlCacheDependency(cmd);
// Add a cache item that will be invalidated if one of its records changes
// (or a new record is added in the same range).
Cache.Insert("Employees", ds, empDependency);
You also need to call the static
SqlDependency.Start() method to initialize the listening service
on the web server. This needs to be performed only once for each database connection. One place
you can call the
Start() method is in the
Application_Start() method of the global.asax file.
This method opens a new, nonpooled connection to the database. ASP.NET checks the queue
for notifications using this connection. The first time you call
Start(), a new queue is created with a
unique, automatically generated name, and a new notification service is created for that queue.
Then, the listening begins. When a notification is received, the web server pulls it from the queue,
raises the SqlDependency.OnChange event, and invalidates the cached item.
Even if you have dependencies on several different tables, the same queue is used for all of
them. That means you need only a single call to
SqlDependency.Start(). If you inadvertently call the
Start() method more than once, nothing happens.
Finally, you can use the following code to detach the listener:
Typically, you’ll use this when the
Application_End() method is called to detach the listener and
release all resources.
N.B: The content of the book is mostly taken from Pro ASP.NET 3.5 from APRESS(Second Edition).
And This Blog is for My Personal documentation. Next Post Will be on CUSTOM CACHE DEPENDENCY- SQL Chache Dependancy Part-II and the Next one will be on A Custom Cache Dependency Using Message Queues- SQL Chache Dependancy Part-III.