MigsLab SQL Server
Update
13 samples, from the least code that works to complete workflows.
What an update does
| What is written | Only the attributes you set. An attribute left out keeps its value; set to null, the column is cleared; ArxIgnore leaves it out |
| Which rows | By default the table's declared id: entity.RecordId (what CreateAsync returned) or the id attribute. ArxUpdateOptions.Match finds rows by a declared key (ArxMatch.Key("account_number")) or by any columns (ArxMatch.Columns("lead_source")), which may match many rows. Match columns are never written. Text is matched as SQL Server compares it: with the default collation, case-insensitively and without trailing spaces ('abc' finds 'ABC ') |
| What comes back | The number of rows changed. An INSTEAD OF trigger that skips a row still counts it: SQL Server reports the rows the statement was for |
| Nothing matched | ARX2002 RecordNotFound. In UpdateMultipleAsync, one entity matching nothing refuses the whole call: nothing changes |
| Strict | RequireSingleRow = true: each entity must change exactly one row, else ARX2002 or ARX2003 MultipleRecordsMatched, and nothing changes |
| Values | Exactly as Create writes them: types, selection sets checked against the catalog, lookups, dates |
| Concurrency | Optional. Declare a version column (.Version(table, column) in the schema catalog) and every update checks it in the same statement (a read returns the version with each record, so read, change and update carries it): an entity that carries an old version is ARX2004 RecordChanged and nothing is written, and each update adds one to the version. .Version(table, column, required: true) refuses an update that doesn't carry it (ARX1001). Without a version column the last writer wins. |
| Without a catalog | By the id, the table's primary key is read from the database first (one more round trip per call, so much slower than with a catalog: meant for simple CRUD and small applications); ARX1019 when it has none ARX can use. By a key or columns no id is needed, so a table without a primary key works (Basic5) |
| Rows by a condition | Update finds rows by equal values: the id, a declared key, or columns you give values for (ArxMatch.Columns). To change every row that meets a condition (amount > 100), pass the values and an ArxFilterExpression to UpdateMultipleAsync(values, filter): one call, all or nothing, from 0.1.0-preview.5 (UpdateMultiple, Basics). Before that version: read the rows with RetrieveMultipleAsync and pass them to UpdateMultipleAsync. |
Reference
Error codes an update can raise
| Code | When |
|---|---|
| ARX1001 InvalidArgument | No id or match value, a NULL match value, an id of the wrong type or one that can't exist (0, negative, empty), a key the table doesn't declare, a NoIdColumn table matched by id, nothing to write, the same entity or match values twice in one call (compared as SQL Server compares them), an entity declaring its own id or keys while a schema exists |
| ARX1002, ARX1003, ARX1004 | Missing table name, an invalid name, an attribute twice |
| ARX1017 SuspectedInjection | A table, attribute or match column name carrying SQL (a quote, ;, a comment, =, an attack word after a space); nothing sent to the database (RealCase4) |
| ARX1005, ARX1006, ARX1011, ARX1012, ARX1013 | Lookups, selection sets and dates, as for Create |
| ARX1008 InvalidConnection | A connection with an open transaction, given without that transaction |
| ARX1016 IdNotDeclared | Matching by id or key on a table nobody declared (a match by columns needs no declaration) |
| ARX2002 RecordNotFound | Nothing matched; in UpdateMultiple, any entity matched nothing (nothing changed) |
| ARX2003 MultipleRecordsMatched | RequireSingleRow, and an entity matched several rows (nothing changed) |
| ARX3001 to ARX3004 | Unique, foreign key (including a natural id still referenced), not null, check |
| ARX4001 to ARX4004 | Out of range, too long, text that doesn't convert to the column's type ('forty' into an int), wrong type (including a match value of another type than its column) |
| ARX5001, ARX5002 | A table or column that doesn't exist, or an IDENTITY or computed column written |
| ARX6001 | No permission, or a row-level security block predicate refusing the change |
| ARX7001 to ARX7003 | Deadlock, snapshot update conflict, lock or command timeout (transient; SQL Server has no statement timeout of its own, so the client's command timeout applies) |
| ARX8001 NotSupported | A view SQL Server can't update (DISTINCT, GROUP BY or aggregates, or a change over several base tables) |
The samples
Basics
The least code that works.
1Update one recordStandaloneThe smallest update: change one record, in four scenarios. Read them in order: the usual way first (the id that CreateAsync returned), then the other way (the id as an attribute), then a text id, then a table with no id column, whose record is found by its compound key.
What an update writes, in three scenarios: an attribute set to null clears the column; an attribute you leave out keeps its value; ArxIgnore leaves out an attribute that is on the entity anyway. Each scenario has its own account.
An update of a record that doesn't exist is an error, ARX2002 RecordNotFound, never silently nothing. Two scenarios: one record, and a list where one is missing.
How an update finds its rows (ArxMatch), in four scenarios: by id (the default), by a declared key, by any columns (which may match many rows), and strict: exactly one row, or nothing changes.
Updates on a service created with only a connection string, in two scenarios: by the id, which ARX reads from the table's primary key; and a table with no primary key, found by columns, which needs no id at all.
Real cases
What an application does: a small CRM and product catalogue.
1An edit form changes some account detailsProgressiveAn "edit account" form, in three scenarios: the fields the user changed are written and the rest kept; a field the user emptied is cleared; the form sends the id it was opened with.
An edit screen turns update errors into messages, in two scenarios: what the database refuses (a taken account number, a negative employee count, a website too long), and what ARX refuses before the database (a NULL to find the row by, nothing to write). Each failed update leaves the account as it was.
The price list has no id column; each price is found by its region and SKU. Three scenarios: change one price by the compound key; move a price to another SKU (changing part of the key, found by the old values through columns); rename a natural id that other rows still use, which the foreign key refuses.
Catching an SQL injection attempt on update, in four scenarios. A name that carries SQL (a field to change, the column to find rows by) is refused with ARX1017 SuspectedInjection before the database. SQL in a value, the classic ' OR '1'='1, is a parameter: it matches nothing instead of every row.
Advanced cases
Dependency injection, transactions, batch scopes and setting the service up.
1Updates in one ARX transaction: commit, roll back, abortAdvancedUpdates inside an ARX transaction, in three scenarios: transfer credit between two accounts and commit; roll back when your own check fails; and an update the database refuses, which aborts the transaction. ARX types only: ArxMsSqlConnection, ArxTransaction.
Placing an order creates rows and updates others, all or nothing, in two scenarios: the order, its lines and the stock in one batch scope; and an order for a product that no longer exists, which keeps nothing.
A security check around updates (SecurityGuard, in the project root), in four scenarios: an API batch of changes passes through; an attack hidden in one item refuses the whole batch and is logged; a list of allowed fields stops unknown names before ARX; and a false positive from your own configuration is reported as a bug, not an attack, so nobody is blocked.
Update on a schema preloaded at service setup, in four scenarios: the convenient setup (keys only) updating by the business key the preload found; the typed setup refusing a value of the wrong type before the database; Strict as an allow list of the columns an endpoint may change; and the table a preload added because it is referenced, used by its own key.