.NET 中的事务消息传递：将 Brighter 的出站箱模式与 SQL Server 和 RabbitMQ 集成

简介  

在上一篇文章中，我们探讨了出站箱模式(Outbox Pattern)及其通用配置方法。本次，我们将深入研究如何在 SQL Server 中实现该模式，以确保数据库更新与消息发布之间的事务一致性。

项目目标  

本项目的核心逻辑是：发送一个创建订单的命令，当订单创建时发布两条消息 OrderPlaced 和 OrderPaid。如果发生故障，则不应发布任何消息。

要求

• .NET 8+ 
• Podman(或 Docker)运行本地容器：  
  • SQL Server  
  • RabbitMQ  
• Brighter 与 RabbitMQ 的基础
• NuGet 包 
  • Paramore.Brighter.Extensions.DependencyInjection  
  • Paramore.Brighter.Extensions.Hosting  
  • Paramore.Brighter.MessagingGateway.RMQ  
  • Paramore.Brighter.Outbox.MsSql
  • Paramore.Brighter.ServiceActivator.Extensions.DependencyInjection
  • Paramore.Brighter.ServiceActivator.Extensions.Hosting

消息定义  

项目需使用以下三条消息：CreateNewOrder, OrderPlaced 和 OrderPaid

public class CreateNewOrder() : Command(Guid.NewGuid())
{
    public decimal Value { get; set; }
}

public class OrderPlaced() : Event(Guid.NewGuid())
{
    public string OrderId { get; set; } = string.Empty;
    public decimal Value { get; set; }
}

public class OrderPaid() : Event(Guid.NewGuid())
{
    public string OrderId { get; set; } = string.Empty;
}

消息映射器  

由于仅 OrderPlaced 和 OrderPaid 事件需发布到 RabbitMQ，需实现 JSON 序列化映射器：

public class OrderPlacedMapper : IAmAMessageMapper<OrderPlaced>
{
    public Message MapToMessage(OrderPlaced request)
    {
        var header = new MessageHeader();
        header.Id = request.Id;
        header.TimeStamp = DateTime.UtcNow;
        header.Topic = "order-placed";
        header.MessageType = MessageType.MT_EVENT;
        var body = new MessageBody(JsonSerializer.Serialize(request));
        return new Message(header, body);
    }
    public OrderPlaced MapToRequest(Message message)
    {
        return JsonSerializer.Deserialize<OrderPlaced>(message.Body.Bytes)!;
    }
}

public class OrderPaidMapper : IAmAMessageMapper<OrderPaid>
{
    public Message MapToMessage(OrderPaid request)
    {
        var header = new MessageHeader();
        header.Id = request.Id;
        header.TimeStamp = DateTime.UtcNow;
        header.Topic = "order-paid";
        header.MessageType = MessageType.MT_EVENT;
        var body = new MessageBody(JsonSerializer.Serialize(request));
        return new Message(header, body);
    }
    public OrderPaid MapToRequest(Message message)
    {
        return JsonSerializer.Deserialize<OrderPaid>(message.Body.Bytes)!;
    }
}

请求处理器  

处理 OrderPlaced 和 OrderPaid  

记录接收到的消息：  

public class OrderPlaceHandler(ILogger<OrderPlaceHandler> logger) : RequestHandlerAsync<OrderPlaced>
{
    public override Task<OrderPlaced> HandleAsync(OrderPlaced command, CancellationToken cancellationToken = default)
    {
        logger.LogInformation("{OrderId} placed with value {OrderValue}", command.OrderId, command.Value);
        return base.HandleAsync(command, cancellationToken);
    }
}

public class OrderPaidHandler(ILogger<OrderPaidHandler> logger) : RequestHandlerAsync<OrderPaid>
{
    public override Task<OrderPaid> HandleAsync(OrderPaid command, CancellationToken cancellationToken = default)
    {
        logger.LogInformation("{OrderId} paid", command.OrderId);
        return base.HandleAsync(command, cancellationToken);
    }
}

