我正在使用sqlDependency来控制我的缓存.我想用它来监控几个表(大约10个).每个观看的表应该有一个sqlDependency.
public void CreateDependency_Table() { if (connectionStringSettings != null) { using (sqlConnection conn = new sqlConnection(connectionStringSettings.ConnectionString)) { conn.Open(); using (sqlCommand cmd = new sqlCommand("SELECT id from dbo.Table",conn)) { cmd.Notification = null; sqlDependency sqlDependency = new sqlDependency(cmd); sqlDependency.OnChange += new OnChangeEventHandler(sqlDep_Table_OnChange); using (sqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { } } } } }
和:
private void sqlDep_Table_OnChange(object sender,sqlNotificationEventArgs e) { sqlDependency dependency = (sqlDependency)sender; dependency.OnChange -= sqlDep_Table_OnChange; MyCacheWhatever.Clear(); //Re-attach dependency CreateDependency_Table(); }
或者我可以在它们之间重复使用它喜欢连接?
这是设置多个通知的首选方式吗?
解决方法@H_404_16@
在这里,我将向您展示一个可以帮助您的linq扩展:
public static class LinqExtensions
{
private static ILog _Log = LogManager.GetLogger(MethodInfo.GetCurrentMethod().DeclaringType);
[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design","CA1031:DoNotCatchGeneralExceptionTypes")]
public static IList<T> LinqCache<T>(this Table<T> query) where T : class
{
string tableName = query.Context.Mapping.GetTable(typeof(T)).TableName;
IList<T> result = HttpContext.Current.Cache[tableName] as List<T>;
if (result == null)
{
try
{
using (sqlConnection cn = new sqlConnection(query.Context.Connection.ConnectionString))
{
cn.Open();
sqlCommand cmd = new sqlCommand(query.Context.GetCommand(query).CommandText,cn);
cmd.Notification = null;
cmd.NotificationAutoEnlist = true;
_Log.DebugFormat("Attempting to enable sql cache dependency notifications for table {0}",tableName);
sqlCacheDependencyAdmin.EnableNotifications(query.Context.Connection.ConnectionString);
string[] tables = sqlCacheDependencyAdmin.GetTablesEnabledForNotifications(query.Context.Connection.ConnectionString);
if (!tables.Contains(tableName))
sqlCacheDependencyAdmin.EnableTableForNotifications(query.Context.Connection.ConnectionString,tableName);
_Log.DebugFormat("sql cache dependency notifications for table {0} is enabled.",tableName);
sqlCacheDependency dependency = new sqlCacheDependency(cmd);
cmd.ExecuteNonQuery();
result = query.ToList();
HttpContext.Current.Cache.Insert(tableName,result,dependency);
_Log.DebugFormat("Table {0} is cached.",tableName);
}
}
catch (Exception ex)
{
result = query.Context.GetTable<T>().ToList();
HttpContext.Current.Cache.Insert(tableName,result);
string msg = string.Format(CultureInfo.InvariantCulture,"Table {0} is cached without sqlCacheDependency!!!",tableName);
_Log.Warn(msg,ex);
}
}
return result;
}
}
public static class LinqExtensions { private static ILog _Log = LogManager.GetLogger(MethodInfo.GetCurrentMethod().DeclaringType); [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design","CA1031:DoNotCatchGeneralExceptionTypes")] public static IList<T> LinqCache<T>(this Table<T> query) where T : class { string tableName = query.Context.Mapping.GetTable(typeof(T)).TableName; IList<T> result = HttpContext.Current.Cache[tableName] as List<T>; if (result == null) { try { using (sqlConnection cn = new sqlConnection(query.Context.Connection.ConnectionString)) { cn.Open(); sqlCommand cmd = new sqlCommand(query.Context.GetCommand(query).CommandText,cn); cmd.Notification = null; cmd.NotificationAutoEnlist = true; _Log.DebugFormat("Attempting to enable sql cache dependency notifications for table {0}",tableName); sqlCacheDependencyAdmin.EnableNotifications(query.Context.Connection.ConnectionString); string[] tables = sqlCacheDependencyAdmin.GetTablesEnabledForNotifications(query.Context.Connection.ConnectionString); if (!tables.Contains(tableName)) sqlCacheDependencyAdmin.EnableTableForNotifications(query.Context.Connection.ConnectionString,tableName); _Log.DebugFormat("sql cache dependency notifications for table {0} is enabled.",tableName); sqlCacheDependency dependency = new sqlCacheDependency(cmd); cmd.ExecuteNonQuery(); result = query.ToList(); HttpContext.Current.Cache.Insert(tableName,result,dependency); _Log.DebugFormat("Table {0} is cached.",tableName); } } catch (Exception ex) { result = query.Context.GetTable<T>().ToList(); HttpContext.Current.Cache.Insert(tableName,result); string msg = string.Format(CultureInfo.InvariantCulture,"Table {0} is cached without sqlCacheDependency!!!",tableName); _Log.Warn(msg,ex); } } return result; } }