Hello Stephane,
Thank you for the positive feedbacks of my initial reply.
The introduction of Caching in ASP.NET with the SqlCacheDependency Class is
the MSDN article:
http://msdn.microsoft.com/en-us/library/ms178604.aspx
It depicts the main features of ASP.NET SQL cache dependency, and tells the
different implementation of the mechanism in SQL server 2000 and 2005: SQL
server 2005 has the built-in support of Query Notification, whereas, SQL
server 2000 replies on polling which is less efficient.
To use the SQL Cache Dependency in the environment of ASP.NET 2.0 + SQL
Server 2005, please follow these two steps:
*Step 1. (SQL Server) Enable "Query Notification" in SQL server 2005*
http://msdn.microsoft.com/en-us/library/ms172133.aspx
with the SQL command:
ALTER DATABASE [DB Name] SET ENABLE_BROKER;
Note1: Before the run of this command, please make sure all the SQL
connections of the DB are disconnected. Otherwise, the command will hang.
Note2: Certain versions of SQL server 2005 don't include Notification
services:
http://technet.microsoft.com/en-us/l.../ms143500.aspx
*Step 2. (ASP.NET) Write code to use SqlCacheDependency*
http://msdn.microsoft.com/en-us/library/9dz445ks.aspx
This is a step-by-step demo of SqlCacheDependency in ASP.NET. If you are
interested in how to use it in Windows application, please refer to:
http://msdn.microsoft.com/en-us/library/a52dhwx7.aspx
A frequently used tip is to put the code:
SqlDependency.Start(GetConnectionString());
Into global.asax Application_Start function:
protected void Application_Start(object sender, EventArgs e)
{
SqlDependency.Start(GetConnectionString());
}
When you call this method, the runtime sets up the relationship between the
application domain and the connection string that will be used later during
the notification registration process.
For more reading of SQL Cache Dependency samples:
http://www.writebetterbits.com/2008/...2005-database_
11.html
This is a good demo of SQL Cache Dependency with LINQ
By the way, you may find some articles like these online:
http://www.codeproject.com/KB/aspnet...ASPNET_20.aspx http://www.codeproject.com/KB/web-ca...endencies.aspx
They are about SQL Cache Dependency for SQL server 2000, which requires the
commands:
aspnet_regsql -ed -E -d School
aspnet_regsql -et -E -d School -t Users
These are not necessary in SQL server 2005.
Please let me know if you have any other concerns, or need anything else.
Regards,
Jialiang Ge (jialge@online.microsoft.com, remove 'online.')
Microsoft Online Community Support
=================================================
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
msdnmg@microsoft.com.
This posting is provided "AS IS" with no warranties, and confers no rights.
=================================================
