In my previous posts, I reviewed three basic implementation of the Sql 2005 notifications for cache invalidation. In this post, I'm going to review the SqlNotificationRequest class.
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.
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.)
The SqlNotificationRequest object is the lowest level class you can use for SQL Server 2005 notifications in the .NET Framework. If you open up Reflector, you'll see that each of the options I outlined in previous posts use this class. You can attach a SqlNotificationRequest to a SqlCommand that tells the SQL Server Service Broker that any changes to the results of this command should be sent to a Service Broker Queue.
Here is the high level outline of what is going on in the example below:
- User requests data for the first time, and it does not exist in cache.
- We create the SqlCommand and attach a SqlNotificationRequest to it.
- We then start a background thread that is listening to the Service Broker Queue
- We put the results of the command in the cache.
- When an item is received from the Service Broker Queue, we clear the cache.
The first thing we need to do is create the SQL Server Service Broker Service and Queue entities. This is something that the SqlCacheDependency and SqlDependency objects did for us.
The result of this will be a service for monitoring changes, and a queue for changes, both of these in SQL Server:
The next thing we need to do is create our listener. The listener will have a method that is called asynchronously. This method will execute a query that listens to the message queue for new messages. If it receives a message, it will process that message, and then start listening again.
Now that we have our listener, we can update our Person getting method to add the SqlNotificationRequest to the command. Note that in my previous examples I used Linq to SQL, however in this one, I am using plain old ADO.NET. I was unable to use the SqlNotificationRequest with the DataContext object. Maybe someone out there has figured that one out. If so, let me know.
Also note that we still need to be using a valid query for notification.
As you can see, this method is a bit more involved, but from here, you start getting even more of an idea of what is going on with interaction between the SQL Server Service Broker and the ASP.NET runtime, especially in light of the previous methods I discussed in earlier posts.
I've got one more to go in this series of posts, and it covers a roll-your-own approach that provides ultimate flexibility in you cache notifications.