# Execute with ARX.SDK on PostgreSQL

## What an Execute does

| | |
|---|---|
| **The call** | `new ArxProcedureExpression("sp_place_order")`, in the pattern of `ArxQueryExpression`: `AddParameter`, `AddOutParameter<T>`, `AddInOutParameter<T>`, `AddCursorParameter`, `AddVariadicParameter`; `ProcedureType = ArxProcedureType.Function` for a function. Never SQL text |
| **Procedure or function** | The caller says which: a procedure runs as `CALL`, a function as `SELECT * FROM`. The wrong one is ARX8001, naming the right `ProcedureType` |
| **Arguments** | Named or positional (positional first), each a typed parameter through the same value path as Create (dates, JSON, arrays, selection sets, lookups). A null for an overloaded routine needs its type: `AddParameter(name, type, null)` |
| **What comes back** | `ArxExecuteResult`: `Outputs` (OUT and INOUT values, as their declared types), `ResultSets` (a procedure's tables by their cursor parameter's name, or a function's rows as `ResultSets[0]`), `Messages` (notices and warnings, in order). `Value<T>()` reads a function's single value |
| **Tables from a procedure** | Each is an `INOUT refcursor` parameter, declared with `AddCursorParameter`. ARX reads every declared cursor in one transaction (two round trips) |
| **Paging** | `PageInfo`, as on a query, three ways: the routine pages itself (`PagingParameters`: ARX fills its page arguments and reads its total), ARX pages a function's rows (`LIMIT`/`OFFSET`, the total in one round trip), ARX pages a procedure's table (`MOVE`, `FETCH`, `MOVE ALL`). The total is opt-in |
| **Transactions** | ARX's own call runs outside any transaction block, so a procedure may commit; in an `ArxTransaction` or a batch scope a procedure that commits is ARX8001 |
| **Errors the routine raises** | `RAISE EXCEPTION`, `ASSERT` and a routine's own codes (`USING ERRCODE = 'AH001'`) are ARX3005 RaisedByDatabase, with the routine's message, `SqlState` and `Hint` (`Detail` too when the connection string has `Include Error Detail=true`: Npgsql redacts it otherwise) |
| **Security** | A routine or parameter name carrying SQL is ARX1017, refused before the database; values are parameters |
| **Names in the samples** | Procedures `sp_`, functions `fn_` (views `vw_`, materialized views `mv_`) |
| **Without a catalog** | Needs no catalog and reads nothing first: a service created with only the connection string calls procedures and functions as any other |

## Reference

### Error codes an Execute can raise

| Code | When |
|---|---|
| ARX1001 InvalidArgument | A positional argument after a named one; OUT, INOUT or cursor parameters on a function; an OUT without a type; `ReturnsCursors` on a procedure; `AddOrder` on a procedure; `PageInfo` with nothing to page or below 1; `PagingParameters` without `PageInfo`; `PagedResultSet` naming no cursor |
| ARX1002, ARX1003, ARX1004 | No routine name; an invalid name (three parts, a typo); a parameter given twice |
| ARX1007, ARX1013 | A value type ARX can't write; `default(DateTime)` |
| ARX1017 SuspectedInjection | A routine, parameter, cursor or paging name carrying SQL |
| ARX2003 MultipleRecordsMatched | `Value<T>()` on several rows |
| ARX3001–3004 | A constraint the routine broke (kept from the database) |
| ARX3005 RaisedByDatabase | The routine refused: `RAISE EXCEPTION` (P0001), `ASSERT` (P0004), a routine's own code |
| ARX4004 DataTypeMismatch | No routine with this name and these argument types (42883: PostgreSQL reports a missing routine the same way); an ambiguous overload (42725) |
| ARX5003 SchemaMismatch | An OUT value of another type than declared, that can't be widened |
| ARX6001 PermissionDenied | No `EXECUTE` privilege |
| ARX7003 Timeout | `CommandTimeout` exceeded |
| ARX8001 NotSupported | A function called as a procedure or the reverse (42809); a procedure that commits inside an ARX transaction or a scope (2D000); a type Npgsql can't read in an OUT value or a cursor row |

## The samples

Every Execute sample, in reading order. Each scenario shows the code (verified against PostgreSQL) and the SQL that checks what it did. `service` is the application's `ArxPgDbService`; 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-postgresql-sample-application.md.

### Call a stored procedure

Basics 1 of Execute.

Calling a stored procedure, in three scenarios: named arguments; positional arguments; a parameter left out, so the procedure's default is used. The call is described with `ArxProcedureExpression`, the way a query is described with `ArxQueryExpression`; ARX sends `CALL` with every value as a parameter.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var service = new ArxPgDbService(connectionString);
var account = new ArxEntity("account");
account["name"] = "Old Name Ltd";
account["account_number"] = "ACC-E101";
var accountId = await service.CreateAsync(account);
```

#### 1. Named arguments

`sp_rename_account(p_account_id, p_name, p_reason)` with every argument named. Nothing comes back.

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

```csharp
var procedure = new ArxProcedureExpression("sp_rename_account");
procedure.AddParameter("p_account_id", accountId.AsInt64());
procedure.AddParameter("p_name", "New Name Ltd");
procedure.AddParameter("p_reason", "rebranding");

var result = await service.ExecuteAsync(procedure);
Console.WriteLine($"renamed; outputs: {result.Outputs.Attributes.Count()}, result sets: {result.ResultSets.Count}");
```

Check it in the database:

```sql
SELECT name
FROM account
WHERE account_number = 'ACC-E101';
```

Expect: New Name Ltd (until scenario 2 renames it again).

#### 2. Positional arguments

The same procedure with positional arguments, in the procedure's order.

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

```csharp
var procedure = new ArxProcedureExpression("sp_rename_account");
procedure.AddParameter(accountId.AsInt64());
procedure.AddParameter("Positional Ltd");
procedure.AddParameter("a positional call");

await service.ExecuteAsync(procedure);
Console.WriteLine("renamed to Positional Ltd");
```

Check it in the database:

```sql
SELECT name
FROM account
WHERE account_number = 'ACC-E101';
```

Expect: Positional Ltd (until scenario 3).

#### 3. A parameter left out: the default is used

`p_reason` left out: the procedure's default (`'not given'`) is used.

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

```csharp
var procedure = new ArxProcedureExpression("sp_rename_account");
procedure.AddParameter("p_account_id", accountId.AsInt64());
procedure.AddParameter("p_name", "Final Name Ltd");

await service.ExecuteAsync(procedure);
Console.WriteLine("renamed to Final Name Ltd, without a reason");
```

Check it in the database:

```sql
SELECT name, modified_on IS NOT NULL AS modified
FROM account
WHERE account_number = 'ACC-E101';
```

Expect: Final Name Ltd, t.

### Read OUT and INOUT values

Basics 2 of Execute.

OUT and INOUT values, in two scenarios: an INOUT goes in and comes back (here filled in by the procedure when it arrives as NULL), and OUT values come back typed in `Outputs`. Each OUT is declared with its .NET type; ARX passes it as a typed NULL, as PostgreSQL requires (PostgreSQL 14 or later).

Usings:

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

Set up by the sample before its scenarios:

```csharp
var account = new ArxEntity("account");
account["name"] = "Totals Trading";
account["account_number"] = "ACC-E201";
var accountId = await service.CreateAsync(account);
foreach (var (number, year, total) in new[] { ("SO-E201", 2025, 100m), ("SO-E202", 2026, 250m), ("SO-E203", 2026, 50m) })
{
    var order = new ArxEntity("sales_order");
    order["order_number"] = number;
    order["account_id"] = new ArxEntityReference("account", accountId);
    order["ordered_on"] = new DateTime(year, 6, 1, 12, 0, 0, DateTimeKind.Utc);
    order["total"] = total;
    await service.CreateAsync(order);
}
```

#### 1. A year in, the totals out

`p_year` = 2026 in; the number of orders and the revenue of that year out.

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

```csharp
var outputs = (await service.ExecuteAsync(Totals(accountId, 2026))).Outputs;
Console.WriteLine($"year {outputs["p_year"]}: {outputs["p_orders"]} orders ({outputs["p_orders"]!.GetType().Name}), revenue {outputs["p_revenue"]:N2} ({outputs["p_revenue"]!.GetType().Name})");
```

Defined in the sample, used above:

```csharp
private static ArxProcedureExpression Totals(ArxRecordId accountId, int? year)
{
    var procedure = new ArxProcedureExpression("sp_account_totals");
    procedure.AddParameter("p_account_id", accountId.AsInt64());
    procedure.AddInOutParameter("p_year", year);
    procedure.AddOutParameter<long>("p_orders");
    procedure.AddOutParameter<decimal>("p_revenue");
    return procedure;
}
```

Check it in the database:

```sql
SELECT count(*), sum(total)
FROM sales_order
WHERE order_number LIKE 'SO-E20%' AND extract(year FROM ordered_on) = 2026;
```

Expect: 2, 300.00.

#### 2. An INOUT the procedure fills in

`p_year` sent as NULL: the procedure fills in the current year and returns it.

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

```csharp
var outputs = (await service.ExecuteAsync(Totals(accountId, null))).Outputs;
Console.WriteLine($"no year given: the procedure used {outputs["p_year"]}, {outputs["p_orders"]} orders");
```

Check it in the database:

```sql
SELECT extract(year FROM now())::int AS this_year, count(*)
FROM sales_order
WHERE order_number LIKE 'SO-E20%' AND extract(year FROM ordered_on) = extract(year FROM now());
```

Expect: the year and the count the sample printed.

### Call a function: a value, rows, an effect

Basics 3 of Execute.

Calling a function, in three scenarios: one value (`Value<T>()`); rows (`ResultSets[0]`); a function run for its effect (`void`: no rows). A function is the same `ArxProcedureExpression` with `Function`: ARX sends `SELECT * FROM` instead of `CALL`. To filter, order or page a function's rows like a table, Retrieve reads it as a row source (RetrieveMultiple's RealCase6, Advanced8).

Usings:

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

Set up by the sample before its scenarios:

```csharp
var account = new ArxEntity("account");
account["name"] = "Function Fabrics";
account["account_number"] = "ACC-E301";
var accountId = await service.CreateAsync(account);
foreach (var (first, title) in new[] { ("Fran", "Buyer"), ("Finn", "Engineer"), ("Faye", "Director") })
{
    var contact = new ArxEntity("contact");
    contact["first_name"] = first;
    contact["last_name"] = "Fn-E301";
    contact["job_title"] = title;
    contact["account_id"] = new ArxEntityReference("account", accountId);
    await service.CreateAsync(contact);
}
```

#### 1. One value

`fn_contact_count(p_account_id)` returns a bigint.

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

```csharp
var function = new ArxProcedureExpression("fn_contact_count") { ProcedureType = ArxProcedureType.Function };
function.AddParameter("p_account_id", accountId.AsInt64());

var count = (await service.ExecuteAsync(function)).Value<long>();
Console.WriteLine($"{count} contacts");
```

Check it in the database:

```sql
SELECT fn_contact_count(a.id)
FROM account a
WHERE a.account_number = 'ACC-E301';
```

Expect: 3.

#### 2. Rows

`fn_contacts_named(p_last_name)` returns a table: its rows are the first result set.

In scope: `service` (ArxPgDbService).

```csharp
var function = new ArxProcedureExpression("fn_contacts_named") { ProcedureType = ArxProcedureType.Function };
function.AddParameter("Fn-E301");

var rows = (await service.ExecuteAsync(function)).ResultSets[0];
Console.WriteLine(string.Join(", ", rows.Entities.Select(x => $"{x["first_name"]} ({x["job_title"]})")));
```

Check it in the database:

```sql
SELECT first_name, job_title
FROM fn_contacts_named('Fn-E301');
```

Expect: 3 rows: Faye Director, Finn Engineer, Fran Buyer.

#### 3. A function run for its effect

`fn_touch_account` returns `void`: it's run for what it does, and returns no rows.

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

```csharp
var function = new ArxProcedureExpression("fn_touch_account") { ProcedureType = ArxProcedureType.Function };
function.AddParameter(accountId.AsInt64());

var result = await service.ExecuteAsync(function);
Console.WriteLine($"rows returned: {result.ResultSets[0].Entities.Count}");
```

Check it in the database:

```sql
SELECT modified_on IS NOT NULL AS touched
FROM account
WHERE account_number = 'ACC-E301';
```

Expect: t.

### A procedure that returns tables

Basics 4 of Execute.

A procedure that returns tables, in two scenarios. A PostgreSQL procedure returns rows through cursors: each table is an `INOUT refcursor` parameter, declared with `AddCursorParameter`. ARX runs the call and reads every declared cursor in one transaction (two round trips), and returns each table as a result set named after its parameter.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var account = new ArxEntity("account");
account["name"] = "Tables Inc";
account["account_number"] = "ACC-E401";
var accountId = await service.CreateAsync(account);
foreach (var (sku, price) in new[] { ("SKU-E401", 10m), ("SKU-E402", 4.5m) })
{
    var product = new ArxEntity("product");
    product["sku"] = sku;
    product["name"] = $"Product {sku}";
    product["unit_price"] = price;
    await service.CreateAsync(product);
}
var place = new ArxProcedureExpression("sp_place_order");
place.AddParameter("p_account_id", accountId.AsInt64());
place.AddParameter("p_order_number", "SO-E401");
place.AddParameter("p_skus", new[] { "SKU-E401", "SKU-E402" });
place.AddParameter("p_quantity", 2);
place.AddOutParameter<long>("p_order_id");
place.AddOutParameter<decimal>("p_total");
var orderId = (long)(await service.ExecuteAsync(place)).Outputs["p_order_id"]!;
```

#### 1. One table

`sp_order_with_lines` asked for its lines only: the other cursor parameter is left at its default.

In scope: `service` (ArxPgDbService), `orderId` (long).

```csharp
var procedure = new ArxProcedureExpression("sp_order_with_lines");
procedure.AddParameter("p_order_id", orderId);
procedure.AddCursorParameter("p_lines");

var lines = (await service.ExecuteAsync(procedure)).ResultSets["p_lines"];
Console.WriteLine(string.Join(", ", lines.Entities.Select(x => $"{x["sku"]} x{x["quantity"]} at {x["unit_price"]:N2}")));
```

Check it in the database:

```sql
SELECT p.sku, l.quantity, l.unit_price
FROM order_line l
JOIN sales_order o ON o.id = l.order_id
JOIN product p ON p.id = l.product_id
WHERE o.order_number = 'SO-E401'
ORDER BY p.sku;
```

Expect: 2 rows: SKU-E401 2 10.00, SKU-E402 2 4.50.

#### 2. Two tables, by name

Both tables: the order and its lines, by name (and by position, in declaration order).

In scope: `service` (ArxPgDbService), `orderId` (long).

```csharp
var procedure = new ArxProcedureExpression("sp_order_with_lines");
procedure.AddParameter("p_order_id", orderId);
procedure.AddCursorParameter("p_order");
procedure.AddCursorParameter("p_lines");

var result = await service.ExecuteAsync(procedure);
var order = result.ResultSets["p_order"].Entities.Single();
Console.WriteLine($"{order["order_number"]} for {order["account"]}, total {order["total"]:N2}; {result.ResultSets[1].Entities.Count} lines (ResultSets[1] is \"{result.ResultSets[1].EntityName}\")");
```

Check it in the database:

```sql
SELECT o.order_number, a.name, o.total, (SELECT count(*) FROM order_line l WHERE l.order_id = o.id) AS lines
FROM sales_order o
JOIN account a ON a.id = o.account_id
WHERE o.order_number = 'SO-E401';
```

Expect: SO-E401, Tables Inc, 29.00, 2.

### Place an order through a procedure

Real case 1 of Execute.

Placing an order through a procedure, in two scenarios: `sp_place_order` writes the order and its lines (from the products' current prices) in one call and returns the new id and the total; the id is then used like any other (Retrieve reads the order back). The application doesn't repeat the pricing rule the procedure owns.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var account = new ArxEntity("account");
account["name"] = "Order Oasis";
account["account_number"] = "ACC-E501";
var accountId = await service.CreateAsync(account);
foreach (var (sku, price) in new[] { ("SKU-E501", 12m), ("SKU-E502", 3.25m), ("SKU-E503", 99m) })
{
    var product = new ArxEntity("product");
    product["sku"] = sku;
    product["name"] = $"Product {sku}";
    product["unit_price"] = price;
    await service.CreateAsync(product);
}
```

#### 1. Place the order

Two products, three of each: the procedure prices them and returns the order's id and total.

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

```csharp
var place = new ArxProcedureExpression("sp_place_order");
place.AddParameter("p_account_id", accountId.AsInt64());
place.AddParameter("p_order_number", "SO-E501");
place.AddParameter("p_skus", new[] { "SKU-E501", "SKU-E502" });
place.AddParameter("p_quantity", 3);
place.AddOutParameter<long>("p_order_id");
place.AddOutParameter<decimal>("p_total");

var outputs = (await service.ExecuteAsync(place)).Outputs;
Console.WriteLine($"order {outputs["p_order_id"]} placed, total {outputs["p_total"]:N2}");

return (long)outputs["p_order_id"]!;
```

Check it in the database:

```sql
SELECT o.total, count(l.id) AS lines, sum(l.quantity) AS units
FROM sales_order o
JOIN order_line l ON l.order_id = o.id
WHERE o.order_number = 'SO-E501'
GROUP BY o.total;
```

Expect: 45.75, 2, 6.

#### 2. Read it back by the returned id

The returned id is the order's id: Retrieve reads the order by it.

In scope: `service` (ArxPgDbService), `orderId` (long).

```csharp
var order = await service.RetrieveAsync("sales_order", orderId, new ArxColumnSet("order_number", "total"));
Console.WriteLine($"{order!["order_number"]}: {order["total"]:N2}");
```

Check it in the database:

```sql
SELECT order_number, total
FROM sales_order
WHERE order_number = 'SO-E501';
```

Expect: SO-E501, 45.75.

### A report with several result sets

Real case 2 of Execute.

An account page built from one call: `sp_account_report` returns three tables (the contacts, the orders, a summary row), read together in one transaction, each by its cursor parameter's name. A function that returns its cursors itself (`RETURNS SETOF refcursor`) is read the same way with `ReturnsCursors = true`.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var account = new ArxEntity("account");
account["name"] = "Report Rangers";
account["account_number"] = "ACC-E601";
var accountId = await service.CreateAsync(account);
foreach (var first in new[] { "Rae", "Rob" })
{
    var contact = new ArxEntity("contact");
    contact["first_name"] = first;
    contact["last_name"] = "Report-E601";
    contact["account_id"] = new ArxEntityReference("account", accountId);
    await service.CreateAsync(contact);
}
foreach (var (number, total) in new[] { ("SO-E601", 120m), ("SO-E602", 80m) })
{
    var order = new ArxEntity("sales_order");
    order["order_number"] = number;
    order["account_id"] = new ArxEntityReference("account", accountId);
    order["ordered_on"] = new DateTime(2026, 11, 8, 9, 0, 0, DateTimeKind.Utc);
    order["total"] = total;
    await service.CreateAsync(order);
}
```

#### 1. Three tables in one call

Contacts, orders and a summary, each a cursor parameter.

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

```csharp
var report = new ArxProcedureExpression("sp_account_report");
report.AddParameter("p_account_id", accountId.AsInt64());
report.AddCursorParameter("p_contacts");
report.AddCursorParameter("p_orders");
report.AddCursorParameter("p_summary");

var sets = (await service.ExecuteAsync(report)).ResultSets;
var summary = sets["p_summary"].Entities.Single();

Console.WriteLine($"contacts: {string.Join(", ", sets["p_contacts"].Entities.Select(x => x["first_name"]))}");
Console.WriteLine($"orders: {string.Join(", ", sets["p_orders"].Entities.Select(x => $"{x["order_number"]} {x["total"]:N2}"))}");
Console.WriteLine($"summary: {summary["contacts"]} contacts, {summary["orders"]} orders, revenue {summary["revenue"]:N2}");
```

Check it in the database:

```sql
SELECT (SELECT count(*) FROM contact c WHERE c.account_id = a.id) AS contacts,
       (SELECT count(*) FROM sales_order o WHERE o.account_id = a.id) AS orders,
       (SELECT sum(total) FROM sales_order o WHERE o.account_id = a.id) AS revenue
FROM account a
WHERE a.account_number = 'ACC-E601';
```

Expect: 2, 2, 200.00.

### Page through a procedure's result: the routine pages itself, ARX pages a function, ARX pages a cursor

Real case 3 of Execute.

Paging what a procedure or function returns, the three ways, in four scenarios. Each sets `PageInfo`, as a query does, and gets back a result set with `CurrentPage`, `HasNextPage` and, when asked, the total. The total is opt-in: ask for it once (page 1), as for RetrieveMultiple.

- **The routine pages itself** (best for large data: the database produces only the page). ARX fills its page arguments from `PageInfo` and reads its total from an OUT parameter.
- **ARX pages a function's rows**: `LIMIT`/`OFFSET` and the total in one round trip. Cheap for a SQL function (PostgreSQL inlines it, the limit reaches the index); a PL/pgSQL function runs whole first.
- **ARX pages a table the procedure returns** (a cursor): `MOVE` past the earlier rows, `FETCH` one page and one row more, `MOVE ALL` to count the rest. The skipped rows aren't sent, but the database still produces them.
- Which to choose.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var products = Enumerable.Range(1, 120).Select(i =>
{
    var product = new ArxEntity("product");
    product["sku"] = $"{PREFIX}{i:D3}";
    product["name"] = $"Paged product {i}";
    product["unit_price"] = 1m + i % 10;
    return product;
}).ToList();
await service.CreateMultipleAsync(products);
```

#### 1. The procedure pages itself

`sp_products_page(p_prefix, p_page, p_page_size, INOUT p_rows, INOUT p_total)` pages itself. ARX fills `p_page` and `p_page_size`, reads the table from `p_rows` and the total from `p_total`.

In scope: `service` (ArxPgDbService).

```csharp
foreach (var pageNumber in new[] { 1, 3 })
{
    var procedure = new ArxProcedureExpression("sp_products_page");
    procedure.AddParameter("p_prefix", PREFIX);
    procedure.AddCursorParameter("p_rows");
    procedure.PageInfo = new ArxPagingInfo { Count = 50, PageNumber = pageNumber, ReturnTotalRecordCount = pageNumber == 1 };
    procedure.PagingParameters = ArxPagingParameters.PageNumber("p_page", "p_page_size", totalRecordCount: "p_total");

    Console.WriteLine(Describe((await service.ExecuteAsync(procedure)).ResultSets["p_rows"]));
}
```

Defined in the sample, used above:

```csharp
private static string Describe(ArxEntityCollection page)
    => $"page {page.CurrentPage}: {page.Entities.Count} rows ({page.Entities.FirstOrDefault()?["sku"]} to {page.Entities.LastOrDefault()?["sku"]}), next page: {page.HasNextPage}"
       + (page.TotalRecordCount > 0 ? $", {page.TotalRecordCount} in all, {page.TotalPageCount} pages" : string.Empty);
```

Check it in the database:

```sql
SELECT count(*), min(sku), max(sku)
FROM (SELECT sku FROM product WHERE sku LIKE 'PG-E7%' ORDER BY sku LIMIT 50 OFFSET 100) AS page_3;
```

Expect: 20, PG-E7101, PG-E7120 (page 1 is PG-E7001 to PG-E7050; 120 in all, 3 pages).

#### 2. ARX pages a function's rows

`fn_products_like(p_prefix)` returns every matching product; ARX orders and pages its rows, with the total in the same round trip.

In scope: `service` (ArxPgDbService).

```csharp
var function = new ArxProcedureExpression("fn_products_like") { ProcedureType = ArxProcedureType.Function };
function.AddParameter("p_prefix", PREFIX);
function.AddOrder("unit_price", ArxOrderType.Descending);
function.AddOrder("sku", ArxOrderType.Ascending);
function.PageInfo = new ArxPagingInfo { Count = 25, PageNumber = 2, ReturnTotalRecordCount = true };

var page = (await service.ExecuteAsync(function)).ResultSets[0];
Console.WriteLine(Describe(page) + $"; prices {page.Entities[0]["unit_price"]:N2} to {page.Entities[^1]["unit_price"]:N2}");
```

Check it in the database:

```sql
SELECT (array_agg(sku ORDER BY unit_price DESC, sku))[1] AS first, (array_agg(sku ORDER BY unit_price DESC, sku))[25] AS last,
       max(unit_price), min(unit_price), count(*)
FROM (SELECT * FROM fn_products_like('PG-E7') ORDER BY unit_price DESC, sku LIMIT 25 OFFSET 25) AS page_2;
```

Expect: PG-E7017, PG-E7015, 8.00, 6.00, 25 (120 in all, 5 pages).

#### 3. ARX pages a table the procedure returns

`sp_products(p_prefix, INOUT p_rows)` returns every product as a table; ARX pages the cursor.

In scope: `service` (ArxPgDbService).

```csharp
var procedure = new ArxProcedureExpression("sp_products");
procedure.AddParameter("p_prefix", PREFIX);
procedure.AddCursorParameter("p_rows");
procedure.PageInfo = new ArxPagingInfo { Count = 40, PageNumber = 3, ReturnTotalRecordCount = true };

Console.WriteLine(Describe((await service.ExecuteAsync(procedure)).ResultSets["p_rows"]));
```

Check it in the database:

```sql
SELECT count(*), min(sku), max(sku)
FROM (SELECT sku FROM product WHERE sku LIKE 'PG-E7%' ORDER BY sku LIMIT 40 OFFSET 80) AS page_3;
```

Expect: 40, PG-E7081, PG-E7120: the last page (no next page; 120 in all, 3 pages).

#### 4. Which to choose

The choice, with the numbers of `ExecutePagingBenchmarks`.

```csharp
Console.WriteLine("large data, deep pages: let the routine page itself (1); the database produces only the page.");
Console.WriteLine("a SQL function: let ARX page it (2); PostgreSQL inlines it, so the limit reaches the index.");
Console.WriteLine("a PL/pgSQL function or a procedure's table: ARX pages it (2, 3), but every skipped row is still produced.");
Console.WriteLine("the total costs a count of every row: ask for it on page 1 only, or not at all for \"load more\".");
```

### Business errors raised by a procedure

Real case 4 of Execute.

Business rules the database enforces, in three scenarios. `sp_reserve_credit` refuses an amount over the credit limit (`RAISE EXCEPTION ... USING HINT`) and an account on hold (its own code, `AH001`). Both arrive as `ARX3005 RaisedByDatabase` with the routine's message, its `SqlState` and its `Hint`, so the application can tell "the database said no" from "something broke", and branch on the routine's own code.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var open = new ArxEntity("account");
open["name"] = "Credit Cove";
open["account_number"] = "ACC-E801";
open["credit_limit"] = 100m;
var openId = await service.CreateAsync(open);
var held = new ArxEntity("account");
held["name"] = "Held Harbour";
held["account_number"] = "ACC-E802";
held["credit_limit"] = 500m;
held["status"] = new ArxSelectionSetValue("AccountStatus", "ON_HOLD");
var heldId = await service.CreateAsync(held);
```

#### 1. Within the limit

40 of a limit of 100: reserved.

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

```csharp
await service.ExecuteAsync(Reserve(accountId, 40m));
Console.WriteLine("40.00 reserved");
```

Defined in the sample, used above:

```csharp
private static ArxProcedureExpression Reserve(ArxRecordId accountId, decimal amount)
{
    var procedure = new ArxProcedureExpression("sp_reserve_credit");
    procedure.AddParameter("p_account_id", accountId.AsInt64());
    procedure.AddParameter("p_amount", amount);
    return procedure;
}
```

Check it in the database:

```sql
SELECT credit_limit
FROM account
WHERE account_number = 'ACC-E801';
```

Expect: 60.00.

#### 2. Over the limit

250 more: refused, with the routine's message and hint; nothing changed.

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

```csharp
try
{
    await service.ExecuteAsync(Reserve(accountId, 250m));
}
catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.RaisedByDatabase)
{
    Console.WriteLine(error.Message);
    Console.WriteLine($"hint: {error.Hint}; for the user: \"{ForTheUser(error)}\"");
}
```

Defined in the sample, used above:

```csharp
private static ArxProcedureExpression Reserve(ArxRecordId accountId, decimal amount)
{
    var procedure = new ArxProcedureExpression("sp_reserve_credit");
    procedure.AddParameter("p_account_id", accountId.AsInt64());
    procedure.AddParameter("p_amount", amount);
    return procedure;
}

private static string ForTheUser(ArxException error) => error.SqlState switch
{
    "AH001" => "This account is on hold. Please contact accounts.",
    "P0001" => $"That's over the credit limit. {error.Hint}.",
    _ => "The request couldn't be completed."
};
```

Check it in the database:

```sql
SELECT credit_limit
FROM account
WHERE account_number = 'ACC-E801';
```

Expect: 60.00: the refused call changed nothing.

#### 3. An account on hold: the routine's own code

An account on hold: the routine's own code, `AH001`, so the application shows another message.

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

```csharp
try
{
    await service.ExecuteAsync(Reserve(accountId, 10m));
}
catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.RaisedByDatabase)
{
    Console.WriteLine($"{error.SqlState}: {error.Message}; for the user: \"{ForTheUser(error)}\"");
}
```

Check it in the database:

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

Expect: ON_HOLD, 500.00.

### A nightly job that commits in batches

Real case 5 of Execute.

A nightly job that commits as it goes, in two scenarios. `sp_recalculate_totals` recalculates order totals in batches and commits after each, so a failure late in the run keeps the batches already done, and reports its progress. ARX's own call runs outside any transaction block, which PostgreSQL requires for a procedure that commits; inside an ARX transaction or a batch scope the same call is refused with ARX8001 and the reason.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var account = new ArxEntity("account");
account["name"] = "Nightly Nets";
account["account_number"] = "ACC-E901";
var accountId = await service.CreateAsync(account);
var product = new ArxEntity("product");
product["sku"] = "SKU-E901";
product["name"] = "Recalculated product";
product["unit_price"] = 5m;
var productId = await service.CreateAsync(product);
for (var i = 1; i <= 7; i++)
{
    var order = new ArxEntity("sales_order");
    order["order_number"] = $"SO-E9{i:D2}";
    order["account_id"] = new ArxEntityReference("account", accountId);
    order["ordered_on"] = new DateTime(2026, 11, 8, 1, 0, 0, DateTimeKind.Utc);
    order["total"] = -1m;
    var orderId = await service.CreateAsync(order);

    var line = new ArxEntity("order_line");
    line["order_id"] = new ArxEntityReference("sales_order", orderId);
    line["product_id"] = new ArxEntityReference("product", productId);
    line["quantity"] = 2;
    line["unit_price"] = 5m;
    await service.CreateAsync(line);
}
```

#### 1. Not inside an ARX transaction

The job in an ARX transaction: PostgreSQL refuses its first commit (2D000), ARX8001 says why, and the transaction rolls back.

In scope: `service` (ArxPgDbService), `connectionString` (string).

```csharp
await using var connection = new ARX.SDK.PostgreSql.Services.ArxPgConnection(connectionString);
await using var transaction = await connection.BeginTransactionAsync();

try
{
    await service.ExecuteAsync(Job(), transaction);
}
catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.NotSupported)
{
    Console.WriteLine(error.Message);
}
```

Defined in the sample, used above:

```csharp
private static ArxProcedureExpression Job()
{
    var procedure = new ArxProcedureExpression("sp_recalculate_totals");
    procedure.AddParameter("p_prefix", "SO-E9");
    procedure.AddParameter("p_batch", 3);
    return procedure;
}
```

Check it in the database:

```sql
SELECT count(*)
FROM sales_order
WHERE order_number LIKE 'SO-E9%' AND total < 0;
```

Expect: 7 right after this scenario (the refused call changed nothing); 0 once scenario 2 has run.

#### 2. Run the job alone: a commit per batch

Batches of three, each committed; the progress arrives as messages. A long job sets a longer timeout.

In scope: `service` (ArxPgDbService).

```csharp
var result = await service.ExecuteAsync(Job(), new ArxExecuteOptions { CommandTimeout = 600 });

foreach (var message in result.Messages)
{
    Console.WriteLine($"{message.Severity}: {message.Text}");
}
```

Check it in the database:

```sql
SELECT count(*) FILTER (WHERE total = 10) AS recalculated, count(*) FILTER (WHERE total < 0) AS left
FROM sales_order
WHERE order_number LIKE 'SO-E9%';
```

Expect: 7, 0.

### Progress messages from a procedure

Real case 6 of Execute.

The messages a routine sends while it runs, in two scenarios: `RAISE NOTICE` and `WARNING` come back in `Messages`, in order, for the application to show or log as it chooses (ARX never writes them to a log); `CollectMessages = false` leaves them out.

Usings:

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

#### 1. The messages, in order

An import of 1,000 rows: a notice every 250, then a warning because it's large.

In scope: `service` (ArxPgDbService).

```csharp
var result = await service.ExecuteAsync(Import(1_000));

foreach (var message in result.Messages)
{
    Console.WriteLine($"{message.Severity,-7} {message.Text}");
}
```

Defined in the sample, used above:

```csharp
private static ArxProcedureExpression Import(int rows)
{
    var procedure = new ArxProcedureExpression("sp_import_progress");
    procedure.AddParameter("p_rows", rows);
    return procedure;
}
```

Check it in the database:

```sql
SELECT proname, prokind
FROM pg_proc
WHERE proname = 'sp_import_progress';
```

Expect: sp_import_progress, p (the messages themselves are only seen by the session that ran the call).

#### 2. Not collected

The same call without collecting them.

In scope: `service` (ArxPgDbService).

```csharp
var result = await service.ExecuteAsync(Import(1_000), new ArxExecuteOptions { CollectMessages = false });
Console.WriteLine($"{result.Messages.Count} messages");
```

Check it in the database:

```sql
SELECT count(*)
FROM pg_proc
WHERE proname = 'sp_import_progress' AND prokind = 'p';
```

Expect: 1.

### Handle Execute errors

Real case 7 of Execute.

The errors an Execute call can raise, turned into messages, in two scenarios: what the database refuses (a routine that doesn't exist with these argument types, a function called as a procedure, a timeout) and what ARX refuses before it (a positional argument after a named one, an OUT parameter without a type, paging with nothing to page).

Usings:

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

#### 1. What the database refuses

Mapped from PostgreSQL's errors.

In scope: `service` (ArxPgDbService).

```csharp
await TryAsync("a procedure that doesn't exist", () => service.ExecuteAsync(new ArxProcedureExpression("sp_no_such_procedure")));

var wrongTypes = new ArxProcedureExpression("sp_rename_account");
wrongTypes.AddParameter("p_account_id", Guid.NewGuid());
wrongTypes.AddParameter("p_name", "x");
await TryAsync("the wrong argument types", () => service.ExecuteAsync(wrongTypes));

var function = new ArxProcedureExpression("fn_contact_count");
function.AddParameter(1L);
await TryAsync("a function called as a procedure", () => service.ExecuteAsync(function));

var slow = new ArxProcedureExpression("sp_slow");
slow.AddParameter("p_seconds", 3.0);
await TryAsync("a timeout of 1 second", () => service.ExecuteAsync(slow, new ArxExecuteOptions { CommandTimeout = 1 }));
```

Defined in the sample, used above:

```csharp
private static async Task TryAsync(string label, Func<Task> call)
{
    try
    {
        await call();
        Console.WriteLine($"{label}: no error");
    }
    catch (ArxException error)
    {
        Console.WriteLine($"{label}: {error.Code} ({(int)error.Code}), {error.Category}");
    }
}
```

Check it in the database:

```sql
SELECT proname, prokind
FROM pg_proc
WHERE proname IN ('sp_no_such_procedure', 'sp_rename_account', 'fn_contact_count', 'sp_slow')
ORDER BY proname;
```

Expect: 3 rows: fn_contact_count f, sp_rename_account p, sp_slow p (no sp_no_such_procedure).

#### 2. What ARX refuses first

Refused before any connection opens.

In scope: `service` (ArxPgDbService).

```csharp
var mixed = new ArxProcedureExpression("sp_rename_account");
mixed.AddParameter("p_account_id", 1L);
mixed.AddParameter("positional after a named one");
await TryAsync("a positional argument after a named one", () => service.ExecuteAsync(mixed));

var untyped = new ArxProcedureExpression("sp_account_totals");
untyped.Parameters.Add(new ArxProcedureParameter("p_orders", null, null, ArxProcedureParameterDirection.Out));
await TryAsync("an OUT parameter without a type", () => service.ExecuteAsync(untyped));

var nothingToPage = new ArxProcedureExpression("sp_rename_account") { PageInfo = new ArxPagingInfo { Count = 10 } };
await TryAsync("paging a procedure that returns no table", () => service.ExecuteAsync(nothingToPage));
```

Check it in the database:

```sql
SELECT count(*)
FROM account
WHERE id = 1 AND name = 'positional after a named one';
```

Expect: 0: nothing reached the database.

### Catch an SQL injection attempt on Execute

Real case 8 of Execute.

Catching an SQL injection attempt on Execute, in three scenarios. A routine name or a parameter name that carries SQL is refused with ARX1017 SuspectedInjection before the database. SQL in a value is a parameter: it's stored as text and runs nothing. Execute never takes SQL text.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var account = new ArxEntity("account");
account["name"] = "Injection: keep me";
account["account_number"] = "ACC-I801";
var accountId = await service.CreateAsync(account);
```

#### 1. A routine name from the request carries SQL

`POST /api/run/{report}` with the report used as the procedure name.

In scope: `service` (ArxPgDbService).

```csharp
try
{
    await service.ExecuteAsync(new ArxProcedureExpression("sp_account_report(1); DROP TABLE account; --"));
}
catch (ArxValidationException error) when (error.Code == ArxErrorCode.SuspectedInjection)
{
    SecurityLog(error);
}
```

Defined in the sample, used above:

```csharp
private static void SecurityLog(ArxValidationException error) => Console.WriteLine($"SECURITY: {error.Message}");
```

Check it in the database:

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

Expect: 1: the table is there.

#### 2. A parameter name from the request carries SQL

A form whose field names become parameter names.

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

```csharp
var procedure = new ArxProcedureExpression("sp_rename_account");
procedure.AddParameter("p_account_id", accountId.AsInt64());
procedure.AddParameter("p_name => 'x'); DELETE FROM account; --", "Pwned");

try
{
    await service.ExecuteAsync(procedure);
}
catch (ArxValidationException error) when (error.Code == ArxErrorCode.SuspectedInjection)
{
    SecurityLog(error);
}
```

Check it in the database:

```sql
SELECT name
FROM account
WHERE account_number = 'ACC-I801';
```

Expect: Injection: keep me (until scenario 3).

#### 3. SQL in a value is a value

SQL typed into a value: it's the account's new name, nothing more.

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

```csharp
var procedure = new ArxProcedureExpression("sp_rename_account");
procedure.AddParameter("p_account_id", accountId.AsInt64());
procedure.AddParameter("p_name", "x'); DROP TABLE account; --");

await service.ExecuteAsync(procedure);
Console.WriteLine("renamed, literally");
```

Check it in the database:

```sql
SELECT name
FROM account
WHERE account_number = 'ACC-I801';
```

Expect: x'); DROP TABLE account; --

### Execute in an ARX transaction

Advanced case 1 of Execute.

Execute inside an ARX transaction, in two scenarios: a Create and a procedure in one transaction, the procedure's writes read back inside it, and everything gone after a rollback; and a business error from the procedure aborting the transaction, so the next call is refused (ARX1015) until the application rolls back.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var product = new ArxEntity("product");
product["sku"] = "SKU-EA11";
product["name"] = "Transactional product";
product["unit_price"] = 20m;
await service.CreateAsync(product);
```

#### 1. Create, call, read inside, roll back

Create an account, place its order through the procedure, read the order inside the transaction, roll back.

In scope: `service` (ArxPgDbService), `connectionString` (string).

```csharp
await using var connection = new ArxPgConnection(connectionString);
await using var transaction = await connection.BeginTransactionAsync();

var account = new ArxEntity("account");
account["name"] = "Rolled Back Retail";
account["account_number"] = "ACC-EA11";
var accountId = await service.CreateAsync(account, transaction);

var place = new ArxProcedureExpression("sp_place_order");
place.AddParameter("p_account_id", accountId.AsInt64());
place.AddParameter("p_order_number", "SO-EA11");
place.AddParameter("p_skus", new[] { "SKU-EA11" });
place.AddParameter("p_quantity", 4);
place.AddOutParameter<long>("p_order_id");
place.AddOutParameter<decimal>("p_total");
var outputs = (await service.ExecuteAsync(place, transaction)).Outputs;

var inside = await service.RetrieveAsync("sales_order", (long)outputs["p_order_id"]!, transaction, new ArxColumnSet("total"));
Console.WriteLine($"inside the transaction: order total {inside!["total"]:N2}");

await transaction.RollbackAsync();
Console.WriteLine("rolled back");
```

Check it in the database:

```sql
SELECT (SELECT count(*) FROM account WHERE account_number = 'ACC-EA11') AS accounts,
       (SELECT count(*) FROM sales_order WHERE order_number = 'SO-EA11') AS orders;
```

Expect: 0, 0: the account and the procedure's order went with the rollback.

#### 2. A raised error aborts the transaction

The procedure refuses (ARX3005); the transaction is aborted, so the next call is ARX1015.

In scope: `service` (ArxPgDbService), `connectionString` (string).

```csharp
await using var connection = new ArxPgConnection(connectionString);
await using var transaction = await connection.BeginTransactionAsync();

var account = new ArxEntity("account");
account["name"] = "Aborted Atelier";
account["account_number"] = "ACC-EA12";
account["credit_limit"] = 10m;
var accountId = await service.CreateAsync(account, transaction);

var reserve = new ArxProcedureExpression("sp_reserve_credit");
reserve.AddParameter("p_account_id", accountId.AsInt64());
reserve.AddParameter("p_amount", 50m);

try
{
    await service.ExecuteAsync(reserve, transaction);
}
catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.RaisedByDatabase)
{
    Console.WriteLine($"refused: {error.Message}; aborted: {transaction.IsAborted}");
}

