/// <summary>
/// Discover all changes listed in the change tables
/// </summary>
/// <param name="config"></param>
/// <param name="stateBase"></param>
/// <returns></returns>
private IEnumerable <DataChange> DiscoverDataChangesUsingChangetables(SqlDatabaseConnectorJobConfiguration config, DatabaseJobState stateBase)
{
foreach (var changeTable in config.ChangeTables)
{
var dataChange = new DataChange(changeTable);
using (var conn = new SqlConnection(config.ConnectionString))
using (var cmd = conn.CreateCommand())
{
cmd.CommandText =
string.Format(
"SELECT * FROM CHANGETABLE (CHANGES {0}, {1}) as CT ORDER BY SYS_CHANGE_VERSION asc",
changeTable.TableName,
stateBase.GetChangetableVersionThreshold(changeTable.TableName));
Log.Logger.Debug($"{config.JobName}: Getting data changes from changetables with: {cmd.CommandText}");
try
{
conn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
dataChange.AddChange(DataRowToChangeTableEvent(reader, changeTable));
}
}
}
catch (SqlException e)
{
Log.Error(e, $"{config.JobName}: SQL error when executing {cmd.CommandText}");
throw;
}
}
yield return(dataChange);
}
}
private IEnumerable <DataChange> DiscoverDataChangesUsingEventTables(SqlDatabaseConnectorJobConfiguration config, DatabaseJobState stateBase)
{
foreach (var eventTable in config.EventTables)
{
var dataChange = new DataChange(new TableDetail()
{
TableName = eventTable.TableName,
PrimaryKeyName = eventTable.MainTableIdColumnName
});
using (var conn = new SqlConnection(config.ConnectionString))
using (var cmd = conn.CreateCommand())
{
cmd.CommandText =
string
.Format(
"SELECT {0}, {1}, {2} FROM {3} WHERE {0} > {4} ORDER BY {0} asc",
eventTable.EventSequenceColumnName,
eventTable.MainTableIdColumnName,
eventTable.EventTypeColumnName,
eventTable.TableName,
stateBase.GetChangetableVersionThreshold(eventTable.TableName));
Log.Logger.Debug($"{config.JobName}: Getting data changes from changetables with: {cmd.CommandText}");
try
{
conn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
dataChange.AddChange(DataRowToChangeTableEvent(reader, eventTable));
}
}
}
catch (SqlException e)
{
Log.Logger.Error($"{config.JobName}: SQL error when executing {cmd.CommandText}", e);
throw;
}
}
yield return(dataChange);
}
}