ASP.NET 中的 SqlDependency

Question

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

或者我可以在它们之间重用某些东西吗？喜欢连接吗？

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

Answer 1

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

Answer 2

我设计了下面的类，它为一个或多个查询连接一个侦听器，它可能不是最好的解决方案，但它有效。

因此，它将为每个触发器创建一个对象，例如，可用于触发 SinalR。您只需要在 Global.asax 中启动 Dependency 和 SqlDependencyHelper 类，所有内容都将存储在 SqlDataManagement 中，就像触发器是更新或删除以及哪个 id 已更改一样。

SELECT 中名为 ReferenceItem 的第三个字段可用于了解触发器是否因更新而发生，因此我使用名为 lastChanged 的​​ DateTime DB 列来了解哪一行已更新。

所有查询都必须来自列表并使用以下格式

选择示例

@“SELECT 'PreStartPhotos' as QueryReferenceName, [id], '' as ReferenceItem FROM [dbo].[PreStartPhotos]”

课程

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.IO;
using System.Linq;

namespace WebAPI.Helpers
{
    public class SqlDependencyHelper
    {
        private static SqlDependency    _dep;
        private static readonly string  SqlConnectionString         = ConfigurationManager.ConnectionStrings["CONNECTION_STRING"].ConnectionString;
        private static Dictionary<string, Dictionary<string,string>> _tableData  = new Dictionary<string, Dictionary<string,string>>();

    ~SqlDependencyHelper()
    {
        SqlDependency.Stop(SqlConnectionString);
    }

    /// <summary>
    /// This method must start via the Global.asax to initialize the SQL Dependency's OnChange trigger. Example:  
    /// SqlDependency.Start(ConfigurationManager.ConnectionStrings["CONNECTION_NAME"].ConnectionString);
    /// SqlDependencyHelper.RegisterSqlNotification().
    /// This method will be recalled by the <see cref="OnDataChange"/> every time that a message is received from the SQL Server. 
    /// </summary>
    /// <param name="notificationType">Notification type received by the SQL Notification using the <see cref="OnDataChange"/> method</param>
    public static void RegisterSqlNotification(SqlNotificationInfo notificationType = SqlNotificationInfo.Invalid)
    {
        try
        {
            using (var conn = new SqlConnection(SqlConnectionString))
            {
                // Start the SQL Dependency with the commands to keep watching the database
                conn.Open();
                var cmd         = conn.CreateCommand();
                cmd.CommandText = string.Join("; ", Global.SqlDependencyList);
                _dep             = new SqlDependency(cmd);
                _dep.OnChange   += OnDataChange;

                // Load the select that has been returned from the database
                var dataResult  = cmd.ExecuteReader();
                var dumpData    = new Dictionary<string, Dictionary<string,string>>();

                do
                {
                    // Load all information using the sql command provided
                    while (dataResult.Read())
                    {
                        if (dataResult[0] == DBNull.Value || dataResult[1] == DBNull.Value || dataResult[2] == DBNull.Value) continue;

                        if(!dumpData.ContainsKey(dataResult[0].ToString()))
                            dumpData.Add(dataResult[0].ToString(),new Dictionary<string, string> { {dataResult[1].ToString(),dataResult[2].ToString()} });
                        else
                            dumpData[dataResult[0].ToString()].Add(dataResult[1].ToString(),dataResult[2].ToString());
                    }

                // As it may have more than one query, keep looping until it load all selects
                } while (dataResult.NextResult());

                // Database diff that point all changes
                // Use this var to inject all changes within a method that triggers the business workflow like a SignalR method
                var dbTracker = (from table in _tableData where dumpData.ContainsKey(table.Key) select new SqlDataManagement(table.Key, table.Value, dumpData[table.Key], notificationType)).ToList();

                // Take a snapshot of the data that has been loaded to be used next time
                _tableData = dumpData;

                dataResult.Dispose();
                cmd.Dispose();
            }
        }
        catch (Exception e)
        {
            // As this module is executed within the Global that doesn't handle exceptions properly
            // An exception controller had to be added to avoid the application to stop working if an exception is raised
            // An email will be send to alert everyone
            const string module = "SQLDependency";
            var emailHtml       = File.ReadAllText($"{Global.DefaultEmailTemplateFolder}/exception_alerts.html").Replace("{pathName}",module)
                                                                                                                .Replace("{dateUtcTime}",CommonHelper.FromUtcToLocalTime(TimeZoneInfo.FindSystemTimeZoneById(Global.DefaultTimeZone)).ToString("F"))
                                                                                                                .Replace("{exceptionMessage}",e.Message)
                                                                                                                .Replace("{exceptionStackTrace}",e.StackTrace)
                                                                                                                .Replace("{exceptionFull}",e.ToString());
            var emails          = new List<string> {Global.DefaultEmailAlerts};
            AmazonApiHelper.SendEmailRaw(emails, $"Exception Alert: {module}", emailHtml);
        }
    }

