In my previous post, I reviewed a basic implementation of the Sql 2005 notifications for cache invalidation. In this post, I'm going to go over the SqlCacheDependency class and how to use with SQL Server 2005 notifications services.
For each of these options, you'll need to ensure that you've enabled the SQL Server Broker on your database. To do this, you can execute the following query (this query needs exclusive access to the database, so if you see it hanging for a while, chances are you'll need to open up SQL Server Activity Monitor and terminate any additional connections. IISRESET works well too):
Note that you'll need to do this whenever you restore a database. Additionally, I've seen the following issue with database notifications occur when restoring a database that was backed up on another instance of SQL server.
The next thing you'll have to do is update the Global.asax to setup the SqlDependency in the Application_Start method. You do this with the following code:
When you call this method, the runtime sets up the relationship between the application domain and the connection string that will be used later during the notification registration process.
UPDATE: Garry Grimshaw also pointed out to me that the version of SQL Server 2005 that you have installed makes a difference. Certain versions of SQL Server 2005 don't include Notifications Services, and your app will immediately never notify you of cache changes (or if using SQLCacheDependency, an immediate invalidation of your cache item will occur.)
SqlCacheDependency is an implementation of the System.Web.Caching.CacheDependency class, and can be used as an invalidation mechanism. It registers a SqlCommand or a table for change notification and clears the associated cache items when it is notified.
If you don't need a granular query to notify you of when a change occurs, and you are already using the System.Web cache, then this option will probably work best for you.
Below is how to use the class, and you can see how simple it is.
Any changes that occur within the database will trigger a notification to this dependency class, and will automatically clear the cache for you.
Note how you can get the SqlCommand from the Linq query to wire to the SqlCacheDependency. The Linq team really did a good job at covering their bases. I have yet to hit an issue with this language that has prevented me from accomplishing something. It's very smart.