Microsoft SQL Server¶

---

To use Microsoft SQL Server - Read Model Store integration, the NuGet package CrystalSharp.MsSql must be installed.

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

• Register the implementation of the read model store with DbContext.
• Use the IReadModelStore interface for read model store operations.

Read Model Store Registration¶

Following is the code that illustrates the DbContext class and how to register the implementation of the read model store with DbContext in the Program.cs file:

“CustomerOrderReadModel.cs” class:

public class CustomerOrderReadModel : ReadModel<int>
{
    public string OrderCode { get; private set; }
    public decimal TotalPrice { get; private set; }
    public string Name { get; private set; }
    public string Address { get; private set; }

    public static CustomerOrderReadModel Create(Guid globalUId,
        string orderCode,
        decimal totalPrice,
        string name,
        string address)
    {
        return new CustomerOrderReadModel
        {
            GlobalUId = globalUId,
            OrderCode = orderCode,
            TotalPrice = totalPrice,
            Name = name,
            Address = address
        };
    }
}

“AppReadModelStoreDbContext.cs” class:

public class AppReadModelStoreDbContext : DbContext
{
    public DbSet<CustomerOrderReadModel> CustomerOrderReadModel { get; set; }

    public AppReadModelStoreDbContext(DbContextOptions<AppReadModelStoreDbContext> options)
    {
        //
    }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);
    }
}

“Program.cs” file:

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

CrystalSharpAdapter.New(builder.Services)
    .AddCqrs(typeof(PlaceOrderCommandHandler))
    .AddMsSqlReadModelStore<AppReadModelStoreDbContext, int>(msSqlReadModelStoreSettings)
    .CreateResolver();

In the above code snippet, when initializing the Crystal Sharp framework, a call to an extension method is made AddMsSqlReadModelStore<AppReadModelStoreDbContext, int>(msSqlReadModelStoreSettings) for the read model store registration.

Here, pay attention to <AppReadModelStoreDbContext, int>. AppReadModelStoreDbContext is the DbContext class that will be utilized in the read model store, and int is the data type of the Id property, which acts as a primary key in all the read models. It is mandatory that all the read models have the same data type for the Id property, which is the primary key.

Store Read Model¶

To store read models in the read model store, the IReadModelStore interface is used. The following code illustrates how to create a read model from multiple tables:

public class PlaceOrderCommandHandler : CommandHandler<PlaceOrderCommand, PlaceOrderResponse>
{
    private readonly OrderDbContext _dbContext;
    private readonly IReadModelStore<int> _readModelStore;

    public PlaceOrderCommandHandler(OrderDbContext dbContext, IReadModelStore<int> readModelStore)
    {
        _dbContext = dbContext;
        _readModelStore = readModelStore;
    }

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

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

        Customer customer = await _dbContext.Customers.SingleOrDefaultAsync(x => x.CustomerId == order.CustomerId, cancellationToken).ConfigureAwait(false);

        CustomerOrderReadModel customerOrder = CustomerOrderReadModel.Create(order.GlobalUId,
            order.OrderCode,
            order.TotalPrice,
            customer.Name,
            customer.Address);

        await _readModelStore.Store(customerOrder, cancellationToken).ConfigureAwait(false);

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

        return await Ok(response);
    }
}

In the above code snippet, the OrderDbContext class and IReadModelStore<int> interface are injected into the PlaceOrderCommandHandler constructor. In the Handle method, a new order is placed and stored in the database. After storing the data in the database, a read model is generated from the Orders and Customers tables and stored in the read model store, and later it can be queried from a single source without additional joins.

Here, pay attention to _readModelStore.Store(customerOrder, cancellationToken), as this method will store the data in the read model store.

Retrieve the Data from Read Model Store¶

Following is the code that illustrates how to retrieve the data from read model store:

“OrderDetailsQuery.cs” class:

public class OrderDetailsQuery : IQuery<QueryExecutionResult<CustomerOrderReadModel>>
{
    public int Id { get; set; }
}

“OrderDetailsQueryHandler.cs” class:

public class OrderDetailsQueryHandler : QueryHandler<OrderDetailsQuery, CustomerOrderReadModel>
{
    private readonly IReadModelStore<int> _readModelStore;

    public OrderDetailsQueryHandler(IReadModelStore<int> readModelStore)
    {
        _readModelStore = readModelStore;
    }

    public override async Task<QueryExecutionResult<CustomerOrderReadModel>> Handle(OrderDetailsQuery request, CancellationToken cancellationToken = default)
    {
        CustomerOrderReadModel customerOrder = _readModelStore.Find<CustomerOrderReadModel>(request.Id, cancellationToken).ConfigureAwait(false);

        return await Ok(customerOrder);
    }
}

In the above code snippet, the IReadModelStore<int> interface has been injected into the OrderDetailsQueryHandler constructor, and then in the Handle method, the read model store has been used to retrieve the data _readModelStore.Find<CustomerOrderReadModel>(request.Id, cancellationToken).ConfigureAwait(false).