Log in

Previous Entry | Next Entry

Below are the broader steps to enable a SQL Cache Dependency: -

  • Enable notifications for the database.

  • Enable notifications for individual tables.

  • Enable ASP.NET polling using “web.config” file

  • Finally use the Cache dependency object in your ASP.NET code

Enable notifications for the database.

Before you can use SQL Server cache invalidation, you need to enable notifications for the database. This task is performed with the aspnet_regsql.exe command-line utility, which is located in the c:\[WinDir]\Microsoft.NET\Framework\[Version] directory.

aspnet_regsql -ed -E -d Northwind

 -ed :- command-line switch
 -E: - Use trusted connection
 -S: - Specify server name it other than the current computer you are working on
 -d: - Database Name

So now, let us try to understand what happens in the database because of “aspnet_regsql.exe”. After we execute the “aspnet_regsql -ed -E -d Northwind” command you will see one new table and four new stored procedures created.

                                                    Figure: - SQL Cache table created for notification

Essentially, when a change takes place, a record is written in this table. The SQL Server polling queries this table for changes.

                                                                   Figure: - Stored procedures

Just to make brief run of what the stored procedures do.

“AspNet_SqlCacheRegisterTableStoredProcedure” :- This stored procedure sets a table to support notifications. This process works by adding a notification trigger to the table, which will fire when any row is inserted, deleted, or updated.

“AspNet_SqlCacheUnRegisterTableStoredProcedure”:- This stored procedure takes a registered table and removes the notification trigger so that notifications won't be generated.

“AspNet_SqlCacheUpdateChangeIdStoredProcedure”:- The notification trigger calls this stored procedure to update the AspNet_SqlCacheTablesForChangeNotification table, thereby indicating that the table has changed.

AspNet_SqlCacheQueryRegisteredTablesStoredProcedure:- This extracts just the table names from the AspNet_SqlCacheTablesForChangeNotification table. It is used to get a quick look at all the registered tables.

AspNet_SqlCachePollingStoredProcedure:- This will get the list of changes from the AspNet_SqlCacheTablesForChangeNotification table,It is used to perform polling.

Enabling notification for individual tables

Once the necessary stored procedure and tables are created then we have to notify saying which table needs to be enabled for notifications.

That can be achieved by two ways:-

  • aspnet_regsql -et -E -d Northwind -t Products

  • Exec spNet_SqlCacheRegisterTableStoredProcedure 'TableName'

Registering tables for notification internally creates triggerfor the tables. For instance, for a “products” table the following trigger is created. So any modifications done to the “Products” table will update the “AspNet_SqlCacheNotification’ table.


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.

                                                                       Figure: - Notification tables

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.

                                                                 Figure: - Cache dependency

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

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 GetProductsList(int catId, string sortBy)
 //Try to Get Products List from the Cache
 List 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(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.

Also see following ASP.NET interview questions videos on Session VS Viewstate difference:  -

Copy right www.questpond.com

Buy 600+ Real Time Interview Videos

Call +91-22-66752917 or mail us on questpond@questpond.com for more details on C#, .NET, ASP.NET, SQL Server interview question videos.

SQL Server interview question videos

Latest Month

March 2017


Powered by LiveJournal.com