Wicked Code
Supporting Database Cache Dependencies in ASP.NET
Jeff Prosise
Code download available at:WickedCode0304.exe(135 KB)
Contents
Database Dependencies in Action
How Database Dependencies Work
Server Farms
Developers love the ASP.NET application cache. One reason they love it is that ASP.NET lets them create dependencies between items placed in the cache and files in the file system. If a file targeted by a dependency changes, ASP.NET automatically removes dependent items from the cache. Combined with cache removal callbacks—notifications broadcast to interested parties when cached items are removed—cache dependencies are a boon to developers seeking to maximize performance by minimizing time-consuming file accesses because they permit file data to be cached without fear of it becoming stale.
As awesome as cache dependencies are, in ASP.NET version 1.0 they lack one critical feature that, if present, would qualify them as a developer's dream come true: support for database entities. In real life, most Web apps fetch data from databases, not files. But while ASP.NET is perfectly willing to link cached items to files, it is incapable of linking cached items to database entities. In other words, you can read the contents of a file into a DataSet, cache the DataSet, and have the DataSet automatically removed from the cache if the file it was initialized from changes. But you can't initialize a DataSet with a database query, cache the DataSet, and have the DataSet automatically discarded if the database changes. That's too bad because too many database accesses, like too much file I/O, is a performance killer.
The fact that ASP.NET doesn't support database dependencies doesn't mean database dependencies are impossible to achieve. This installment of Wicked Code presents a technique for extending the ASP.NET application cache to support database dependencies. It involves database triggers and extended stored procedures. Though the implementation presented here works exclusively with Microsoft® SQL Server™, the general technique is applicable to any database that supports triggers and user-defined procedures that interact with the file system.
Database Dependencies in Action
Let's begin with a demonstration. Figure 1 contains the source code for an ASP.NET page that displays randomly selected quotations from a SQL Server database named Quotes. To create the database, run the installation script shown in abbreviated form in Figure 2. The complete script is included in the downloadable zip file that accompanies this column. You can execute the script inside the SQL Server Query Analyzer or from the command line with an OSQL command.) Each time the page is fetched, Page_Load initializes a DataSet with all the records in the database's Quotations table, randomly selects a record from DataSet, and writes it to the page. Press F5 a few times and you'll see a random variety of quotations from some famous (and not-so-famous) people, as shown in Figure 3.
Figure 2 Quotes-1.sql (Abbreviated)
CREATE DATABASE Quotes GO USE Quotes GO CREATE TABLE Quotations ( Quotation varchar(256) NOT NULL, Author varchar(64) NOT NULL ) GO INSERT INTO Quotations (Quotation, Author) VALUES ( 'Give me chastity and continence, but not yet.', 'Saint Augustine' ) ••• INSERT INTO Quotations (Quotation, Author) VALUES ( '640K ought to be enough for anybody.', 'William Gates III' ) GO
Figure 1 DumbDBQuotes.aspx
<%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <html> <body> <h3><asp:Label ID="Quotation" RunAt="server" /></h3> <i><asp:Label ID="Author" RunAt="server" /></i> </body> </html> <script language="C#" runat="server"> void Page_Load (Object sender, EventArgs e) { SqlDataAdapter adapter = new SqlDataAdapter ( "SELECT * FROM Quotations", "server=localhost;database=quotes;uid=sa;pwd=" ); DataSet ds = new DataSet (); adapter.Fill (ds, "Quotations"); DataTable table = ds.Tables["Quotations"]; Random rand = new Random (); int index = rand.Next (0, table.Rows.Count); DataRow row = table.Rows[index]; Quotation.Text = (string) row["Quotation"]; Author.Text = (string) row["Author"]; } </script>
Figure 3** Random Quote **
The page is called DumbDBQuotes.aspx for a reason. It's not very smart considering it queries the database each time it's requested. Accessing a database on every page access—especially a database hosted on a remote server—is a guaranteed way to build an application that won't scale.
The ASP.NET application cache is the solution to the problem of too many database accesses. If the DataSet were cached, it could be fetched directly from memory—that is, from the cache—thereby eliminating the redundant database accesses. It's easy enough to cache a DataSet; the application cache accepts instances of any type that derives from System.Object. In the Microsoft .NET Framework, that means instances of any managed type, including DataSet. The problem is that if you cache a DataSet and the database changes underneath it, you serve stale data to your users. You could implement a solution that requeries the database periodically, but the ideal solution is the one that requires no polling and that delivers fresh data from the data source the moment that data becomes available.
Take a look at Figure 4 and Figure 5, which contain the source code for a smarter quotes application. SmartDBQuotes.aspx doesn't retrieve quotations from the database; it gets them from the application cache. Global.asax primes the cache and refreshes it if the database changes. Here are directions for taking them for a test drive:
- Create a subdirectory named AspNetSql in the root directory of your Web server's C: drive. Inside AspNetSql, create a zero-byte file named Quotes.Quotations. Make sure Everyone, or at least SYSTEM and ASPNET (a special account created when ASP.NET is installed), has access to Quotes.Quotations.
- Copy XSP.dll, which is included in this column's downloadable code sample, into the SQL Server binn directory (for example, C:\Program Files\Microsoft SQL Server\MSSQL\Binn) or to any location on your Web server that Windows will automatically search for DLLs (for example, C:\Windows\System32).
- Rebuild the database using the modified script in Figure 6.
- Deploy Global.asax and SmartDBQuotes.aspx to a virtual directory on your Web server (for example, wwwroot).
- Request SmartDBQuotes.aspx in your browser. Refresh the page a few times until the quote "The use of COBOL cripples the mind; its teaching should therefore be regarded as a criminal offense" appears.
- Use SQL Server Enterprise Manager or the tool of your choice to modify the quotation in the Quotes database's Quotations table. Change it to read "The use of Visual Basic® enriches the mind; its teaching should therefore not be regarded as a criminal offense." Then refresh the page until the modified quotation appears. Observe that the new quotation appears, not the old one, even though the query results are now being stored in the application cache.
Figure 6 Quotes-2.sql (Abbreviated)
USE master EXEC sp_addextendedproc 'xsp_UpdateSignalFile', 'XSP.dll' GRANT EXECUTE ON xsp_UpdateSignalFile TO PUBLIC GO CREATE DATABASE Quotes GO USE Quotes GO CREATE TABLE Quotations ( Quotation varchar(256) NOT NULL, Author varchar(64) NOT NULL ) GO INSERT INTO Quotations (Quotation, Author) VALUES ( 'Give me chastity and continence, but not yet.', 'Saint Augustine' ) ••• INSERT INTO Quotations (Quotation, Author) VALUES ( '640K ought to be enough for anybody.', 'William Gates III' ) GO CREATE TRIGGER DataChanged ON Quotations FOR INSERT, UPDATE, DELETE AS EXEC master..xsp_UpdateSignalFile 'Quotes.Quotations' GO
Figure 5 SmartDBQuotes.aspx
<%@ Import Namespace="System.Data" %> <html> <body> <h3><asp:Label ID="Quotation" RunAt="server" /></h3> <i><asp:Label ID="Author" RunAt="server" /></i> </body> </html> <script language="C#" runat="server"> void Page_Load (Object sender, EventArgs e) { DataSet ds = (DataSet) Cache["Quotes"]; if (ds != null) { // Display a randomly selected quotation DataTable table = ds.Tables["Quotations"]; Random rand = new Random (); int index = rand.Next (0, table.Rows.Count); DataRow row = table.Rows[index]; Quotation.Text = (string) row["Quotation"]; Author.Text = (string) row["Author"]; } else { // If quotes is null, this request arrived after the // DataSet was removed from the cache and before a new // DataSet was inserted. Tell the user the server is // busy; a page refresh should solve the problem. Quotation.Text = "Server busy"; } } </script>
Figure 4 Global.asax
<%@ Import NameSpace="System.Data" %> <%@ Import NameSpace="System.Data.SqlClient" %> <script language="C#" runat="server"> static Cache _cache = null; void Application_Start () { _cache = Context.Cache; // Save reference for later // // Query the database and cache the resulting DataSet. // RefreshCache (null, null, 0); } static void RefreshCache (string key, object item, CacheItemRemovedReason reason) { // // Query the database. // SqlDataAdapter adapter = new SqlDataAdapter ( "SELECT * FROM Quotations", "server=localhost;database=quotes;uid=sa;pwd=" ); DataSet ds = new DataSet (); adapter.Fill (ds, "Quotations"); // // Add the DataSet to the application cache. // _cache.Insert ( "Quotes", ds, new CacheDependency ("C:\\AspNetSql\\Quotes.Quotations"), Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.Default, new CacheItemRemovedCallback (RefreshCache) ); } </script>
You just demonstrated that the ASP.NET application cache can be combined with database dependencies to produce high-volume, data-driven applications. The question is how? How was a link formed between the cached DataSet and the database, and how scalable is the solution?
How Database Dependencies Work
On the outside, both DumbDBQuotes.aspx and SmartDBQuotes.aspx look the same, producing identical output. On the inside, they could hardly be more different. The former performs a database access every time it's requested; the latter fetches data from the application cache. Moreover, SmartDBQuotes.aspx uses a database dependency to ensure that if the database changes, the cached data changes, too. If the database doesn't change, the database is queried just once during the lifetime of the application. If the database changes, one more query updates the cache.
Figure 7** Database Dependencies **
Figure 7 illustrates how the database dependency works. When it places the DataSet in the cache, Global.asax creates a file-system dependency between the DataSet and a file named Quotes.Quotations in the C:\AspNetSql directory. Quotes.Quotations is a zero-byte signal file—a file whose only purpose is to trigger the ASP.NET application cache-removal logic. Here's the statement in Global.asax that creates the CacheDependency object linking the DataSet to Quotes.Quotations:
new CacheDependency ("C:\\AspNetSql\\Quotes.Quotations")
Global.asax also registers its own RefreshCache method to be called when the DataSet is removed from the cache—that is, when the signal file changes:
new CacheItemRemovedCallback (RefreshCache)
RefreshCache's job is to query the database and place the resulting DataSet in the application cache. It's called once when the application starts up and again when—or if—the DataSet is removed from the cache.
That's half of the equation. The other half involves the database. The revised database installation script in Figure 6 adds an insert/update/delete trigger to the database's Quotations table:
CREATE TRIGGER DataChanged ON Quotations FOR INSERT, UPDATE, DELETE AS EXEC master..xsp_UpdateSignalFile 'Quotes.Quotations' GO
The trigger fires when records are added to or deleted from the table and when records change. What does the trigger do? It calls an extended stored procedure—the SQL Server euphemism for code in a Win32® DLL—named xsp_UpdateSignalFile. The extended stored procedure, in turn, uses the Win32 CreateFile function to update Quotes.Quotations' time stamp.
The cached DataSet's lifetime is tied to Quotes.Quotations using an ordinary file-system cache dependency; updating the Quotations table causes a database trigger to fire; and the trigger calls an extended stored procedure that "updates" Quotes.Quotations, prompting ASP.NET to remove the DataSet from the application cache and call Global.asax's RefreshCache method, which then performs a brand new database query and starts the whole process all over again.
The final piece of the puzzle is the extended stored procedure. It's housed in XSP.dll, the DLL that you installed earlier. I wrote XSP.dll in unmanaged C++ using Visual C++® 6.0. Its source code appears in Figure 8. The path to the signal file—C:\AspNetSql—is hardcoded into the DLL, but you can change that if you'd like and make it an input parameter just like the file name.
Figure 8 XSP.cpp
#include <windows.h> #include <srv.h> /////////////////////////////////////////////////////////////////////// // Entry point extern "C" BOOL WINAPI DllMain (HINSTANCE hInstance, DWORD dwReason, LPVOID lpReserved) { return TRUE; } /////////////////////////////////////////////////////////////////////// // Exported functions extern "C" __declspec (dllexport) ULONG __GetXpVersion () { return ODS_VERSION; } extern "C" __declspec (dllexport) SRVRETCODE xsp_UpdateSignalFile (SRV_PROC *srvproc) { // // Make sure an input parameter is present. // if (srv_rpcparams (srvproc) == 0) return -1; // // Extract the file name from the input parameter. // BYTE bType; char file[256]; ULONG ulMaxLen = sizeof (file); ULONG ulActualLen; BOOL fNull; if (srv_paraminfo (srvproc, 1, &bType, &ulMaxLen, &ulActualLen, (BYTE*) file, &fNull) == FAIL) return -1; if (bType != SRVBIGCHAR && bType != SRVBIGVARCHAR) return -1; file[ulActualLen] = 0; // // Update the file's time stamp. // char path[288] = "C:\\AspNetSql\\"; lstrcat (path, file); HANDLE hFile = CreateFile (path, GENERIC_WRITE, 0, NULL, CREATE_ALWAYS, FILE_ATTRIBUTE_NORMAL, NULL); if (hFile != INVALID_HANDLE_VALUE) CloseHandle (hFile); return 0; }
Extended stored procedures must be installed before they're used. The following statements in the SQL installation script that you executed install xsp_UpdateSignalFile in the master database and grant execute permission to all comers:
USE master EXEC sp_addextendedproc 'xsp_UpdateSignalFile', 'XSP.dll' GRANT EXECUTE ON xsp_UpdateSignalFile TO PUBLIC GO
Why write a custom extended stored procedure to update a file's time stamp when a built-in extended stored procedure such as xp_cmdshell could be used instead? The reason is security—xp_cmdshell can be used for all sorts of malicious purposes, while xsp_UpdateSignalFile cannot. Because xsp_UpdateSignalFile does little more than call the Windows CreateFile function, it is also more efficient than xp_cmdshell.
Server Farms
SmartDBQuotes.aspx and friends work great if the Web server and the database server live on the same machine, but what if the database is installed on a different machine? And what about Web farms? Would a change notification mechanism based on database triggers, extended stored procedures, and file-system dependencies be compatible with multiserver installations?
You bet. Under the hood, ASP.NET cache dependencies that are based on the file system rely on Win32 file change notifications. And Win32 file change notifications support Universal Naming Convention (UNC) path names. To take advantage of database cache dependencies on Web farms, let the signal file reside on the database server, as shown in Figure 9. Then pass CacheDependency's constructor a UNC path name specifying the signal file's network address:
new CacheDependency (@"\\ServerName\AspNetSql\Quotes.Quotations"),
Figure 9** Signal File on Database Server **
The greatest obstacle to creating dependencies that target remote files is security. By default, the ASP.NET worker process runs as ASPNET when paired with Microsoft Internet Information Services (IIS) 5.0 and when configured to run in compatibility mode under IIS 6.0. ASPNET is a local account that can't authenticate on remote machines. Without configuration changes, attempting to create a cache dependency with a UNC path name produces an access denied error—even if you give Everyone access to the remote share.
Several solutions exist. One is to configure ASP.NET to use a domain account that can be authenticated on the database server. That change is easy enough to accomplish: you simply specify the account's user name and password in the <processModel> section of each Web server's Machine.config. Many companies, however, have security policies that prevent passwords from being stored in plaintext configuration files. If that's true of your company, but you'd still like to run ASP.NET using a domain account, you can either upgrade to version 1.1 of the .NET Framework (which allows worker process credentials to be encrypted and stored securely in the registry) or download a hotfix for version 1.0 that does the same. You'll find information about the hotfix at Stronger Credentials for processModel, identity, and sessionState.
A variation on this technique involves setting up identical local accounts (using the same user name and password) on both machines and configuring ASP.NET to run as that identical local account on the Web server.
Another solution to the problem of authenticating on a back-end database server containing a signal file is to upgrade to Windows Server 2003. The latest addition to the Windows Server family comes with IIS 6.0, which allows the ASP.NET worker process to run using the identity of Network Service. Unlike ASPNET, Network Service can perform authentication on remote machines. Or you could pull an old trick out of the ASP playbook and access the database through a COM+ component running on the Web server and configure the component to run as a principal that has network credentials.
However you choose to make the remote signal file accessible to your ASP.NET app, the bottom line is that combining database cache dependencies with UNC path names delivers a scalable solution that works equally well for Web farms and single-server installations. That's good news for developers who are using ASP.NET to build high-volume, data-driven applications—and good news for users as well.
Send your questions and comments to Jeff at wicked@microsoft.com.
Jeff Prosiseis the author of several books, including Programming Microsoft .NET and Programming Windows with MFC, both from Microsoft Press. He is also a cofounder of Wintellect, a consulting and training firm that specializes in the Microsoft .NET Framework.