Demonstrates integration of the SQL Server transport with SQL persistence and an ADO.NET user data store using the outbox feature.
Prerequisites
Ensure an instance of SQL Server (Version 2016 or above for custom saga finders sample, or Version 2012 or above for other samples) is installed and accessible on localhost and port 1433. A Docker image can be used to accomplish this by running docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=yourStrong(!)Password' -p 1433:1433 -d mcr. in a terminal.
Alternatively, change the connection string to point to different SQL Server instance.
At startup each endpoint will create its required SQL assets including databases, tables, and schemas.
This sample uses the NsbSamplesSqlOutbox database.
The outbox feature ensures exactly-once delivery guarantees without requiring the Distributed Transaction Coordinator (DTC). To prevent warnings in the console, disable the DTC service. If not disabled, a DtcRunningWarning will appear when starting the sample.
Running the project
- Start the solution.
- The Sender console displays
Press.<enter> to send a message - Press enter to send a new message.
Verifying correct behavior
- The Receiver logs that an order was submitted.
- The Sender logs that the order was accepted.
- After a few seconds, the Receiver logs that the timeout message was received.
- Open SQL Server Management Studio and check the
NsbSamplesSqlOutboxdatabase:- One row should exist in the saga state table (
receiver.).OrderLifecycleSaga - One row should exist in the orders table (
receiver.).SubmittedOrder
- One row should exist in the saga state table (
Code walk-through
This sample includes three projects:
- Shared — Contains shared types such as message definitions.
- Sender — A console app that sends the initial
OrderSubmittedmessage and handles the follow-upOrderAccepted. - Receiver — A console app that handles incoming order messages.
Sender and Receiver use different schemas within the same database. The database includes business data, NServiceBus queue tables, and persistence tables.
Sender project
The Sender does not persist data. It simulates a front-end system that submits orders, which are passed to the back-end via the bus. It's configured to use:
- SQL Server transport
- SQL persistence
- Outbox support
//for local instance or SqlExpress
// string connectionString = @"Data Source=(localdb)\mssqllocaldb;Database=NsbSamplesSqlOutbox;Trusted_Connection=True;MultipleActiveResultSets=true";
var connectionString = @"Server=localhost,1433;Initial Catalog=NsbSamplesSqlOutbox;User Id=SA;Password=yourStrong(!)Password;Max Pool Size=100;Encrypt=false";
var transport = new SqlServerTransport(connectionString)
{
DefaultSchema = "sender",
TransportTransactionMode = TransportTransactionMode.ReceiveOnly
};
transport.SchemaAndCatalog.UseSchemaForQueue("error", "dbo");
transport.SchemaAndCatalog.UseSchemaForQueue("audit", "dbo");
endpointConfiguration.UseTransport(transport);
var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
persistence.ConnectionBuilder(
connectionBuilder: () => new SqlConnection(connectionString)
);
var dialect = persistence.SqlDialect<SqlDialect.MsSqlServer>();
dialect.Schema("sender");
persistence.TablePrefix("");
transport.Subscriptions.DisableCaching = true;
transport.Subscriptions.SubscriptionTableName = new SubscriptionTableName(
table: "Subscriptions",
schema: "dbo"
);
endpointConfiguration.EnableOutbox();
endpointConfiguration.UseSerialization<SystemJsonSerializer>();
Receiver project
The Receiver simulates a back-end system and is also configured with SQL Server transport, SQL persistence, and the outbox. It uses ADO.NET to store business data (orders).
var transport = new SqlServerTransport(connectionString)
{
DefaultSchema = "receiver",
TransportTransactionMode = TransportTransactionMode.ReceiveOnly
};
transport.SchemaAndCatalog.UseSchemaForQueue("error", "dbo");
transport.SchemaAndCatalog.UseSchemaForQueue("audit", "dbo");
var routing = endpointConfiguration.UseTransport(transport);
routing.UseSchemaForEndpoint("Samples.SqlOutbox.Sender", "sender");
var persistence = endpointConfiguration.UsePersistence<SqlPersistence>();
persistence.ConnectionBuilder(
connectionBuilder: () => new SqlConnection(connectionString)
);
var dialect = persistence.SqlDialect<SqlDialect.MsSqlServer>();
dialect.Schema("receiver");
persistence.TablePrefix("");
transport.Subscriptions.DisableCaching = true;
transport.Subscriptions.SubscriptionTableName = new SubscriptionTableName(
table: "Subscriptions",
schema: "dbo"
);
endpointConfiguration.EnableOutbox();
endpointConfiguration.UseSerialization<SystemJsonSerializer>();
When a message arrives:
- It's dequeued using a native SQL Server transaction.
- A separate Outbox SQL transaction begins
This transaction codes:
- Business data persistence:
var session = context.SynchronizedStorageSession.SqlPersistenceSession();
var sql = @"insert into receiver.SubmittedOrder
(Id, Value)
values (@Id, @Value)";
await using (var command = new SqlCommand(
cmdText: sql,
connection: (SqlConnection)session.Connection,
transaction: (SqlTransaction)session.Transaction))
{
var parameters = command.Parameters;
parameters.AddWithValue("Id", message.OrderId);
parameters.AddWithValue("Value", message.Value);
await command.ExecuteNonQueryAsync(context.CancellationToken);
}
OrderLifecycleSagasaga state persistence- Storing the reply and timeout messages in the outbox:
public Task Handle(OrderSubmitted message, IMessageHandlerContext context)
{
var orderTimeout = new OrderTimeout();
return RequestTimeout(context, TimeSpan.FromSeconds(5), orderTimeout);
}
Once the outbox transaction commits, both business data and outgoing messages are durably persisted. The outbox messages are then dispatched. The timeout message is stored in the NServiceBus timeout table and sent back to the saga after a 5-second delay.
For use with other ORMs, see Accessing the ambient database details.