How do we enable SQL Cache Dependency in ASP.NET 2.0? contd.

 Posted by Articlesmaint on 9/18/2009 | Category: ASP.NET Interview questions | Views: 5239

Continuation of the previous question


dbo.[Products_AspNet_SqlCacheNotification_Trigger] ON

EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure

“AspNet_SqlCacheTablesForChangeNotification” contains a single record for every table you're monitoring. When you make a change in the table (such as inserting, deleting or updating a record), the change Id column is incremented by 1.ASP.NET queries this table repeatedly keeps track of the most recent changed values for every table. When this value changes in a subsequent read, ASP.NET knows that the table has changed.

Entries in the Cache notification table

Enable ASP.NET polling using “web.config” file
Now that all our database side is configured in order to get the SQL Cache working in the ASP.NET side we need to do some configuration in the web.config file.
We need to set two attributes in the “web.config” file:-
• Set “Enabled” attribute to true to set the caching on.
• Set the poll time attribute to the number of milliseconds between each poll
Below is the snapshot of the web.config file.












Web.config file modifications for SQL cache

Finally use the Cache dependency object in your ASP.NET code
Now comes the final step to use our cache dependency with programmatic data caching, a data source control, and output caching.
For programmatic data caching, we need to create a new SqlCacheDependency and supply that to the Cache.Insert() method. In the SqlCacheDependency constructor, you supply two strings. The first is the name of the database you defined in the element in the section of the web.config file e.g: Northwind. The second is the name of the linked table e.g: Products.


private static void CacheProductsList(List<ClsProductItem> products)

{SqlCacheDependency sqlDependency = new SqlCacheDependency("Northwind", "Products");
HttpContext.Current.Cache.Insert("ProductsList", products, sqlDependency, DateTime.Now.AddDays(1), Cache.NoSlidingExpiration);}
private static List<ClsProductItem> GetCachedProductList()
{return HttpContext.Current.Cache["ProductsList"] as List<ClsProductItem>;}

ClsProductItem is business class, and here we are trying to cache a list of ClsProductItem instead of DataSet or DataTable.
The following method is used by an ObjectDataSource Control to retrieve List of Products


public static List<ClsProductItem> GetProductsList(int catId, string sortBy)

{//Try to Get Products List from the Cache

List<ClsProductItem> products = GetCachedProductList();
if (products == null)
//Products List not in the cache, so query the Database layer
ClsProductsDB db = new ClsProductsDB(_connectionString);
DbDataReader reader = null;
products = new List<ClsProductItem>(80);
if (catId > 0)
//Return Product List from the Data Layer
reader = db.GetProductsList(catId);
//Return Product List from the Data Layer
reader = db.GetProductsList();
//Create List of Products -List if ClsProductItem-
products = BuildProductsList(reader);

//Add entry to products list in the Cache

products.Sort(new ClsProductItemComparer(sortBy));

if (sortBy.Contains("DESC")) products.Reverse();
return products;


To perform the same trick with output caching, you simply need to set the SqlDependency property with the database dependency name and the table name, separated by a colon:

<%@ OutputCache Duration="600" SqlDependency="Northwind:Products" VaryByParam="none" %>
The same technique works with the SqlDataSource and ObjectDataSource controls:

<asp:SqlDataSource EnableCaching="True" SqlCacheDependency="Northwind:Products" ... />

Note: - ObjectDataSource doesn't support built in caching for Custom types such as the one in our example. It only supports this feature for DataSets and DataTables.

Just to make a sample check run the SQL Server profiler and see that does the SQL actually hit the database after the first run.

Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response