创建新订单处理器  

模拟业务逻辑：延迟 10ms 后发布 OrderPlaced，若金额可被 3 整除则抛出异常，否则发布 OrderPaid。  

public class CreateNewOrderHandler(IAmACommandProcessor commandProcessor,
    IUnitOfWork unitOfWork,
    ILogger<CreateNewOrderHandler> logger) : RequestHandlerAsync<CreateNewOrder>
{
    public override async Task<CreateNewOrder> HandleAsync(CreateNewOrder command, CancellationToken cancellationToken = default)
    {
        await unitOfWork.BeginTransactionAsync(cancellationToken);
        try
        {
            string id = Guid.NewGuid().ToString();
            logger.LogInformation("Creating a new order: {OrderId}", id);
            await Task.Delay(10, cancellationToken); // 模拟处理
            _ = await commandProcessor.DepositPostAsync(new OrderPlaced { OrderId = id, Value = command.Value }, cancellationToken: cancellationToken);
            if (command.Value % 3 == 0)
            {
                throw new InvalidOperationException("invalid value");
            }
            _ = await commandProcessor.DepositPostAsync(new OrderPaid { OrderId = id }, cancellationToken: cancellationToken);
            await unitOfWork.CommitAsync(cancellationToken);
            return await base.HandleAsync(command, cancellationToken);
        }
        catch
        {
            logger.LogError("Invalid data");
            await unitOfWork.RollbackAsync(cancellationToken);
            throw;
        }
    }
}

关键点:

• IUnitOfWork 共享 Brighter 的 SQL 事务，确保原子性（订单持久化 + 出站消息写入）。  
• 仅当事务提交时才会发布事件。

配置 SQL Server  

1. SQL 表结构  

确保存在 OutboxMessages 表:

IF OBJECT_ID('OutboxMessages', 'U') IS NULL
BEGIN 
  CREATE TABLE [OutboxMessages]
  (
    [Id] [BIGINT] NOT NULL IDENTITY,
    [MessageId] UNIQUEIDENTIFIER NOT NULL,
    [Topic] NVARCHAR(255) NULL,
    [MessageType] NVARCHAR(32) NULL,
    [Timestamp] DATETIME NULL,
    [CorrelationId] UNIQUEIDENTIFIER NULL,
    [ReplyTo] NVARCHAR(255) NULL,
    [ContentType] NVARCHAR(128) NULL,  
    [Dispatched] DATETIME NULL,
    [HeaderBag] NTEXT NULL ,
    [Body] NTEXT NULL,
    PRIMARY KEY ( [Id] ) 
  );
END

2. 依赖注入配置  

注册出站箱与事务：  

services
    .AddServiceActivator(opt => { /* 订阅配置（见前文） */ })
    .UseMsSqlOutbox(new MsSqlConfiguration(ConnectionString, "OutboxMessages"), typeof(SqlConnectionProvider), ServiceLifetime.Scoped)            
    .UseMsSqlTransactionConnectionProvider(typeof(SqlConnectionProvider))
    .UseOutboxSweeper(opt => opt.BatchSize = 10);

原理：  

• UseMsSqlOutbox 将出站箱绑定到 SQL Server。  
• UseOutboxSweeper 配置后台轮询未发送消息。  

3. 事务管理  

通过 IMsSqlTransactionConnectionProvider 和 `IUnitOfWork` 实现共享事务上下文，确保业务逻辑与消息发布原子性。  

a. SqlConnectionProvider  

public class SqlConnectionProvider(SqlUnitOfWork sqlConnection) : IMsSqlTransactionConnectionProvider
{
    private readonly SqlUnitOfWork _sqlConnection = sqlConnection;
    public SqlConnection GetConnection() => _sqlConnection.Connection;
    public Task<SqlConnection> GetConnectionAsync(CancellationToken cancellationToken = default) => Task.FromResult(_sqlConnection.Connection);
    public SqlTransaction? GetTransaction() => _sqlConnection.Transaction;
    public bool HasOpenTransaction => _sqlConnection.Transaction != null;
    public bool IsSharedConnection => true;
}

