# CreateMultiple with ARX.SDK on SQL Server

## What a CreateMultiple does

| | |
|---|---|
| **Many records** | `CreateMultipleAsync` returns one id per entity, in list order (`ids[i]` is `list[i]`'s), each also set on its entity. All or nothing: if the call fails, nothing is stored, nothing is returned, and no entity gets an id. The same `ArxEntity` object twice in one list is `ARX1001` (one entity holds one id). Rows with the same attributes and value types are grouped: 20 or more go in as one JSON parameter read by `OPENJSON`, smaller groups as a list of values under SQL Server's limit of 2,100 parameters, all in one transaction |
| **Without a catalog** | Every table in the list is read once, before the first row is written: one more round trip per call, not per row; still slower than with a catalog (Basic2) |
| **Without ids** | `new ArxCreateMultipleOptions { ReturnIds = false }` reads no id back: a group of 1,000 rows or more of one shape goes in by `SqlBulkCopy` (smaller ones by the ordinary statement, faster below that size), with constraints and triggers checked; a shape that writes an IDENTITY column, or a `datetime` or `xml` column, takes the ordinary `INSERT`; all or nothing, and `ArxRecordId.None` for each entity (Basic3). From 0.1.0-preview.5 |

## The samples

Every CreateMultiple 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.

### Create many records in one call and get their ids

Basics 1 of CreateMultiple.

Several records in one call, in seven scenarios. Three ways to declare the id column (in the service's schema, on each entity, in a schema for this one call), then two tables with no id column, then two things that only happen with many records: several tables in one list, and all or nothing when one record is refused.

Usings:

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

#### 1. Id column in the schema

The usual way: the id column declared in the schema, the ids read from the list CreateMultipleAsync returns.

In scope: `connectionString` (string).

```csharp
var schema = ArxSchemaCatalog.Create()
    .Table("account", ArxIdColumn.Generated<long>("id"))
    .Build();

var service = new ArxMsSqlDbService(connectionString, schema);

var first = new ArxEntity("account");
first["name"] = "Contoso Ltd.";
first["account_number"] = "ACC-0002";

var second = new ArxEntity("account");
second["name"] = "Fabrikam Inc.";
second["account_number"] = "ACC-0003";

var third = new ArxEntity("account");
third["name"] = "Litware Inc.";
third["account_number"] = "ACC-0004";

var accounts = new List<ArxEntity> { first, second, third };

// CreateMultipleAsync returns one id per entity, in the list's order: ids[0] is first's id, and so on.
IReadOnlyList<ArxRecordId> ids = await service.CreateMultipleAsync(accounts);

Console.WriteLine($"created accounts {string.Join(", ", ids)}");
```

Check it in the database:

```sql
SELECT id, name, account_number
FROM account
WHERE account_number IN ('ACC-0002', 'ACC-0003', 'ACC-0004')
ORDER BY id;
```

Expect: 3 rows, Contoso Ltd., Fabrikam Inc., Litware Inc., ids in list order.

#### 2. Id column on each entity

The other way: the id column declared on each entity, the ids read from the entities.

In scope: `connectionString` (string).

```csharp
// No schema catalog: each entity says what its id column is.
var service = new ArxMsSqlDbService(connectionString);

// Declared once, outside the loop: every entity shares the same object.
var accountId = ArxIdColumn.Generated<long>("id");

var accounts = new List<ArxEntity>();

foreach (var (name, number) in new[] { ("Alpine Ski House", "ACC-0007"), ("Blue Yonder Airlines", "ACC-0008"), ("Coho Winery", "ACC-0009") })
{
    var account = new ArxEntity("account");
    account.IdColumn = accountId;
    account["name"] = name;
    account["account_number"] = number;
    accounts.Add(account);
}

await service.CreateMultipleAsync(accounts);

// Each id is also set on its own entity.
foreach (var account in accounts)
{
    Console.WriteLine($"{account["name"]} got id {account.RecordId}");
}
```

Check it in the database:

```sql
SELECT id, name, account_number
FROM account
WHERE account_number IN ('ACC-0007', 'ACC-0008', 'ACC-0009')
ORDER BY id;
```

Expect: 3 rows, Alpine Ski House, Blue Yonder Airlines, Coho Winery.

#### 3. A schema for this call

A third way: a schema for this one call. The entities declare nothing; the schema passed to CreateMultipleAsync declares the table for this call only, in place of the service's.

In scope: `connectionString` (string).

```csharp
// No schema catalog on the service: the call brings its own.
var service = new ArxMsSqlDbService(connectionString);

var schema = ArxSchemaCatalog.Create()
    .Table("account", ArxIdColumn.Generated<long>("id"))
    .Build();

var accounts = new List<ArxEntity>();

foreach (var (name, number) in new[] { ("Wide World Importers", "ACC-0011"), ("Woodgrove Bank", "ACC-0012") })
{
    var account = new ArxEntity("account");
    account["name"] = name;
    account["account_number"] = number;
    accounts.Add(account);
}

// The schema declares the table, so the entities mustn't: one with its own IdColumn or Keys is refused (ARX1001).
var ids = await service.CreateMultipleAsync(accounts, schema);

Console.WriteLine($"created accounts {string.Join(", ", ids)}");
```

Check it in the database:

```sql
SELECT id, name, account_number
FROM account
WHERE account_number IN ('ACC-0011', 'ACC-0012')
ORDER BY id;
```

Expect: 2 rows, Wide World Importers, Woodgrove Bank.

#### 4. Compound key

A table with no id column, where a compound key (region + sku) identifies each record.

In scope: `connectionString` (string).

```csharp
var schema = ArxSchemaCatalog.Create()
    .Table("country", ArxIdColumn.Supplied<string>("code"))
    .Table("price_list", ArxIdColumn.NoIdColumn).Key("price_list", "region", "sku")
    .Build();

var service = new ArxMsSqlDbService(connectionString, schema);

// A price's region must be a country that exists, so add the country first.
var netherlands = new ArxEntity("country");
netherlands["code"] = "NL";
netherlands["name"] = "Netherlands";
await service.CreateAsync(netherlands);

var espresso = new ArxEntity("price_list");
espresso["region"] = "NL";
espresso["sku"] = "SKU-000002";
espresso["unit_price"] = 1.20m;
espresso["valid_from"] = new DateOnly(2026, 1, 1);

var cappuccino = new ArxEntity("price_list");
cappuccino["region"] = "NL";
cappuccino["sku"] = "SKU-000003";
cappuccino["unit_price"] = 1.80m;
cappuccino["valid_from"] = new DateOnly(2026, 1, 1);

// There is no id column, so each id in the list is ArxRecordId.None.
var ids = await service.CreateMultipleAsync(new List<ArxEntity> { espresso, cappuccino });

Console.WriteLine($"created {ids.Count} prices; their ids: {string.Join(", ", ids)}");
```

Check it in the database:

```sql
SELECT region, sku, unit_price
FROM price_list
WHERE region = 'NL'
ORDER BY sku;
```

Expect: 2 rows, SKU-000002 (1.20) and SKU-000003 (1.80).

#### 5. No key

A table with no id column and no key: the same row can go in twice.

In scope: `connectionString` (string).

```csharp
var schema = ArxSchemaCatalog.Create()
    .Table("activity_log", ArxIdColumn.NoIdColumn)
    .Build();

var service = new ArxMsSqlDbService(connectionString, schema);

var first = new ArxEntity("activity_log");
first["action"] = "report exported";

var again = new ArxEntity("activity_log");
again["action"] = "report exported";

var ids = await service.CreateMultipleAsync(new List<ArxEntity> { first, again });

Console.WriteLine($"logged \"report exported\" {ids.Count} times");
```

Check it in the database:

```sql
SELECT action, count(*)
FROM activity_log
WHERE action = 'report exported'
GROUP BY action;
```

Expect: count 2 (the same row went in twice).

#### 6. Several tables

Several tables in one call: the ids come back in list order, each of its own table's type.

In scope: `connectionString` (string).

```csharp
var schema = ArxSchemaCatalog.Create()
    .Table("country", ArxIdColumn.Supplied<string>("code"))
    .Table("device", ArxIdColumn.Generated<Guid>("id"))
    .Build();

var service = new ArxMsSqlDbService(connectionString, schema);

var france = new ArxEntity("country");
france["code"] = "FR";
france["name"] = "France";

var kiosk = new ArxEntity("device");
kiosk["serial_number"] = "KIOSK-0001";

var germany = new ArxEntity("country");
germany["code"] = "DE";
germany["name"] = "Germany";

var ids = await service.CreateMultipleAsync(new List<ArxEntity> { france, kiosk, germany });

Console.WriteLine($"country {ids[0].AsString()}, device {ids[1].AsGuid()}, country {ids[2].AsString()}");
```

Check it in the database:

```sql
SELECT 'country' AS source, code AS id, name FROM country WHERE code IN ('FR', 'DE')
UNION ALL
SELECT 'device', CAST(id AS nvarchar(max)), serial_number FROM device WHERE serial_number = 'KIOSK-0001';
```

Expect: 3 rows: France, Germany and the kiosk device with its uuid.

#### 7. All or nothing

All or nothing: when the database refuses one record, none of the list is stored and no entity gets an id.

In scope: `connectionString` (string).

```csharp
var schema = ArxSchemaCatalog.Create()
    .Table("account", ArxIdColumn.Generated<long>("id"))
    .Build();

var service = new ArxMsSqlDbService(connectionString, schema);

var fine = new ArxEntity("account");
fine["name"] = "Margie's Travel";
fine["account_number"] = "ACC-0010";

// Account numbers are unique, and ACC-0002 was created in scenario 1.
var taken = new ArxEntity("account");
taken["name"] = "Contoso (again)";
taken["account_number"] = "ACC-0002";

try
{
    await service.CreateMultipleAsync(new List<ArxEntity> { fine, taken });
}
catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.UniqueViolation)
{
    Console.WriteLine($"refused ({error.Code}); Margie's Travel wasn't stored either, and has no id ({(fine.RecordId == null ? "RecordId is null" : fine.RecordId)})");
}
```

Check it in the database:

```sql
SELECT count(*)
FROM account
WHERE account_number = 'ACC-0010';
```

Expect: 0: the list was refused, so Margie's Travel wasn't stored either.

### Create many records without a catalog

Basics 2 of CreateMultiple.

Many records in one call on a service created with only a connection string: accounts and a country in one list. ARX reads both tables from the database once, before it writes the first row, then returns one id per entity.

Good to know:

A call over many rows resolves every table it touches in one go, so the extra round trip is paid once per call, not once per row. It is still slower than a service with a catalog; for imports, declare the catalog or preload it.

Usings:

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

#### 1. Two tables in one call

Two tables in one list: each table is read once, then every row is written, all or nothing.

In scope: `connectionString` (string).

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

var first = new ArxEntity("account");
first["name"] = "Litware Inc.";
first["account_number"] = "ACC-N011";

var second = new ArxEntity("account");
second["name"] = "Proseware Inc.";
second["account_number"] = "ACC-N012";

var estonia = new ArxEntity("country");
estonia["code"] = "EE";
estonia["name"] = "Estonia";

// One id per entity, in list order: two generated numbers, then the code that was supplied.
IReadOnlyList<ArxRecordId> ids = await service.CreateMultipleAsync(new List<ArxEntity> { first, second, estonia });

Console.WriteLine($"created {string.Join(", ", ids)}");
```

Check it in the database:

```sql
SELECT account_number, name
FROM account
WHERE account_number IN ('ACC-N011', 'ACC-N012');
```

Expect: 2 rows, Litware Inc. and Proseware Inc. SELECT code, name FROM country WHERE code = 'EE'; 1 row, Estonia.

### Create many records without reading their ids

Basics 3 of CreateMultiple.

Many records created when the program doesn't need their ids: ReturnIds = false reads none back, and SQL Server loads a group of 1,000 rows or more of one shape with SqlBulkCopy, constraints and triggers checked. Still all or nothing.

Good to know:

The call returns one ArxRecordId.None per entity, so code that switches between the two forms keeps its type.

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

Usings:

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

#### 1. A load without ids

A thousand accounts in one call; no id comes back.

In scope: `connectionString` (string).

```csharp
var service = new ArxMsSqlDbService(connectionString, ArxSchemaCatalog.Create()
    .Table("account", ArxIdColumn.Generated<long>("id"))
    .Build());

var accounts = new List<ArxEntity>();

for (var i = 1; i <= 1000; i++)
{
    var account = new ArxEntity("account");
    account["name"] = $"Bulk account {i}";
    account["account_number"] = $"ACC-F3{i:0000}";
    account["employee_count"] = i % 50;
    accounts.Add(account);
}

var ids = await service.CreateMultipleAsync(accounts, new ArxCreateMultipleOptions { ReturnIds = false });

Console.WriteLine($"{ids.Count} accounts created; ids returned: {ids.Count(x => x.HasValue)}");
```

Check it in the database:

```sql
SELECT count(*)
FROM account
WHERE account_number LIKE 'ACC-F3%';
```

Expect: 1000.

### Save an account's contacts in one call

Real case 1 of CreateMultiple.

A new customer's contacts, entered on one screen and saved in one call, in two scenarios: three contacts with different attributes, and a second save where one row fails, so none is stored.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var account = new ArxEntity("account");
account["name"] = "Fabrikam Manufacturing";
account["industry"] = new ArxSelectionSetValue("Industry", Industry.Manufacturing);
await service.CreateAsync(account);
```

#### 1. Three contacts in one CreateMultipleAsync

Three contacts in one call. Rows don't need the same attributes: ARX groups them by shape.

In scope: `service` (ArxMsSqlDbService), `account` (ArxEntity).

```csharp
var ana = new ArxEntity("contact");
ana["account_id"] = new ArxEntityReference("account", account.RecordId);
ana["first_name"] = "Ana";
ana["last_name"] = "Silva";
ana["job_title"] = "Purchasing manager";
ana["email"] = "ana.silva@fabrikam.example";
ana["preferred_channel"] = new ArxSelectionSetValue("ContactChannel", 'E');
ana["consent_given_at"] = new DateTimeOffset(2026, 11, 2, 9, 30, 0, TimeSpan.FromHours(8));

var ben = new ArxEntity("contact");
ben["account_id"] = new ArxEntityReference("account", account.RecordId);
ben["first_name"] = "Ben";
ben["last_name"] = "Okafor";
ben["job_title"] = "Plant director";
ben["email"] = "ben.okafor@fabrikam.example";
ben["preferred_channel"] = new ArxSelectionSetValue("ContactChannel", 'P');

// No email or channel for this one, but a birthday and a note.
var chen = new ArxEntity("contact");
chen["account_id"] = new ArxEntityReference("account", account.RecordId);
chen["first_name"] = "Chen";
chen["last_name"] = "Wei";
chen["job_title"] = "Accounts payable";
chen["birthday"] = new DateOnly(1985, 9, 14);
chen["notes"] = "Prefers calls before 10am.";

var ids = await service.CreateMultipleAsync(new List<ArxEntity> { ana, ben, chen });
Console.WriteLine($"Ana {ids[0]}, Ben {ids[1]}, Chen {ids[2]}");
```

Check it in the database:

```sql
SELECT c.first_name, c.last_name, c.email, c.preferred_channel, c.birthday, c.notes
FROM contact c
JOIN account a ON a.id = c.account_id
WHERE a.name = 'Fabrikam Manufacturing'
ORDER BY c.id;
```

Expect: 3 rows: Ana (E), Ben (P), Chen (a birthday and a note, no email).

#### 2. A second save where one email is already taken: nothing is stored

A second save where one email is already taken: the whole call is refused, and no entity gets an id.

In scope: `service` (ArxMsSqlDbService), `account` (ArxEntity).

```csharp
var dana = new ArxEntity("contact");
dana["account_id"] = new ArxEntityReference("account", account.RecordId);
dana["first_name"] = "Dana";
dana["last_name"] = "Ruiz";
dana["email"] = "dana.ruiz@fabrikam.example";

var anaAgain = new ArxEntity("contact");
anaAgain["account_id"] = new ArxEntityReference("account", account.RecordId);
anaAgain["first_name"] = "Ana";
anaAgain["last_name"] = "Silva";
anaAgain["email"] = "ana.silva@fabrikam.example";

try
{
    await service.CreateMultipleAsync(new List<ArxEntity> { dana, anaAgain });
}
catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.UniqueViolation)
{
    Console.WriteLine(error.Message);
}
```

Check it in the database:

```sql
SELECT count(*)
FROM contact
WHERE email = 'dana.ruiz@fabrikam.example';
```

Expect: 0: the whole call was refused, Dana included.

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

Real case 2 of CreateMultiple.

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.

Usings:

```csharp
using ARX.SDK;
using ARX.SDK.Models;
using ARX.SDK.Services;
using System.Text.Json;
```

#### 1. The categories first, so products can point at them

The categories first, so the products can point at them.

In scope: `service` (ArxMsSqlDbService).

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

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

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

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

Check it in the database:

```sql
SELECT id, name
FROM product_category
WHERE name IN ('Beverages', 'Condiments')
ORDER BY id;
```

Expect: 2 rows.

#### 2. 20,000 products in one CreateMultipleAsync

20,000 products in one call, with JSON and delimited tag columns.

In scope: `service` (ArxMsSqlDbService), `beverages` (ArxRecordId), `condiments` (ArxRecordId).

```csharp
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 service.CreateMultipleAsync(products);
```

Check it in the database:

```sql
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.

#### 3. Next week's feed has a negative price in its last item: nothing from it is stored

Next week's feed has a negative price in its last item: nothing from it is stored.

In scope: `service` (ArxMsSqlDbService).

```csharp
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 service.CreateMultipleAsync(nextFeed);
}
catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.CheckViolation)
{
    Console.WriteLine(error.Message);
}
```

Check it in the database:

```sql
SELECT count(*)
FROM product
WHERE sku LIKE 'NEXT-%';
```

Expect: 0: one negative price refused the whole feed.

### Import 5,000 contacts in batches, in one transaction

Advanced case 1 of CreateMultiple.

A 5,000-contact import in batches of 500, in one transaction that ARX manages (batch scopes), in three scenarios: automatic commit with RunBatchAsync; manual commit with BeginBatchAsync when you want to decide after a failed batch; and savepoints off when any failure must stop the whole import.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var account = new ArxEntity("account");
account["name"] = "Wide World Importers";
var accountId = await service.CreateAsync(account);
```

