ServiceStack OrmLite, custom/raw query, deserializing to Model types

21 Views Asked by At

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).

0

There are 0 best solutions below