b. UnitOfWork 接口  

public interface IUnitOfWork
{
    Task BeginTransactionAsync(CancellationToken cancellationToken, IsolationLevel isolationLevel = IsolationLevel.Serializable);
    Task CommitAsync(CancellationToken cancellationToken);
    Task RollbackAsync(CancellationToken cancellationToken);
}

c. SqlUnitOfWork 实现  

public class SqlUnitOfWork(MsSqlConfiguration configuration) : IUnitOfWork
{
    public SqlConnection Connection { get; } = new(configuration.ConnectionString);
    public SqlTransaction? Transaction { get; private set; }

    public async Task BeginTransactionAsync(CancellationToken cancellationToken, IsolationLevel isolationLevel = IsolationLevel.Serializable)
    {
        if (Transaction == null)
        {
            if (Connection.State != ConnectionState.Open)
            {
                await Connection.OpenAsync(cancellationToken);
            }
            Transaction = Connection.BeginTransaction(isolationLevel);
        }
    }

    public async Task CommitAsync(CancellationToken cancellationToken)
    {
        if (Transaction != null) await Transaction.CommitAsync(cancellationToken);
    }

    public async Task RollbackAsync(CancellationToken cancellationToken)
    {
        if (Transaction != null) await Transaction.RollbackAsync(cancellationToken);
    }

    public async Task<SqlCommand> CreateSqlCommandAsync(string sql, SqlParameter[] parameters, CancellationToken cancellationToken)
    {
        if (Connection.State != ConnectionState.Open) await Connection.OpenAsync(cancellationToken);
        SqlCommand command = Connection.CreateCommand();
        if (Transaction != null) command.Transaction = Transaction;
        command.CommandText = sql;
        if (parameters.Length > 0) command.Parameters.AddRange(parameters);
        return command;
    }
}

d. 注册依赖注入服务  

services
    .AddScoped<SqlUnitOfWork, SqlUnitOfWork>()
    .TryAddScoped<IUnitOfWork>(provider => provider.GetRequiredService<SqlUnitOfWork>());

结论  

通过在 Brighter 和 SQL Server 中实现出站箱模式，我们实现了以下目标：  

1. 事务一致性 
   1. 使用 DepositPostAsync，消息（如 OrderPlaced 和 OrderPaid）与业务数据在同一事务中写入 OutboxMessages 表。若处理器失败（如模拟错误），事务回滚且不发送消息。  
   2. Brighter 的 IMsSqlTransactionConnectionProvider 确保数据库更新与消息写入共享同一事务。  
2. 容错机制：出站箱清理器(Outbox Sweeper)  
   1.  UseOutboxSweeper 轮询未发送消息并重试，直至 RabbitMQ 确认接收。此机制解耦消息发布与处理器执行，确保可靠性。  
3. 解耦架构
   1. 应用聚焦于本地事务，Brighter 异步处理消息传递。避免与消息中间件紧耦合，简化扩展性。  

此实现展示了 Brighter 如何抽象复杂性，使开发者专注业务逻辑，同时保障分布式系统的可靠性。生产环境中建议结合监控工具(如 Prometheus)、死信队列(DLQ)处理中毒消息，并在出站表的 Dispatched 和 Timestamp 列添加索引。

参考  

完整代码仓库(GitHub)

using System.Data;
using System.Text.Json;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.DependencyInjection.Extensions;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using Paramore.Brighter;
using Paramore.Brighter.Extensions.DependencyInjection;
using Paramore.Brighter.Extensions.Hosting;
using Paramore.Brighter.MessagingGateway.RMQ;
using Paramore.Brighter.MsSql;
using Paramore.Brighter.Outbox.MsSql;
using Paramore.Brighter.ServiceActivator.Extensions.DependencyInjection;
using Paramore.Brighter.ServiceActivator.Extensions.Hosting;
using Serilog;

