# UpdateMultiple with ARX.SDK on SQL Server

## What an UpdateMultiple does

| | |
|---|---|
| **Many rows** | Rows with the same attributes and value types go in one statement (`UPDATE … FROM … JOIN` the new values), wherever they are in the list: 20 or more as one JSON parameter read by `OPENJSON`, smaller groups as a list of values. The checks run in the same round trip, and a failed one undoes the whole call |
| **Without a catalog** | The table is read once for the whole list, then every row is changed, all or nothing (Basic2) |
| **By a filter** | One entity with the values to set and an `ArxFilterExpression`: one `UPDATE … SET … FROM … WHERE …` for every row the filter matches, returning the count (Basic3). The entity carries no id (ARX1001); an empty filter needs `AllRows`; a version column goes up by one on each changed row. From 0.1.0-preview.5 |

## The samples

Every UpdateMultiple sample, in reading order. Each scenario shows the code (verified against SQL Server) and the SQL that checks what it did. `service` is the application's `ArxMsSqlDbService`; values marked "In scope" come from earlier in the sample. What the samples' code refers to beyond the packages (SampleCatalog.cs, `dbContext`) is in the sample application file, arx-sql-sample-application.md.

### Update many records in one call

Basics 1 of UpdateMultiple.

Many records in one call, in three scenarios: a different value for each row, rows that change different attributes, and all or nothing when one record doesn't exist.

Usings:

```csharp
using ARX.SDK;
using ARX.SDK.SQL.Services;
using ARX.SDK.Services;
```

#### 1. A value per row

The usual way: one entity per record, each with its own value. The call returns the rows changed.

In scope: `connectionString` (string).

```csharp
var service = Service(connectionString);
var ids = await CreateAccountsAsync(service, 1, 5);

var changes = new List<ArxEntity>();

for (var i = 0; i < ids.Count; i++)
{
    var change = new ArxEntity("account");
    change.RecordId = ids[i];
    change["employee_count"] = 100 + i;
    changes.Add(change);
}

int changed = await service.UpdateMultipleAsync(changes);

Console.WriteLine($"{changed} accounts changed in one call");
```

Defined in the sample, used above:

```csharp
private static ArxMsSqlDbService Service(string connectionString) => new ArxMsSqlDbService(connectionString, ArxSchemaCatalog.Create()
    .Table("account", ArxIdColumn.Generated<long>("id"))
    .Build());

private static async Task<IReadOnlyList<ArxRecordId>> CreateAccountsAsync(ArxMsSqlDbService service, int first, int count)
{
    var accounts = new List<ArxEntity>();

    for (var i = 0; i < count; i++)
    {
        var account = new ArxEntity("account");
        account["name"] = $"Basic update {first + i}";
        account["account_number"] = $"ACC-U2{first + i:00}";
        account["employee_count"] = 10;
        accounts.Add(account);
    }

    return await service.CreateMultipleAsync(accounts);
}
```

Check it in the database:

```sql
SELECT account_number, employee_count
FROM account
WHERE account_number BETWEEN 'ACC-U201' AND 'ACC-U205'
ORDER BY account_number;
```

Expect: 5 rows, employee_count 100, 101, 102, 103, 104.

#### 2. Different attributes

Rows don't have to change the same attributes: each one writes only what it sets.

In scope: `connectionString` (string).

```csharp
var service = Service(connectionString);
var ids = await CreateAccountsAsync(service, 11, 2);

var renamed = new ArxEntity("account");
renamed.RecordId = ids[0];
renamed["name"] = "Basic update 11 (renamed)";

var resized = new ArxEntity("account");
resized.RecordId = ids[1];
resized["employee_count"] = 999;

await service.UpdateMultipleAsync(new[] { renamed, resized });

Console.WriteLine("one renamed, one resized");
```

Check it in the database:

```sql
SELECT account_number, name, employee_count
FROM account
WHERE account_number IN ('ACC-U211', 'ACC-U212')
ORDER BY account_number;
```