    /// <summary>
    /// OnChange function that receives the trigger from the SQL broker.
    /// It gets the broker information and call the <see cref="RegisterSqlNotification"/> again to re-attach the broker.
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private static void OnDataChange(object sender, SqlNotificationEventArgs e)
    { 
        var dep = sender as SqlDependency;
        dep.OnChange -= OnDataChange;
        RegisterSqlNotification(e.Info);
    }
}

/// <summary>
/// Object used to map changes to a individual query executed by the SqlDependency.
/// </summary>
public class SqlDataManagement
{
    public SqlDataManagement(string queryReferenceName, Dictionary<string,string> objectsOldFromDb, Dictionary<string,string> objectsNewFromDb, SqlNotificationInfo sqlNotificationInfo)
    {
        QueryReferenceName      = queryReferenceName;
        ObjectsNewFromDb        = objectsNewFromDb;
        ObjectsOldFromDb        = objectsOldFromDb;
        ObjectsStatus           = new Dictionary<string, SqlNotificationInfo>();

        // Check if any id has been removed or added
        var newObjectIds        = objectsNewFromDb.Keys.ToList();
        var oldObjectIds        = objectsOldFromDb.Keys.ToList();
        var newIds              = newObjectIds.Except(oldObjectIds).ToList();
        var removedIds          = oldObjectIds.Except(newObjectIds).ToList();

        // Update the ObjectsStatus with all new and removed ids
        foreach (var newId in newIds)
        {
            ObjectsStatus.Add(newId,SqlNotificationInfo.Insert);
        }
        foreach (var removedId in removedIds)
        {
            ObjectsStatus.Add(removedId,SqlNotificationInfo.Delete);
        }

        // Check if an object has been inserted or deleted to update the status of the transaction
        if (!objectsOldFromDb.All(objectsNewFromDb.Contains) || objectsOldFromDb.Count != objectsNewFromDb.Count)
        {
            SqlNotificationInfo = sqlNotificationInfo;
        }
        else
        {
            SqlNotificationInfo = SqlNotificationInfo.Unknown;
        }

        // Check if any item has been changed since the last update
        foreach (var objectNew in ObjectsNewFromDb)
        {
            // Check if the item matches in both old and new tables
            if (!ObjectsOldFromDb.ContainsKey(objectNew.Key)) continue;

            // Ignore if the object is the same
            if (ObjectsOldFromDb[objectNew.Key] == objectNew.Value) continue;

            // Change the notification to update and add the id to the UpdatedList
            SqlNotificationInfo = SqlNotificationInfo.Update;
            ObjectsStatus.Add(objectNew.Key,SqlNotificationInfo.Update);
        }

        // Add all unchangedIds to the final object
        var unchangedIds        = oldObjectIds.Except(ObjectsStatus.Keys).ToList();
        foreach (var unchangedId in unchangedIds)
        {
            ObjectsStatus.Add(unchangedId,SqlNotificationInfo.Unknown);
        }
    }

    /// <summary>
    /// The first field of every SQL Dependency command must be the Query Reference name.
    /// It will be used as reference and help any method that rely on this result to use the data.
    /// E.g. SELECT 'PreStartPhotos' as QueryReferenceName, [id], '' as ReferenceItem FROM [dbo].[PreStartPhotos]
    /// </summary>
    public string QueryReferenceName { get; set; }

    /// <summary>
    /// Contain all new and old ids plus all ids that have been updated since the last trigger.
    /// SqlNotificationInfo.Unknown -> hasn't changed since last trigger.
    /// SqlNotificationInfo.Update -> the id has been updated.
    /// SqlNotificationInfo.Delete -> the id has been deleted.
    /// SqlNotificationInfo.Insert -> the id has been inserted.
    /// </summary>
    public Dictionary<string,SqlNotificationInfo> ObjectsStatus { get; set; }

    /// <summary>
    /// Data from the last trigger
    /// </summary>
    public Dictionary<string,string> ObjectsOldFromDb { get; set; }

    /// <summary>
    /// Data that has been captured from the recent trigger
    /// </summary>
    public Dictionary<string,string> ObjectsNewFromDb { get; set; }

    /// <summary>
    /// If any update, delete or insert is detected within the ObjectStatus, this var will be true
    /// </summary>
    public bool HasAnyChange => ObjectsStatus.Any(p=> p.Value != SqlNotificationInfo.Unknown);

    /// <summary>
    /// Information about the SQL notification that triggered this update.
    /// SqlNotificationInfo.Unknown is used if nothing has happened.
    /// </summary>
    public SqlNotificationInfo SqlNotificationInfo { get; set; }
}

}