const string ConnectionString = "Server=127.0.0.1,1433;Database=BrighterTests;User Id=sa;Password=Password123!;Application Name=BrighterTests;Connect Timeout=60;Encrypt=false;";

using (SqlConnection connection = new("Server=127.0.0.1,1433;Database=master;User Id=sa;Password=Password123!;Application Name=BrighterTests;Connect Timeout=60;Encrypt=false;"))
{
    await connection.OpenAsync();

    using SqlCommand command = connection.CreateCommand();
    command.CommandText =
      """
      IF DB_ID('BrighterTests') IS NULL
      BEGIN
        CREATE DATABASE BrighterTests;
      END;
      """;
    _ = await command.ExecuteNonQueryAsync();

}

using (SqlConnection connection = new(ConnectionString))
{
    await connection.OpenAsync();

    using SqlCommand command = connection.CreateCommand();
    command.CommandText =
      """
      IF OBJECT_ID('OutboxMessages', 'U') IS NULL
      BEGIN 
        CREATE TABLE [OutboxMessages]
        (
          [Id] [BIGINT] NOT NULL IDENTITY ,
          [MessageId] UNIQUEIDENTIFIER NOT NULL ,
          [Topic] NVARCHAR(255) NULL ,

          [MessageType] NVARCHAR(32) NULL ,
          [Timestamp] DATETIME NULL ,
          [CorrelationId] UNIQUEIDENTIFIER NULL,

          [ReplyTo] NVARCHAR(255) NULL,
          [ContentType] NVARCHAR(128) NULL,  
          [Dispatched] DATETIME NULL,
          [HeaderBag] NTEXT NULL ,
          [Body] NTEXT NULL ,
          PRIMARY KEY ( [Id] ) 
        );
      END 
      """;
    try
    {
        _ = await command.ExecuteNonQueryAsync();
    }
    catch
    {
        // Ignore if it exists
    }
}

Log.Logger = new LoggerConfiguration()
    .MinimumLevel.Information()
    .MinimumLevel.Override("Paramore.Brighter", Serilog.Events.LogEventLevel.Warning)
    .Enrich.FromLogContext()
    .WriteTo.Console()
    .CreateLogger();

IHost host = new HostBuilder()
    .UseSerilog()
    .ConfigureServices(
        (ctx, services) =>
        {
            RmqMessagingGatewayConnection connection = new()
            {
                AmpqUri = new AmqpUriSpecification(new Uri("amqp://guest:guest@localhost:5672")),

                Exchange = new Exchange("paramore.brighter.exchange"),
            };

            services
              .AddScoped<SqlUnitOfWork, SqlUnitOfWork>()
              .TryAddScoped<IUnitOfWork>(provider => provider.GetRequiredService<SqlUnitOfWork>());

            _ = services
                .AddHostedService<ServiceActivatorHostedService>()
                .AddServiceActivator(opt =>
                {
                    opt.Subscriptions =
                    [
                        new RmqSubscription<OrderPlaced>(
                            new SubscriptionName("subscription"),
                            new ChannelName("queue-order-placed"),
                            new RoutingKey("order-placed"),
                            makeChannels: OnMissingChannel.Create,
                            runAsync: true
                        ),

                        new RmqSubscription<OrderPaid>(
                            new SubscriptionName("subscription"),
                            new ChannelName("queue-order-paid"),
                            new RoutingKey("order-paid"),
                            makeChannels: OnMissingChannel.Create,
                            runAsync: true
                        ),
                    ];

                    opt.ChannelFactory = new ChannelFactory(
                        new RmqMessageConsumerFactory(connection)
                    );
                })
                .AutoFromAssemblies()
                .UseMsSqlOutbox(new MsSqlConfiguration(ConnectionString, "OutboxMessages"), typeof(SqlConnectionProvider), ServiceLifetime.Scoped)
                .UseMsSqlTransactionConnectionProvider(typeof(SqlConnectionProvider))
                .UseOutboxSweeper(opt =>
                {
                    opt.BatchSize = 10;
                })
                .UseExternalBus(
                    new RmqProducerRegistryFactory(
                        connection,
                        [
                            new RmqPublication
                            {
                                MakeChannels = OnMissingChannel.Create,
                                Topic = new RoutingKey("order-paid"),
                            },
                            new RmqPublication
                            {
                                MakeChannels = OnMissingChannel.Create,
                                Topic = new RoutingKey("order-placed"),
                            },
                        ]
                    ).Create()
                );

        }
    )
    .Build();


