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); }