Sql cache dependency, DOT NET Programming

How do we enable SQL Cache Dependency?

Below are some of the steps to enable the SQL Cache Dependency:-

  • Enable the notifications for database.
  • Enable the notifications for individual tables.
  • Enable the ASP.NET polling by using the "web.config" file
  • Finally use the Cache dependency object in your ASP.NET code

To enable notifications for the database.

Before you can use SQL Server cache invalidation, you need to enable notifications for the database. This task is done 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's 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.

1497_sql cache.png

Figure : - SQL Cache table created for notification

1413_sql cache1.png

Figure : - New stored procedures created

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

"AspNet_SqlCacheRegisterTableStoredProcedure" :- This stored procedure fixed a table to support notifications. This method works by adding a notification trigger to the table, which will fire whenever any row is deleted,inserted , or updated,   .

"AspNet_SqlCacheUnRegisterTableStoredProcedure":- This stored procedure takes a registered table and discard the notification trigger so that notifications won't be created.

"AspNet_SqlCacheUpdateChangeIdStoredProcedure":- The notification trigger calls this stored procedure to update the AspNet_SqlCacheTablesForChangeNotification table, therefore  indicating that the table has modified.

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

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

Enabling notification for individual tables

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

There are 2 ways to achieve this:-

  • aspnet_regsql -et -E -d Northwind -t Products
  • Exec spNet_SqlCacheRegisterTableStoredProcedure 'TableName'

Registering the tables for the notification internally generate trigger for the tables. For instance for a "products" table the following trigger is generated  So any modifications  to the "Products" table will update the "AspNet_SqlCacheNotification' table.


dbo.[Products_AspNet_SqlCacheNotification_Trigger] ON



AS BEGIN SET NOCOUNT ON EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure



The "AspNet_SqlCacheTablesForChangeNotification" contains a single record for each  table you're monitoring. When you make a change in the table (like  inserting, updating, or deleting a record), the change Id column is incremented by one .ASP.NET queries this table continuously keeps track of the most recent changed values for each  table. When this value changes in a subsequent read, ASP.NET knows that the table has changed.

357_sql cache2.png

Figure : - Entries in the Cache notification table

Posted Date: 9/21/2012 9:17:14 AM | Location : United States

Related Discussions:- Sql cache dependency, Assignment Help, Ask Question on Sql cache dependency, Get Answer, Expert's Help, Sql cache dependency Discussions

Write discussion on Sql cache dependency
Your posts are moderated
Related Questions
Need Report Generation in SQL / .NET Project Description: Prepare a report generation web page by pulling information from an SQL Database. Filter parameters per report :

What is Thread.Sleep() The Thread's execution can be paused by calling Thread.Sleep method. This Thread.Sleep() method takes an integer value which  determines how long the thr

What is a workflow? A workflow is a collection of actions (called activities) that presents the model of a process. A workflow gives a way to explain the order of the execution

What is the namespace in which .NET have the data functionality classes? System.data : This namespace contains the basic objects used for accessing &  storing relational d

What are satellite assemblies? We use resource files to store data according to the localized languages. But when you actually go for the deployment you will not like to also i

How do we connect to SQL SERVER, which namespace do we use ? The code is shown below, after that i will  give the explanation for it. For this sample we will also require a SQL

Can we run DOT.NET in UNIX platform? One of the drawbacks of using Visual Studio.NET and the .NET framework to develop applications has been the lack of cross-platform support.

Explain Forms authentication You, as a Web application developer, are supposed to increase the Web page and authenticate the user by checking the given user ID and password aga

Project Description:  You require access to a commercial version of the 3CX PBX system in order to be able to program the API You need sufficient technical knowledge about PB

What is cross page posting? By default, the button controls in the ASP.NET pages post back to the similar page that contains button, where you can write an event handler for th