MigsLab SQL Server
Retrieve
6 samples, from the least code that works to complete workflows.
What a read does
| Retrieve | One record by id (RetrieveAsync("account", id, columns)), or by a key or columns (RetrieveAsync(entity, new ArxRetrieveOptions { Match = ArxMatch.Key("email") })). Null when nothing matches; ARX2003 when a key or columns match more than one row |
| What it reads from | A table, a view (in any schema: reporting.vw_product_catalogue), or a table-valued function (ArxRowSource.Function("fn", value), arguments by position). Procedures and scalar functions are Execute's. The samples name views vw_, functions fn_ and procedures sp_ |
| Columns | The ones asked for, plus the declared id (so RecordId is set); none means all. column._lk.table.c1,c2 joins the referenced table directly (columns column.c1); a link entity's columns come back as alias.column |
| Values read back | date → DateOnly, time → TimeOnly, datetime2 → DateTime (Kind Unspecified), datetimeoffset → DateTimeOffset with its offset; NULL is a null attribute, "" stays "" |
| Lookups | A column declared with .Lookup(...) reads back as an ArxEntityReference, its Name filled from the referenced table's .DisplayName(...) |
| Isolation | Reads on connections ARX opens are READ COMMITTED, whatever another user of the pooled connection left set. Under it a row another transaction changed but hasn't committed makes the read wait (until the commit or the command timeout); a database with READ_COMMITTED_SNAPSHOT on reads the last committed version instead |
| Security | A name carrying SQL (a sort field, a filter field, a table from a request) is ARX1017, refused before the database; a name that breaks the name rules (a space, a hyphen) is ARX1003, and a well-formed name the table doesn't have (a misspelling) comes back from the database as ARX5002 |
| Without a catalog | By id or key, the table is read from the database first to find the row (one more round trip per call, so much slower than with a catalog: meant for simple CRUD and small applications). The values come back as they would with a catalog, so without one a read sets no RecordId (ask for the id column) and a lookup column is its plain value (Basic4) |
Reference
Value types read back
| SQL Server column | Reads back as | Notes |
|---|---|---|
nvarchar(n), varchar(n), nchar(n), nvarchar(max) |
string |
nchar(n) keeps its padding spaces; "" stays "" |
tinyint / smallint / int / bigint |
byte / short / int / long |
|
decimal(p, s), money |
decimal |
More than 28 significant digits: ARX4001 ValueOutOfRange |
real / float |
float / double |
|
bit |
bool |
|
uniqueidentifier |
Guid |
|
datetimeoffset |
DateTimeOffset |
The clock time and the offset it was stored with |
datetime2, datetime, smalldatetime |
DateTime, Kind Unspecified |
The clock time as stored: SQL Server keeps no zone |
date / time |
DateOnly / TimeOnly |
|
varbinary(n), varbinary(max), rowversion |
byte[] |
|
JSON in nvarchar(max) |
string |
SQL Server 2022 has no JSON type: the text as stored |
sql_variant |
the value's own type | |
An alias type (CREATE TYPE … FROM nvarchar(320)) |
Its base type | |
A declared lookup (.Lookup(...)) |
ArxEntityReference |
Name from the referenced table's .DisplayName(...) |
| A selection set column | Its stored code | entity.GetSelectionSet(column, set, catalog) gives the option |
| NULL | null |
The attribute is present, with a null value |
RealCase1_EveryDataTypeReadBack writes one row of every type with Create and prints each value's .NET type as it
reads back.
Aggregates
| Aggregate | On SQL Server | Reads back as |
|---|---|---|
| Count, CountBig, CountDistinct, CountBigDistinct | COUNT_BIG(*), or of the attribute when one is named |
long |
| Avg | AVG(column * 1.0): SQL Server's own AVG of integers is an integer (2 and 3 give 2) |
decimal |
| Sum, Min, Max | as they are (a Sum of int past 2³¹ is an overflow error, ARX4001, not a wrong value) |
the column's type |
Error codes a read can raise
| Code | When |
|---|---|
| ARX1001 InvalidArgument | An id of the wrong type or one that can't exist; a NoIdColumn table read by id; an operator without the values it needs (Between with one value, Equal with NULL, NULL in an In list); paging values below 1; Top with PageInfo; an alias used twice or reserved; a link asking for all columns |
| ARX1002, ARX1003, ARX1004 | A missing table name; an invalid name; a column asked for twice |
| ARX1008 InvalidConnection | A connection with an open transaction, given without that transaction |
| ARX1016 IdNotDeclared | Retrieve by id or key on an undeclared table; a _lk join on an undeclared table without ~column |
| ARX1017 SuspectedInjection | A table, column, alias, condition, order, group-by, aggregate, function or argument name carrying SQL |
| ARX2003 MultipleRecordsMatched | Retrieve by a key or columns found more than one row |
| ARX4001 ValueOutOfRange | A decimal larger than a .NET decimal can hold; an aggregate that overflows |
| ARX4003 InvalidValueFormat | Text compared with a number column that doesn't convert ('forty' against an int) |
| ARX4004 DataTypeMismatch | A value of a type the column can't be compared with; a function given more arguments than it has |
| ARX5001, ARX5002 | A table, view or function that doesn't exist (a scalar function read as a table, too); a column that doesn't exist, or a selected column that isn't grouped |
| ARX6001 PermissionDenied | No SELECT permission |
| ARX7003 Timeout | CommandTimeout exceeded (a read waiting for another transaction's lock, too) |
| ARX8001 NotSupported | A removed column form (._opt, ignoredefault.); paging a source with no order and no declared id; a function with named arguments or declared result columns; full-text search on a column without a full-text index; a procedure |
The samples
Basics
The least code that works.
1Retrieve one recordStandaloneThe smallest read: one record, in three scenarios. The usual way first (the id CreateAsync returned, and the columns you want), then a text id, then an id nobody has: Retrieve returns null, not an error.
Which columns come back, in three scenarios: the ones you ask for (plus the id); all of them; and how an empty value differs from a NULL one.
The three ways to find one record, in four scenarios: by id, by a declared key, by any columns, and what happens when columns match more than one row.
One record read on a service created with only a connection string, in two scenarios: by the id, which ARX finds from the table's primary key; and by a key, the unique account number.
Real cases
What an application does: a small CRM and product catalogue.
1Every data type, written and read backProgressiveWhat each SQL Server type reads back as, in one scenario: a row of data_type_showcase is written with Create and read with Retrieve, and each attribute's .NET type is printed.
Advanced cases
Dependency injection, transactions, batch scopes and setting the service up.
1Read your own writes in an ARX transactionAdvancedReads inside an ARX transaction, in two scenarios: a read sees the transaction's own uncommitted writes, another connection doesn't; and a check that reads before it commits, rolling back when the check fails.