Import a 20,000-item product feed in one call

Runs the sample. Generated for this page; it reads the connection string from the environment.

using ArxSamples.MSSQL;
using ArxSamples.MSSQL.Operations.CreateMultiple.RealCases;

// The connection string comes from the environment, never from the code (see "Configure the connection").
var connectionString = Environment.GetEnvironmentVariable("ARX_SAMPLES_SQLSERVER")
    ?? throw new InvalidOperationException("Set the ARX_SAMPLES_SQLSERVER environment variable to your SQL Server connection string.");

await new RealCase2_ImportAProductCatalog().RunAsync(new SampleContext(connectionString));

The sample, as it is in the repository.

using ARX.SDK;
using ARX.SDK.Models;
using ARX.SDK.Services;
using System.Text.Json;
using static ArxSamples.MSSQL.SampleOutput;

namespace ArxSamples.MSSQL.Operations.CreateMultiple.RealCases;

/// <summary>
/// A supplier's product feed of 20,000 items, imported in one call, in three scenarios: the categories first, the feed
/// itself (you don't split the list: ARX does, and keeps the call all or nothing), and next week's feed with a bad row,
/// which refuses the whole feed.
/// </summary>
public sealed class RealCase2_ImportAProductCatalog : ISample
{
    public string Name => "create-multiple-real-2-import-a-product-catalog";

    public string Title => "Import a 20,000-item product feed in one call";

    public async Task RunAsync(SampleContext dbContext)
    {
        var (beverages, condiments) = await Scenario1_TheCategoriesFirstAsync(dbContext);

        await Scenario2_TwentyThousandProductsInOneCallAsync(dbContext, beverages, condiments);

        await Scenario3_ABadRowRefusesTheWholeFeedAsync(dbContext);
    }

    /// <summary>1. The categories first, so the products can point at them.</summary>
    private static async Task<(ArxRecordId Beverages, ArxRecordId Condiments)> Scenario1_TheCategoriesFirstAsync(SampleContext dbContext)
    {
        Step("1. The categories first, so products can point at them");

        var beverages = new ArxEntity("product_category");
        beverages["name"] = "Beverages";

        var condiments = new ArxEntity("product_category");
        condiments["name"] = "Condiments";

        var ids = await dbContext.Service.CreateMultipleAsync(new List<ArxEntity> { beverages, condiments });
        Show($"Beverages {ids[0]}, Condiments {ids[1]}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT id, name
         * FROM product_category
         * WHERE name IN ('Beverages', 'Condiments')
         * ORDER BY id;
         * -- Expect: 2 rows.
         * ===========================================================
         */

        return (ids[0], ids[1]);
    }

    /// <summary>2. 20,000 products in one call, with JSON and delimited tag columns.</summary>
    private static async Task Scenario2_TwentyThousandProductsInOneCallAsync(SampleContext dbContext, ArxRecordId beverages, ArxRecordId condiments)
    {
        Step("2. 20,000 products in one CreateMultipleAsync");

        var products = new List<ArxEntity>();

        for (var i = 1; i <= 20_000; i++)
        {
            var product = new ArxEntity("product");
            product["sku"] = $"SKU-{i:D6}";
            product["name"] = $"Product {i}";
            product["category_id"] = new ArxEntityReference("product_category", i % 2 == 0 ? beverages : condiments);
            product["unit_price"] = 1.50m + i % 400 * 0.25m;
            product["weight_kg"] = 0.25 + i % 40 * 0.05;
            product["stock_quantity"] = i % 500;
            product["tags"] = i % 3 == 0 ? "imported,organic" : "imported,standard";
            product["attributes"] = new ArxJsonB { SerializedValue = JsonSerializer.Serialize(new { origin = i % 2 == 0 ? "PT" : "BR", shelfLifeDays = 180 }) };
            product["launched_on"] = new DateOnly(2024, 1, 1).AddDays(i % 700);
            products.Add(product);
        }

        var ids = await dbContext.Service.CreateMultipleAsync(products);
        Show($"stored {await dbContext.Direct.CountAsync("product"):N0} products, ids {ids[0]} to {ids[^1]}");

        var stored = await dbContext.Direct.TextRowAsync("product", ids[42].AsInt64(), "sku", "unit_price", "tags", "attributes");
        Show($"product 43: {stored["sku"]}, price {stored["unit_price"]}, tags {stored["tags"]}, attributes {stored["attributes"]}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*) AS products, min(sku) AS first_sku, max(sku) AS last_sku
         * FROM product
         * WHERE sku LIKE 'SKU-%';
         *
         * SELECT sku, unit_price, tags, attributes
         * FROM product
         * WHERE sku = 'SKU-000043';
         * -- Expect: 20000 products, SKU-000001 to SKU-020000; SKU-000043 has tags imported,standard.
         * ===========================================================
         */
    }

    /// <summary>3. Next week's feed has a negative price in its last item: nothing from it is stored.</summary>
    private static async Task Scenario3_ABadRowRefusesTheWholeFeedAsync(SampleContext dbContext)
    {
        Step("3. Next week's feed has a negative price in its last item: nothing from it is stored");

        var nextFeed = new List<ArxEntity>();

        for (var i = 1; i <= 5_000; i++)
        {
            var product = new ArxEntity("product");
            product["sku"] = $"NEXT-{i:D6}";
            product["name"] = $"New product {i}";
            product["unit_price"] = i == 5_000 ? -1.00m : 9.99m;
            nextFeed.Add(product);
        }

        try
        {
            await dbContext.Service.CreateMultipleAsync(nextFeed);
        }
        catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.CheckViolation)
        {
            ShowError(error);
            Show($"products in the table: still {await dbContext.Direct.CountAsync("product"):N0}");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM product
         * WHERE sku LIKE 'NEXT-%';
         * -- Expect: 0: one negative price refused the whole feed.
         * ===========================================================
         */
    }
}

  > 1. The categories first, so products can point at them
      Beverages 1, Condiments 2
  > 2. 20,000 products in one CreateMultipleAsync
      stored 20,000 products, ids 1 to 20000
      product 43: SKU-000043, price 12.25, tags imported,standard, attributes {"origin":"BR","shelfLifeDays":180}
  > 3. Next week's feed has a negative price in its last item: nothing from it is stored
      caught ArxDatabaseException: ARX3004 CheckViolation: CreateMultiple on 'product' violates check constraint 'product_unit_price_check'.
      products in the table: still 20,000