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.
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.
New stored procedures created
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_SqlCacheTablesFor ChangeNotification table, thereby indicating that the table has changed.
Asp Net_Sql Cache Query Registered Tables Stored Procedure:- 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.
Continues the same question in the next part.