public async Task InsertJobHistoryEntry(IJobExecutionContext context, JobExecutionException jobException)
{
var sql = AdoJobStoreUtil.ReplaceTablePrefix(SqlInsertJobExecuted, tablePrefix, null);
using (var connection = GetConnection(IsolationLevel.ReadUncommitted))
{
using (var command = Delegate.PrepareCommand(connection, sql))
{
Delegate.AddCommandParameter(command, "schedulerName", context.Scheduler.SchedulerName);
Delegate.AddCommandParameter(command, "instanceName", context.Scheduler.SchedulerInstanceId);
Delegate.AddCommandParameter(command, "jobName", context.JobDetail.Key.Name);
Delegate.AddCommandParameter(command, "jobGroup", context.JobDetail.Key.Group);
Delegate.AddCommandParameter(command, "triggerName", context.Trigger.Key.Name);
Delegate.AddCommandParameter(command, "triggerGroup", context.Trigger.Key.Group);
Delegate.AddCommandParameter(command, "scheduledTime", Delegate.GetDbDateTimeValue(context.ScheduledFireTimeUtc));
Delegate.AddCommandParameter(command, "firedTime", Delegate.GetDbDateTimeValue(context.FireTimeUtc));
Delegate.AddCommandParameter(command, "runTime", Delegate.GetDbTimeSpanValue(context.JobRunTime));
Delegate.AddCommandParameter(command, "error", Delegate.GetDbBooleanValue(jobException != null));
Delegate.AddCommandParameter(command, "errorMessage", jobException?.ToString());
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
connection.Commit();
}
}
}
public async Task <IReadOnlyList <JobHistoryEntryDto> > SelectJobHistoryEntries(string schedulerName)
{
var sql = AdoJobStoreUtil.ReplaceTablePrefix(SqlSelectHistoryEntry, tablePrefix, null);
List <JobHistoryEntryDto> entries = new List <JobHistoryEntryDto>();
using (var dbConnection = GetConnection(IsolationLevel.ReadUncommitted))
{
using (var dbCommand = Delegate.PrepareCommand(dbConnection, sql))
{
Delegate.AddCommandParameter(dbCommand, "schedulerName", schedulerName);
using (var reader = await dbCommand.ExecuteReaderAsync().ConfigureAwait(false))
{
while (await reader.ReadAsync().ConfigureAwait(false))
{
JobHistoryEntryDto entry = new JobHistoryEntryDto
{
JobName = reader.GetString("JOB_NAME"),
JobGroup = reader.GetString("JOB_GROUP"),
TriggerName = reader.GetString("TRIGGER_NAME"),
TriggerGroup = reader.GetString("TRIGGER_GROUP"),
FiredTime = Delegate.GetDateTimeFromDbValue(reader["FIRED_TIME"]).GetValueOrDefault(),
ScheduledTime = Delegate.GetDateTimeFromDbValue(reader["SCHED_TIME"]).GetValueOrDefault(),
RunTime = Delegate.GetTimeSpanFromDbValue(reader["RUN_TIME"]).GetValueOrDefault(),
Error = Delegate.GetBooleanFromDbValue(reader["ERROR"]),
ErrorMessage = reader.GetString("ERROR_MESSAGE")
};
entries.Add(entry);
}
}
}
}
return(entries);
}
public async Task <long> GetTotalJobsFailed(string schedulerName, CancellationToken cancellationToken = default)
{
using (ConnectionAndTransactionHolder dbConnection = GetConnection(IsolationLevel.ReadUncommitted))
{
string sqlCount = AdoJobStoreUtil.ReplaceTablePrefix(PropertySqlSelectHistoryFailedCount, _tablePrefix, null);
using (DbCommand dbCommand = Delegate.PrepareCommand(dbConnection, sqlCount))
{
Delegate.AddCommandParameter(dbCommand, "schedulerName", schedulerName);
return((long)await dbCommand.ExecuteScalarAsync(cancellationToken).ConfigureAwait(false));
}
}
}
public async Task <IEnumerable <ExecutionHistoryEntry> > FilterLast(string schedulerName, int limit, CancellationToken cancellationToken = default)
{
//return await Task.FromResult(new List<ExecutionHistoryEntry>());
List <ExecutionHistoryEntry> list = new List <ExecutionHistoryEntry>();
using (ConnectionAndTransactionHolder dbConnection = GetConnection(IsolationLevel.ReadUncommitted))
{
string sql = string.Format(PropertySqlServerSelectHistory, _tablePrefix);
if (_provider.Equals("MySql"))
{
sql = string.Format(PropertyMySqlSelectHistory, _tablePrefix);
}
using (DbCommand dbCommand = Delegate.PrepareCommand(dbConnection, sql))
{
Delegate.AddCommandParameter(dbCommand, "schedulerName", schedulerName);
Delegate.AddCommandParameter(dbCommand, "limit", limit);
using (DbDataReader reader = await dbCommand.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false))
{
while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
{
var entry = new ExecutionHistoryEntry
{
Recovering = Delegate.GetBooleanFromDbValue(reader["Recovering"]),
Vetoed = Delegate.GetBooleanFromDbValue(reader["Vetoed"]),
ActualFireTimeUtc = Convert.ToDateTime(reader["Actual_Fire_Time_Utc"]),
FinishedTimeUtc = Convert.ToDateTime(reader["Finished_Time_Utc"]),
FireInstanceId = reader.GetString("Fire_Instance_Id"),
ScheduledFireTimeUtc = Convert.ToDateTime(reader["Scheduled_Fire_Time_Utc"]),
SchedulerInstanceId = reader.GetString("Scheduler_Instance_Id"),
SchedulerName = reader.GetString("SCHED_NAME"),
JobName = reader.GetString("JOB_NAME"),
JobGroup = reader.GetString("JOB_GROUP"),
TriggerName = reader.GetString("TRIGGER_NAME"),
TriggerGroup = reader.GetString("TRIGGER_GROUP"),
FiredTime = Delegate.GetDateTimeFromDbValue(reader["FIRED_TIME"]).GetValueOrDefault(),
ScheduledTime = Delegate.GetDateTimeFromDbValue(reader["SCHED_TIME"]).GetValueOrDefault(),
RunTime = Delegate.GetTimeSpanFromDbValue(reader["RUN_TIME"]).GetValueOrDefault(),
Error = Delegate.GetBooleanFromDbValue(reader["ERROR"]),
ErrorMessage = reader.GetString("ERROR_MESSAGE")
};
list.Add(entry);
}
}
}
}
list.Reverse();
return(list);
}
public async Task UpdateJobHistoryEntryVetoed(
IJobExecutionContext context,
CancellationToken cancellationToken = default(CancellationToken))
{
string sql = AdoJobStoreUtil.ReplaceTablePrefix(PropertySqlUpdateHistoryVetoed, _tablePrefix, null);
using (ConnectionAndTransactionHolder connection = GetConnection(IsolationLevel.ReadUncommitted))
{
using (DbCommand command = Delegate.PrepareCommand(connection, sql))
{
Delegate.AddCommandParameter(command, "vetoed", true);
Delegate.AddCommandParameter(command, "fireInstanceId", context.FireInstanceId);
await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);
connection.Commit(false);
}
}
}
public async Task UpdateJobHistoryEntryError(
IJobExecutionContext context,
JobExecutionException jobException,
CancellationToken cancellationToken = default(CancellationToken))
{
string sql = AdoJobStoreUtil.ReplaceTablePrefix(PropertySqlUpdateHistoryError, _tablePrefix, null);
using (ConnectionAndTransactionHolder connection = GetConnection(IsolationLevel.ReadUncommitted))
{
using (DbCommand command = Delegate.PrepareCommand(connection, sql))
{
Delegate.AddCommandParameter(command, "error", Delegate.GetDbBooleanValue(jobException != null));
Delegate.AddCommandParameter(command, "errorMessage", jobException?.GetBaseException()?.Message);
Delegate.AddCommandParameter(command, "fireInstanceId", context.FireInstanceId);
await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);
connection.Commit(false);
}
}
}
public async Task CreateJobHistoryEntry(
IJobExecutionContext context,
CancellationToken cancellationToken = default(CancellationToken))
{
string sql = AdoJobStoreUtil.ReplaceTablePrefix(PropertySqlInsertJobExecuted, _tablePrefix, null);
using (ConnectionAndTransactionHolder connection = GetConnection(IsolationLevel.ReadUncommitted))
{
using (DbCommand command = Delegate.PrepareCommand(connection, sql))
{
Delegate.AddCommandParameter(command, "schedulerName", context.Scheduler.SchedulerName);
Delegate.AddCommandParameter(command, "schedulerInstanceName", context.Scheduler.SchedulerInstanceId);
Delegate.AddCommandParameter(command, "jobName", context.JobDetail.Key.Name);
Delegate.AddCommandParameter(command, "jobGroup", context.JobDetail.Key.Group);
Delegate.AddCommandParameter(command, "triggerName", context.Trigger.Key.Name);
Delegate.AddCommandParameter(command, "triggerGroup", context.Trigger.Key.Group);
Delegate.AddCommandParameter(command, "scheduledTime", Delegate.GetDbDateTimeValue(context.ScheduledFireTimeUtc));
Delegate.AddCommandParameter(command, "firedTime", Delegate.GetDbDateTimeValue(context.FireTimeUtc));
Delegate.AddCommandParameter(command, "runTime", Delegate.GetDbTimeSpanValue(context.JobRunTime));
Delegate.AddCommandParameter(command, "fireInstanceId", context.FireInstanceId);
Delegate.AddCommandParameter(command, "scheduledFireTimeUtc", context.ScheduledFireTimeUtc?.UtcDateTime);
Delegate.AddCommandParameter(command, "actualFireTimeUtc", context.FireTimeUtc.UtcDateTime);
Delegate.AddCommandParameter(command, "finishedTimeUtc", DateTime.UtcNow);
Delegate.AddCommandParameter(command, "recovering", context.Recovering);
Delegate.AddCommandParameter(command, "vetoed", false);
Delegate.AddCommandParameter(command, "error", false);
try
{
await command.ExecuteNonQueryAsync(cancellationToken).ConfigureAwait(false);
connection.Commit(false);
}
catch (Exception ex)
{
throw ex;
}
}
}
}
public async Task <PagedResultDto <ExecutionHistoryEntry> > GetPageJobHistoryEntries(
string schedulerName,
int pageIndex, int pageSize,
string orderByStr,
CancellationToken cancellationToken = default)
{
var pageResult = new PagedResultDto <ExecutionHistoryEntry>();
using (ConnectionAndTransactionHolder dbConnection = GetConnection(IsolationLevel.ReadUncommitted))
{
//string sql = AdoJobStoreUtil.ReplaceTablePrefix(PropertySqlServerSelectHistoryEntry, _tablePrefix, null);
string sql = string.Format(PropertySqlServerSelectHistoryEntryPage, _tablePrefix, orderByStr);
if (_provider.Equals("MySql"))
{
//sql = AdoJobStoreUtil.ReplaceTablePrefix(PropertyMySqlSelectHistoryEntry, _tablePrefix, null);
sql = string.Format(PropertyMySqlSelectHistoryEntryPage, _tablePrefix, orderByStr);
}
pageResult.TotalCount = await GetAllCount(schedulerName, cancellationToken);
using (DbCommand dbCommand = Delegate.PrepareCommand(dbConnection, sql))
{
int page = pageIndex > 1 ? (pageIndex - 1) * pageSize : 0;
Delegate.AddCommandParameter(dbCommand, "schedulerName", schedulerName);
Delegate.AddCommandParameter(dbCommand, "page", page);
if (_provider.Equals("MySql"))
{
// pageSize
Delegate.AddCommandParameter(dbCommand, "pageSize", pageSize);
}
else
{
// endPage
Delegate.AddCommandParameter(dbCommand, "endPage", page + pageSize);
}
using (DbDataReader reader = await dbCommand.ExecuteReaderAsync(cancellationToken).ConfigureAwait(false))
{
List <ExecutionHistoryEntry> list = new List <ExecutionHistoryEntry>();
while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
{
var entry = new ExecutionHistoryEntry
{
Recovering = Delegate.GetBooleanFromDbValue(reader["Recovering"]),
Vetoed = Delegate.GetBooleanFromDbValue(reader["Vetoed"]),
ActualFireTimeUtc = Convert.ToDateTime(reader["Actual_Fire_Time_Utc"]),
FinishedTimeUtc = Convert.ToDateTime(reader["Finished_Time_Utc"]),
FireInstanceId = reader.GetString("Fire_Instance_Id"),
ScheduledFireTimeUtc = Convert.ToDateTime(reader["Scheduled_Fire_Time_Utc"]),
SchedulerInstanceId = reader.GetString("Scheduler_Instance_Id"),
SchedulerName = reader.GetString("SCHED_NAME"),
JobName = reader.GetString("JOB_NAME"),
JobGroup = reader.GetString("JOB_GROUP"),
TriggerName = reader.GetString("TRIGGER_NAME"),
TriggerGroup = reader.GetString("TRIGGER_GROUP"),
FiredTime = Delegate.GetDateTimeFromDbValue(reader["FIRED_TIME"]).GetValueOrDefault(),
ScheduledTime = Delegate.GetDateTimeFromDbValue(reader["SCHED_TIME"]).GetValueOrDefault(),
RunTime = Delegate.GetTimeSpanFromDbValue(reader["RUN_TIME"]).GetValueOrDefault(),
Error = Delegate.GetBooleanFromDbValue(reader["ERROR"]),
ErrorMessage = reader.GetString("ERROR_MESSAGE")
};
list.Add(entry);
}
pageResult.Items = list.AsReadOnly();
}
}
}
return(pageResult);
}