#### 1. Automatic commit: 10 batches, one commit at the end

RunBatchAsync commits when your code returns, and rolls back if anything in it throws.

In scope: `service` (ArxMsSqlDbService), `accountId` (ArxRecordId).

```csharp
await service.RunBatchAsync(async scope =>
{
    foreach (var batch in ReadContactFile(accountId, "first"))
    {
        await scope.CreateMultipleAsync(batch);
    }
});
```

Defined in the sample, used above:

```csharp
private static IEnumerable<List<ArxEntity>> ReadContactFile(ArxRecordId accountId, string file, int? badRowInBatch = null)
{
    for (var batchNumber = 1; batchNumber <= 10; batchNumber++)
    {
        var batch = new List<ArxEntity>();

        for (var row = 1; row <= 500; row++)
        {
            var contact = new ArxEntity("contact");
            contact["account_id"] = new ArxEntityReference("account", accountId);
            contact["first_name"] = $"Customer {batchNumber}-{row}";
            contact["last_name"] = batchNumber == badRowInBatch && row == 250 ? null : "Imported";
            contact["email"] = $"{file}.{batchNumber}.{row}@wideworld.example";
            batch.Add(contact);
        }

        yield return batch;
    }
}
```

Check it in the database:

```sql
SELECT count(*)
FROM contact
WHERE email LIKE 'first.%';
```

