/// <summary>
/// 设置具有SqlChangeMonitor策略的CacheItemPolicy（绝对或相对过期时间为分钟）
/// </summary>
/// <param name="connStr"></param>
/// <param name="sql"></param>
/// <param name="cacheTime"></param>
/// <param name="slidingExpiration"></param>
/// <returns></returns>
private CacheItemPolicy SetSqlPolicy(string connStr, string sql, int?cacheTime = null, int?slidingExpiration = null)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand command = new SqlCommand(sql, conn))
{
command.Notification = null;
conn.Open();
var dependency = new SqlDependency(command);
dependency.AddCommandDependency(command);
var monitor = new SqlChangeMonitor(dependency);
var policy = new CacheItemPolicy();
policy.ChangeMonitors.Add(monitor);
command.ExecuteScalar();
if (cacheTime.HasValue)
{
policy.AbsoluteExpiration = DateTime.Now.AddMinutes(cacheTime.Value);
}
if (slidingExpiration.HasValue)
{
policy.SlidingExpiration = TimeSpan.FromMinutes(slidingExpiration.Value);
}
return(policy);
}
}
}
public ChangeMonitor Enlist()
{
using (var connection = new SqlConnection(connectionString))
{
using (var exeCommand = new System.Data.SqlClient.SqlCommand(command, connection))
{
//is the command a sproc
if (isStoredProcedure)
{
exeCommand.CommandType = CommandType.StoredProcedure;
}
if (commandTimeout.HasValue)
{
exeCommand.CommandTimeout = this.commandTimeout.Value;
}
var dependency = new SqlDependency(exeCommand);
var monitor = new SqlChangeMonitor(dependency);
connection.Open();
exeCommand.ExecuteNonQuery();
return(monitor);
}
}
}
public DataTable GetCache(string cacheKey, string connectionString, string cmdText)
{
ObjectCache cache = MemoryCache.Default;
var result = cache.Get(cacheKey) as DataTable;
if (result == null)
{
CacheItemPolicy policy = new CacheItemPolicy();
policy.AbsoluteExpiration = DateTime.Now.AddMinutes(10);
SqlDependency.Start(connectionString);
using (var connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(cmdText, connection);
var depency = new SqlDependency(command);
SqlChangeMonitor monitor = new SqlChangeMonitor(depency);
policy.ChangeMonitors.Add(monitor);
connection.Open();
using (var reader = command.ExecuteReader(CommandBehavior.CloseConnection))
{
result.Load(reader);
}
if (result != null)
{
cache.Add(cacheKey, result, policy);
}
}
}
return(result);
}
public void Set(string forUser, IEnumerable <Order> orders)
{
var policy = new CacheItemPolicy();
string connStr = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
SqlDependency.Start(connStr);
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand command = new SqlCommand("Select * From Orders", conn))
{
command.Notification = null;
SqlDependency dep = new SqlDependency();
dep.AddCommandDependency(command);
conn.Open();
SqlChangeMonitor monitor = new SqlChangeMonitor(dep);
policy.ChangeMonitors.Add(monitor);
}
cache.Set(prefix + forUser, orders, policy);
}
}
private SqlChangeMonitor GetMonitor(string query, string connectionString)
{
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
var command = new SqlCommand(query, connection);
var monitor = new SqlChangeMonitor(new SqlDependency(command));
command.ExecuteNonQuery();
return(monitor);
}
}
public void Set(string forUser, IEnumerable <Shipper> shippers)
{
var policy = new CacheItemPolicy();
var sqlMonitor = new SqlChangeMonitor(
new SqlDependency(
new SqlCommand("SELECT * FROM dbo.Shippers")));
policy.ChangeMonitors.Add(sqlMonitor);
_cache.Set(Prefix + forUser, shippers, policy);
}
/**
* @ 创建单个对象的SQL类型的缓存策略，该方法适用于单个字段类型对象
* @ T 要创建的缓存对象类型，仅限单个类型
* @ key 缓存的键
* @ cmdText sql语句，必须包含架构信息，并且不能使用“*”号代替要查询的字段
* @ offset 缓存过期时间
* @ policy 缓存策略对象
* @ sourceChange 数据源更改通知事件
* @ priority 缓存逐出的优先级
* */
public CacheItem CreateSQLCacheScalar(string key, string cmdText, DateTimeOffset offset, out CacheItemPolicy policy, CacheItemPriority priority = CacheItemPriority.Default)
{
policy = new CacheItemPolicy();
object result = SelectScalar <object>(cmdText, out dency);
monitor = new SqlChangeMonitor(dency);
policy.AbsoluteExpiration = offset;
policy.ChangeMonitors.Add(monitor);
cacheItem = new CacheItem(key);
cacheItem.Value = result;
return(cacheItem);
}
/**
* @ 创建单个对象的SQL类型的缓存策略，该方法适用于单个字段类型对象
* @ T 要创建的缓存对象类型，仅限单个类型
* @ key 缓存的键
* @ cmdText sql语句，必须包含架构信息，并且不能使用“*”号代替要查询的字段
* @ offset 缓存过期时间
* @ policy 缓存策略对象
* @ sourceChange 数据源更改通知事件
* @ priority 缓存逐出的优先级
* */
public CacheItem CreateSQLCacheList <T>(string key, string cmdText, DateTimeOffset offset, out CacheItemPolicy policy, CacheItemPriority priority = CacheItemPriority.Default)
where T : class, new()
{
policy = new CacheItemPolicy();
List <T> resultList = SelectData <T>(cmdText, out dency);
monitor = new SqlChangeMonitor(dency);
policy.AbsoluteExpiration = offset;
policy.ChangeMonitors.Add(monitor);
cacheItem = new CacheItem(key);
cacheItem.Value = resultList;
return(cacheItem);
}
public static T GetOrSetCache <T>(string cacheKey, SqlCommand command, bool isDependency, Func <T> getItemCallback) where T : class
{
T item = MemoryCache.Default.Get(cacheKey) as T;
if (item == null)
{
item = getItemCallback();
CacheItemPolicy policy = new CacheItemPolicy();
//If the AbsoluteExpirationTime isn't set or is 0, the SlidingExpirationTime will be enabled by default.
var absoluteExpirationTime = string.IsNullOrEmpty(ConfigurationManager.AppSettings.Get("AbsoluteExpirationTime")) ? 0 : int.Parse(ConfigurationManager.AppSettings.Get("AbsoluteExpirationTime"));
if (absoluteExpirationTime > 0)
{
policy.AbsoluteExpiration = DateTimeOffset.Now.Add(new TimeSpan(0, absoluteExpirationTime, 0));
}
else
{
var slidingExpirationTime = string.IsNullOrEmpty(ConfigurationManager.AppSettings.Get("SlidingExpirationTime")) ? 10 : int.Parse(ConfigurationManager.AppSettings.Get("SlidingExpirationTime"));
policy.SlidingExpiration = new TimeSpan(0, slidingExpirationTime, 0);
}
if (isDependency)
{
try
{
var connString = DABase.GetConnectionString();
using (var connection = new SqlConnection(connString))
{
connection.Open();
SqlDependency.Start(connString);
command.Connection = connection;
var dependency = new SqlDependency(command);
SqlChangeMonitor monitor = new SqlChangeMonitor(dependency);
policy.ChangeMonitors.Add(monitor);
DataTable dt = new DataTable();
var reader = command.ExecuteReader();
dt.Load(reader);
reader.Close();
MemoryCache.Default.Add(cacheKey, item, policy);
}
}
catch (Exception ex)
{
throw;
}
}
}
return(item);
}
public IEnumerable <T> GetData <T>() where T : class, new()
{
Console.WriteLine("Get data");
var user = Thread.CurrentPrincipal.Identity.Name;
var data = _cache.Get <T>(user);
if (data == null)
{
Console.WriteLine("From DB");
using (var dbContext = new Northwind())
{
dbContext.Configuration.LazyLoadingEnabled = false;
dbContext.Configuration.ProxyCreationEnabled = false;
data = dbContext.Set <T>().ToList();
if (_type == 0)
{
_cache.Set(user, data);
}
else if (_type == 1)
{
CacheItemPolicy policy = new CacheItemPolicy();
SqlDependency.Start(_connectionStrings);
using (SqlConnection connection = new SqlConnection(_connectionStrings))
using (SqlCommand command = new SqlCommand(_commandString, connection))
{
connection.Open();
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += dependency_OnChange;
SqlChangeMonitor monitor = new SqlChangeMonitor(dependency);
command.ExecuteReader();
policy.ChangeMonitors.Add(monitor);
}
_cache.Set(user, data, policy);
}
}
}
return(data);
}
private CacheItemPolicy CreatePolicy(string sqlQuery)
{
var cacheItemPolicy = new CacheItemPolicy();
using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(sqlQuery, connection))
{
var sqlDependency = new SqlDependency(command);
SqlDependency.Start(ConnectionString);
var monitor = new SqlChangeMonitor(sqlDependency);
cacheItemPolicy.ChangeMonitors.Add(monitor);
command.ExecuteNonQuery();
return(cacheItemPolicy);
}
}
}
public void Set(string forUser, IEnumerable <TEntity> entities)
{
_forUser = forUser;
var key = _prefix + _forUser;
switch (PolicyLevel)
{
case PolicyType.ChangeMonitorSql:
var connectionString = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
SqlDependency.Stop(connectionString);
SqlDependency.Start(connectionString);
var policy = new CacheItemPolicy
{
AbsoluteExpiration = DateTime.Now.AddMinutes(10)
};
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
using (var command = new SqlCommand(Sql, connection))
{
command.Notification = null;
var dependency = new SqlDependency(command);
var monitor = new SqlChangeMonitor(dependency);
policy.ChangeMonitors.Add(monitor);
_cache.Set(key, entities, policy);
command.ExecuteNonQuery();
}
}
break;
case PolicyType.ExpirationTime:
_cache.Set(key, entities, new DateTimeOffset(DateTime.UtcNow.AddSeconds(3)));
break;
case PolicyType.None:
_cache.Set(key, entities, ObjectCache.InfiniteAbsoluteExpiration);
break;
default:
throw new ArgumentOutOfRangeException();
}
}
private DataTable ReloadCachedData(string cacheKey, string connStr, string sql)
{
Debug.Assert(!string.IsNullOrWhiteSpace(connStr), "Connection string is mandatory");
_TRACER.Info("Reload cache data from database, cacheKey = {0}", cacheKey);
Stopwatch stopWatch = Stopwatch.StartNew();
// Can be called many times without issue
StartSqlDependency(connStr);
using (var conn = new SqlConnection(connStr))
using (var command = new SqlCommand(sql, conn))
{
command.Notification = null;
var dep = new SqlDependency(command);
var dt = new DataTable();
conn.Open();
dt.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
// Create a monitor for changes in Sql Server
var monitor = new SqlChangeMonitor(dep);
// Create a monitor cache policy (more advanced than sliding expire)
var policy = new CacheItemPolicy();
policy.ChangeMonitors.Add(monitor);
policy.RemovedCallback =
(o) =>
{
String strLog = String.Format("CacheItem removed, Reason: {0}, CacheItemKey: {1}, CacheItemValue: {2}",
o.RemovedReason.ToString(), o.CacheItem.Key,
o.CacheItem.Value.ToString());
_TRACER.Info(strLog);
};
// Add config data to in-memory cache which will reload if data changes in SQL
MemoryCache.Default.Set(cacheKey, dt, policy);
stopWatch.Stop();
_TRACER.Info("Time for ReloadCachedData(): {0} milliseconds", stopWatch.ElapsedMilliseconds);
return(dt);
}
}
static public bool IsInMaintenanceMode()
{
bool inMaintenanceMode;
if (MemoryCache.Default["MaintenanceMode"] == null)
{
CacheItemPolicy policy = new CacheItemPolicy();
string connStr = "DB CONNECTION STRING";
SqlDependency.Start(connStr);
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand command = new SqlCommand("Select MaintenanceMode From dbo.MaintenanceMode", conn))
{
command.Notification = null;
SqlDependency dep = new SqlDependency();
dep.AddCommandDependency(command);
conn.Open();
inMaintenanceMode = (bool)command.ExecuteScalar();
SqlChangeMonitor monitor = new SqlChangeMonitor(dep);
policy.ChangeMonitors.Add(monitor);
}
}
MemoryCache.Default.Add("MaintenanceMode", inMaintenanceMode, policy);
}
else
{
inMaintenanceMode = (bool)MemoryCache.Default.Get("MaintenanceMode");
}
return(inMaintenanceMode);
}
/// <summary>
/// 添加到缓存
/// </summary>
/// <param name="value">被缓存的数据</param>
/// <param name="cacheKey">缓存key</param>
/// <param name="depency">SQL依赖缓存项</param>
public static void SetCache_M(this object value, string cacheKey, SqlDependency dependency_sql)
{
ObjectCache cache = MemoryCache.Default;
CacheItemPolicy policy = new CacheItemPolicy();
//缓存优先级别
policy.Priority = System.Runtime.Caching.CacheItemPriority.Default;
cache.Set(cacheKey, value, policy);
//设置监视对象
SqlChangeMonitor monitor = new SqlChangeMonitor(dependency_sql);
//设置监视对象的回调操作
//依赖文件发生变化 即删除缓存
monitor.NotifyOnChanged(new OnChangedCallback(o =>
{
RemoveCache_M(cacheKey);
}));
//添加到监视器
policy.ChangeMonitors.Add(monitor);
}
private CacheItem LoadData(out CacheItemPolicy policy, string key)
{
var data = new List <Employee>();
using (var connection = new SqlConnection(CONNECTION_STRING))
{
connection.Open();
using (SqlCommand command = new SqlCommand(SQL_STATEMENT, connection))
{
//Add new dependency
dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var employee = new Employee()
{
EmployeeID = reader.GetInt32(0),
LastName = reader.GetString(1),
FirstName = reader.GetString(2)
};
data.Add(employee);
}
}
// add monitor
monitor = new SqlChangeMonitor(dependency);
policy = new CacheItemPolicy();
policy.ChangeMonitors.Add(monitor);
policy.RemovedCallback = CacheRemovedCallback;
var item = new CacheItem(key, data);
return(item);
}
}
}
/// <summary>
/// Get entities from database and initialize custom cache policy for monitoring cache.
/// </summary>
/// <param name="policy">The cache policy.</param>
/// <returns>The requested entities.</returns>
private IEnumerable <T> LoadEntities(out CacheItemPolicy policy)
{
ApplicationLogger.LogMessage(LogMessageType.Info, $"Connection string = {_connectionString}. Command = {_commandText}. Getting entities...");
policy = new CacheItemPolicy();
IList <T> entities;
SqlDependency.Start(_connectionString);
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
using (var sqlCommand = new SqlCommand(_commandText, connection))
{
_dependency = new SqlDependency(sqlCommand);
_dependency.OnChange += DependencyOnOnChange;
using (IDataReader reader = sqlCommand.ExecuteReader())
{
entities = new List <T>();
while (reader.Read())
{
T entity = new T();
entity.ReadSingleRow(reader, entity);
entities.Add(entity);
}
}
}
}
_monitor = new SqlChangeMonitor(_dependency);
policy.ChangeMonitors.Add(_monitor);
policy.UpdateCallback = UpdateCallback;
_hasDataChanged = false;
return(entities);
}
public void Set(string forUser, IEnumerable <Customer> entities)
{
CacheItemPolicy policy = new CacheItemPolicy();
string connectionString = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
this.StartListening(connectionString);
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand("SELECT * FROM dbo.Customers", connection))
{
SqlDependency sqlDependency = new SqlDependency(command);
SqlChangeMonitor changeMonitor = new SqlChangeMonitor(sqlDependency);
policy.ChangeMonitors.Add(changeMonitor);
}
}
_cache.Set(_prefix + forUser, entities, policy);
}
public void UpdateCache()
{
var policy = new CacheItemPolicy();
policy.RemovedCallback = (s) => UpdateCache();
var connectionString =
ConfigurationManager.ConnectionStrings["Default"]
.ConnectionString;
SqlDependency.Start(connectionString);
using (var conn = new SqlConnection(connectionString))
{
using (SqlCommand command =
new SqlCommand("SELECT PersonType FROM Person.Person WHERE PersonType = 'EM'",
conn))
{
SqlDependency sqlDependency = new SqlDependency(command);
//sqlDependency.AddCommandDependency();
//sqlDependency.OnChange += (s,x) => UpdateCache();
ChangeMonitor sqlMonitor = new SqlChangeMonitor(sqlDependency);
conn.Open();
command.ExecuteScalar();
var empCount = GetCurrentEmployeeCount();
policy.ChangeMonitors.Add(sqlMonitor);
cache.Add("employee_count", empCount, policy);
UpdateList(empCount);
}
}
}
public bool IsCacheValidForProducts()
{
bool isValid;
if (MemoryCache.Default["OwnMonitoring"] == null)
{
string connectionString = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
CacheItemPolicy policy = new CacheItemPolicy();
SqlDependency.Start(connectionString);
using (var connection = new SqlConnection(connectionString))
using (var command = new SqlCommand("select count(*) from [Northwind].[dbo].[Products]", connection))
{
command.Notification = null;
SqlDependency dependency = new SqlDependency();
dependency.AddCommandDependency(command);
connection.Open();
isValid = ((int)command.ExecuteScalar() == rowCount);
SqlChangeMonitor monitor = new SqlChangeMonitor(dependency);
policy.ChangeMonitors.Add(monitor);
connection.Close();
}
MemoryCache.Default.Add("OwnMonitoring", isValid, policy);
}
else
{
isValid = (bool)MemoryCache.Default.Get("OwnMonitoring");
}
return(isValid);
}
