ASP.NET Caching Interview Question:

Do you know using SQL Cache Invalidation?

Submitted by: Administrator

The Cache API introduced with ASP.NET 1.x was a powerful feature that can be immensely useful in increasing the performance of a web application. The Cache API also allows you to invalidate items in the cache based on some predefined conditions, such as change in an XML file, change in another cache item, and so on. Using this feature, you can remove or invalidate an item from the cache when the data or another cached item changes. However, the Cache API in ASP.NET 1.x versions did not provide a mechanism to invalidate an item in the cache when data in a SQL Server database changed. This is a very common capability that many web applications require. Now with ASP.NET 2.0, Microsoft has introduced a new cache invalidation mechanism that works with SQL Server as well. Using this new capability, you can invalidate an item in the Cache object whenever the data in a SQL Server database changes. This built-in cache invalidation mechanism works with SQL Server 7.0 and above. However, with SQL Server 7.0 and 2000, only table-level cache invalidation mechanism is supported. The next release of SQL Server (named SQL Server 2005) will also feature a row-level cache invalidation mechanism, providing a finer level of accuracy over the cached data. To enable the SQL Server-based cache invalidation mechanism, you need to do the following:

Add a <caching> element to the Web.config file, and specify the polling time and the connection string information.
Enable SQL cache invalidation at the database and table levels by using either the aspnet_regsql utility or the SqlCacheDependencyAdmin class. This is not required if you are using SQL Server 2005 as your database.
Specify the SqlCacheDependency attribute in the SqlDataSource control.

That’s all you need to do to leverage SQL Server cache invalidation from your ASP.NET pages.
Submitted by: Administrator