Implementing the Design on the Client

Figure 2: Cache invalidation sequence diagram

In the main article, the client I refer to is the Web application. I implemented the article's client solutions in ASP and ASP.NET, but you could just as easily implement the clients in Cold Fusion, CGI, IBM WebSphere, and other Web-application platforms. Both sample clients I provided in this article have a dynamic Web page that retrieves all the rows from the Northwind database's Customers table, then displays the count of all customers to the screen.

When executing, each client application's Web page must perform the following steps:

Of the Web page's execution steps, the only ones that really participate in my design are the ones that create the cache dependency in the database and create the trigger on the database table. To create the database cache dependency, you need to call the sp_CreateCacheDependency stored procedure, which Listing A creates.

This stored procedure accepts as its parameters the URL for the Web page that invalidates the local data cache and the database table that the cache is dependent on. The procedure first checks that no record already exists in the database for the given URL, then inserts the data into the CacheExpiryUrl and CacheDependency tables.

To create the trigger, the code from the Web application sends a dynamic SQL string to the database server and executes it. To perform this action in SQL Server, the user must be a member of the db_ddladmin fixed database role. Although the Web applications in the example solution create the trigger dynamically, you could also create it ahead of time.

In addition to the execution steps I just mentioned, each Web application must implement a Web page that does nothing but invalidate the local data cache. This client piece of the design is extremely important because it completes Figure A's cache-invalidation sequence diagram. It's important that the Web page does nothing else because this page's URL is called by the database server's job, and you want this page to return a response to the database server as quickly as possible.

Listing A: Creation Code for the sp_CreateCacheDependency Stored Procedure

CREATE  PROCEDURE sp_CreateCacheDependency@table_name sysname,@cache_expiry_url varchar(200)ASDECLARE@cache_expiry_url_id int-- Link the table in cache to the expiry URL.IF NOT EXISTS (SELECT 1 FROM CacheExpiryUrl cu INNER JOIN CacheDependency cdt ON cu.cache_expiry_url_id = cdt.cache_expiry_url_id WHERE cu.cache_expiry_url = @cache_expiry_url )BEGININSERT INTO CacheExpiryUrl (cache_expiry_url) VALUES (@cache_expiry_url)SET @cache_expiry_url_id = SCOPE_IDENTITY()INSERT INTO CacheDependency ( cache_expiry_url_id, table_name ) VALUES ( @cache_expiry_url_id,@table_name )ENDRETURN(@@ERROR)GO