asp.net中的SqlDependency

发布时间：2020-12-15 22:32:08 所属栏目：asp.Net 来源：网络整理

导读：我正在使用SqlDependency来控制我的缓存.我想用它来监控几个表(大约10个).每个观看的表应该有一个SqlDependency. 我应该为每个代码创建这样的代码： public void CreateDependency_Table() { if (connectionStringSettings != null) { using (SqlConnection

我正在使用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();
    }

或者我可以在它们之间重复使用它喜欢连接？

这是设置多个通知的首选方式吗？

解决方法

在这里,我将向您展示一个可以帮助您的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;
        }
    }

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!