There are various types of caching available in .Net. Then what's special in Sqlserver query notification. Basically one of the pitfalls with ASP.NET is that it is app domain specific. So if you have a web farm environment, each web server has its own copy of the cache, and this means there is a potential for data being out of sync. With SQL cache dependency you can cache the data on the web server but have SQL Server notify you when the data changes.
An overview of Query Notification
With query notification enabled then when you run a query against the back-end database, you not only retrieve the data into the cache, but also tell SQL Server to register a subscription for notification if the underlying data changes in a way that will affect the result of the query.
When the notification is received, the event handler in your application invalidates the cache and the next time the application runs the query, it will fetch the data from the back-end server.
All this is done without the need to write any complex application code.
- Configure SQL Server to support SQL Cache invalidation. This is a one-time setup of the tables or databases in the SQL Server database that you want to monitor.
- Add the necessary configuration information to the web.config file.
You can perform the configuration of SQL Server 2000 to support SQL Cache invalidation in two ways:
- Using the aspnet_regsql utility
- Using the EnableTableForNotifications method of the SqlCacheDependencyAdmin class
Command : aspnet_regsql -S localhost -U sa -P atanu -d TestDB -ed
- S—Name of the Server
- U—User ID to use to connect to the SQL Server
- P—Password to use to connect to the SQL Server
- d—The name of the database
- ed—Enables the database for SQL Server-triggered cache invalidation
Command :aspnet_regsql -S localhost -U sa -P atanu -t Products -d TestDB -et
In the above command:
- t—Specifies the name of the table
- et—Enables the table for SQL Server-triggered cache invalidation
Web Configuration Settings for SQL Cache Invalidation
protected void Page_Load(object sender, EventArgs e)
GridView1.DataSource = bindCachedData();
private DataSet bindCachedData()