SqlDependency and VaryByParam

[imaginekitty]

In every explanation and example of using SqlDependency="CommandNotification" I see that VaryByParam is set to "none". Is there a reason that it has to be set to "none" or can I do this:

<&#37;@ OutputCache Duration="999999" SqlDependency="CommandNotification" VaryByParam="pageID" %>

As I have different content depending on the pageID.

Also, does CommandNotification only cache the items selected from the database or am I caching the output of the entire page?

[honeymonster]

1) I would definately NOT set it to "none" by default. That's a BAD practice. A much more sensible default would be "*" - meaning all of the parameters are significant. Otherwise you will risk serving the wrong result once the page is cached in the database.

If you absolutely, positively know that pageID is the only distinguishing parameter you could just set it to "pageID".

2) This is an output cache; it will cache the entire result of the page. If another requests "hits" the page with the same parameters before the cache is evicted, it will not even reach the page handler code.

When using CommandNotification the SqlClient library will actually track all of the queries you make which are output cached and record a dependency (with SQL Server 2005; not Express). If the database is updated in a way which would produce another result for any of the queries which are monitored; the server will actually notify the ASP.NET runtime which will then immediately evict the cached output from cache. This will result in a new rendering of the page upon the next request.

[imaginekitty]

Thanks again for your insight HM.

If you google SqlDependency="CommandNotification" you'll see that every example uses VaryByParam="none". I just thought I was doing something wrong by using pageID.