/// <summary>
/// Creates a provider implementation
/// </summary>
/// <param name="dependencyParams">Dependency monitoring parameters</param>
/// <param name="providerType">Type of the provider to create</param>
/// <param name="serviceBrokerQueue">Queue name for the service broker implementation</param>
/// <param name="periodForPollingProvider">Period for polling implementation</param>
/// <returns>Sql Dependency Provider instance</returns>
public static SqlDependencyProvider Create(SqlDependencyParams dependencyParams,
SqlDependencyProviderType providerType,
string serviceBrokerQueue, TimeSpan?periodForPollingProvider)
{
// Validate
if (dependencyParams == null)
{
throw new ArgumentNullException(nameof(dependencyParams));
}
if (periodForPollingProvider.HasValue &&
(periodForPollingProvider.Value.TotalMilliseconds < 0))
{
throw new ArgumentNullException(nameof(periodForPollingProvider));
}
serviceBrokerQueue = string.IsNullOrWhiteSpace(serviceBrokerQueue) ? null : serviceBrokerQueue.Trim();
// Make a copy & validate
dependencyParams = dependencyParams.Clone() as SqlDependencyParams;
if (dependencyParams == null)
{
throw new ArgumentNullException(nameof(dependencyParams));
}
dependencyParams.ValidateAndAdjust();
// Check service broker availability
if ((providerType == SqlDependencyProviderType.Polling) ||
((providerType == SqlDependencyProviderType.Auto) &&
!IsServiceBrokerEnabled(dependencyParams.ConnectionString, serviceBrokerQueue)))
{
return(new PollingSqlDependencyProvider(dependencyParams, periodForPollingProvider));
}
return(new BrokerSqlDependencyProvider(dependencyParams));
}
private static void DeleteChangeHolderTable(SqlConnection conn, SqlTransaction trans,
SqlDependencyParams dependencyParams)
{
var refCount = DecrementRefCount(dependencyParams.Options.ChangeHolderTable);
if (refCount <= 0)
{
var cmdText = string.Format(CultureInfo.InvariantCulture,
@"IF EXISTS (SELECT 1 FROM [sys].[tables] WHERE [object_id] = OBJECT_ID('{0}'))
DROP TABLE {0};" ,
dependencyParams.Options.ChangeHolderTable);
// Execute
using (var cmd = new SqlCommand(cmdText, conn))
{
// Process
EnsureOpenConnection(conn);
// Properties
cmd.CommandType = CommandType.Text;
cmd.Transaction = trans;
// Execute
cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="dependencyParams">Notification parameters to use</param>
protected SqlDependencyProvider(SqlDependencyParams dependencyParams)
{
if (dependencyParams == null)
{
throw new ArgumentNullException(nameof(dependencyParams));
}
Parameters = dependencyParams;
SyncLock = new object();
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="dependencyParams">Notification parameters to use</param>
/// <param name="pollingPeriod">Polling period</param>
public PollingSqlDependencyProvider(SqlDependencyParams dependencyParams, TimeSpan?pollingPeriod)
: base(dependencyParams)
{
if (pollingPeriod.HasValue &&
(pollingPeriod.Value.TotalMilliseconds < 0))
{
throw new ArgumentNullException(nameof(pollingPeriod));
}
_pollingPeriod = pollingPeriod ?? DefaultPollingPeriod;
}
private static void EnsureTableTriggers(SqlConnection conn, SqlTransaction trans,
SqlDependencyParams dependencyParams)
{
// Format for each table
var cmdTextFormat =
@"CREATE TRIGGER {0}
ON {1}
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
-- Insert a record in changes table for this table
IF NOT EXISTS (SELECT 1 FROM {2} WHERE [ObjectId] = OBJECT_ID('{1}'))
INSERT INTO {2} VALUES (OBJECT_ID('{1}'), '{1}', NULL, NULL, NULL);
-- Update the record appropriately
IF EXISTS (SELECT 1 FROM INSERTED)
IF EXISTS (SELECT 1 FROM DELETED)
UPDATE {2} SET [LastUpdateDate] = SYSDATETIMEOFFSET() WHERE ([ObjectId] = OBJECT_ID('{1}'));
ELSE
UPDATE {2} SET [LastInsertDate] = SYSDATETIMEOFFSET() WHERE ([ObjectId] = OBJECT_ID('{1}'));
ELSE
IF EXISTS (SELECT 1 FROM DELETED)
UPDATE {2} SET [LastDeleteDate] = SYSDATETIMEOFFSET() WHERE ([ObjectId] = OBJECT_ID('{1}'));
END" ;
// Create
dependencyParams.Tables.ForEach(t =>
{
var trigger = dependencyParams.Options.GetTableTrigger(t);
if (!TriggerExists(conn, trans, trigger))
{
var cmdText = string.Format(CultureInfo.InvariantCulture, cmdTextFormat,
trigger, t, dependencyParams.Options.ChangeHolderTable);
// Execute
using (var cmd = new SqlCommand(cmdText, conn))
{
// Process
EnsureOpenConnection(conn);
// Properties
cmd.CommandType = CommandType.Text;
cmd.Transaction = trans;
// Execute
cmd.ExecuteNonQuery();
}
}
});
// Increment ref counts
dependencyParams.Tables.ForEach(t => IncrementRefCount(dependencyParams.Options.GetTableTrigger(t)));
}
/// <summary>
/// Ensures that the required db objects are deleted
/// </summary>
/// <param name="dependencyParams">Dependency parameters</param>
public static void DeleteSqlObjects(SqlDependencyParams dependencyParams)
{
// Create connection
using (new MonitorLock(_syncLock))
using (var conn = new SqlConnection(dependencyParams.ConnectionString))
{
// Open
EnsureOpenConnection(conn);
// Take a backup of ref counts
var backupRefCounts = new Dictionary <string, int>(_refCounts);
// Transaction
using (var trans = conn.BeginTransaction(IsolationLevel.Serializable))
{
// Enclose to rollback uncommitted transaction
var transCommitted = false;
try
{
// Delete Table Triggers for all monitored tables
DeleteTableTriggers(conn, trans, dependencyParams);
// Delete Changes table
DeleteChangeHolderTable(conn, trans, dependencyParams);
// Commit
trans.Commit();
transCommitted = true;
}
finally
{
if (!transCommitted)
{
// Rollback db
trans.Rollback();
// Rollback Ref Counts
_refCounts.Clear();
backupRefCounts.ToList().ForEach(kv => _refCounts[kv.Key] = kv.Value);
}
}
}
}
}
private static void EnsureChangeHolderTable(SqlConnection conn, SqlTransaction trans,
SqlDependencyParams dependencyParams)
{
var cmdText = string.Format(CultureInfo.InvariantCulture,
@"IF NOT EXISTS (SELECT 1 FROM [sys].[tables] WHERE [object_id] = OBJECT_ID('{0}'))
BEGIN
CREATE TABLE {0} (
[ObjectId] [int] NOT NULL,
[ObjectName] [nvarchar](128) NOT NULL,
[LastInsertDate] [datetimeoffset](7) NULL,
[LastUpdateDate] [datetimeoffset](7) NULL,
[LastDeleteDate] [datetimeoffset](7) NULL,
CONSTRAINT [PK_{1}] PRIMARY KEY CLUSTERED
([ObjectId] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
CREATE UNIQUE NONCLUSTERED INDEX [IX_{1}] ON {0}
([ObjectName] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);
END" ,
dependencyParams.Options.ChangeHolderTable,
dependencyParams.Options.ChangeHolderTable.Name);
// Execute
using (var cmd = new SqlCommand(cmdText, conn))
{
// Process
EnsureOpenConnection(conn);
// Properties
cmd.CommandType = CommandType.Text;
cmd.Transaction = trans;
// Execute
cmd.ExecuteNonQuery();
}
// Increment ref count
IncrementRefCount(dependencyParams.Options.ChangeHolderTable);
}
private static void DeleteTableTriggers(SqlConnection conn, SqlTransaction trans,
SqlDependencyParams dependencyParams)
{
// Delete for all
dependencyParams.Tables.ForEach(t =>
{
// Trigger
var tableTrigger = dependencyParams.Options.GetTableTrigger(t);
// Decrement ref count
var refCount = DecrementRefCount(tableTrigger);
// Delete if ref count is 0
if (refCount <= 0)
{
// Delete
var cmdText = string.Format(CultureInfo.InvariantCulture,
@"IF EXISTS (SELECT 1 FROM [sys].[triggers] WHERE [object_id] = OBJECT_ID('{0}'))
DROP TRIGGER {0};" ,
tableTrigger);
// Execute
using (var cmd = new SqlCommand(cmdText, conn))
{
// Process
EnsureOpenConnection(conn);
// Properties
cmd.CommandType = CommandType.Text;
cmd.Transaction = trans;
// Execute
cmd.ExecuteNonQuery();
}
}
});
}
/// <summary>
/// Creates a provider implementation
/// </summary>
/// <param name="dependencyParams">Dependency monitoring parameters</param>
/// <param name="providerType">Type of the provider to create</param>
/// <param name="serviceBrokerQueue">Queue name for the service broker implementation</param>
/// <returns>Sql Dependency Provider instance</returns>
public static SqlDependencyProvider Create(SqlDependencyParams dependencyParams,
SqlDependencyProviderType providerType, string serviceBrokerQueue)
{
return(Create(dependencyParams, providerType, serviceBrokerQueue, null));
}
/// <summary>
/// Creates a provider implementation
/// </summary>
/// <param name="dependencyParams">Dependency monitoring parameters</param>
/// <param name="providerType">Type of the provider to create</param>
/// <returns>Sql Dependency Provider instance</returns>
public static SqlDependencyProvider Create(SqlDependencyParams dependencyParams,
SqlDependencyProviderType providerType)
{
return(Create(dependencyParams, providerType, null, null));
}
