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);
         }
     }
}

Whether we use ADO.NET or not generally we don't know about hierarcy of ADO.NET namespaces and classes. Therefore, I want to write its general hierarcy:

ADO.NET NAMESPACES

System.Data: The core of ADO.NET. Tables, Rows, Columns and DataSet  are all tools that are in System.Data. This namespace implements IDbCommand, IDbConnection, IDbDataAdapter interfaces.

System.Data.Common: This namespace is generally for custom data providers because it includes base classes of all providers in ASP.NET. For example if you want to write your own SQL provider, then you should use System.Data.Common.

System.Data.SqlClient: Data Provider for SQL. SqlCommand,SqlConnection etc. are in this class.

Now let's group ADO.NET classes considering their base features.

Classes don't need live connection to database:  Their most important and basic future is to recreate tables (with its rows, columns, functional dependencies and all its futures) in memory and manipulate them. When needed they send this tables to client side. These classes includes DataSets, DataTables.

Classes do need live connection to database:   Like DataReader class, gets the actual form of database and sends it to the client. If there is an update on the server side it should be resent. To do so, it requires live connection.

Base classes for Data Providers : These classes are in System.Data.Common and are base classes for all Data Providers. (including our own custom providers)

DataProvider classes: For each Data Provider, we have a unique DataProvider class. We do all operation related to a specific data source by its own DataProvider. Each and every Data Provider has its own DataConnection, Command, Adapter and Reader objects.

Let's examine a portion of a data access layer class. (This is a general ADO.NET example. In subsequent entries, I'll give more specific examples)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;           //This is for MSSQL operations. 

public static void Insert_Customers(Guid? userID, string name, string surname, string tel_no)
{
SqlConnection conn = null;  //for database connection
SqlCommand cmd = null;   
//  with this object we send necessary commands (sql queries)
//  to database
SqlTransaction trans = null;  //SqlTransaction is used for insert and delete
//when there is an exception, we can undo our operations
//with this object 
try
{
conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ImaletConnectionString"].ConnectionString);
conn.Open();
trans = conn.BeginTransaction();
cmd = new SqlCommand("Insert_Customer",conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Transaction = trans;
cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier); 
//add sql parameters with the same name in sql queries
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 20);  
cmd.Parameters.Add("@Surname", SqlDbType.NVarChar, 20);
cmd.Parameters.Add("@Tel_No", SqlDbType.NVarChar, 20);
cmd.Parameters[1].Value = name;
cmd.Parameters[2].Value = surname;
cmd.Parameters[3].Value = tel_no;
cmd.ExecuteNonQuery();
trans.Commit();
}
catch (Exception pr)
{
if (trans != null)
trans.Rollback();       
//if there is an exception undo current operation
throw new Exception();
}
finally
{
if (conn != null)
conn.Close();  
// with Dispose() we prompt C# compiler to delete our objects
cmd.Dispose();
trans.Dispose();
conn.Dispose();
}
}