public virtual async Task <List <ISqlTransactionalOutboxItem <TUniqueIdentifier> > > RetrieveOutboxItemsAsync(
OutboxItemStatus status,
int maxBatchSize = -1
)
{
var statusParamName = OutboxTableConfig.StatusFieldName;
var sql = QueryBuilder.BuildSqlForRetrieveOutboxItemsByStatus(status, maxBatchSize, statusParamName);
await using var sqlCmd = CreateSqlCommand(sql);
AddParam(sqlCmd, statusParamName, status.ToString(), SqlDbType.VarChar);
var results = new List <ISqlTransactionalOutboxItem <TUniqueIdentifier> >();
await using var sqlReader = await sqlCmd.ExecuteReaderAsync().ConfigureAwait(false);
while (await sqlReader.ReadAsync().ConfigureAwait(false))
{
var createdDateUtcFromDb = (DateTime)sqlReader[OutboxTableConfig.CreatedDateTimeUtcFieldName];
var outboxItem = OutboxItemFactory.CreateExistingOutboxItem(
uniqueIdentifier: ConvertUniqueIdentifierFromDb(sqlReader),
status: sqlReader[OutboxTableConfig.StatusFieldName] as string,
fifoGroupingIdentifier: sqlReader[OutboxTableConfig.FifoGroupingIdentifier] as string,
publishAttempts: (int)sqlReader[OutboxTableConfig.PublishAttemptsFieldName],
createdDateTimeUtc: new DateTimeOffset(createdDateUtcFromDb, TimeSpan.Zero),
publishTarget: sqlReader[OutboxTableConfig.PublishTargetFieldName] as string,
serializedPayload: sqlReader[OutboxTableConfig.PayloadFieldName] as string
);
results.Add(outboxItem);
}
return(results);
}
public virtual async Task <List <ISqlTransactionalOutboxItem <TUniqueIdentifier> > > InsertNewOutboxItemsAsync(
IEnumerable <ISqlTransactionalOutboxInsertionItem <TPayload> > outboxItems,
int insertBatchSize = 20
)
{
await using var sqlCmd = CreateSqlCommand(string.Empty);
//Use the Outbox Item Factory to create a new Outbox Item with serialized payload.
var outboxItemsList = outboxItems.Select(
i => OutboxItemFactory.CreateNewOutboxItem(
i.PublishingTarget,
i.PublishingPayload,
i.FifoGroupingIdentifier
)
).ToList();
var batches = outboxItemsList.Chunk(insertBatchSize);
foreach (var batch in batches)
{
sqlCmd.CommandText = QueryBuilder.BuildParameterizedSqlToInsertNewOutboxItems(batch);
sqlCmd.Parameters.Clear();
//Add the Parameters!
for (var batchIndex = 0; batchIndex < batch.Length; batchIndex++)
{
var outboxItem = batch[batchIndex];
var uniqueIdentifierForDb = ConvertUniqueIdentifierForDb(outboxItem.UniqueIdentifier);
AddParam(sqlCmd, OutboxTableConfig.UniqueIdentifierFieldName, uniqueIdentifierForDb, SqlDbType.UniqueIdentifier, batchIndex);
//NOTE: The for Sql Server, the CreatedDateTimeUtcField is automatically populated by Sql Server.
// this helps eliminate risks of datetime sequencing across servers or server-less environments.
////AddParam(sqlCmd, OutboxTableConfig.CreatedDateTimeUtcFieldName, outboxItem.CreatedDateTimeUtc, batchIndex);
AddParam(sqlCmd, OutboxTableConfig.FifoGroupingIdentifier, outboxItem.FifoGroupingIdentifier, SqlDbType.VarChar, batchIndex);
AddParam(sqlCmd, OutboxTableConfig.StatusFieldName, outboxItem.Status.ToString(), SqlDbType.VarChar, batchIndex);
AddParam(sqlCmd, OutboxTableConfig.PublishAttemptsFieldName, outboxItem.PublishAttempts, SqlDbType.Int, batchIndex);
AddParam(sqlCmd, OutboxTableConfig.PublishTargetFieldName, outboxItem.PublishTarget, SqlDbType.VarChar, batchIndex);
AddParam(sqlCmd, OutboxTableConfig.PayloadFieldName, outboxItem.Payload, SqlDbType.NVarChar, batchIndex);
}
//Execute the Batch and continue...
await using var sqlReader = await sqlCmd.ExecuteReaderAsync().ConfigureAwait(false);
//Since some fields are actually populated in the Database, we post-process to update the models with valid
// values as returned from teh Insert process...
var outboxBatchLookup = batch.ToLookup(i => i.UniqueIdentifier);
while (await sqlReader.ReadAsync().ConfigureAwait(false))
{
var uniqueIdentifier = ConvertUniqueIdentifierFromDb(sqlReader);
var outboxItem = outboxBatchLookup[uniqueIdentifier].First();
var createdDateUtcFromDb = (DateTime)sqlReader[OutboxTableConfig.CreatedDateTimeUtcFieldName];
outboxItem.CreatedDateTimeUtc = new DateTimeOffset(createdDateUtcFromDb, TimeSpan.Zero);
}
}
return(outboxItemsList);
}
