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!