Expect: ACC-U211 renamed with employee_count 10; ACC-U212 still named Basic update 12, employee_count 999.

#### 3. All or nothing

All or nothing: if one record doesn't exist, no record changes, and the error says how many were missing.

In scope: `connectionString` (string).

```csharp
var service = Service(connectionString);
var ids = await CreateAccountsAsync(service, 21, 3);

var changes = ids.Select(id =>
{
    var change = new ArxEntity("account");
    change.RecordId = id;
    change["employee_count"] = 7;
    return change;
}).ToList();

// An id no account has: maybe the account was deleted in the meantime.
var missing = new ArxEntity("account");
missing["id"] = 999_999_999L;
missing["employee_count"] = 7;
changes.Add(missing);

try
{
    await service.UpdateMultipleAsync(changes);
}
catch (ArxException error) when (error.Code == ArxErrorCode.RecordNotFound)
{
    Console.WriteLine($"{error.Message}");
}
```

Check it in the database:

```sql
SELECT count(*)
FROM account
WHERE account_number BETWEEN 'ACC-U221' AND 'ACC-U223' AND employee_count = 7;
```

Expect: 0: the three accounts that exist were not changed either.

### Update many records without a catalog

Basics 2 of UpdateMultiple.

Many records in one call on a service created with only a connection string: the account table is read from the database once for the whole list, then every row is changed, all or nothing.

Good to know:

The extra round trip is paid once per call, not once per row; still, a service with a catalog never pays it.

Usings:

```csharp
using ARX.SDK.SQL.Services;
using ARX.SDK.Services;
```

#### 1. A value per row

One entity per record, by the ids CreateMultiple returned.

In scope: `connectionString` (string).

```csharp
var service = new ArxMsSqlDbService(connectionString);

var accounts = new List<ArxEntity>();
for (var i = 1; i <= 3; i++)
{
    var account = new ArxEntity("account");
    account["name"] = $"No catalog {i}";
    account["account_number"] = $"ACC-N03{i}";
    account["employee_count"] = 10;
    accounts.Add(account);
}

var ids = await service.CreateMultipleAsync(accounts);

var changes = new List<ArxEntity>();
for (var i = 0; i < ids.Count; i++)
{
    var change = new ArxEntity("account");
    change.RecordId = ids[i];
    change["employee_count"] = 20 + i;
    changes.Add(change);
}

int changed = await service.UpdateMultipleAsync(changes);

Console.WriteLine($"{changed} accounts changed in one call");
```

Check it in the database:

```sql
SELECT account_number, employee_count
FROM account
WHERE account_number IN ('ACC-N031', 'ACC-N032', 'ACC-N033')
ORDER BY account_number;
```

Expect: 3 rows, employee_count 20, 21, 22.

### Update every row a filter matches

Basics 3 of UpdateMultiple.

Every row a condition matches, changed in one call: the entity carries only the values to set, and a filter says which rows. The call returns how many rows it changed.

Good to know:

The filter takes the operators of a query, Or groups included, on the table's own columns; its values are parameters.

An empty filter is refused, so a missing condition never changes the whole table: pass new ArxFilterWriteOptions { AllRows = true } when every row is meant.

Needs ARX.SDK 0.1.0-preview.5 or later.

Usings:

```csharp
using ARX.SDK;
using ARX.SDK.Query;
using ARX.SDK.SQL.Services;
using ARX.SDK.Services;
```

#### 1. Every row the filter matches

Small accounts put on hold: one UPDATE for every row the filter matches, however many there are.

In scope: `connectionString` (string).