try
{
    await service.RetrieveAsync("account", accountId, transaction);
}
catch (ArxValidationException error) when (error.Code == ArxErrorCode.TransactionAborted)
{
    Console.WriteLine(error.Message);
}
```

Check it in the database:

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

Expect: 0: the aborted transaction was rolled back when it was disposed.

### Execute in a batch scope: a refused call undone alone

Advanced case 2 of Execute.

Execute in a batch scope, in one scenario: an import of three orders through the procedure, one transaction. Each call has its own savepoint (asked for with Savepoints = true), so the order the procedure refuses (a credit limit) is undone alone and the import continues; the others are committed together at the end.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var product = new ArxEntity("product");
product["sku"] = "SKU-EA21";
product["name"] = "Scoped product";
product["unit_price"] = 10m;
await service.CreateAsync(product);
var account = new ArxEntity("account");
account["name"] = "Scoped Supplies";
account["account_number"] = "ACC-EA21";
account["credit_limit"] = 100m;
var accountId = await service.CreateAsync(account);
```

#### 1. Import three orders in one scope

Three orders: each is a credit reservation then the order. The second is over the limit: its reservation is refused and the order skipped; the scope stays active.

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

```csharp
await service.RunBatchAsync(async scope =>
{
    foreach (var (number, amount, quantity) in new[] { ("SO-EA21", 30m, 3), ("SO-EA22", 500m, 50), ("SO-EA23", 20m, 2) })
    {
        var reserve = new ArxProcedureExpression("sp_reserve_credit");
        reserve.AddParameter("p_account_id", accountId.AsInt64());
        reserve.AddParameter("p_amount", amount);

        try
        {
            await scope.ExecuteAsync(reserve);
        }
        catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.RaisedByDatabase)
        {
            Console.WriteLine($"{number}: refused in call {error.BatchNumber} ({error.Message}); the scope is {scope.State}");
            continue;
        }

        var place = new ArxProcedureExpression("sp_place_order");
        place.AddParameter("p_account_id", accountId.AsInt64());
        place.AddParameter("p_order_number", number);
        place.AddParameter("p_skus", new[] { "SKU-EA21" });
        place.AddParameter("p_quantity", quantity);
        place.AddOutParameter<long>("p_order_id");
        place.AddOutParameter<decimal>("p_total");
        var total = (await scope.ExecuteAsync(place)).Outputs["p_total"];
        Console.WriteLine($"{number}: placed, total {total:N2}");
    }
}, new ArxBatchOptions { Savepoints = true });
```