Expect: 5000.

#### 2. Manual commit: batch 8 has a contact without a last name

Each call has its own savepoint (asked for with Savepoints = true), so a failed batch is undone alone and the scope carries on. The error says which batch failed; you decide what to do.

In scope: `service` (ArxMsSqlDbService), `accountId` (ArxRecordId).

```csharp
await using (var scope = await service.BeginBatchAsync(new ArxBatchOptions { Savepoints = true }))
{
    foreach (var batch in ReadContactFile(accountId, "second", badRowInBatch: 8))
    {
        try
        {
            await scope.CreateMultipleAsync(batch);
        }
        catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.NotNullViolation)
        {
            Console.WriteLine($"batch {error.BatchNumber} skipped: {error.Message}");
        }
    }

    // Your rule for this import: one bad batch is acceptable, more is not.
    if (scope.FailedBatches.Count <= 1)
    {
        await scope.CommitAsync();
    }

    // Leaving the block without CommitAsync() rolls back, even when an exception is passing through.
}
```

Check it in the database:

```sql
SELECT count(*) AS contacts, SUM(CASE WHEN email LIKE 'second.8.%' THEN 1 ELSE 0 END) AS from_batch_8
FROM contact
WHERE email LIKE 'second.%';
```

Expect: 4500, 0: batch 8 was undone alone, the other nine committed.

