SqlCacheDependency and Strongly Typed Datasets

[chris]

Hello all,

Background: Using .NET 2.0, Sql Server 2005, SqlCacheDependency is
utilized successfully against SqlCommand objects elsewhere in the code
(in other words I have successful instances of using
SqlCacheDependency). ASP.NET website built in 3 separate tiers:
Presentation, Business Logic Layer (BLL), and Data Access Layer (DAL).


I have a strongly typed data set that is utilized by my BLL and returns

the strongly typed data table to the ObjectDataSource that consumes it.

This works fine. Now I want to tie the SqlCacheDependency dependency
to this data set so that if the underlying data changes, it will
invalidate my cache, thus causing the data set to refresh.


How do you do this? I see the SqlCacheDependency object has two
constructors: one for SqlCommand (I use this elsewhere successfully),
and one for the string representation of the database and table to be
notified of when it changes (this will not do, it is not granular
enough).


Any ideas are greatly appreciated.


Regards,
Chris

 

[Bruce Barker]

as you are using sqlserver 2005, SqlCacheDependency uses it notification
services. Notification services allows you to execute a query, and be
notified (through another connection), when the result set has been modified
or it can notify when a table has been modified (thus the two interfaces).
you may want to use notification services directly.

-- bruce (sqlwork.com)

 

[chris]

Thanks for the reply Bruce, but I would hope there would be a simpler
way.

The challenge is that the SqlCommand object used by the TableAdapter
class to fill the DataSet is protected. So, I tried creating a class
TableAdapterExt inheriting the TableAdapter class to extend the
functionality of the GetData method. I basically created a
SqlCacheDependency property and set it in the GetData method after
instantiating the object using the TableAdapter's SqlCommand object.
There were no errors when I ran this, but I was not able to get it to
persist in cache. Note that I used the SqlCacheDependency property
from my TableAdapterExt to set in my Cache.Insert() method for the
dependency parameter.

Isn't there anyone else out there that has tried this? I would think
this would be a powerful thing to know, because you can cache your
DataSets until the underlying data changes.

Thanks,
Chris