Check it in the database:

```sql
SELECT (SELECT string_agg(order_number || ' ' || total, ', ' ORDER BY order_number) FROM sales_order WHERE order_number LIKE 'SO-EA2%') AS orders,
       (SELECT credit_limit FROM account WHERE account_number = 'ACC-EA21') AS credit_left;
```

Expect: SO-EA21 30.00, SO-EA23 20.00 | 50.00.

### Export what a procedure returns, page by page

Advanced case 3 of Execute.

Exporting everything a procedure returns, page by page, in one scenario: `sp_products_page` pages itself (the first way of paging), the export asks for the total on the first page only, and reads pages until `HasNextPage` is false. Each page is one call, so the memory is one page's.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var products = Enumerable.Range(1, 1_050).Select(i =>
{
    var product = new ArxEntity("product");
    product["sku"] = $"{PREFIX}-{i:D4}";
    product["name"] = $"Exported product {i}";
    product["unit_price"] = 1m + i % 7;
    return product;
}).ToList();
await service.CreateMultipleAsync(products);
```

#### 1. Every page, the total asked once

Pages of 200: the first says how many pages there are; the loop ends on HasNextPage.

In scope: `service` (ArxPgDbService).

```csharp
var exported = 0;
var sum = 0m;
var pageCount = 0;
ArxEntityCollection page;
var number = 0;

