Using caching with a SQL dependency can dramatically increase application performance in certain scenarios.For example, imagine you are building an e-commerce application that displays product information from a database.This can make sure that you users always get the latest data from the server. Start() method must be called before the first SQL query is executed.
In the Private Sub Page_Load(By Val sender As System.
SQL cache dependency is useful for data such as product catalogs or customer registration information that remains comparatively static.
The query notification mechanism of SQL Server 2005 detects changes to data that invalidate the results of an SQL query and removes any cached items associated with the SQL query from the System. Caching class to add items to your application's Cache that are dependent on either a SQL Server database table or on an SQL query when using SQL Server 2005.
You can also use this class with the directive to make an output-cached page or a user control dependent on a SQL Server database table.
This enables cached query results to be automatically evicted from the cache if the underlying data changed. After you run the statement, you hit refresh at your database.
You will notice there is an extra table created to store the cache data and a trigger created for the particular table.For this class to work correctly when using table-based notifications, the database and any tables that you want to make dependencies on must have notifications enabled.You can enable notifications by calling methods of the Sql Cache Dependency Admin class or by using the command-line tool.You can set a dependency on a table in Microsoft SQL Server 7.0, SQL Server 2000, and SQL Server 2005.If you are using SQL Server 2005 you can also set a dependency on a specific record.Furthermore, no special configuration using the aspnet_tool is needed. This option is much more efficient as there is no pollung going on but the acts as client, only if there is changes, will get notified.