Retrieve · MySQL in C#

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, or a view (in another database too: `arx_samples_reporting.vw_product_catalogue`). MySQL functions return one value, never rows, so a function as a row source (`ArxRowSource.Function`) is ARX8001 before anything is sent: read a table or view, or call a procedure through Execute. 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` (a `TimeSpan` past 24 hours), `DATETIME` → `DateTime` (Kind `Unspecified`), `TIMESTAMP` → `DateTime` (Kind `Utc` on ARX's connections, which run in UTC); 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 run under the server's default isolation (`REPEATABLE READ` unless it says otherwise). InnoDB's consistent reads never wait for another transaction: a row another transaction changed but hasn't committed reads as it was last committed. MySqlConnector resets a pooled connection's session when it is returned, so an isolation level another user left set never reaches an ARX read
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

MySQL column	Reads back as	Notes
`VARCHAR(n)`, `CHAR(n)`, `TEXT`	`string`	`CHAR(n)` drops its padding spaces when read; `""` stays `""`
`TINYINT` / `SMALLINT` / `INT` / `BIGINT`	`sbyte` / `short` / `int` / `long`	`TINYINT UNSIGNED` is a `byte`
`INT UNSIGNED`, `BIGINT UNSIGNED`	`uint`, `ulong`
`DECIMAL(p, s)`	`decimal`	More than 28 significant digits: `ARX4001 ValueOutOfRange`
`FLOAT` / `DOUBLE`	`float` / `double`
`BOOLEAN` (`TINYINT(1)`)	`bool`
`CHAR(36)`	`Guid`	Text that isn't a Guid: `ARX5003 SchemaMismatch`
`BINARY(16)`	`byte[]`; a `Guid` for a declared Guid id
`TIMESTAMP`	`DateTime`, Kind `Utc`	The instant, read in ARX's UTC session (on your own connection, in its session's zone)
`DATETIME`	`DateTime`, Kind `Unspecified`	The clock time as stored: MySQL keeps no zone
`DATE` / `TIME`	`DateOnly` / `TimeOnly`	A `TIME` past 24 hours or below zero is a `TimeSpan`
`BLOB`, `LONGBLOB`, `BINARY(n)`	`byte[]`
`JSON`	`string`	MySQL's own form of the document
`ENUM`, `SET`	`string`	The label; a `SET`'s labels comma-separated
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 MySQL	Reads back as
Count, CountBig, CountDistinct, CountBigDistinct	`COUNT(*)`, or of the attribute when one is named	`long`
Avg	`AVG`: of integers, a `DECIMAL` (2 and 3 give 2.5000)	`decimal`
Sum	of integers, a `DECIMAL`, so it never overflows	`decimal`
Min, Max	as they are	the column's type
StDev, StDevP, Var, VarP	`STDDEV_SAMP`, `STDDEV_POP`, `VAR_SAMP`, `VAR_POP`	`double`

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
ARX4004 DataTypeMismatch	A value of a type the column can't be compared with; text of different collations compared
ARX5001, ARX5002	A table or view that doesn't exist; a column that doesn't exist, or a selected column that isn't grouped (`ONLY_FULL_GROUP_BY`)
ARX5003 SchemaMismatch	A value that isn't of the declared type (a `CHAR(36)` id that isn't a Guid)
ARX6001 PermissionDenied	No SELECT permission
ARX7003 Timeout	`CommandTimeout` exceeded
ARX8001 NotSupported	A function as a row source (MySQL's return one value); a removed column form (`._opt`, `ignoredefault.`); full-text search on a column without a `FULLTEXT` index

The samples

Basics

The least code that works.

1Retrieve one recordStandalone

The 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.

2Choose the columns to readStandalone

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.

3Find a record by id, key or columnsStandalone

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.

4Retrieve without a catalogStandalone

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 backProgressive

What each MySQL 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 transactionAdvanced

Reads 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.