public static bool IsInMaintenanceMode()
{
bool inMaintenanceMode;
string connStr = "Data Source=KIM-MSI\\KIMSSQLSERVER;Initial Catalog=MVWDataBase;User ID=sa;Password=mis123;MultipleActiveResultSets=True";
if (MemoryCache.Default["MaintenanceMode"] == null)
{
Console.WriteLine("Hitting the database...");
CacheItemPolicy policy = new CacheItemPolicy();
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);
dep.OnChange += Dep_OnChange;
}
}
MemoryCache.Default.Add("MaintenanceMode", inMaintenanceMode, policy);
}
else
{
inMaintenanceMode = (bool)MemoryCache.Default.Get("MaintenanceMode");
}
return inMaintenanceMode;
}
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 static void SetMonitor()
{
string connStr = ConfigurationManager.ConnectionStrings["lab"].ConnectionString;
CacheItemPolicy policy = new CacheItemPolicy();
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();
bool inMaintenanceMode = (bool)command.ExecuteScalar();
SqlChangeMonitor monitor = new SqlChangeMonitor(dep);
policy.ChangeMonitors.Add(monitor);
dep.OnChange += Dep_OnChange;
MemoryCache.Default.Add("MaintenanceMode", inMaintenanceMode, policy);
}
}
}
/// <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 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;
}
}