#### 3. Savepoints off: one failed batch rolls back the whole import

With savepoints off, one failed batch rolls back the whole import.

In scope: `service` (ArxMsSqlDbService), `accountId` (ArxRecordId).

```csharp
await using (var scope = await service.BeginBatchAsync(new ArxBatchOptions { Savepoints = false }))
{
    try
    {
        foreach (var batch in ReadContactFile(accountId, "third", badRowInBatch: 2))
        {
            await scope.CreateMultipleAsync(batch);
        }

        await scope.CommitAsync();
    }
    catch (ArxDatabaseException error)
    {
        Console.WriteLine($"batch {error.BatchNumber} failed; the scope is {scope.State}");
    }
}
```

Check it in the database:

```sql
SELECT count(*)
FROM contact
WHERE email LIKE 'third.%';
```

Expect: 0: with savepoints off, one failed batch rolled back the whole import.

### An order and its lines: all steps or none

Advanced case 2 of CreateMultiple.

A process of several steps in one batch scope: create the order, create its lines, then check the payment. In two scenarios: the payment is declined, so neither the order nor its lines are kept (any step that fails, including your own code, undoes them all); and the payment is accepted, so both are committed together.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var account = new ArxEntity("account");
account["name"] = "Coho Winery";
var accountId = await service.CreateAsync(account);
var redWine = new ArxEntity("product");
redWine["sku"] = "WINE-RED";
redWine["name"] = "Red wine";
redWine["unit_price"] = 19.99m;
var whiteWine = new ArxEntity("product");
whiteWine["sku"] = "WINE-WHITE";
whiteWine["name"] = "White wine";
whiteWine["unit_price"] = 17.50m;
var productIds = await service.CreateMultipleAsync(new List<ArxEntity> { redWine, whiteWine });
```

#### 1. Payment declined: nothing is kept

Your own code throws after the order and its lines were created: the scope rolls them all back.

In scope: `service` (ArxMsSqlDbService), `accountId` (ArxRecordId), `productIds` (IReadOnlyList<ArxRecordId>).

```csharp
try
{
    await PlaceOrderAsync(service, accountId, productIds, paymentAccepted: false);
}
catch (InvalidOperationException error)
{
    Console.WriteLine($"the process failed: {error.Message}");
}
```

Defined in the sample, used above:

```csharp
private static async Task PlaceOrderAsync(ArxMsSqlDbService service, ArxRecordId accountId, IReadOnlyList<ArxRecordId> productIds, bool paymentAccepted)
{
    await service.RunBatchAsync(async scope =>
    {
        var order = new ArxEntity("sales_order");
        order["order_number"] = "SO-3001";
        order["account_id"] = new ArxEntityReference("account", accountId);
        order["ordered_on"] = DateTime.UtcNow;
        order["total"] = 57.48m;
        var orderId = await scope.CreateAsync(order);

        var redLine = new ArxEntity("order_line");
        redLine["order_id"] = new ArxEntityReference("sales_order", orderId);
        redLine["product_id"] = new ArxEntityReference("product", productIds[0]);
        redLine["quantity"] = 2;
        redLine["unit_price"] = 19.99m;

        var whiteLine = new ArxEntity("order_line");
        whiteLine["order_id"] = new ArxEntityReference("sales_order", orderId);
        whiteLine["product_id"] = new ArxEntityReference("product", productIds[1]);
        whiteLine["quantity"] = 1;
        whiteLine["unit_price"] = 17.50m;

        await scope.CreateMultipleAsync(new List<ArxEntity> { redLine, whiteLine });

        if (!paymentAccepted)
        {
            throw new InvalidOperationException("Payment declined.");   // the order and its lines are rolled back
        }
    });
}
```

Check it in the database:

```sql
SELECT count(DISTINCT o.id) AS orders, count(l.order_id) AS lines
FROM sales_order o
LEFT JOIN order_line l ON l.order_id = o.id
WHERE o.order_number = 'SO-3001';
```

Expect: 1 order, 2 lines: the declined attempt left nothing; the order is the one scenario 2 committed.

#### 2. Payment accepted: the order and its lines are committed

Every step succeeds: the order and its lines are committed together.

In scope: `service` (ArxMsSqlDbService), `accountId` (ArxRecordId), `productIds` (IReadOnlyList<ArxRecordId>).

```csharp
await PlaceOrderAsync(service, accountId, productIds, paymentAccepted: true);
```

Check it in the database:

```sql
SELECT o.order_number, o.total, p.name AS product, l.quantity, l.unit_price
FROM sales_order o
JOIN order_line l ON l.order_id = o.id
JOIN product p ON p.id = l.product_id
WHERE o.order_number = 'SO-3001';
```

Expect: 2 rows, red wine x2 and white wine x1.