我创建了一个简单的缓存数据访问层,它使用企业库缓存应用程序块进行缓存,并使用SQL查询通知,因此不支持任何对查询通知无效的查询。
背景:这是在开发应用程序之后,为了减轻数据库的负载并加快应用程序的速度而进行的。此DAL的主要用途是提取预计不会经常更改的数据,例如查找表中的数据(在UI下拉列表中显示等)。
其主要用途如下:
var cachingDal = new CachingDataAccessLayer();
var productTypes = cachingDal.LoadData<ProductType>();
其中producttype是linq to sql表。我很想知道人们对我提出的实现有什么看法,以及它是可怕的还是惊人的。
这是密码。寻找任何建议、批评等。请记住,我没有选择这项技术,而是在现有系统的基础上进行构建,因此切换数据访问故事并不是我真正需要的。
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
}
