ASP.NET 2.0 #9: SQLCacheDependency

Here is the code for a user control that displays a report: the control only goes through the process of loading the report from disk and retrieving its data if the report that is to be displayed is not already in the ASP.NET cache, otherwise, it merely retrieves the report from the cache, which is much quicker:

public string Report
{
set
{
string key = value;

ReportDocument document = (ReportDocument)this.Cache[key];
if (document == null)
{
document = new ReportDocument();
document.Load(key);

SqlCacheDependency cacheDependency = new SqlCacheDependency("SalerSales", "Invoice");
this.Cache.Insert(key, document, cacheDependency);
}

this.ReportViewer.ReportSource = document;
this.ReportViewer.DataBind();
}
}

Clearly, this use of the ASP.NET cache will both accelerate the performance of the application by reducing the time required to load each report, and improve the application’s scalability by minimizing traffic to and from the database. The snag with this approach up to now, though, has been that if the data displayed in the report is modified in the database but the report is taken from the cache, then the report will be out of date. Finding and implementing some balance between the report being up-to-date and not having to retrieve the data for the report every single time has been tough in the past. It has been tough both to decide on the right balance and to implement that balance with code.

ASP.NET 2.0 provides an extremely elegant, ready-to-use solution to that problem when used in conjunction with SQL Server 7 or higher. This feature, SQL Server Cache Dependency, is by far the most powerful and useful new feature of ASP.NET 2.0. It is also a great example of the principal benefit of developing on the Microsoft platform, which Steve Ballmer described as integrated innovation, whereby the true steps forward are achieved through Microsoft’s ability to enhance the capabilities of multiple products at the same time.

What SQL Server Cache Dependency allows one to do is have ASP.NET automatically and quietly monitor any number of tables in any number of SQL Server databases in the background, and if the data in any of those tables changes, then items connected to those tables are deleted from the cache. Thus, if we look closely at the code for the report custom control, we see that if the report to be displayed is not found in the cache, then the control adds the report to cache, specifying that the addition to the cache is dependent on there being no changes to the table of invoices in SQL Server which is the source of the revenue figures shown on the reports. The name of the SQL Server database that is specified in the code for the user control corresponds to an entry in the application's Web.config file, which tells ASP.NET to poll that database for changes every fifteen seconds:

<caching>
<sqlCacheDependency enabled = "true" >
<databases>
<add name="SalerSales"
connectionStringName="SalerSales"
pollTime = "15000"
/>
</databases>
</sqlCacheDependency>

</caching>