Friday, January 11, 2008

Overview of SQL Server 2005 Database Cache Invalidation Options - Part 2 SqlCacheDependency

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.






protected void Application_Start(object sender, EventArgs e)




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.

public static List<Person> GetPeople()
    string cacheKey = "personsCacheKey";
    List<Person> people = (List<Person>) HttpContext.Current.Cache.Get(cacheKey);
    if (people == null)
        DataClasses1DataContext ctx = new DataClasses1DataContext();
        var query = from p in ctx.Persons select p;
        people = query.ToList();
        SqlCacheDependency dependency = new SqlCacheDependency(ctx.GetCommand(query) as SqlCommand);
        HttpContext.Current.Cache.Add(cacheKey, people, dependency, DateTime.MaxValue,
                                              TimeSpan.Zero, CacheItemPriority.Normal, null);
    return people;

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.


Fabiano said...

Friend'm trying to use his example so that when they do, the change in the Cache Bank and not back!
I need to do some other setting in SQL Server for the SQL Server Broker Work?

Jim Fiorato said...


I put together some troubleshooting service broker tips. See the bottom of that post.

Fabiano said...

Jim I read the Post, and my application is correct!
Fighting the script at the base so that the cache not inspired, I make changes in the base, and the cache does not change anything.
I do not know where most moving. I need to do something with IIS? machine from the ISS and the Bank may be different machines?
Here I am using the code if you can give me a Help

Fabiano said...

private List KeyWord "GetKeyWordFromCache()
string cacheKey = "KeyWordCacheKey";

List KeyWord ListKeyWord = (List KeyWord)System.Web.HttpContext.Current.Cache.Get(cacheKey);
if (ListKeyWord == null)
NewsDataContext DbNews = new NewsDataContext(
var query = from p in DbNews.KeyWords select p;
ListKeyWord = query.ToList();

SqlCacheDependency dependency = new SqlCacheDependency(DbNews.GetCommand(query) as SqlCommand);

System.Web.HttpContext.Current.Cache.Add(cacheKey, ListKeyWord, dependency, DateTime.MaxValue,
TimeSpan.Zero, CacheItemPriority.Normal, null);
return ListKeyWord;

Fabiano said...

I picked out the signal of Greater and Lesser Do List to make the Post.
Sorry I bothered!
Thank you for your help.