ASP.Net SQL Cache Dependencies with SQL 2005

I was testing out some ASP.Net features and got SQL cache dependencies working just fine with SQL 2000.  However, I wanted to  see the new features of SQL 2005 using query notification to get immediate cache invalidation without polling the database.  I couldn't find any documentation, so I did a little spelunking.  It turns out that instead of configuring a dependency and referencing it in the properties for the SQL data source like you do for SQL 7.0 and 2000, you use "CommandNotification" as the SQL cache dependency property.  This tells ASP.Net that you are using SQL 2005 and it then correctly uses query notification instead of trying to use the polling mechanism that is required for earlier versions of SQL. 

 

The great thing about this solution is that it does not require you to run any setup scripts or create triggers and stored procedures in your database.  It does however require that you enable Service Broker on your SQL database which is not enabled by default for security reasons. 

 

I hope this helps some other people trying out the betas.