To configure SQL cache invalidation two steps are required, first configure SQL server to cache invalidation and second add configuration information in “caching” section under “system.web” in web.config file.
Following are the important steps to configure SQL server:
I’ve used database named “Payroll”. In payroll, I am going to set SQL cache validation on “Employee” table which contains detailed information of working employees.
1. Open Visual Studio .NET 2005 command prompt. (By navigating to Microsoft Visual Studio 2005 -> Visual Studio Tools -> Visual Studio .NET 2005 Command Prompt)
2. Use aspnet_regsql tool to enable SQL cache invalidation. The aspnet_regsql tool is present in your Windows\Microsoft.NET\Framework\[version] folder. Use command prompt to navigate for this folder.
3. To enable SQL cache invalidation on payroll table in SQL Server issue following command.
aspnet_regsql -E -d Payroll –ed
Press enter to execute the command.
Following is the information about options used with aspnet_regsql command:
-E: Used to enable integrated security when connecting to your database server.
-d: Used to select the Payroll database.
-ed: Used to enable the database for SQL Cache Invalidation.
payroll: It is the name of database.
After successful execution of an above command AspNet_SqlCacheTablesForChangeNotification, new table is added to the payroll database. This table contains a list of all of the database tables that are enabled for SQL cache invalidation. Some new stored procedures are also added to the database.
4. Now select a table in payroll database for SQL cache invalidation. Let’s select “Employee” table.
aspnet_regsql -E -d Payroll -t Employee –et
Following is the information about option used in above commands:
-t: Used to select a database table. In my case it is Employee table.
-et: Used to enable a database table for SQL cache invalidation.
To enable cache invalidation for multiple tables re-execute this command for each database table.
This command adds trigger to database table. Trigger fires whenever changes are occurred in that table.
By doing above steps configuration at SQL server is complete here. But second part is still remaining that is adding some entries in web.config file without this caching won’t work even if you configured it for SQL Server.
File Name: web.config
<configuration>
<connectionStrings>
<add name="PayrollConnection"
connectionString="Server=localhost;Database=Payroll" />
</connectionStrings>
<system.web>
<caching>
<sqlCacheDependency enabled="true">
<databases>
<add name="Payroll"
connectionStringName="PayrollConnection"
pollTime="50000" />
</databases>
</sqlCacheDependency>
</caching>
</system.web>
</configuration>
