Accessing data via SQL persistence

Component: Sql Persistence
NuGet Package NServiceBus.Persistence.Sql (3-pre)
Target NServiceBus Version: 7.x
This page targets a pre-release version and is subject to change prior to the final release.

SQL persistence supports a mechanism that allows using the same data context used by NServiceBus internals to also store business data. This ensures atomicity of changes done across multiple handlers and sagas involved in processing of the same message. See accessing data to learn more about other ways of accessing the data in the handlers.

The current DbConnection and DbTransaction can be accessed via the current context.

The session (context.SynchronizedStorageSession.SqlPersistenceSession()) is only supported when the SQL persistence has been selected to be used for Sagas and/or the Outbox. See also Persistence Storage Types and Configuration order for persistence.

Using in a Handler

public class HandlerThatUsesSession :
    IHandleMessages<MyMessage>
{
    static ILog log = LogManager.GetLogger<HandlerThatUsesSession>();

    public Task Handle(MyMessage message, IMessageHandlerContext context)
    {
        var sqlPersistenceSession = context.SynchronizedStorageSession.SqlPersistenceSession();
        log.Info(sqlPersistenceSession.Connection.ConnectionString);
        // use Connection and/or Transaction of ISqlStorageSession to persist or query the database
        return Task.CompletedTask;
    }
}

Using in a Saga

Other than interacting with its own internal state, a saga should not access a database, call out to web services, or access other resources. See Accessing databases and other resources from a saga.

If the situation is special enough to warrant going against this recommendation, the following documentation will describe how to do so.

public class SagaThatUsesSession :
    SqlSaga<SagaThatUsesSession.SagaData>,
    IHandleMessages<MyMessage>
{
    public Task Handle(MyMessage message, IMessageHandlerContext context)
    {
        var sqlPersistenceSession = context.SynchronizedStorageSession.SqlPersistenceSession();
        log.Info(sqlPersistenceSession.Connection.ConnectionString);
        // use Connection and/or Transaction of ISqlStorageSession to persist or query the database
        return Task.CompletedTask;
    }

Regardless of how the database connection is accessed, it is fully managed by NServiceBus.

TransactionScope mode

When the transport is set to TransactionScope transaction mode NServiceBus expects the persistence to hook into the ambient transaction in order to ensure exactly-once message processing. The persistence does it by ensuring that the database connection created for synchronized storage session is enlisted in the transports transaction by calling DbConnection.EnlistTransaction method.

If a database driver configured for the persistence does not support this method (e.g. MySQL), an exception will be thrown to prevent incorrect business behavior (executing handler in at-least-once mode when user expects exactly-once). To mitigate this problem

  • use database that supports distributed transactions (such as SQL Server or Oracle) or
  • enable Outbox or
  • rewrite message handler logic to ensure it is idempotent.

Samples

Related Articles


Last modified