Enabling SQL Cache Dependency in ASP.NET

1• Enable notifications for the database.
2• Enable notifications for individual tables.
3• Enable ASP.NET polling using “web.config” file
4• Finally use the Cache dependency object in your ASP.NET code

1. Enabling notifications for the database.

a. Enable notifications for the database, that can be done by using aspnet_regsql.exe, a command line utility.

CD c:\[WinDir]\Microsoft.NET\Framework\[Version] directory

b. execute the command at the same location as

c:>[WinDir]\Microsoft.NET\Framework\[Version] directory> aspnet_regsql -ed -E -d MyDBName

Switch	Description
-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

The above command will cause to create one new table and new stored procedures as specified below in the database.

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

Procedure Name	Description
AspNet_Sql CacheRegisterTable StoredProcedure	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_SqlCache UnRegisterTable StoredProcedure	This stored procedure takes a registered table and removes the notification trigger so that notifications won't be generated.
AspNet_Sql CacheUpdateChange IdStoredProcedure	The notification trigger calls this stored procedure to update the AspNet_SqlCacheTablesFor ChangeNotification table, thereby indicating that the table has changed.
AspNet_SqlCache QueryRegistered TablesStored Procedure	This extracts just the table names from the AspNet_SqlCacheTablesForChangeNotification table. It’s used to get a quick look at all the registered tables.
AspNet_SqlCache PollingStored Procedure	This will get the list of changes from the AspNet_SqlCacheTablesForChangeNotification table. It is used to perform polling.

Table Name	Description
AspNet_SqlCacheTablesForChangeNotification	Has three columns: tableName, notificationCreated, and changeId. This table is used to track changes. Essentially, when a change takes place, a record is written into this table. The SQL Server polling queries this table. Also a set of stored procedures is added to the database as well. See the following table.

2. Enabling notification for individual tables

After enabling notification for database, need to enable notification support for each individual table.

a. This also can be done manually using AspNet_SqlCacheRegisterTableStoredProcedure in query analyzer, for example, as given below:

exec AspNet_SqlCacheRegisterTableStoredProcedure 'TableName'

b. This also can be done with the use of "aspnet_regsql" with parameters as discussed

Parameter	Description
-et	to enable a able for sql cache dependency notifications
-t	to name the table

e.g.

aspnet_regsql -et -E -d Northwind -t 'TableName'

 Registering tables for notification internally creates trigger for the tables. For instance for
a "TableName" table the following trigger is created. So any modifications done to the
"TableName" table will update the "AspNet_SqlCacheNotification" table.

CREATE TRIGGER
dbo.[Products_AspNet_SqlCacheNotification_Trigger] ON
[TableName]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON
EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure
N'Products‘
END

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.

Continue .......