do
{
    var procedure = new ArxProcedureExpression("sp_products_page");
    procedure.AddParameter("p_prefix", PREFIX);
    procedure.AddCursorParameter("p_rows");
    procedure.PageInfo = new ArxPagingInfo { Count = 200, PageNumber = ++number, ReturnTotalRecordCount = number == 1 };
    procedure.PagingParameters = ArxPagingParameters.PageNumber("p_page", "p_page_size", totalRecordCount: "p_total");

    page = (await service.ExecuteAsync(procedure)).ResultSets["p_rows"];

    if (number == 1)
    {
        pageCount = page.TotalPageCount;
    }

    exported += page.Entities.Count;
    sum += page.Entities.Sum(x => (decimal)x["unit_price"]!);
}
while (page.HasNextPage);

Console.WriteLine($"{exported} products in {number} pages (page 1 said {pageCount}), prices summing to {sum:N2}");
```

Check it in the database:

```sql
SELECT count(*), sum(unit_price), ceil(count(*) / 200.0)
FROM product
WHERE sku LIKE 'EXP-EA3%';
```

Expect: 1050, the sum the sample printed, 6.

### A security check around Execute: probing, blocking, unblocking a false positive

Advanced case 4 of Execute.

A security check around Execute (`SecurityGuard`, in the project root), in three scenarios: a report run through an allowed list of procedures passes; an endpoint that runs whatever procedure the request names is probed, and the attacker is blocked after three attempts; and a false positive (an old client sending "account report (v1)") is triaged: support unblocks the user and the endpoint maps the old name.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var guard = new SecurityGuard(blockAfter: 3);
var account = new ArxEntity("account");
account["name"] = "Guarded Goods";
account["account_number"] = "ACC-EA41";
var accountId = await service.CreateAsync(account);
```

