private void ListenToChanges(SqlNotificationEventArgs e = null)
{
using (SqlCommand command = new SqlCommand(_sqlCommandText, _sqlConnection))
{
// Create a dependency and associate it with the SqlCommand.
var sqlDependency = new SqlDependency(command);
sqlDependency.OnChange += OnChange;
// Execute the command here to prepopulate the current state and then invalidate the
// state upon asynchronous notification
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var dbDto = new DbChanges();
for (int i = 0; i < reader.FieldCount; i++)
{
dbDto.Data.Add(reader.GetName(i), reader[i]);
}
if (DbChanged != null)
{
DbChanged(this, dbDto);
}
}
}
}
}
private bool ExistsInLastDtos(DbChanges dbDto)
{
foreach (var lastDbDto in _lastDbDtos)
{
if (dbDto.Equals(lastDbDto))
{
return(true);
}
}
return(false);
}
private void PollingThread()
{
while (!_shutdownThread)
{
Thread.Sleep(PollingRateMilliseconds);
using (SqlCommand command = new SqlCommand(_sqlSelect, _sqlConnection))
{
try
{
var lastDbDtos = new List <DbChanges>();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
var dbDto = new DbChanges();
for (int i = 0; i < reader.FieldCount; i++)
{
dbDto.Data.Add(reader.GetName(i), reader[i]);
}
if (!ExistsInLastDtos(dbDto))
{
if (DbChanged != null)
{
DbChanged(this, dbDto);
}
}
lastDbDtos.Add(dbDto);
}
}
_lastDbDtos = lastDbDtos;
}
catch (Exception e)
{
_logger.Error("Cannot read DB", e);
}
}
}
}