Text version of the video
All ASP .NET Text Articles
All ASP .NET Slides
All Dot Net and SQL Server Tutorials in English
All Dot Net and SQL Server Tutorials in Arabic
In this video, we will discuss about removing data from cache, when the data in the table from which it has come, has changed. Let us understand cache dependency on sql server database table, with an example.
First create "tblProducts" table and populate with sample data using the script below
CREATE TABLE [tblProducts]
[Id] [int] PRIMARY KEY IDENTITY,
[Name] [nvarchar](50) NULL,
[Description] [nvarchar](250) NULL
Insert into tblProducts values ('Laptops', 'Dell Laptops')
Insert into tblProducts values ('iPhone', 'iPhone 4S')
Insert into tblProducts values ('LCD TV', 'Samsung LCD TV')
Insert into tblProducts values ('Desktop', 'HP Desktop Computer')
To enable SqlCacheDependency on the database and table, we are using "EnableNotifications()" and "EnableTableForNotifications()" methods, as shown below.
Alternatively, to enable SqlCacheDependency on the database and table, we can use a command line tool, aspnet_regsql.exe. Open visual studio command prompt and execute the following 2 commands
To enable SqlCacheDependency on the "Sample" database:
aspnet_regsql -ed -E -d Sample
To enable SqlCacheDependency on "tblProducts" table in "Sample" database:
aspnet_regsql -et -E -d Sample -t tblProducts
If you need to understand the purpose of -et, -E, -d, -t, use the help, by typing the following command
Finally, in web.config specify database connection string and SqlCacheDependency. Notice that, we have set pollTime="2000". pollTime attribute specifies the frequency, at which, asp.net is going to check the database for changes. This time is in milli-seconds. Since, we have specified 2000 milli-seconds, asp.net is going to check the database for changes every 2 seconds. The default is 500 milli-seconds.
Run the application and when we click "Get Products" button for the first time, data is loaded from database. Click again, the data should be loaded from cache. Now, execute the following UPDATE query.
Update tblProducts set Name='Laptops' where Id = 1
Now, click the button again, and notice that the data is loaded from the database, as existing data is removed from cache.