#### 1. A report through the allowed list

`POST /api/reports/account-report`: run.

In scope: `service` (ArxPgDbService), `guard` (SecurityGuard), `accountId` (ArxRecordId).

```csharp
var result = await RunThroughTheListAsync(service, guard, "ana", "account-report", accountId);
var unknown = await RunThroughTheListAsync(service, guard, "ana", "sp_account_report; DROP TABLE account", accountId);

Console.WriteLine($"ana: {result.Outcome}, {result.Value!.ResultSets["p_summary"].Entities[0]["contacts"]} contacts; an unknown report: \"{unknown.ForTheUser}\" (never reaches ARX)");
```

Defined in the sample, used above:

```csharp
private static readonly Dictionary<string, string> Reports = new(StringComparer.OrdinalIgnoreCase)
{
    ["account-report"] = "sp_account_report",
};

private static ArxProcedureExpression Report(string procedureName, ArxRecordId accountId)
{
    var report = new ArxProcedureExpression(procedureName);
    report.AddParameter("p_account_id", accountId.AsInt64());
    report.AddCursorParameter("p_summary");
    return report;
}

private static Task<GuardResult<ArxExecuteResult>> RunThroughTheListAsync(ArxPgDbService service, SecurityGuard guard, string user, string report, ArxRecordId accountId)
    => Reports.TryGetValue(report, out var procedure)
        ? guard.RunAsync(user, $"report {report}", NameSource.Code, () => service.ExecuteAsync(Report(procedure, accountId)))
        : Task.FromResult(new GuardResult<ArxExecuteResult>(GuardOutcome.Refused, null, $"404 Not Found: no report '{report}'."));
```