await host.StartAsync();

CancellationTokenSource cancellationTokenSource = new();

Console.CancelKeyPress += (_, _) => cancellationTokenSource.Cancel();

while (!cancellationTokenSource.IsCancellationRequested)
{
    await Task.Delay(TimeSpan.FromSeconds(10));
    Console.Write("Type an order value (or q to quit): ");
    string? tmp = Console.ReadLine();

    if (string.IsNullOrEmpty(tmp))
    {
        continue;
    }

    if (tmp == "q")
    {
        break;
    }

    if (!decimal.TryParse(tmp, out decimal value))
    {
        continue;
    }

    try
    {
        using IServiceScope scope = host.Services.CreateScope();
        IAmACommandProcessor process = scope.ServiceProvider.GetRequiredService<IAmACommandProcessor>();
        await process.SendAsync(new CreateNewOrder { Value = value });
    }
    catch
    {
        // ignore any error
    }
}

await host.StopAsync();

public class CreateNewOrder() : Command(Guid.NewGuid())
{
    public decimal Value { get; set; }
}

public class OrderPlaced() : Event(Guid.NewGuid())
{
    public string OrderId { get; set; } = string.Empty;
    public decimal Value { get; set; }
}


public class OrderPaid() : Event(Guid.NewGuid())
{
    public string OrderId { get; set; } = string.Empty;
}

public class CreateNewOrderHandler(IAmACommandProcessor commandProcessor,
    IUnitOfWork unitOfWork,
    ILogger<CreateNewOrderHandler> logger) : RequestHandlerAsync<CreateNewOrder>
{
    public override async Task<CreateNewOrder> HandleAsync(CreateNewOrder command, CancellationToken cancellationToken = default)
    {
        await unitOfWork.BeginTransactionAsync(cancellationToken);
        try
        {
            string id = Guid.NewGuid().ToString();
            logger.LogInformation("Creating a new order: {OrderId}", id);

            await Task.Delay(10, cancellationToken); // emulating an process

            _ = await commandProcessor.DepositPostAsync(new OrderPlaced { OrderId = id, Value = command.Value }, cancellationToken: cancellationToken);
            if (command.Value % 3 == 0)
            {
                throw new InvalidOperationException("invalid value");
            }

            _ = await commandProcessor.DepositPostAsync(new OrderPaid { OrderId = id }, cancellationToken: cancellationToken);

            await unitOfWork.CommitAsync(cancellationToken);
            return await base.HandleAsync(command, cancellationToken);
        }
        catch
        {
            logger.LogError("Invalid data");
            await unitOfWork.RollbackAsync(cancellationToken);
            throw;
        }
    }
}

public class OrderPlaceHandler(ILogger<OrderPlaceHandler> logger) : RequestHandlerAsync<OrderPlaced>
{
    public override Task<OrderPlaced> HandleAsync(OrderPlaced command, CancellationToken cancellationToken = default)
    {
        logger.LogInformation("{OrderId} placed with value {OrderValue}", command.OrderId, command.Value);
        return base.HandleAsync(command, cancellationToken);
    }
}

public class OrderPaidHandler(ILogger<OrderPaidHandler> logger) : RequestHandlerAsync<OrderPaid>
{
    public override Task<OrderPaid> HandleAsync(OrderPaid command, CancellationToken cancellationToken = default)
    {
        logger.LogInformation("{OrderId} paid", command.OrderId);
        return base.HandleAsync(command, cancellationToken);
    }
}

