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

• IamDeveloper: This is for sqlCacheDependency - this is quite different.

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

}