Caching data that comes from an SQL database (in other words dependencies to database tables) in RAM has been supported since ASP.NET 2.0. In practice this means, we can cache the data for undetermined period until the source database table is updated. This cache mechanism works for SQL Server 7 and later including SQL 2005 and SQL 2008. SQL 2005 and SQL 2008 supports another type of cache invalidation based on events. I call first mechanism as Table Level SQL Dependencies and second as Command Level SQL Dependencies. In this article, "Table Level SQL Dependencies" will be spoken. For "Command Level SQL Dependencies" you can look at "Caching with SQL Dependency Support-2".

Table Level SQL Dependencies
This cache invalidation mechanism use polling technique and creates a table AspNet_CacheTablesForChangeNotification for the database in which you want to enable cache invalidation and creates a row for each table, you customly select these ones, in this database. ASP.NET runtime engine places a counter in these rows and checks every so often (the interval is configurable) and if it is updated than deletes the cached information from the cache. To create required database schema run aspnet_regsql.exe run below command:

aspnet-regsql.exe -E -S .\YourCustomSqlServerNameHere -d YourCustomDatabaseNameHere -ed
 -E option is for Windows Integrated Security
 -S for Sql Server Instance name
 -d for database name
 -ed for "enable database"

Above command creates required tables, triggers, stored procedures etc. The next step is adding a row to this table for the table to which you're adding support  (this also automatically adds a trigger to your table)

aspnet-regsql.exe -E -S .\YourCustomSqlServerNameHere -d YourCustomDatabaseNameHere -t Customers -et
 -t is for table name
 -et is for "enable table"

The last thing to enable Sql Cache dependency is to configure web.config:

<configuration>
 <connectionStrings>
  <add name="MyConString"............ //other details here    />
 </connectionStrings>
 
 <system.web>
  <caching>
   <sqlCacheDependency enabled="true" pollTime="20000">
    <databases>
     <add name="MyCustomCache " connectionStringName="MyConString" pollTime="5000" />
    </databases> 
   </sqlCacheDependency>
  </caching>
 </system.web>
</configuration>

pollTime above defines the polling interval of 20000 milliseconds (20 seconds) default and re-writes it for our specific database by 5000 milliseconds (5 seconds) Now everything is configured so let’s try code:

public List GetProducts()
{
     List productNames = null;
     if (Cache["ProductNames"] != null) 
     {
         productNames = (List)Cache["ProductNames"];
         //if cache already contains the info return it
     }
     else
     {
         using (SqlConnection conn = new SqlConnection(_connString))
         {
             SqlCommand cmd = new SqlCommand("SELECT ProductNames FROM Products", cn);
             productNames = cmd.ExecuteReader().FillListFromReader(cmd.ExecuteReader());

             //FillListFromReader is an umimplemented method that takes the
             //the result of "ExecuteReader" and converts it to List

             SqlCacheDependency dep = new SqlCacheDependency("MyCustomCache", "Products");
             //MyCustomCache is the name we defined in web.config
             //Products is the table name we want to cache on it

             Cache.Insert("ProductName", productNames, dep);
         }
     }
}