source:
http://www.dotnetjunkies.com/Tutorial/A4ED5FD6-D385-4475-A357-27CF43A78205.dcik
begin:
We have seen over and over again the question, "How can I invalidate a cache item in my ASP.NET Web application when a change is made in my database?" Well, this article will demonstrate one way to accomplish this using triggers, a console application, and dependency files. As the title implies this is a two part article, the second part (or example) will be much more complicated and will span many different types of technologies including HttpHandlers, SQL Server, and enabling SQL Server itself to make HTTP requests using extended stored procedures. The only reason I wrote this example was because as I was working through the very complicated version I thought of this one and decided, what the heck I'll write about it.
Project Description
Within the download you'll receive the complete solution which will consist of the following:
ASP.NET Web Application
.NET Console Application
Sql.Sql
A folder named dependfiles with one file named authors.xml in it.
The Web application will host just one Web Form that will be used for demonstration purposes. The console application contains just a few lines of code, but is responsible for invalidating cache items. Finally, Sql.Sql is a script file with example code for a trigger and a user defined function - this is what binds the two together!
Setting up the Project
The Web application should load up without any worries; the only thing you'll probably have to do is create a new VRoot first. To load the trigger and function into SQL server you can open up Microsoft SQL Server Query Analyzer, connect to the Pubs database on your local server, open up sql.sql, and execute the code. But before doing that please read the warnings below.
Warning:
Before running building the project you will most likely have to change the following:
Open SqlDepend.sln (the solution) in notepad and change the following line to reflect the URL to your VRoot:
Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "SqlDependsWeb", "http://localhost:1/SqlDepend/SqlDependsWeb.csproj",
"{DE282A39-E4BF-4FE2-ABA5-82F45E9047B9}"
This line in global.asax.cs to reflect where this file is on your file system
public static string DependPath = "c:\\dependfiles\\";
This line in fnInvalidateCache in sql.sql to reflect where the exe is on your file system.
SET @CMDS = 'c:\dependfiles\SqlDepend.exe ' + 'authors'
In SqlDepend.aspx.cs fill in the proper user name and password for your SQL Server Pubs table
Running the Project
Please do the following to run the project:
1. Build the project on your local machine, ensure there are no build errors
2. Copy SqlDepend.exe into the dependfiles directory
3. Open up SqlDepend.aspx in your browser. You should get a message "Getting Item From Database"
4. Refresh the page and you should get a message "Getting Item From Cache". If you keep refreshing you should get the same message
5. Now go to the authors table and change something
6. Go back to SqlDepend.aspx and hit refresh and pow, you'll get the message "Getting Item From Database"
How it Works
We'll start with SqlDepend.aspx.cs. Within Page_Load BindData is invoked. BindData is used to bind the DataGrid to a DataSource. You'll notice that the DataSource property is set to another method, GetData. GetData determines whether the item should be retrieved from the Cache or from the database. Let's take a look at the code:
Listing 1
private DataSet GetData(){
if ( null != Cache["authors-key"] )
{
Label1.Text = "Getting Item From Cache";
return (DataSet)Cache["authors-key"];
}
else
{
Label1.Text = "Getting Item From Database";
SqlConnection _sqlCon = new SqlConnection();
_sqlCon.ConnectionString = "Server=LocalHost;Database=Pubs;Uid=dmack;pwd=!1admin";
SqlDataAdapter _da = new SqlDataAdapter("select * from authors", _sqlCon);
DataSet _ds = new DataSet();
_da.Fill(_ds, "authors");
Cache.Insert( "authors-key", _ds, new CacheDependency(Global.DependPath + "authors.xml" ));
return _ds;
}
}
We determine if the item should be taken out of the cache by checking if it is null. If it isn't then we'll grab it from the cache and return it. If it isn't then we'll get it from the database and add it to the cache, and then return the new DataSet. The important part comes here; notice that we create a new CacheDependency for that item named authors.xml (DependPath can be found in global.asax.cs) that is used in the Cache.Insert method. A CacheDependency invalidates the cache item if it's changed.
So this is all good, but how do we know when an item has changed in the authors table? Well, let's take a look at the trigger we created for the authors table:
Listing 2
CREATE TRIGGER
Authors_Cache
ON
Authors
AFTER
INSERT,
DELETE,
UPDATE
AS
BEGIN
EXEC fnInvalidateCache 'authors'
END
All this trigger does is execute a function named fnInvalidateCache and passes in a parameter named authors after an insert, delete or update has occurred, still simple enough. Now let's take a look at fnInvalidateCache:
Listing 3
CREATE FUNCTION fnInvalidateCache (@TableName VarChar(25))
RETURNS INT
AS
BEGIN
DECLARE @CMDS VarChar(100)
SET @CMDS = 'c:\dependfiles\SqlDepend.exe ' + 'authors'
EXEC Master..xp_cmdshell @CMDS
RETURN 0
END
Nothing special here either, fnInvalidateCache expects one parameter, @TableName. @TableName is used to create a string that will be passed into the SQL extended stored procedure xp_cmdshell which executes the command string as an operating system command. In this case, "c:\dependfiles\SqlDepend.exe authors". Let's see what SqlDepend.exe is, we'll just take a look at the entry point code since that's really the only code executed:
Lisitng 4
static void Main( string[ ] args )
{
try
{
string _table = args[0].ToString();
Random _r = new Random( unchecked ( ( int ) DateTime.Now.Ticks ) );
string _value = _r.Next().ToString();
Path = Path + _table + ".xml";
StreamWriter _SWriter = new StreamWriter( File.Open( Path, FileMode.Open, FileAccess.Write ) );
_SWriter.Write( _value );
_SWriter.Close();
}
catch ( Exception ) { }
}
We take the first argument passed in, which is the table name authors, and put it into a local variable. Eventually, this value is used to create a path to a file - in this case c:\dependfiles\authors.xml. Next we create a random number based on the current time, this value is going to be used to change the CacheDependency files data. Then we open up the CacheDependency file, write the new value to it, and finally close it.
Now that the file has changed on the next request, the Cache item authors-key will be found invalid and a new database query will be invoked and a new, refreshed item will be inserted into the cache.
Conclusion
Again, this is a very simple example illustrating how to make cache items in your ASP.NET Web application become invalid if the data that populated it was from SQL and it has changed. Stay tuned for the next example (still in the works), you won't be disappointed!
