Cache Dependency on SQL
One of the biggest improvements in ASP.NET 2.0 Caching is the feature
of Cache dependency on database tables. This means that the data will
be present in the Cache as long as the table entries does not change.
As, soon as the database table is changed the Cache is expired. You can
enable the SQL Cache dependency by using the
aspnet_regsql.exe command line tool. Simply, type the following command on the Visual Studio.NET 2005 command line.
aspnet_regsql -ed -E -d School
The command above will enable the Cache dependency on the "School"
database. The next step is to enable the caching on the individual
table. You can do that by using the following line.
aspnet_regsql -et -E -d School -t Users
The above line will enable the caching on the Users table which is contained in the School database.
The next step is to create the
connectionString in the
connectionStrings section and
sqlCacheDependency in the
web.config file. Take a look at the code below:
<connectionStrings>
<add name="ConnectionString"
connectionString="Server=localhost;Database=School;
Trusted_Connection=true"/>
</connectionStrings>
<system.web>
<caching>
<sqlCacheDependency pollTime="10000" enabled="true" >
<databases>
<add connectionStringName="ConnectionString" name="School"/>
</databases>
</sqlCacheDependency>
</caching>lt;/caching>
As, you have noticed that the
sqlCacheDependency have a
pollTime
attribute which is set to "1000" milliseconds. This means that the
ASP.NET will check the database table for any changes every 10 seconds.
The database section of the
<caching> contains the
connectionString which is used to connect to the database.
The final step is to use the caching in your code. You can do this in
various ways. Take a look at the following code which uses caching
programmatically.
private void BindData()
{
if (Cache["Users"] == null)
{
SqlCacheDependency dep = new SqlCacheDependency("School", "Users");
string connectionString = ConfigurationManager.ConnectionStrings[
"ConnectionString"].ConnectionString;
SqlConnection myConnection = new SqlConnection(connectionString);
SqlDataAdapter ad = new SqlDataAdapter("SELECT FirstName, LastName " +
"FROM Users", myConnection);
DataSet ds = new DataSet();
ad.Fill(ds);
Cache.Insert("Users", ds, dep);
}
gvUsers.DataSource = Cache["Users"] as DataSet;
gvUsers.DataBind();
}
The line
SqlCacheDependency dep = new SqlCacheDependency("School", "Users"); is used to create the caching on the School database and Users table. In the beginning of the
BindData
method I check that if the item is already in the Cache. If it is then I
simply return the item using caller by casting it from the Cache
object. If the item is not in the Cache then the data is fetched from
the database and inserted into the Cache object. The Cache will be
discarded anytime you make a change in the database table "Users". This
means that if you INSERT, DELETE, UPDATE any data in any row in the
Users table then the Cache will be considered obsolete and a copy of the
fresh data will be fetched from the database.
Caching in SQL Server 2005 have a different architecture then in SQL Server 2000. You don't have to write any lines in
web.config
to enable Caching in SQL Server 2005. Also, in SQL Server 2005 the
Cache is only expired when the row is changed in the database table.
