SQL dependency with Velocity

SQL dependency with Velocity CTP3

Velocity CTP3 was released on April 8 and one of the first things that interested me is the ability of removing items from the cache when they change in the database.
Like the known SQL dependency in the .NET cache.( SqlCacheDependency )

Unfortunatly Velocity CTP3 does not support SQL Dependency yet. (they are supposed to support this ability in the future)
Here ill show a simple trick for creating SQL dependeny and attaching it to Velocity.

First we will create a singltone DataCache :

static readonly object padlock = new object();

public static DataCache GetCache()
{

lock (padlock)
{

if (_cache == null)
{

//Define Array for 1 Cache Host

DataCacheServerEndpoint[] servers = new DataCacheServerEndpoint[1];

//Specify Cache Host Details
// Parameter 1 = host name
// Parameter 2 = cache port number
// Parameter 3 = cache service name

servers[0] = new DataCacheServerEndpoint("ServerName", +22233, "DistributedCacheService");

//Select Routing or Simple Client
// False = Simple Client (no routing table)
// True = Routing Client

bool routingClient = false;

//Select local cache if desired
// True = Enable local cache
// False = Disable local cache

bool localCache = false;

//Disable exception messages since this sample works on a cache aside

DataCacheFactory.DisableLogSinks();

//Pass configuration settings to cacheFactory constructor

DataCacheFactory _factory = null; _factory = new DataCacheFactory(servers,

routingClient, localCache);

//Get reference to named cache called "NamedCache"

_cache = _factory.GetCache("default");

}

}

return _cache;

}

Now we will create a CacheItemRemovedCallback delegate :

public static event System.Web.Caching.CacheItemRemovedCallback removeRegionDelegate;

And a method that will be called every time the SQL dependency event is trigered

static void _Default_removeRegionDelegate(string key, object value, System.Web.Caching.CacheItemRemovedReason reason)

{

if (_cache != null)

{

_cache.ClearRegion("RegionName");

}

}

Now all wee need to do is add an empty object to the regular .NET cache with an sqlDependency and add the removeRegionDelegate so every time the item is removed from the cache the _Default_removeRegionDelegate method is called and clears all the items from the Velocity region.

if (System.Web.HttpContext.Current.Cache["dependency"] == null)

{

SqlCacheDependency sqlDependency = new SqlCacheDependency("Name", "connections"); //dependency information 

removeRegionDelegate += new System.Web.Caching.CacheItemRemovedCallback(_Default_removeRegionDelegate);

System.Web.HttpContext.Current.Cache.Insert("dependency", " ", sqlDependency, System.DateTime.Now.AddDays(10), System.Web.Caching.Cache.NoSlidingExpiration, System.Web.Caching.CacheItemPriority.NotRemovable, removeRegionDelegate);

}

• The creation of the local cache "dependency" is suppose to run once (Global.Asax Application_Start)
• If you have many clients you can make shure it runs only on one of the clients buy checking if the region is full before clearing the region

For conclusion

This litle code is a workaround for enabling SQL dependency for velocity until they will publish a version that includes this feture.