Microsoft SQL Server¶

---

To use Microsoft SQL Server database integration, the NuGet package CrystalSharp.MsSql must be installed.

In order to integrate a database effectively, it is necessary to adhere to the following mandatory steps:

• Inject the IMsSqlEntityFrameworkCoreContext interface into the DbContext constructor.
• Override the method public virtual Task<int> SaveChangesAsync(CancellationToken cancellationToken = default) of the DbContext class.
• Call the SaveChanges method of the IMsSqlEntityFrameworkCoreContext interface.
• Register the implementation of DbContext.

Following is the code that illustrates how to perform the above steps:

public class AppDbContext : DbContext
{
    private readonly IMsSqlEntityFrameworkCoreContext _msSqlEfContext;
    public DbSet<Order> Order { get; set; }

    public AppDbContext()
    {
        //
    }

    public AppDbContext(DbContextOptions<AppDbContext> options, IMsSqlEntityFrameworkCoreContext msSqlEfContext)
        : base(options)
    {
        _msSqlEfContext = msSqlEfContext;
    }

    public override async Task<int> SaveChangesAsync(CancellationToken cancellationToken = default)
    {
        return await _msSqlEfContext.SaveChanges(this, cancellationToken).ConfigureAwait(false);
    }
}

In the above code snippet, the AppDbContext class overrides the SaveChangesAsync method of the DbContext class. Overriding the SaveChangesAsync method is a mandatory requirement. The constructor injects the IMsSqlEntityFrameworkCoreContext interface, which is necessary for saving changes in the database. Within the overridden SaveChangesAsync method, pay attention to the utilization of the await _msSqlEfContext.SaveChanges(this, cancellationToken) method. This method executes all the essential operations prior to saving and ultimately persists the data in the database.

Registration for the DbContext implementation is required. Following is the code that illustrates how to register AppDbContext in the Program.cs file:

MsSqlSettings msSqlSettings = new("CONNECTION-STRING");

CrystalSharpAdapter.New(builder.Services)
    .AddCqrs(typeof(PlaceOrderCommandHandler))
    .AddMsSql<AppDbContext>(msSqlSettings)
    .CreateResolver();

In the above code snippet, when initializing the Crystal Sharp framework, the MsSqlSettings class is instantiated with a connection string and then a call to an extension method AddMsSql<AppDbContext>(msSqlSettings) for the DbContext registration.

In order to save the data, call the overridden method SaveChangesAsync. Following is the code that illustrates the saving of data:

public class PlaceOrderCommandHandler : CommandHandler<PlaceOrderCommand, PlaceOrderResponse>
{
    private readonly AppDbContext _dbContext;

    public PlaceOrderCommandHandler(AppDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    public override async Task<CommandExecutionResult<PlaceOrderResponse>> Handle(PlaceOrderCommand request, CancellationToken cancellationToken = default)
    {
        Order order = Order.PlaceOrder(request.OrderCode, request.TotalPrice);

        await _dbContext.Order.AddAsync(order, cancellationToken).ConfigureAwait(false);
        await _dbContext.SaveChangesAsync(cancellationToken).ConfigureAwait(false);

        PlaceOrderResponse response = new() { OrderId = order.GlobalUId };

        return await Ok(response);
    }
}

In the above code snippet, AppDbContext is injected into the command handler constructor, and then in the Handle method of the command handler, a new order is created and saved. Here, notice the await _dbContext.SaveChangesAsync(cancellationToken) method; it is the same SaveChangesAsync of the DbContext class, which is overridden in the AppDbContext class.