Accessing data via SQL persistence

Accessing business data

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.

Using SQL data context

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

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

    public HandlerThatUsesSessionViaDI(ISqlStorageSession sqlPersistenceSession)
    {
        this.sqlPersistenceSession = sqlPersistenceSession;
    }

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

config.RegisterComponents(c =>
{
    c.AddScoped(b =>
    {
        var session = b.GetService<ISqlStorageSession>();
        var repository = new MyRepository(
            session.Connection,
            session.Transaction);

        //Ensure changes are saved before the transaction is committed
        session.OnSaveChanges((s, ctx) => repository.SaveChangesAsync());

        return repository;
    });
});

Using Entity Framework

When using Entity Framework (or another object/relational mapper) to access business data, there is the option to create an Entity Framework data context within a handler and use the Synchronized Storage Session to reuse the connection to the database.

Another option is to inject the Entity Framework data context into the handler. When NServiceBus has finished processing a message it will publish an in-process event that provides the ability to call the SaveChanges method on the Entity Framework data context. More information can be found in the sample for using samples/entity-framework.

Using in 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 :
    Saga<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.

Even if the database driver supports TransactionScope, it must be used with a transport that also supports TransactionScope (i.e. MSMQ or SQL Server). If a transaction is elevated to a distributed transaction and the transport or environment doesn't support it, the following exception will be thrown:

System.Transactions.TransactionAbortedException: The transaction has aborted. 
System.Transactions.TransactionManagerCommunicationException: Communication with the underlying transaction manager has failed.
System.Runtime.InteropServices.COMException: The Transaction Manager is not available. (Exception from HRESULT: 0x8004D01B)