/// <summary>
/// Gets the formatted column name to use for the given <see cref="JobRecordResultsOrderBy"/> value.
/// </summary>
/// <param name="orderBy">The order by value to get the column name for.</param>
/// <returns>A column name.</returns>
protected virtual string GetOrderByColumnName(JobRecordResultsOrderBy orderBy)
{
string orderByColumn = null;
switch (orderBy)
{
case JobRecordResultsOrderBy.FinishDate:
orderByColumn = this.ColumnName("FinishDate");
break;
case JobRecordResultsOrderBy.JobType:
orderByColumn = this.ColumnName("JobType");
break;
case JobRecordResultsOrderBy.Name:
orderByColumn = this.ColumnName("Name");
break;
case JobRecordResultsOrderBy.QueueDate:
orderByColumn = this.ColumnName("QueueDate");
break;
case JobRecordResultsOrderBy.ScheduleName:
orderByColumn = this.ColumnName("ScheduleName");
break;
case JobRecordResultsOrderBy.StartDate:
orderByColumn = this.ColumnName("StartDate");
break;
case JobRecordResultsOrderBy.Status:
orderByColumn = this.ColumnName("Status");
break;
default:
throw new NotImplementedException();
}
return orderByColumn;
}
/// <summary>
/// Creates a select command.
/// </summary>
/// <param name="connection">The connection to create the command with.</param>
/// <param name="likeName">A string representing a full or partial job name to filter on.</param>
/// <param name="withStatus">A <see cref="JobStatus"/> to filter on, or null if not applicable.</param>
/// <param name="inSchedule">A schedule name to filter on, if applicable.</param>
/// <param name="orderBy">A field to order the resultset by.</param>
/// <param name="sortDescending">A value indicating whether to order the resultset in descending order.</param>
/// <param name="pageNumber">The page number to get.</param>
/// <param name="pageSize">The size of the pages to get.</param>
/// <returns>A select command.</returns>
protected virtual DbCommand CreateSelectCommand(DbConnection connection, string likeName, JobStatus? withStatus, string inSchedule, JobRecordResultsOrderBy orderBy, bool sortDescending, int pageNumber, int pageSize)
{
const string Sql = @"SELECT * FROM {0} WHERE {1} LIKE {2}";
if (pageNumber < 1)
{
pageNumber = 1;
}
if (pageSize < 0)
{
pageSize = 0;
}
DbCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.Parameters.Add(this.ParameterWithValue(this.ParameterName("Name"), String.Concat("%", (likeName ?? String.Empty).Trim(), "%")));
StringBuilder sb = new StringBuilder();
sb.AppendFormat(
CultureInfo.InvariantCulture,
Sql,
this.TableName,
this.ColumnName("Name"),
this.ParameterName("Name"));
if (withStatus != null)
{
sb.AppendFormat(CultureInfo.InvariantCulture, " AND {0} = {1}", this.ColumnName("Status"), this.ParameterName("Status"));
command.Parameters.Add(this.ParameterWithValue(this.ParameterName("Status"), withStatus.Value.ToString()));
}
if (!String.IsNullOrEmpty(inSchedule))
{
sb.AppendFormat(CultureInfo.InvariantCulture, " AND {0} = {1}", this.ColumnName("ScheduleName"), this.ParameterName("ScheduleName"));
command.Parameters.Add(this.ParameterWithValue(this.ParameterName("ScheduleName"), withStatus.Value.ToString()));
}
sb.AppendFormat(
CultureInfo.InvariantCulture,
" ORDER BY {0} {1} LIMIT {2} OFFSET {3};",
this.GetOrderByColumnName(orderBy),
sortDescending ? "DESC" : "ASC",
pageSize,
(pageNumber - 1) * pageSize);
command.CommandText = sb.ToString();
return command;
}
/// <summary>
/// Gets a collection of jobs that match the given filter parameters, ordered by the given sort parameters.
/// </summary>
/// <param name="likeName">A string representing a full or partial job name to filter on.</param>
/// <param name="withStatus">A <see cref="JobStatus"/> to filter on, or null if not applicable.</param>
/// <param name="inSchedule">A schedule name to filter on, if applicable.</param>
/// <param name="orderBy">A field to order the resultset by.</param>
/// <param name="sortDescending">A value indicating whether to order the resultset in descending order.</param>
/// <param name="pageNumber">The page number to get.</param>
/// <param name="pageSize">The size of the pages to get.</param>
/// <param name="transaction">The transaction to execute the command in.</param>
/// <returns>A collection of jobs.</returns>
public override IEnumerable<JobRecord> GetJobs(string likeName, JobStatus? withStatus, string inSchedule, JobRecordResultsOrderBy orderBy, bool sortDescending, int pageNumber, int pageSize, IJobStoreTransaction transaction)
{
SqlJobStoreTransaction trans = transaction as SqlJobStoreTransaction;
DbConnection connection = null;
DbCommand command = null;
List<JobRecord> records = new List<JobRecord>();
try
{
if (trans != null)
{
command = this.CreateSelectCommand(trans.Connection, likeName, withStatus, inSchedule, orderBy, sortDescending, pageNumber, pageSize);
command.Transaction = trans.Transaction;
}
else
{
connection = this.CreateAndOpenConnection();
command = this.CreateSelectCommand(connection, likeName, withStatus, inSchedule, orderBy, sortDescending, pageNumber, pageSize);
}
using (DbDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
records.Add(this.CreateRecord(reader));
}
}
}
finally
{
if (command != null)
{
command.Dispose();
}
this.DisposeConnection(connection);
}
return records;
}
/// <summary> /// Gets a collection of jobs that match the given filter parameters, ordered by the given sort parameters. /// </summary> /// <param name="likeName">A string representing a full or partial job name to filter on.</param> /// <param name="withStatus">A <see cref="JobStatus"/> to filter on, or null if not applicable.</param> /// <param name="inSchedule">A schedule name to filter on, if applicable.</param> /// <param name="orderBy">A field to order the resultset by.</param> /// <param name="sortDescending">A value indicating whether to order the resultset in descending order.</param> /// <param name="pageNumber">The page number to get.</param> /// <param name="pageSize">The size of the pages to get.</param> /// <param name="transaction">The transaction to execute the command in.</param> /// <returns>A collection of jobs.</returns> public abstract IEnumerable<JobRecord> GetJobs(string likeName, JobStatus? withStatus, string inSchedule, JobRecordResultsOrderBy orderBy, bool sortDescending, int pageNumber, int pageSize, IJobStoreTransaction transaction);
/// <summary>
/// Gets a collection of jobs that match the given filter parameters, ordered by the given sort parameters.
/// </summary>
/// <param name="likeName">A string representing a full or partial job name to filter on.</param>
/// <param name="withStatus">A <see cref="JobStatus"/> to filter on, or null if not applicable.</param>
/// <param name="inSchedule">A schedule name to filter on, if applicable.</param>
/// <param name="orderBy">A field to order the resultset by.</param>
/// <param name="sortDescending">A value indicating whether to order the resultset in descending order.</param>
/// <param name="pageNumber">The page number to get.</param>
/// <param name="pageSize">The size of the pages to get.</param>
/// <returns>A collection of jobs.</returns>
public virtual IEnumerable<JobRecord> GetJobs(string likeName, JobStatus? withStatus, string inSchedule, JobRecordResultsOrderBy orderBy, bool sortDescending, int pageNumber, int pageSize)
{
return this.GetJobs(likeName, withStatus, inSchedule, orderBy, sortDescending, pageNumber, pageSize, null);
}
/// <summary>
/// Creates a select command.
/// </summary>
/// <param name="connection">The connection to create the command with.</param>
/// <param name="likeName">A string representing a full or partial job name to filter on.</param>
/// <param name="withStatus">A <see cref="JobStatus"/> to filter on, or null if not applicable.</param>
/// <param name="inSchedule">A schedule name to filter on, if applicable.</param>
/// <param name="orderBy">A field to order the resultset by.</param>
/// <param name="sortDescending">A value indicating whether to order the resultset in descending order.</param>
/// <param name="pageNumber">The page number to get.</param>
/// <param name="pageSize">The size of the pages to get.</param>
/// <returns>A select command.</returns>
protected override DbCommand CreateSelectCommand(DbConnection connection, string likeName, JobStatus? withStatus, string inSchedule, JobRecordResultsOrderBy orderBy, bool sortDescending, int pageNumber, int pageSize)
{
const string Sql = @"SELECT * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY {0} {1}) AS {2} FROM {3} WHERE {4} LIKE {5}";
if (pageNumber < 1)
{
pageNumber = 1;
}
if (pageSize < 0)
{
pageSize = 0;
}
DbCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.Parameters.Add(this.ParameterWithValue(ParameterName("Name"), String.Concat("%", (likeName ?? String.Empty).Trim(), "%")));
StringBuilder sb = new StringBuilder();
sb.AppendFormat(
CultureInfo.InvariantCulture,
Sql,
GetOrderByColumnName(orderBy),
sortDescending ? "DESC" : "ASC",
ColumnName("RowNumber"),
TableName,
ColumnName("Name"),
ParameterName("Name"));
if (withStatus != null)
{
sb.AppendFormat(CultureInfo.InvariantCulture, " AND {0} = {1}", ColumnName("Status"), ParameterName("Status"));
command.Parameters.Add(this.ParameterWithValue(ParameterName("Status"), withStatus.Value.ToString()));
}
if (!String.IsNullOrEmpty(inSchedule))
{
sb.AppendFormat(CultureInfo.InvariantCulture, " AND {0} = {1}", ColumnName("ScheduleName"), ParameterName("ScheduleName"));
command.Parameters.Add(this.ParameterWithValue(ParameterName("ScheduleName"), withStatus.Value.ToString()));
}
sb.AppendFormat(CultureInfo.InvariantCulture, ") t WHERE {0} > {1} AND {0} <= {2};", ColumnName("RowNumber"), ParameterName("SkipFrom"), ParameterName("SkipTo"));
command.CommandText = sb.ToString();
int skipFrom = (pageNumber - 1) * pageSize;
command.Parameters.Add(this.ParameterWithValue(ParameterName("SkipFrom"), skipFrom));
command.Parameters.Add(this.ParameterWithValue(ParameterName("SkipTo"), skipFrom + pageSize));
return command;
}
