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 null then fetch from the database
    if (Cache["Users"] == null)
    {
        // Create the cache dependency
        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);

        // put in the cache object
        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.

 

 

http://www.codeproject.com/Articles/14976/ASP-NET-Caching-Dependencies

http://www.c-sharpcorner.com/UploadFile/vishnuprasad2005/ImplementingCachinginASP.NET11302005072210AM/ImplementingCachinginASP.NET.aspx

http://www.asp.net/web-forms/tutorials/data-access/caching-data/using-sql-cache-dependencies-cs