public RecordList<WorkerRecord> GetWorkerList(string applicationName, string search, int limit, int offset, IDbTransaction transaction)
{
StringBuilder cb = new StringBuilder(
@"SELECT CAST(COUNT([Id]) AS bigint)
FROM [BlueCollarWorker]
WHERE
[ApplicationName] = @ApplicationName");
StringBuilder sb = new StringBuilder(
@"SELECT *
FROM [BlueCollarWorker]
WHERE
[ApplicationName] = @ApplicationName");
if (!string.IsNullOrEmpty(search))
{
const string Search = @"
AND
(
[Name] LIKE @Search
OR [MachineName] LIKE @Search
OR [MachineAddress] LIKE @Search
OR [QueueNames] LIKE @Search
OR [Status] LIKE @Search
OR [Signal] LIKE @Search
OR [Startup] LIKE @Search
)";
cb.Append(Search);
sb.Append(Search);
}
cb.Append(";\n\n");
sb.Append("\n");
sb.Append(
@"ORDER BY [Name] ASC
LIMIT @Limit OFFSET @Offset;");
sb.Append("\n");
sb.Append(CountsSql);
var p = new
{
ApplicationName = applicationName,
Search = !string.IsNullOrEmpty(search) ? string.Concat("%", search, "%") : null,
Limit = limit,
Offset = offset
};
var list = new RecordList<WorkerRecord>();
using (var multi = this.connection.QueryMultiple(cb.ToString() + sb.ToString(), p, transaction, null, null))
{
list.SetPaging(multi.Read<long>().First(), limit, offset);
foreach (var record in multi.Read<WorkerRecord>())
{
list.Records.Add(record);
}
list.Counts = CreateCounts(multi);
}
return list;
}