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.
Figure : - SQL Cache table created for notification
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.
CREATE TRIGGER
dbo.[Products_AspNet_SqlCacheNotification_Trigger] ON
[Products]
FOR INSERT, UPDATE, DELETE
AS BEGIN SET NOCOUNT ON EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure
N'Products'
END
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.
Figure : - Entries in the Cache notification table