Check it in the database:

```sql
SELECT count(*)
FROM contact c
JOIN account a ON a.id = c.account_id
WHERE a.account_number = 'ACC-EA41';
```

Expect: 0, the contacts the report counted.

#### 2. Probing the legacy endpoint gets the user blocked

The legacy endpoint is probed three times: each probe is refused by ARX (ARX1017) and logged; then the user is blocked.

In scope: `service` (ArxPgDbService), `guard` (SecurityGuard), `accountId` (ArxRecordId).

```csharp
foreach (var probe in new[] { "sp_account_report; DROP TABLE account; --", "sp_account_report\"(1); --", "pg_sleep UNION SELECT 1" })
{
    await RunDirectlyAsync(service, guard, "mallory", probe, accountId);
}

var valid = await RunDirectlyAsync(service, guard, "mallory", "sp_account_report", accountId);

Console.WriteLine($"mallory: {guard.Events.Count(x => x.User == "mallory")} events logged, blocked: {guard.IsBlocked("mallory")}; a valid report: {valid.Outcome}");
```

Defined in the sample, used above:

```csharp
private static ArxProcedureExpression Report(string procedureName, ArxRecordId accountId)
{
    var report = new ArxProcedureExpression(procedureName);
    report.AddParameter("p_account_id", accountId.AsInt64());
    report.AddCursorParameter("p_summary");
    return report;
}

private static Task<GuardResult<ArxExecuteResult>> RunDirectlyAsync(ArxPgDbService service, SecurityGuard guard, string user, string procedure, ArxRecordId accountId)
    => guard.RunAsync(user, $"report {procedure}", NameSource.UserInput, () => service.ExecuteAsync(Report(procedure, accountId)));
```

