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

Answer 2

I've designed the below class that hook up a listener for one or more queries, it may not be the best solution but it works.

So it will create an object for every trigger that can, for example, be used to trigger the SinalR. You just need to start the Dependency and the class SqlDependencyHelper within the Global.asax and everything will be stored within the SqlDataManagement like if the trigger is an update or delete and which id has changed.

A third field within the SELECT that is called ReferenceItem can be used to know if the trigger happened due to an update, so I used the DateTime DB column called lastChanged to know which row has updated.

All queries must be from a list and using the format below

Select Sample

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

Classes

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

}