Introduction
To improve web application performance a number of techniques are used and caching is one of them. Caching is heavily used in applications where data remains static so to avoid database operations we fetch data for the data request from database after which data is cached for later use thus avoiding the round trips to database server and improving performance. All goes well but now lets take another scenario wherein data changes after a certain period of time lets say for example take the case of print edition of magazine where data changes every month.
We can use caching for this purpose but after one month we need to update the cache manually or through external application but that prior to .Net 2.0. Asp.Net 2.0 introduced two new classes:-
1.) SQLCacheDependency
2.) XMLCachedependency
In this article I will try to put some light on SQLCacheDependency class. SQLCacheDependency class establishes a relationship between an item stored in an ASP.NET application's Cache object and either a specific SQL Server database table or the results of a SQL Server 2005 query. This class cannot be inherited.
NameSpace:- System.Web.Caching
Syntax:
[AspNetHostingPermissionAttribute(SecurityAction.LinkDemand, Level = AspNetHostingPermissionLevel.Minimal)]
public sealed class SqlCacheDependency : CacheDependency
At the time to adding item to cache we also specify a SQLCacheDependency on a database table.The SQLCacheDependency class basically puts a watch on a specified database table and whenever contents of the table changes, item is removed from cache and a new version of the item is added to the Cache. The SqlCacheDependency class also supports integration with the System.Data.SqlClient..::.SqlDependency class when using a SQL Server 2005 database. 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.Web.Caching.Cache.
There are a few steps to configure SQLCacheDepdency in your application:-
1.) Before SqlCacheDependency will work with SQL Server 2005, you first have to enable Service Broker, which is reponsible for the notification services that let the web cache know a change has been made to the underlying database and that the item in the cache must be removed.
ALTER DATABASE Store SET ENABLE_BROKER;
2.) In ASP.NET, you need to run SqlCacheDependency.Start(connectionString) in the Global.asax:
void Application_Start(object sender, EventArgs e)
{
string connectionString = System.Web.WebConfigurationManager.
ConnectionStrings["key"].ConnectionString;
SqlDependency.Start(connectionString);
}
3.) Now create SqlCacheDependency in your ASP.NET 2.0 page as
protected void Page_Load(object sender, EventArgs e)
{
DataTable myTable = (DataTable)Cache.Get("myTableName ");
if (myTable == null)
{
myTable = GetTableContents();
}
GridView1.DataSource = myTable.DefaultView;
GridView1.DataBind();
}
private DataTable GetTableContents()
{
string connectionString = System.Web.WebConfigurationManager.
ConnectionStrings["key"].ConnectionString;
DataTable myTable = new DataTable();
using (SqlConnection connection =
new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(
"SELECT col1,col2,col3
FROM dbo. myTableName", connection);
SqlCacheDependency dependency =
new SqlCacheDependency(command);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = command;
DataSet ds = new DataSet();
adapter.Fill(ds);
myTable = ds.Tables[0];
Cache.Insert("myTable", myTable, dependency);
}
return myTable;
}
4.) And this is what web.config looks like
Or Just change the web.config file as
connectionStringName="pubs"
pollTime="9000000"
/>
And now you can do everything we did from step 1-4 by below code
SqlCacheDependency dep;
if (Cache("SqlSource") != null)
{
try
{
SqlDep = new SqlCacheDependency("Northwind", "Categories");
}
catch (DatabaseNotEnabledForNotificationException exDBDis)
{
try
{
SqlCacheDependencyAdmin.EnableNotifications["Northwind"];
// If the database does not have permissions set for creating tables,
// the UnauthorizedAccessException is thrown. Handle it by redirecting
// to an error page.
}
catch ( UnauthorizedAccessException exPerm )
{
Response.Redirect("~/ErrorPage.htm");
}
}
catch ( TableNotEnabledForNotificationException exTabDis )
{
try
{
SqlCacheDependencyAdmin.EnableTableForNotifications("Northwind", "Categories");
}
// If a SqlException is thrown, redirect to an error page.
catch ( SqlException exc)
{
Response.Redirect("~/ErrorPage.htm");
}
}
finally
{
Cache.Insert("SqlSource", Source1, SqlDep);
}
}
|
| Author: Rakesh Kumar 12 Jun 2008 | Member Level: Bronze Points : 0 |
Very nice article Sir
Thnks
|
| Author: Sebastian 13 Jun 2008 | Member Level: Gold Points : 1 |
This is very informative. Thanks for sharing the details.
|