Check it in the database:

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

Expect: 1: the table is there.

#### 3. A false positive, triaged

A false positive: an old client sends "account report (v1)". The parentheses make ARX flag it as SQL, the client retries, and after three strikes the user is blocked. Support reads the log, sees the same harmless name three times, unblocks the user, and the endpoint maps the old name to the procedure.

In scope: `service` (ArxPgDbService), `guard` (SecurityGuard), `accountId` (ArxRecordId).

```csharp
for (var attempt = 0; attempt < 3; attempt++)
{
    await RunDirectlyAsync(service, guard, "olga", "account report (v1)", accountId);
}

Console.WriteLine($"olga blocked: {guard.IsBlocked("olga")}; logged: {guard.Events.Last(x => x.User == "olga").Detail}");

guard.Unblock("olga");
Reports["account report (v1)"] = "sp_account_report";
var mapped = await RunThroughTheListAsync(service, guard, "olga", "account report (v1)", accountId);

Console.WriteLine($"after triage: olga blocked: {guard.IsBlocked("olga")}, the old name runs: {mapped.Outcome}");
```

Defined in the sample, used above:

```csharp
private static readonly Dictionary<string, string> Reports = new(StringComparer.OrdinalIgnoreCase)
{
    ["account-report"] = "sp_account_report",
};

private static ArxProcedureExpression Report(string procedureName, ArxRecordId accountId)
{
    var report = new ArxProcedureExpression(procedureName);
    report.AddParameter("p_account_id", accountId.AsInt64());
    report.AddCursorParameter("p_summary");
    return report;
}

private static Task<GuardResult<ArxExecuteResult>> RunThroughTheListAsync(ArxPgDbService service, SecurityGuard guard, string user, string report, ArxRecordId accountId)
    => Reports.TryGetValue(report, out var procedure)
        ? guard.RunAsync(user, $"report {report}", NameSource.Code, () => service.ExecuteAsync(Report(procedure, accountId)))
        : Task.FromResult(new GuardResult<ArxExecuteResult>(GuardOutcome.Refused, null, $"404 Not Found: no report '{report}'."));

private static Task<GuardResult<ArxExecuteResult>> RunDirectlyAsync(ArxPgDbService service, SecurityGuard guard, string user, string procedure, ArxRecordId accountId)
    => guard.RunAsync(user, $"report {procedure}", NameSource.UserInput, () => service.ExecuteAsync(Report(procedure, accountId)));
```

