I created a simple Caching Data Access Layer that has caching using the Enterprise Library Caching Application Block and also makes use of SQL Query Notification - therefore not supporting any queries which are not valid for query notification.
Background: This was put in place after the application was developed in order to lighten the load on the database and speed up the application. The main use of this DAL is for pulling data that is not expected to change very often such as data in Look Up Tables (presented in drop downs on the UI, etc).
It is mainly used like the following example:
var cachingDal = new CachingDataAccessLayer();
var productTypes = cachingDal.LoadData<ProductType>();
Where ProductType is a Linq to SQL table. I am curious to see what people think of the implementation I came up with and if it is horrible or amazing.
Here's the code. Looking for any suggestions, criticisms, etc etc. Keep in mind I didn't choose the technology and am building on top of an existing system so switching data access stories is not really my call.
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using Microsoft.Practices.EnterpriseLibrary.Caching;
using Microsoft.Practices.EnterpriseLibrary.Logging;
using MyDatabase;
public class CachingDataAccessLayer
{
#region Cache Keys
private const string CacheManagerName = "CachingDataAccessLayer";
#endregion
#region Database
/// <summary>
/// Instantiate new MyDataContext
/// </summary>
/// <returns></returns>
private MyDataContext DatabaseConnection()
{
// instantiate database connection
var database = new MyDataContext(Constants.DatabaseConnectionString);
// set transaction isolation level to read committed
database.ExecuteQuery(typeof(string), "SET TRANSACTION ISOLATION LEVEL READ COMMITTED");
return database;
}
#endregion
#region Generic Data Access with Caching
/// <summary>
/// Calls .Exists on list using predicate and if it evaluates to false, adds records to list using predicate.
/// </summary>
/// <typeparam name="TEntity">Database table</typeparam>
/// <param name="list">List to add records to</param>
/// <param name="predicate">The delagate that defines the conditions of elements to search for.</param>
public void AddRecordsIfNeeded<TEntity>(ref List<TEntity> list, Predicate<TEntity> predicate) where TEntity : class
{
// check if items are in list based on predicate and if not, add them to the list
if (!list.Exists(predicate))
{
list.AddRange(LoadData<TEntity>(predicate.Invoke));
}
}
/// <summary>
/// Retrieve all records of type TEntity from the cache if available with filter Active = true (if Active property exists).<br/>
/// If data is not available in cache go directly to the database.<br/>
/// In addition, sets up query notification and refreshes cache on database change.
/// </summary>
/// <typeparam name="TEntity">Database table to retrieve.</typeparam>
/// <returns>returns List of TEntity</returns>
public List<TEntity> LoadData<TEntity>() where TEntity : class
{
// default filter is no filter
Func<TEntity, bool> predicate = delegate { return true; };
// check for active property
var activeProperty = typeof (TEntity).GetProperty("Active");
// if active property exists and is a boolean, set predicate to filter Active == true
if (activeProperty != null)
if (activeProperty.PropertyType.FullName == typeof (bool).FullName)
predicate = (x => (bool) activeProperty.GetValue(x, null));
// load data & return
return LoadData(predicate);
}
/// <summary>
/// Retrieve all records of type TEntity from the cache if available.<br/>
/// If data is not available in cache go directly to the database.<br/>
/// In addition, sets up query notification and refreshes cache on database change.
/// </summary>
/// <typeparam name="TEntity">Database table to retrieve.</typeparam>
/// <param name="predicate">A function to test each element for a condition.</param>
/// <returns>returns List of TEntity</returns>
public List<TEntity> LoadData<TEntity>(Func<TEntity, bool> predicate) where TEntity : class
{
// default is to not refresh cache
return LoadData(predicate, false);
}
/// <summary>
/// Retrieve all records of type TEntity from the cache if available.<br/>
/// If data is not available in cache or refreshCache is set to true go directly to the database.<br/>
/// In addition, sets up query notification and refreshes cache on database change.
/// </summary>
/// <typeparam name="TEntity">Database table to retrieve.</typeparam>
/// <param name="predicate">A function to test each element for a condition.</param>
/// <param name="refreshCache">If true, ignore cache and go directly to the database and update cache.</param>
/// <returns></returns>
public List<TEntity> LoadData<TEntity>(Func<TEntity, bool> predicate, bool refreshCache) where TEntity : class
{
// instantiate database connection
using (var database = DatabaseConnection())
{
// instantiate the cache
var cache = CacheFactory.GetCacheManager(CacheManagerName);
// get cache key name
var cacheKey = typeof(TEntity).Name;
// if the value is in the cache, return it
if (cache.Contains(cacheKey) && !refreshCache)
// get data from cache, filter it and return results
return (cache.GetData(cacheKey) as List<TEntity>).Where(predicate).ToList();
// retrieve the data from the database
var data = from x in database.GetTable<TEntity>()
select x;
// if value is in cache, remove it
if (cache.Contains(cacheKey))
cache.Remove(cacheKey);
// add unfiltered results to cache
cache.Add(cacheKey, data.ToList());
Logger.Write(string.Format("Added {0} to cache {1} with key '{2}'", typeof(TEntity).Name, CacheManagerName, cacheKey));
// set up query notification
SetUpQueryNotification<TEntity>();
// return filtered results
return data.Where(predicate).ToList();
}
}
#endregion
#region Query Notification
public void SetUpQueryNotification<TEntity>() where TEntity : class
{
// get database connection
var database = DatabaseConnection();
// set up query notification
using (var sqlConnection = new SqlConnection(Constants.DatabaseConnectionString))
{
// linq query
var query = from t in database.GetTable<TEntity>()
select t;
var command = database.GetCommand(query);
// create sql command
using (var sqlCommand = new SqlCommand(command.CommandText, sqlConnection))
{
// get query parameters
var sqlCmdParameters = command.Parameters;
// add query parameters to dependency query
foreach (SqlParameter parameter in sqlCmdParameters)
{
sqlCommand.Parameters.Add(new SqlParameter(parameter.ParameterName, parameter.SqlValue));
}
// create sql dependency
var sqlDependency = new SqlDependency(sqlCommand);
// set up query notification
sqlDependency.OnChange += sqlDependency_OnChange<TEntity>;
// open connection to database
sqlConnection.Open();
// need to execute query to make query notification work
sqlCommand.ExecuteNonQuery();
}
}
Logger.Write(string.Format("Query notification set up for {0}", typeof(TEntity).Name));
}
/// <summary>
/// Calls LoadData of type TEntity with refreshCache param set to true.
/// </summary>
/// <typeparam name="TEntity">Database table to refresh.</typeparam>
void RefreshCache<TEntity>() where TEntity : class
{
// refresh cache
LoadData<TEntity>(delegate { return true; }, true);
}
/// <summary>
/// Refreshes data in cache for type TEntity if type is Delete, Insert or Update.<br/>
/// Also re-sets up query notification since query notification only fires once.
/// </summary>
/// <typeparam name="TEntity">Database table</typeparam>
void sqlDependency_OnChange<TEntity>(object sender, SqlNotificationEventArgs e) where TEntity : class
{
var sqlDependency = sender as SqlDependency;
// this should never happen
if (sqlDependency == null)
return;
// query notification only happens once, so remove it, it will be set up again in LoadData
sqlDependency.OnChange -= sqlDependency_OnChange<TEntity>;
// if the data is changed (delete, insert, update), refresh cache & set up query notification
// otherwise, just set up query notification
if (e.Info == SqlNotificationInfo.Delete || e.Info == SqlNotificationInfo.Insert || e.Info == SqlNotificationInfo.Update)
{
// refresh cache & set up query notification
Logger.Write(string.Format("sqlDependency_OnChange (Info: {0}, Source: {1}, Type: {2}). Refreshing cache for {3}", e.Info, e.Source, e.Type, typeof(TEntity).Name));
RefreshCache<TEntity>();
}
else
{
// set up query notification
SetUpQueryNotification<TEntity>();
}
}
#endregion
}