internal void ValidateAndAdjust()
{
// Connection string
if (ConnectionString == null)
{
// ReSharper disable once NotResolvedInText
throw new ArgumentNullException("SqlDependencyProvider.Parameters.ConnectionString");
}
// Tables
var tableInfos = new List <SqlDependencyQualifiedObjectName>(Tables);
tableInfos = tableInfos
.Where(p => p?.Name != null)
.Distinct()
.ToList();
if (tableInfos.Count == 0)
{
// ReSharper disable once NotResolvedInText
throw new ArgumentNullException("SqlDependencyProvider.Parameters.Tables");
}
Tables.Clear();
Tables.AddRange(tableInfos.OrderBy(p => p.Schema, StringComparer.OrdinalIgnoreCase)
.ThenBy(p => p.Name, StringComparer.OrdinalIgnoreCase));
// Monitored changes
if (MonitoredChanges == SqlDependencyMonitoredChanges.None)
{
// ReSharper disable once NotResolvedInText
throw new ArgumentNullException("SqlDependencyProvider.Parameters.MonitoredChanges");
}
// Check all monitored tables exist
using (var conn = new SqlConnection(ConnectionString))
{
// Ensure open
Helper.EnsureOpenConnection(conn);
// Check
var nonExistingTableNames = new List <string>();
if (!Tables.Any(t =>
{
if (!Helper.TableExists(conn, null, t))
{
nonExistingTableNames.Add(t.ToString());
return(false);
}
return(true);
}))
{
#if USETRANSLATIONS
throw new InvalidOperationException(
Translations.Default["SText_SqlDependencyOneOrMoreMonitoredTablesDoNotExist_Names",
string.Join(", ", nonExistingTableNames)]);
#else
throw new InvalidOperationException(string.Format(CultureInfo.InvariantCulture,
Resources.SText_SqlDependencyOneOrMoreMonitoredTablesDoNotExist_Names,
string.Join(", ", nonExistingTableNames)));
#endif
}
}
}
private void Poll()
{
// Current values
var currentChanges = new Dictionary <SqlDependencyQualifiedObjectName, SqlDependencyMonitoredChanges>();
// Open connection
using (var conn = new SqlConnection(Parameters.ConnectionString))
{
// Ensure open
Helper.EnsureOpenConnection(conn);
// Command Text
var cmdText = string.Format(CultureInfo.InvariantCulture,
@"SELECT * FROM {0} WHERE [ObjectId] IN ({1})",
Parameters.Options.ChangeHolderTable,
string.Join(", ", Parameters.Tables.Select(t => string.Format(CultureInfo.InvariantCulture,
@"OBJECT_ID('{0}')", t))));
// Execute
using (var cmd = new SqlCommand(cmdText, conn))
{
cmd.CommandType = CommandType.Text;
using (var dataReader = cmd.ExecuteReader())
{
while (dataReader.Read())
{
// Get object details
var objectName = dataReader["ObjectName"] as string;
var table = Parameters.Tables.SingleOrDefault(t => t.FullName.Equals(objectName, StringComparison.OrdinalIgnoreCase));
if (table == null)
{
continue;
}
// Get current dates
var lastInsertDate = Helper.GetNullableFieldValue <DateTimeOffset>(dataReader, "LastInsertDate");
var lastUpdateDate = Helper.GetNullableFieldValue <DateTimeOffset>(dataReader, "LastUpdateDate");
var lastDeleteDate = Helper.GetNullableFieldValue <DateTimeOffset>(dataReader, "LastDeleteDate");
// Previous dates
Tuple <DateTimeOffset?, DateTimeOffset?, DateTimeOffset?> prevDates;
if (!_previousChanges.TryGetValue(table, out prevDates))
{
// ReSharper disable once RedundantAssignment
prevDates = null;
}
// Set Changes to none
currentChanges[table] = SqlDependencyMonitoredChanges.None;
// Check what has changed
if (((Parameters.MonitoredChanges & SqlDependencyMonitoredChanges.Insert) == SqlDependencyMonitoredChanges.Insert) &&
lastInsertDate.HasValue)
{
if ((prevDates?.Item1 == null) ||
(lastInsertDate.Value > prevDates.Item1.Value))
{
currentChanges[table] |= SqlDependencyMonitoredChanges.Insert;
}
}
if (((Parameters.MonitoredChanges & SqlDependencyMonitoredChanges.Update) == SqlDependencyMonitoredChanges.Update) &&
lastUpdateDate.HasValue)
{
if ((prevDates?.Item2 == null) ||
(lastUpdateDate.Value > prevDates.Item2.Value))
{
currentChanges[table] |= SqlDependencyMonitoredChanges.Update;
}
}
if (((Parameters.MonitoredChanges & SqlDependencyMonitoredChanges.Delete) == SqlDependencyMonitoredChanges.Delete) &&
lastDeleteDate.HasValue)
{
if ((prevDates?.Item3 == null) ||
(lastDeleteDate.Value > prevDates.Item3.Value))
{
currentChanges[table] |= SqlDependencyMonitoredChanges.Delete;
}
}
// Update previous changes data
_previousChanges[table] = new Tuple <DateTimeOffset?, DateTimeOffset?, DateTimeOffset?>(lastInsertDate, lastUpdateDate, lastDeleteDate);
}
}
}
}
// Fire changes
currentChanges
.Where(kv => kv.Value != SqlDependencyMonitoredChanges.None)
.ToList().ForEach(kv => FireTableChangedEvent(kv.Key, kv.Value));
}