Check it in the database:

```sql
SELECT count(*)
FROM pg_proc
WHERE proname = 'sp_account_report';
```

Expect: 1: the procedure the old name now maps to.

### Execute on a preloaded, Strict schema: routines aren't covered, tables are

Advanced case 5 of Execute.

Execute on a service whose schema is preloaded and Strict, in three scenarios: a procedure and a function called as usual, because Strict covers tables and views, not routines; a function read as a row source, with the routine's own columns; and the same service still refusing a table column that wasn't preloaded.

Good to know:

A routine's parameters and result columns are the routine's, not a table's, so a preload doesn't list them and Strict doesn't check them. What a routine may touch is the database's business: grant EXECUTE only on the routines an endpoint may call.

Usings:

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

Set up by the sample before its scenarios:

```csharp
var account = new ArxEntity("account");
account["name"] = "Routine Roasters";
account["account_number"] = "ACC-PRE-EX5";
account["credit_limit"] = 2_500m;
var accountId = await service.CreateAsync(account);
var tables = new ArxPreLoadTableRegister()
    .PreLoadTable("account", ["name", "account_number", "status"]);
var schema = await new ArxPreLoadTable(tables).LoadAsync(connectionString);
var service = new ArxPgDbService(connectionString, schema) { Strict = true };
```

#### 1. A procedure and a function on a Strict service

A procedure renames the account; a function counts its contacts. Neither is in the preload.

In scope: `service` (ArxPgDbService), `accountId` (long).

```csharp
var rename = new ArxProcedureExpression("sp_rename_account");
rename.AddParameter("p_account_id", accountId);
rename.AddParameter("p_name", "Routine Roasters & Co");
await service.ExecuteAsync(rename);

var count = new ArxProcedureExpression("fn_contact_count") { ProcedureType = ArxProcedureType.Function };
count.AddParameter("p_account_id", accountId);
Console.WriteLine($"renamed through sp_rename_account; fn_contact_count says {(await service.ExecuteAsync(count)).Value<long>()} contacts");

var renamed = new ArxQueryExpression("account");
renamed.ColumnSet.AddColumns("name");
renamed.Criteria.AddCondition("account_number", ArxConditionOperator.Equal, "ACC-PRE-EX5");
Console.WriteLine($"read back through the preloaded columns: {(await service.RetrieveMultipleAsync(renamed)).Entities.Single()["name"]}");
```

Check it in the database:

```sql
SELECT name, modified_on IS NOT NULL AS renamed
FROM account
WHERE account_number = 'ACC-PRE-EX5';
```

Expect: Routine Roasters & Co, true.

#### 2. A function as a row source: the routine's columns

fn_accounts_with_status returns account rows: as a row source, every column it returns comes back.

In scope: `service` (ArxPgDbService).

```csharp
var active = new ArxQueryExpression(ArxRowSource.Function("fn_accounts_with_status", "ACTIVE")) { ColumnSet = ArxColumnSet.All };
active.Criteria.AddCondition("account_number", ArxConditionOperator.Equal, "ACC-PRE-EX5");
var row = (await service.RetrieveMultipleAsync(active)).Entities.Single();
Console.WriteLine($"{row.Attributes.Count} columns, credit_limit {row["credit_limit"]:N2} among them");
```

Check it in the database:

```sql
SELECT account_number, credit_limit
FROM fn_accounts_with_status('ACTIVE')
WHERE account_number = 'ACC-PRE-EX5';
```

Expect: 1 row, 2500.00.

#### 3. The table is still an allow list

The table itself stays an allow list: credit_limit wasn't preloaded.

In scope: `service` (ArxPgDbService).

```csharp
var probe = new ArxQueryExpression("account");
probe.ColumnSet.AddColumns("name", "credit_limit");

try
{
    await service.RetrieveMultipleAsync(probe);
}
catch (ArxValidationException exception) when (exception.Code == ArxErrorCode.NotPreloaded)
{
    Console.WriteLine(exception.Message);
}
```

Note: Nothing to check: the read was refused before the database (ARX1018).