Retrieving records affected by statement

[roji]

DbDataRecord has a RecordsAffected property, which allows users to know how many rows were inserted/updated/deleted. However, when executing a multi-statement command (e.g. UPDATE xxx; UPDATE xxx), this property provides an aggregation of all rows across statements. There's no programmatic way to get records affected per statement.

For an example of why this might be useful, see EFCore's modification command batch. One of EFCore's major new features are command batching (i.e. the use of multi-statement commands), but in order to implement optimistic concurrency it needs to know, for each command, how many records were affected. For SqlServer this is implemented by sending SELECT @@ROWCOUNT after each update, and parsing the results.

PostgreSQL has no equivalent SQL query, so in Npgsql I actually developed a statement-by-statement RecordsAffected mechanism to support this. In a nutshell, a collection of NpgsqlStatements is exposed by NpgsqlDataReader, and each object has its own RecordsAffected. Let me know if you want more detail.

Note that this is somewhat related to #15375, where the possibility of a better API for command batching is discussed (the latter may/could also depend on multiple DbStatement instances being managed within a single DbCommand).

[karelz]

@YoungGah says it will likely need new API. We need formal API proposal.

[roji]

@karelz and @YoungGah, in the absence of a standard API for this I implemented an Npgsql-specific one, here are some details which may help shaping things.

Npgsql includes an NpgsqlStatement command which identifies a single statement within a batch. NpgsqlCommand exposes a Statements property which is an IReadOnlyList of these. NpgsqlStatement exposes a Rows property which provides the rows affected by that specific statement (this should probably be named RowsAffected or maybe RecordsAffected for consistency with the existing DbDataReader.RecordsAffected property). This property gets populated once a statement has finished processing.

Note that NpgsqlStatement also exposes an SQL property, allowing access to the individual statement's SQL. This is useful, for example, when some statement in a batch triggers an exception - the exception refers back to the NpgsqlStatement that triggered it, and the user has access to that statement's SQL.

Note that this tightly interacts with #15375, which concerns a better (structured) API for batching. A possible solution to that issue would be to let users populate NpgsqlStatement instances in the command (rather than concatenate semi-colon delimited SQL statements in CommandText).

Let me know if you guys want more info.

[roji]

Superceded by dotnet/corefx#35135