Cache dependency on sql server database table Part 131

Text version of the video
http://csharp-video-tutorials.blogspot.com/2013/02/cache-dependency-on-sql-server-database.html

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.
https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1

Slides
http://csharp-video-tutorials.blogspot.com/2013/08/part-131-cache-dependency-on-sql-server.html

All ASP .NET Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-aspnet-video-tutorial.html

All ASP .NET Slides
http://csharp-video-tutorials.blogspot.com/p/aspnet-slides.html

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd

All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists

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.
System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications(CS)
System.Web.Caching.SqlCacheDependencyAdmin.EnableTableForNotifications(CS, "tblProducts");

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
aspnet_regsql /?

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.