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));

About eagle081183

Passionate, Loyal
This entry was posted in C#, Software architecture. Bookmark the permalink.

One Response to Creating a database dependency for Enterprise Library caching block

  1. ravi says:

    how to use the Notify() method to refresh the cache item when data related to cache item changes.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s