Using Sql Server with Jasper

The Jasper.Persistence.SqlServer Nuget library provides Jasper users with a quick way to integrate Sql Server-backed persistence into their Jasper applications. To get started, just add the Jasper.Persistence.SqlServer Nuget to your project, and enable the persistence like this:

public class AppUsingSqlServer : JasperOptions
{
    public AppUsingSqlServer()
    {
        // If you know the connection string
        Extensions.PersistMessagesWithSqlServer("your connection string", "my_app_schema");


    }

    public override void Configure(IHostEnvironment hosting, IConfiguration config)
    {
        // In this case, you need to work directly with the SqlServerBackedPersistence
        // to get access to all the advanced properties of the Sql Server-backed persistence
        Extensions.Include<SqlServerBackedPersistence>(x =>
        {
            if (hosting.IsDevelopment())
            {
                // if so desired, the context argument gives you
                // access to both the IConfiguration and IHostingEnvironment
                // of the running application, so you could do
                // environment specific configuration here
            }

            x.Settings.ConnectionString = config["sqlserver"];

            // If your application uses a schema besides "dbo"
            x.Settings.SchemaName = "my_app_schema";

            // If you're using a database principal that is NOT "dbo":
            x.Settings.DatabasePrincipal = "not_dbo";
        });

    }
}

Enabling this configuration adds a couple things to your system:

• Service registrations in your IoC container for DbConnection and SqlConnection, with the Scoped lifecycle
• "Outbox" pattern usage with Sql Server as demonstrated below
• Message persistence using your application's Sql Server database, including outbox support with Sql Server and durable message persistence using Sql Server
• Support for the [Transactional] attribute as shown below

Transactional Middleware

Assuming that the Jasper.Persistence.SqlServer Nuget is referenced by your project, you can use the [Transactional] attribute on message (or HTTP) handler methods to wrap the message handling inside a single Sql Server transaction like so:

public class ItemCreatedHandler
{
    [Transactional]
    public static async Task Handle(
        ItemCreated created,
        SqlTransaction tx // the current transaction
    )
    {
        // Using some extension method helpers inside of Jasper here
        await tx.CreateCommand("insert into receiver.item_created (id, name) values (@id, @name)")
            .With("id", created.Id)
            .With("name", created.Name)
            .ExecuteNonQueryAsync();
    }
}

When you use this middleware, be sure to pull in the current SqlTransaction object as a parameter to your handler method.

"Outbox" Pattern Usage

See Jasper’s “Outbox” Pattern Support for more context around why you would care about the "outbox" pattern.

Jasper supports the "outbox" pattern with Sql Server connections. You can explicitly opt into this usage with code like this:

using (var conn = new SqlConnection(Servers.SqlServerConnectionString))
{
    await conn.OpenAsync();

    var tx = conn.BeginTransaction();

    // "context" is an IMessageContext object
    await context.EnlistInTransaction(tx);

    await action(context);

    tx.Commit();

    await context.SendAllQueuedOutgoingMessages();
}

If you use the [Transaction] middleware in a message handler, the middleware will take care of some of the repetitive mechanics for you. In the code below, the IMessageContext is enrolled in the current transaction before the action runs, and the outgoing messages are flushed into the outgoing sending queue after the action runs.

[Transactional]
public async Task<ItemCreatedEvent> Handle(CreateItemCommand command, SqlTransaction tx)
{
    var item = new Item {Name = command.Name};

    // persist the new Item with the
    // current transaction
    await persist(tx, item);

    return new ItemCreatedEvent {Item = item};
}

Message Persistence Schema

The message persistence requires and adds these tables to your schema:

1. jasper_incoming_envelopes - stores incoming and scheduled envelopes until they are successfully processed
2. jasper_outgoing_envelopes - stores outgoing envelopes until they are successfully sent through the transports
3. jasper_dead_letters - stores "dead letter" envelopes that could not be processed. See Dead Letter Envelopes for more information
4. EnvelopeIdList - table type that is used by some of the functions listed below

and also these functions that are all used by the durable messaging in its "message recovery" functionality:

1. uspDeleteIncomingEnvelopes
2. uspDeleteOutgoingEnvelopes
3. uspDiscardAndReassignOutgoing
4. uspMarkIncomingOwnership
5. uspMarkOutgoingOwnership

Managing the Sql Server Schema

In testing, you can build -- or rebuild -- the message storage in your system with a call to the RebuildMessageStorage() extension method off of either IWebHost or IJasperHost as shown below in a sample taken from xUnit integration testing with Jasper:

public class MyJasperAppFixture : IDisposable
{
    public MyJasperAppFixture()
    {
        Host = JasperHost.For<MyJasperApp>();

        // This extension method will blow away any existing
        // schema items for message persistence in your configured
        // database and then rebuilds the message persistence objects
        // before the *first* integration test runs
        Host.RebuildMessageStorage();
    }

    public IHost Host { get;  }

    public void Dispose()
    {
        Host?.Dispose();
    }


}

// An xUnit test fixture that uses our MyJasperAppFixture
public class IntegrationTester : IClassFixture<MyJasperAppFixture>
{
    private readonly MyJasperAppFixture _fixture;

    public IntegrationTester(MyJasperAppFixture fixture)
    {
        _fixture = fixture;
    }
}

See this GitHub issue for some utilities to better manage the database objects.