I m trying to execute a customized query and I don t want to involve OrmLite for anything other that the mapping from IDataReader to a defined ServiceStack model type.
Currently, I m doing something like this:
var result = con.Exec(command => {
command.AddParam("now", DateTimeOffset.Now, dbType: System.Data.DbType.DateTimeOffset);
return command.ConvertTo<T>($@"BEGIN TRANSACTION;
WITH cte AS (
SELECT TOP(1) *
FROM {con.Connection.GetQuotedTableName<T>()} with (rowlock, readpast)
WHERE {statusColumnName} = {(int)QueueItemStatus.Ready}
order by {idColumnName})
UPDATE cte SET
{statusColumnName} = {(int)QueueItemStatus.Locked},
{lockedColumnName} = @now,
{doneColumnName} = NULL
OUTPUT deleted.*;
COMMIT;");
});
I ve seen SqlList<T>(string sql) but it looks that method doesn t adhere to ServiceStack s annotations (Alias, etc).