```csharp
var service = Service(connectionString);
var accounts = new List<ArxEntity>();

for (var i = 1; i <= 5; i++)
{
    var account = new ArxEntity("account");
    account["name"] = $"Filter update {i}";
    account["account_number"] = $"ACC-F10{i}";
    account["employee_count"] = i * 5;
    accounts.Add(account);
}

await service.CreateMultipleAsync(accounts);

var change = new ArxEntity("account");
change["status"] = "ON_HOLD";

var filter = new ArxFilterExpression();
filter.AddCondition("account_number", ArxConditionOperator.BeginsWith, "ACC-F10");
filter.AddCondition("employee_count", ArxConditionOperator.LessThan, 15);

int changed = await service.UpdateMultipleAsync(change, filter);

Console.WriteLine($"{changed} accounts put on hold");
```

Defined in the sample, used above:

```csharp
private static ArxMsSqlDbService Service(string connectionString) => new ArxMsSqlDbService(connectionString, ArxSchemaCatalog.Create()
    .Table("account", ArxIdColumn.Generated<long>("id"))
    .Build());
```

Check it in the database:

```sql
SELECT account_number, employee_count, status
FROM account
WHERE account_number LIKE 'ACC-F10%'
ORDER BY account_number;
```

Expect: 5 rows; ACC-F101 and ACC-F102 (5 and 10 employees) ON_HOLD, the other three ACTIVE.

#### 2. The values carry no id

The entity carries the values only: with an id as well as a filter, ARX can't tell which rows were meant.

In scope: `connectionString` (string).

```csharp
var service = Service(connectionString);

var account = new ArxEntity("account");
account["name"] = "Filter update with an id";
account["account_number"] = "ACC-F111";
var id = await service.CreateAsync(account);

var change = new ArxEntity("account");
change.RecordId = id;
change["status"] = "CLOSED";

var filter = new ArxFilterExpression();
filter.AddCondition("account_number", ArxConditionOperator.Equal, "ACC-F111");

try
{
    await service.UpdateMultipleAsync(change, filter);
}
catch (ArxValidationException error)
{
    Console.WriteLine($"Refused: {error.Code}. Nothing was changed.");
}
```

Check it in the database:

```sql
SELECT account_number, status
FROM account
WHERE account_number = 'ACC-F111';
```

Expect: 1 row, status ACTIVE.

### Put accounts on hold (selection set, by key)

Real case 1 of UpdateMultiple.

Credit control puts accounts on hold, in three scenarios: one account by its account number (the declared key); a value the status picklist doesn't allow is refused before the database; every account of a segment at once.

Usings:

```csharp
using ARX.SDK;
using ARX.SDK.Services;
```

Set up by the sample before its scenarios:

```csharp
foreach (var (number, segment) in new[] { ("ACC-U701", "smb"), ("ACC-U702", "smb"), ("ACC-U703", "smb") })
{
    var account = new ArxEntity("account");
    account["name"] = "On hold customer " + number;
    account["account_number"] = number;
    account["segments"] = segment;
    await service.CreateAsync(account);
}
```

#### 1. One account, by its account number

The credit team knows the account number, not the id: find the row by the declared key.

In scope: `service` (ArxMsSqlDbService).

```csharp
var hold = new ArxEntity("account");
hold["account_number"] = "ACC-U701";
hold["status"] = new ArxSelectionSetValue("AccountStatus", "ON_HOLD");

await service.UpdateAsync(hold, new ArxUpdateOptions { Match = ArxMatch.Key("account_number"), RequireSingleRow = true });
Console.WriteLine("ACC-U701 is ON_HOLD");
```

Check it in the database:

```sql
SELECT account_number, status
FROM account
WHERE account_number = 'ACC-U701';
```

Expect: 1 row, ON_HOLD.

#### 2. A value the picklist doesn't allow

"SUSPENDED" isn't an AccountStatus option: refused by the catalog, nothing sent.

In scope: `service` (ArxMsSqlDbService).

