Creating a database dependency for Enterprise Library caching block

The Caching Application Block ships with four expiration policies out of which three are time based expiration policies and one is a notification based expiration policy. This article focuses on creating a notification based expiration policy for Caching application block to create a database dependency for the cached items.

The only notification based expiration policy that ships along with the caching application block is a FileBasedExpiration policy. If you need to implement any other notification based expiration policy for your application you need to implement the ICacheItemExpiration interface shipped along with the Caching Application Block. The ICacheItemExpiration interface has three method signatures defined.

public interface ICacheItemExpiration

{

bool HasExpired();

void Initialize(CacheItem owningCacheItem);

void Notify();

}

In this article I will explain how you can create a custom expiration policy for creating a database dependency for your caching application block that used the data access application block also.

When you need to cache the master data for an enterprise application that exists in a database, a useful notification-based expiration would be one that will remove a cached item based on modifications that are made to a database table. If you look into the enterprise library notification policies, you will find that no notification policy is defined for changes made to database tables. We need to create a custom notification policy by implementing the ICacheItemExpiration interface to create a DataBaseDependecyNotification.

The DataBaseDependecyNotification class should implement the HasExpired method that returns a Boolean value based on the modifications made. I have implemented the method as follows.

public Boolean HasExpired()

{

Boolean expired = false;

try

{

DateTime lastChangedDate = DateTime.MinValue;

Database db = DatabaseFactory.CreateDatabase("ConnectionString");

using (IDataReader dataReader = db.ExecuteReader("GetLastModifiedDate", dependencyTableName))

{

if (dataReader.Read())

lastChangedDate = dataReader.IsDBNull(0) ? DateTime.MinValue : dataReader.GetDateTime(0);

}

if (tableChangedDate.Equals(DateTime.MinValue))

{

tableChangedDate = lastChangedDate;

}

if (tableChangedDate.Equals(lastChangedDate) == false)

{

tableChangedDate = lastChangedDate;

expired = true;

}

}

catch (Exception ex)

{

throw new ArgumentException("Failed to retirieve the last modified date from database " + ex.Message);

}

return expired;

}

In the database you need to create a ChangeLog table that will hold the details of the Master tables and the changed date for the tables.

Column	DataType
TableName	nvarchar(250)
ChangeID	int
ModifiedDate	datetime

The next step is to create triggers for the master tables that will update the ChangeLog table that I have created for tracking the data changes.

CREATE TRIGGER [dbo].[MasterTableNotificationTrigger]

ON [dbo].[MasterTable]

FOR INSERT, UPDATE, DELETE

AS

BEGIN

SET NOCOUNT ON

DECLARE @TableCount INT

SELECT @TableCount = Count(*) FROM [dbo].[ChangeLog] WHERE TableName = 'MasterTable'

IF(@TableCount <> 0)

UPDATE [dbo].[ChangeLog] WITH (ROWLOCK) SET ChangeID = ChangeID + 1, ModifiedDate = GetDate() WHERE TableName = 'MasterTable'

ELSE

INSERT INTO [dbo].[ChangeLog]

(TableName, ChangeID)

VALUES

('MasterTable', 0)

END

The last step is to write the procedure that is called from the notification class.

ALTER PROCEDURE [dbo].[GetLastModifiedDate]

@TableName varchar(250)

AS

BEGIN

SET NOCOUNT ON;

Declare @TableNameCount int

SELECT @TableNameCount = COUNT(*) FROM ChangeLog WHERE TableName = @TableName

SELECT ModifiedDate

From ChangeLog

Where TableName = @TableName

END

Now you can add the database dependency into the caching layer while inserting items into the cache like.

cacheManager.Add(key, item,

CacheItemPriority.Normal, new CacheRefreshPolicy(),

new DatabaseDependencyNotification(TableName));