SqlDependency in asp.net

Question

I'm using SqlDependency to control my cache. I want to use it to monitor several tables (around 10). There should be one SqlDependency per watched table.

Should I create for each of them code like that:

   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))
                    {
                    }
                }
            }
        }
    }

and:

   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();
    }

or can I reuse something between them? Like connection?

Is this the preferred way of setting multiple notifications?

Answer 1

Here I'll show you a linq extension that may help you:

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;
        }
    }