```csharp
var hold = new ArxEntity("account");
hold["account_number"] = "ACC-U702";
hold["status"] = new ArxSelectionSetValue("AccountStatus", "SUSPENDED");

try
{
    await service.UpdateAsync(hold, new ArxUpdateOptions { Match = ArxMatch.Key("account_number") });
}
catch (ArxValidationException error)
{
    Console.WriteLine(error.Message);
}
```

Check it in the database:

```sql
SELECT account_number, status
FROM account
WHERE account_number = 'ACC-U702';
```

Expect: 1 row, ACTIVE (the default): unchanged.

#### 3. Several accounts in one call

Several accounts by their numbers in one call: each found by its key, all or nothing.

In scope: `service` (ArxMsSqlDbService).

```csharp
var holds = new[] { "ACC-U701", "ACC-U703" }.Select(number =>
{
    var hold = new ArxEntity("account");
    hold["account_number"] = number;
    hold["status"] = new ArxSelectionSetValue("AccountStatus", "ON_HOLD");
    return hold;
}).ToList();

var changed = await service.UpdateMultipleAsync(holds, new ArxUpdateOptions { Match = ArxMatch.Key("account_number") });
Console.WriteLine($"{changed} accounts on hold");
```

Check it in the database:

```sql
SELECT account_number, status
FROM account
WHERE account_number IN ('ACC-U701', 'ACC-U702', 'ACC-U703')
ORDER BY account_number;
```

Expect: ACC-U701 ON_HOLD, ACC-U702 ACTIVE (refused in scenario 2), ACC-U703 ON_HOLD.

### Reprice 20,000 products from a feed

Real case 2 of UpdateMultiple.

A supplier's price feed reprices the catalogue, in two scenarios: 20,000 products by SKU in one call (one set-based statement), and a feed with a SKU the catalogue doesn't have, which changes nothing.

Usings:

```csharp
using ARX.SDK;
using ARX.SDK.Services;
```

Set up by the sample before its scenarios:

```csharp
var products = Enumerable.Range(1, PRODUCTS).Select(i =>
{
    var product = new ArxEntity("product");
    product["sku"] = $"SKU-U8{i:000000}";
    product["name"] = $"Feed product {i}";
    product["unit_price"] = 10.00m;
    return product;
}).ToList();
await service.CreateMultipleAsync(products);
```

#### 1. The whole feed, by SKU

Every line of the feed is one entity, found by its SKU (the declared key). All of them go in one statement.

In scope: `service` (ArxMsSqlDbService).

```csharp
var feed = Enumerable.Range(1, PRODUCTS).Select(i => (Sku: $"SKU-U8{i:000000}", Price: 10.00m + i % 100 / 100m));

var prices = feed.Select(line =>
{
    var price = new ArxEntity("product");
    price["sku"] = line.Sku;
    price["unit_price"] = line.Price;
    return price;
}).ToList();

var started = DateTime.UtcNow;
var changed = await service.UpdateMultipleAsync(prices, new ArxUpdateOptions { Match = ArxMatch.Key("sku") });
Console.WriteLine($"{changed} prices changed in {(DateTime.UtcNow - started).TotalMilliseconds:0} ms");
```

Check it in the database:

```sql
SELECT count(*) AS products, min(unit_price), max(unit_price)
FROM product
WHERE sku LIKE 'SKU-U8%';
```

Expect: 20000, 10.00, 10.99.

#### 2. A feed with an unknown SKU

One SKU in the feed isn't in the catalogue: the call refuses the whole feed, so no price is half-applied.

In scope: `service` (ArxMsSqlDbService).

```csharp
var prices = new[] { ("SKU-U8000001", 99.00m), ("SKU-U8000002", 99.00m), ("SKU-NOT-IN-CATALOG", 99.00m) }.Select(line =>
{
    var price = new ArxEntity("product");
    price["sku"] = line.Item1;
    price["unit_price"] = line.Item2;
    return price;
}).ToList();

try
{
    await service.UpdateMultipleAsync(prices, new ArxUpdateOptions { Match = ArxMatch.Key("sku") });
}
catch (ArxException error) when (error.Code == ArxErrorCode.RecordNotFound)
{
    Console.WriteLine(error.Message);
}
```

