Use File Dependancy for Caching in Asp.net 2.0

        Today I get some free time, so I thought lets work on other project currently running in organisation. I found that we are using caching one of the project and database is SQL Server 2000. We have to set dependancy for cached data. I have implemented this previously and asked my Project Lead to let me do that part. However when I started working on that I found I forgot the few things ( Specially SP that generates a text file when data in perticular table or tables be changed).

         I seach on my old archives and foud the SP (obviosly that is not written by me, as I not that much good in SQL anyway). Lets start with the things what I did. I have created a demo application which has two pages. One with read only Grid and other with editable grid. The basic Idea is when ever changes occure in few tables or selected table, one text file is generated (is it is not already generated) or append.

         Lets start with SP, below is the SP that append or generate text file.

CREATE PROCEDURE sp_AppendToFile(@FileName varchar(255), @Text1 varchar(255)) AS

DECLARE @FS int, @OLEResult int, @FileID int

EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT

IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'

execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1

IF @OLEResult <> 0 PRINT 'OpenTextFile'

execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1

IF @OLEResult <> 0 PRINT 'WriteLine'

EXECUTE @OLEResult = sp_OADestroy @FileID

EXECUTE @OLEResult = sp_OADestroy @FS

Fig - (1)  SP: to modify Text file.

         My requirement is that whenever records in Employee table chage, it should modify the data that is available in cache and data should be displayed from Server instead of cache. To do this I have created one trigger on Employee table which call Sp (shown in Fig - (1)) and change the text file. Below is the trigger that I used,

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [UpdateEmployee]
ON [dbo].[Employee]
FOR INSERT, UPDATE , DELETE
AS
BEGIN

exec sp_AppendToFile 'FileName.txt' , 'Message'

END

Fig - (2) Trigger: This will change Text file

 

  Now for coding, below is the code that I have used for inserting dataset in cache with file dependency.

 

if (Cache["Data"] == null)
{
     SqlDataAdapter sda = new SqlDataAdapter(cmd);
     sda.Fill(dsTemp);
    System.Web.Caching.CacheDependency objDependancy   =new System.Web.Caching.CacheDependency(@"FileName.txt");
Cache.Insert("Data", dsTemp, objDependancy,   DateTime.Now.AddDays(1), System.Web.Caching.Cache.NoSlidingExpiration, System.Web.Caching.CacheItemPriority.High, null);

}
else
{
          dsTemp = (DataSet)Cache["Data"];
}

I hope this will help you. Happy Programming.