Here is a little code that will create a class that you can use with Enterprise Library caching. Basically it allows you to define a WHERE clause where any object in that WHERE clause changes (across a single table) then the cache will expire the object in the cache. Think of it as a way to store a List<> of object that represent rows in a table in the cache and be able to detect if any row changes so that you can drop the cached list. LastUpdate is a timestamp column that needs to be on every row. This works since timestamp column type is always incremented by one, which means that SELECT MAX(LastUpdate) will tell you the maximum change across all rows.
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Caching;
using Microsoft.Practices.EnterpriseLibrary.Caching.Properties;
namespace ERS.Base
{
/// <summary>
///
/// </summary>
public class TableSqlDependency : ICacheItemExpiration
{
String _tableName = String.Empty;
String _connectionString = String.Empty;
String _where = String.Empty;
Byte[] _lastUpdate = null;
/// <summary>
///
/// </summary>
/// <param name="connectionString"></param>
/// <param name="tableName"></param>
public TableSqlDependency(String connectionString, String tableName)
{
_connectionString = connectionString;
_tableName = tableName;
}
public TableSqlDependency(String connectionString,
String tableName,
String where)
{
_connectionString = connectionString;
_tableName = tableName;
_where = where;
}
// Summary:
// Specifies if item has expired or not.
//
// Returns:
// Returns true if the item has expired, otherwise false.
public bool HasExpired()
{
Byte[] lastUpdate = LastUpdate;
if ((lastUpdate == null) && (_lastUpdate != null))
return (true);
if ((lastUpdate != null) && (_lastUpdate == null))
return (true);
if ((lastUpdate == null) && (_lastUpdate == null))
return (false);
if (lastUpdate.Length != _lastUpdate.Length)
return (true);
for (int i = 0; i < lastUpdate.Length; i++)
if (lastUpdate[i] != _lastUpdate[i])
return (true);
return (false);
}
private Byte[] LastUpdate
{
get
{
Byte[] lastUpdate = null;
using (SqlConnection sqlConnection =
new SqlConnection(
ConfigurationManager.ConnectionStrings[_connectionString]
.ConnectionString))
{
String sql = String.Empty;
if (!String.IsNullOrEmpty(_where))
sql = String.Format(
"SELECT MAX([LastUpdate]) 'Max' FROM [{0}] WHERE {1}",
_tableName, _where);
else
sql = String.Format(
"SELECT MAX([LastUpdate]) 'Max' FROM [{0}]",
_tableName);
using (SqlCommand sqlCommand =
new SqlCommand(sql, sqlConnection))
{
sqlConnection.Open();
sqlCommand.CommandType = CommandType.Text;
using (SqlDataReader sqlDataReader =
sqlCommand.ExecuteReader())
{
if (!sqlDataReader.HasRows)
lastUpdate = null;
sqlDataReader.Read();
lastUpdate = (Byte[])sqlDataReader["Max"];
}
}
}
return (lastUpdate);
}
}
//
// Summary:
// Called to give the instance the opportunity to
// initialize itself from information
// contained in the CacheItem.
//
// Parameters:
// owningCacheItem:
// CacheItem that owns this expiration object
public void Initialize(CacheItem owningCacheItem)
{
_lastUpdate = LastUpdate;
}
//
// Summary:
// Called to tell the expiration that the CacheItem to which
// this expiration
// belongs has been touched by the user
public void Notify()
{
}
}
}
{6230289B-5BEE-409e-932A-2F01FA407A92}