Check it in the database:

```sql
SELECT count(*)
FROM product
WHERE sku LIKE 'SKU-U8%' AND unit_price = 99.00;
```

Expect: 0: the two known SKUs weren't repriced either.

### Stock counts in batches: one transaction, a failed batch skipped

Advanced case 1 of UpdateMultiple.

A stock count file updates products in batches, in two scenarios: every batch in one transaction with automatic commit; and a batch that fails, skipped while the others are kept (savepoints, manual commit).

Usings:

```csharp
using ARX.SDK;
using ARX.SDK.Services;
```

Set up by the sample before its scenarios:

```csharp
foreach (var warehouse in new[] { "A", "B" })
{
    var products = Enumerable.Range(1, PRODUCTS).Select(i =>
    {
        var product = new ArxEntity("product");
        product["sku"] = $"SKU-U9{warehouse}{i:00000}";
        product["name"] = $"Counted product {warehouse}{i}";
        product["unit_price"] = 5.00m;
        return product;
    }).ToList();
    await service.CreateMultipleAsync(products);
}
```

#### 1. Four batches, one transaction

RunBatchAsync: four batches of 500, one commit when the code returns.

In scope: `service` (ArxMsSqlDbService).

```csharp
var options = new ArxUpdateOptions { Match = ArxMatch.Key("sku") };

await service.RunBatchAsync(async scope =>
{
    foreach (var batch in ReadStockFile("A", count: 12))
    {
        await scope.UpdateMultipleAsync(batch, options);
    }

    Console.WriteLine($"{scope.RowsWritten} products counted in {scope.BatchCount} batches");
});
```

Defined in the sample, used above:

```csharp
private static IEnumerable<List<ArxEntity>> ReadStockFile(string warehouse, int count, int? brokenBatch = null)
{
    for (var start = 1; start <= PRODUCTS; start += BATCH)
    {
        var batch = Enumerable.Range(start, BATCH).Select(i =>
        {
            var line = new ArxEntity("product");
            line["sku"] = $"SKU-U9{warehouse}{i:00000}";
            line["stock_quantity"] = count;
            return line;
        }).ToList();

        if ((start - 1) / BATCH == brokenBatch)
        {
            batch[3]["sku"] = "SKU-NOT-COUNTED";
        }

        yield return batch;
    }
}
```

Check it in the database:

```sql
SELECT count(*)
FROM product
WHERE sku LIKE 'SKU-U9A%' AND stock_quantity = 12;
```

Expect: 2000.

#### 2. A failed batch is skipped, the rest committed

Batch 3 has a SKU the catalogue doesn't know: that batch is undone (its savepoint), the others are kept.

In scope: `service` (ArxMsSqlDbService).

```csharp
var options = new ArxUpdateOptions { Match = ArxMatch.Key("sku") };

await using (var scope = await service.BeginBatchAsync(new ArxBatchOptions { Savepoints = true }))
{
    foreach (var batch in ReadStockFile("B", count: 30, brokenBatch: 2))
    {
        try
        {
            await scope.UpdateMultipleAsync(batch, options);
        }
        catch (ArxException error) when (error.Code == ArxErrorCode.RecordNotFound)
        {
            Console.WriteLine($"batch {error.BatchNumber} skipped: {error.Message}");
        }
    }

    await scope.CommitAsync();
    Console.WriteLine($"{scope.RowsWritten} products counted; failed batches: {string.Join(", ", scope.FailedBatches)}");
}
```

Check it in the database:

```sql
SELECT stock_quantity, count(*)
FROM product
WHERE sku LIKE 'SKU-U9B%'
GROUP BY stock_quantity
ORDER BY stock_quantity;
```

Expect: 0 for 500 products (batch 3, skipped: their stock stays at the default 0), 30 for 1500.