public class OrderPlacedMapper : IAmAMessageMapper<OrderPlaced>
{
    public Message MapToMessage(OrderPlaced request)
    {
        var header = new MessageHeader();
        header.Id = request.Id;
        header.TimeStamp = DateTime.UtcNow;
        header.Topic = "order-placed";
        header.MessageType = MessageType.MT_EVENT;

        var body = new MessageBody(JsonSerializer.Serialize(request));
        return new Message(header, body);
    }

    public OrderPlaced MapToRequest(Message message)
    {
        return JsonSerializer.Deserialize<OrderPlaced>(message.Body.Bytes)!;
    }
}

public class OrderPaidMapper : IAmAMessageMapper<OrderPaid>
{
    public Message MapToMessage(OrderPaid request)
    {
        var header = new MessageHeader();
        header.Id = request.Id;
        header.TimeStamp = DateTime.UtcNow;
        header.Topic = "order-paid";
        header.MessageType = MessageType.MT_EVENT;

        var body = new MessageBody(JsonSerializer.Serialize(request));
        return new Message(header, body);
    }

    public OrderPaid MapToRequest(Message message)
    {
        return JsonSerializer.Deserialize<OrderPaid>(message.Body.Bytes)!;
    }
}

public class SqlConnectionProvider(SqlUnitOfWork sqlConnection) : IMsSqlTransactionConnectionProvider
{
    private readonly SqlUnitOfWork _sqlConnection = sqlConnection;

    public SqlConnection GetConnection()
    {
        return _sqlConnection.Connection;
    }

    public Task<SqlConnection> GetConnectionAsync(CancellationToken cancellationToken = default)
    {
        return Task.FromResult(_sqlConnection.Connection);
    }

    public SqlTransaction? GetTransaction()
    {
        return _sqlConnection.Transaction;
    }

    public bool HasOpenTransaction => _sqlConnection.Transaction != null;
    public bool IsSharedConnection => true;
}

public interface IUnitOfWork
{
    Task BeginTransactionAsync(CancellationToken cancellationToken, IsolationLevel isolationLevel = IsolationLevel.Serializable);
    Task CommitAsync(CancellationToken cancellationToken);
    Task RollbackAsync(CancellationToken cancellationToken);
}

public class SqlUnitOfWork(MsSqlConfiguration configuration) : IUnitOfWork
{
    public SqlConnection Connection { get; } = new(configuration.ConnectionString);
    public SqlTransaction? Transaction { get; private set; }

    public async Task BeginTransactionAsync(CancellationToken cancellationToken,
        IsolationLevel isolationLevel = IsolationLevel.Serializable)
    {

        if (Transaction == null)
        {
            if (Connection.State != ConnectionState.Open)
            {
                await Connection.OpenAsync(cancellationToken);
            }

            Transaction = Connection.BeginTransaction(isolationLevel);
        }
    }

    public async Task CommitAsync(CancellationToken cancellationToken)
    {
        if (Transaction != null)
        {
            await Transaction.CommitAsync(cancellationToken);
        }
    }

    public async Task RollbackAsync(CancellationToken cancellationToken)
    {
        if (Transaction != null)
        {
            await Transaction.RollbackAsync(cancellationToken);
        }
    }

    public async Task<SqlCommand> CreateSqlCommandAsync(string sql, SqlParameter[] parameters, CancellationToken cancellationToken)
    {
        if (Connection.State != ConnectionState.Open)
        {
            await Connection.OpenAsync(cancellationToken);
        }

        SqlCommand command = Connection.CreateCommand();

        if (Transaction != null)
        {
            command.Transaction = Transaction;
        }

        command.CommandText = sql;
        if (parameters.Length > 0)
        {
            command.Parameters.AddRange(parameters);
        }

        return command;
    }
}