Tuesday, April 22, 2008

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

39 comments:

Unknown said...

Hi,

Nice article, very helpful for creating database dependency.

Anonymous said...

hi,can you upload the sourcecode to download?

thanks.

Prajeesh Prathap said...

send me your email address. I will mail the source code.
prajeesh.prathap@gmail.com

Unknown said...

this article is really good

Anonymous said...

Hi,

Is this the best strategy around? Actually, I am in a process of developing a Cache layer for my application and looking for some good solutions.

I am using EntityFramework for my Dataaccess and all. Also, I am not very much keen on using triggers?

Any Pointers ?

Cheers,

Anonymous said...

Prajeesh, thanks for the article. Do you know how often is ICacheItemExpiration.HasExpired() called?

Doesn't it look like HasExpired is too expensive? I think this may be a db hit per cache access. Is that true?

Prajeesh Prathap said...

HasExpired is called everytime the cache is requested. This approach is not recommended for all the situations, if you have some huge data that changes frequenlty and want to implement database dependency, then you can go for this approach. Otherwise this approach creates unwanted performance and network overhead.

..:: karthik ::.. said...

Hi,

Could u upload ur "db dependency entlib cahce block" for download.

tks.

Unknown said...

Hi Prajeesh,

Its really an excellent article!
Thanks a lot for sharing this with us.
I have a smilar requirement in my current project.
It would be of great help if u can send me the source code including the custom class for creating the database dependency..

Thanks,
Parvathi

Prajeesh Prathap said...

For all who need the source code for the article. Leave a comment with you email address and i'll mail it to you.

Unknown said...

Hi Prajeesh,
I have liked your strategy for building a custom expiration policy. If possible please send me the entire code on my email id: pankaj.del@gmail.com

Many Thanks!
Pankaj Kumar

Ravi R A said...

Hi,

Interesting Article.. I've gotta implement a similar expiration policy ... Can u please mail me the code.. it'll be really helpful.. - r_a_ravi85@yahoo.co.in

Anonymous said...

Nice blog as for me. It would be great to read something more about that matter. The only thing it would also be great to see here is a few pics of any gizmos.
John Trider
Cell phone blocker

Anonymous said...

Can you send me a copy of the code?

kbanashek@hanleywood.com

Thanks
Kyle

Anonymous said...

Could you please send me the source code for this article - it's exactly what I've been looking for.

Thanks
Kyle
kbanashek@hanleywood.com

Unknown said...

Hi,

My ID is Kumar_knmiet@yahoo.com.
Could u please send me the source code ASAP so that I can implement in the Project.
Thank you in advance :)

Unknown said...

Could you please send me the source code for this article - it's exactly what I've been looking for..
Thanks in Advance..

My ID is Kumar_knmiet@yahoo.com

Raquib said...

Hi Prajeesh
Suppose i have large data in database and i want to store in cache with sql dependency which you suggested. At each time any operation on database cache upload all data or updated data.
Can you send me code in my mail id
raquib.alam.dhn@gmail.com

ravi kore said...

Hi Prajeesh,

Please send me the source code on ravikore86@gmail.com. I have similar requirement in my current projet.

thanks,
Ravi Kore

ravi kore said...

Hi Prajeesh,

Please send me the source code on my id ravikore86@gmail.com. I have same requirement in my current project.

Thanks,
Ravi Kore

ravi kore said...

Hi,

if anybody will have the source code for the implementation this strategy then plz can you give me the source code on ravikore86@gmail.com


I need the same functionality on immediate basis. thanks in advance.


Thanks,
Ravi Kore

ravi kore said...

hi ,

anybdy can send me the source code of this on my id ravikore86@gmial.com. i need to implement this functionality on immediate basis.
thanks in advance.

Thanks,
Ravi Kore

Anonymous said...

Hi,

Please send me source code at tahir.niazi@d-p-s.com

Anonymous said...

Hi,

Its really a good article!

I am able to Implement the Database dependency as specified but it is not notifying the changes and cache is still holding the old data. Do I need to set SQL dependency to get the notification? Or how I will get the latest data while cacheManager.GetData(tableName) if cache is expired? I will really appreciate if you can share the code(atul192@yahoo.com) may be I am missing something.

Anonymous said...

Hi,

Nice article, it is exactly what I want.
Could you email me the source code at richard_li99@hotmail.com

Thanks a lot.

Anonymous said...

Hi,

Nice article, it is exactly what I want.
Could you email me the source code at richard_li99@hotmail.com

Thanks a lot.

Anonymous said...

Hi Can You Please Mail The Source Code To This ID rajeshpamarthi4b7@gmail.com

anubhav said...

Please share the source code:
ur_ anubhav@hotmail.com

Thanks.

anubhav said...

Hi please share the source code.
ur_anubhav@hotmail.com

小竹 said...

could you email source code to me as well. Thank you chen.sd@gmail.com

Anonymous said...

could you please send me the source code as well? Thank you. sboman73@yahoo.com

Unknown said...

Could you please send me the source code for this article - it's exactly what I've been looking for.

-Ashish Mavadiya
Email - ashish@unlocktech.in

Unknown said...

could you please send me the source code as well? Thank you.
asmforyou@gmail.com

Anonymous said...

could you please send me the source code as well? Thank you. asmforyou@gmail.com

hr_kool said...

Hi,

Very Nice article, this is exactly what I want.
Could you email me the source code at hr_kool1807@yahoo.com.


Thanks a lot.

hr_kool said...

Hi,

Very nice article, this is exactly what I want.
Could you email me the source code at hr_kool1807@yahoo.com

Thanks a lot.

Anonymous said...

I really liked this article and very helpful

Can you please email this source code to hemanthchowdary@outlook.com

Unknown said...

Hi,

Could you please share source code and here is my mailID sendtoshakthi@gmail.com

Thanks.

Malik.fbi said...

Hi Prajeesh,

Its really an excellent article, Thanks for sharing this with us.

Please can you provide the Source Code.
malik.fbi@gmail.com