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:
Hi,
Nice article, very helpful for creating database dependency.
hi,can you upload the sourcecode to download?
thanks.
send me your email address. I will mail the source code.
prajeesh.prathap@gmail.com
this article is really good
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,
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?
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.
Hi,
Could u upload ur "db dependency entlib cahce block" for download.
tks.
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
For all who need the source code for the article. Leave a comment with you email address and i'll mail it to you.
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
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
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
Can you send me a copy of the code?
kbanashek@hanleywood.com
Thanks
Kyle
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
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 :)
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
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
Hi Prajeesh,
Please send me the source code on ravikore86@gmail.com. I have similar requirement in my current projet.
thanks,
Ravi Kore
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
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
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
Hi,
Please send me source code at tahir.niazi@d-p-s.com
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.
Hi,
Nice article, it is exactly what I want.
Could you email me the source code at richard_li99@hotmail.com
Thanks a lot.
Hi,
Nice article, it is exactly what I want.
Could you email me the source code at richard_li99@hotmail.com
Thanks a lot.
Hi Can You Please Mail The Source Code To This ID rajeshpamarthi4b7@gmail.com
Please share the source code:
ur_ anubhav@hotmail.com
Thanks.
Hi please share the source code.
ur_anubhav@hotmail.com
could you email source code to me as well. Thank you chen.sd@gmail.com
could you please send me the source code as well? Thank you. sboman73@yahoo.com
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
could you please send me the source code as well? Thank you.
asmforyou@gmail.com
could you please send me the source code as well? Thank you. asmforyou@gmail.com
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.
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.
I really liked this article and very helpful
Can you please email this source code to hemanthchowdary@outlook.com
Hi,
Could you please share source code and here is my mailID sendtoshakthi@gmail.com
Thanks.
Hi Prajeesh,
Its really an excellent article, Thanks for sharing this with us.
Please can you provide the Source Code.
malik.fbi@gmail.com
Post a Comment