Introduction
Among all of .NET's good features caching is one of my favorites. Caching is really very easy to understand. It's concept is that if we need to fetch the data multiple times from SQL Server or from any file then it will cause a reduction of performance as we are retrieving the same data multiple times from the database or a file so why retrieve the same data again and again we can just store that data in our cache object.
The scenario is somewhat like this,
If your site is related to a game such as cricket or football then many people are simultaneously and constantly watching your site and refreshing the page; just assume that there are approximately 50k persons online on your site and they refresh the page at approximately the same time; how can you show them the data, opening the database or the file multiple times; that's really impossible so we can use caching instead so we just need to read data one time and store in the server's memory and giving same data to rest of the people.
We can configure SQL Server and ASP.NET to cache page requests in order to reduce server load, until the data on which the page depends has been updated in SQL Server. SQL cache dependency is useful for data such as product catalogs or customer registration information that remains comparatively static until we don't make changes in the database.
Now to do this we need to create sqlcahcedependancy and with the help of that we can make cache object clear whenever there is some change in the database table and till that we can show the data of our cache object to user.
Now we will go towards the coding part.
First we need to create notification tables in the SQL Server with the help of aspnet_regsql.exe command
The typical syntax will look like,
aspnet_regsql.exe -S localhost -U sa -P password -d Northwind -ed
here,
-d <database> Database name for use with SQL cache dependency. The database can optionally be specified using the connection string with the -c option instead. (Required)
-ed Enable a database for SQL cache dependency.
-dd Disable a database for SQL cache dependency.
-et Enable a table for SQL cache dependency. Requires -t option.
-dt Disable a table for SQL cache dependency. Requires -t option.
-t <table> Name of the table to enable or disable for SQL cache dependency. Requires -et or -dt option.
-lt List all tables enabled for SQL cache dependency.
Or you can fire this query,
ALTER DATABASE mydb SET ENABLE_BROKER
SELECT name, is_broker_enabled FROM sys.databases
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "your server\sqlexpress"
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "your server \ASPNET"
After doing this you can see that there are some tables and procedures generated in your database.
If not then you might have done something wrong.
Now your SQL Server is ready for the notification and now to get notification from sqlserver to your application.
For that you need to fire following code best thing is that if you put this in the global.asax file in application_start event.
void Application_Start(object sender, EventArgs e)
{
string cn = ConfigurationManager.ConnectionStrings["myconn"].ConnectionString;
System.Data.SqlClient.SqlDependency.Start(cn);
}
Now your application is ready to use caching.
Here is the simple code for that,
private void refresh()
{
if (Cache["data"] == null)
{
cmd.Notification = null;
cmd.NotificationAutoEnlist = true;
System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications(ConfigurationManager.ConnectionStrings["myconn"].ConnectionString); System.Web.Caching.SqlCacheDependencyAdmin.EnableTableForNotifications(ConfigurationManager.ConnectionStrings["myconn"].ConnectionString, "table1");
System.Web.Caching.SqlCacheDependency dep = new System.Web.Caching.SqlCacheDependency("myconn","table1");
SqlDataAdapter _adp = new SqlDataAdapter("select * from table1", cn);
DataSet ds = new DataSet();
_adp.Fill(ds);
//Cache.Add("file", ds1, new System.Web.Caching.CacheDependency(str1), DateTime.Now.AddDays(55), TimeSpan.Zero, System.Web.Caching.CacheItemPriority.Default, null);
// Cache.Add("data", ds, dep, DateTime.Now.AddDays(234), TimeSpan.Zero, System.Web.Caching.CacheItemPriority.Default, null);
Cache.Insert("data", ds, dep);
}
else
{
}
GridView1.DataSource = Cache["data"];
GridView1.DataBind();
}