API Proposal: DbCommandSet -- allows multiple DbCommands to be transmitted to server in single message #28794
Description
This is offered in the friendliest way possible as an alternative to #28633 (this general idea was discussed in that thread but didn't fit the direction being taken for that proposal). Both have the same high-level goal but differ in the details of how they achieve that goal.
Need/Problem Statement
Within the realm of SQL-based relational database engines:
- All support executing command batches that consist of a single SQL statement.
- Some support command batches that consist of multiple SQL statements (e.g. SQL Server supports
SELECT * FROM TableA; SELECT * FROM TableB). - Some also support multiple independent command batches being sent in the same wire protocol level message. (In turn, depending on the server, these independent batches may themselves each contain multiple SQL statements.) Transmitting multiple commands in the same message can offer performance benefits.
The first two scenarios are supported by ADO.Net's DbCommand. However, no public ADO.Net API currently exists which allows multiple DbCommands to be grouped into a single wire protocol level message. This proposal suggests one way to add this functionality.
Design Strategy
Commands are passed in as independent units. Results are returned in similar fashion—as independent units, one unit per command passed in.
In general, the consumer's experience in retrieving results should be (almost) identical to the experience they'd have if they executed each command separately. For example, the consumer should be able to easily determine which result set (or result sets, as a single command can possibly return several) came from which command, affected row counts should be determinable on a per-command basis and the reader-level optimizations achieved by methods like ExecuteScalar as well as CommandBehavior options should be available on a per-command basis (as different optimizations may apply to different commands in the set).
API Proposal Draft
public abstract class DbCommandSet : IDisposable, IAsyncDisposable, IEnumerable<(DbCommand, CommandBehavior)>
{
#region Command-Set Management
// Adds a single command to the set
public abstract void Add(DbCommand command);
public abstract void Add(DbCommand command, CommandBehavior commandBehavior);
// Adds multiple commands to the set
public virtual void AddRange(IEnumerable<DbCommand> commands);
public virtual void AddRange(IEnumerable<DbCommand> commands, CommandBehavior commandBehavior);
public virtual void AddRange(IEnumerable<(DbCommand, CommandBehavior)> commands);
// Clears all commands
public abstract void Clear();
public abstract IEnumerator<(DbCommand, CommandBehavior)> GetEnumerator();
IEnumerator IEnumerable.GetEnumerator() => GetEnumerator();
#endregion
#region Execution
public abstract DbCommandSetResult Execute();
public abstract Task<DbCommandSetResult> ExecuteAsync(CancellationToken cancellationToken = default);
// Shortcut - executes then returns sum of number of rows affected by each command in the set
public abstract int ExecuteNonQuery();
public abstract Task<int> ExecuteNonQueryAsync(CancellationToken cancellationToken = default);
public abstract void Prepare();
public abstract Task PrepareAsync(CancellationToken cancellationToken = default);
public abstract void Cancel();
public abstract Task CancelAsync(CancellationToken cancellationToken = default);
#endregion
// Could be used for any command that does not have a timeout set; or individual command timeouts could be ignored in deference to this timeout.
public abstract int Timeout { get; set; }
// Specifies the connection to be used when executed. (If a connection is set on an individual command and it does not match this connection, possibly an exception should be thrown.)
public abstract DbConnection Connection { get; set; }
// If set, used for all commands in the set. (If a transaction is set on an individual command and it does not match this transaction, possibly an exception should be thrown.)
public abstract DbTransaction Transaction { get; set; }
}
public abstract class DbCommandSetResults : IDisposable, IAsyncDisposable, IEnumerable<DbCommandSetResult> {
public virtual DbDataReader GetReader();
public abstract DbDataReader GetReader(CommandBehavior commandBehavior);
public virtual Task<DbDataReader> GetReaderAsync(CancellationToken cancellationToken = default);
public abstract Task<DbDataReader> GetReaderAsync(CommandBehavior commandBehavior, CancellationToken cancellationToken = default);
public abstract int GetNonQueryResult();
public abstract Task<int> GetNonQueryResultAsync(CancellationToken cancellationToken = default);
public abstract object GetScalarResult();
public abstract Task<object> GetScalarResultAsync(CancellationToken cancellationToken = default);
// Advances DbCommandSetResults to the results associated with the next command in the set.
public abstract bool NextCommandSetResult();
}
Notes
CommandBehaviormay optionally be passed when adding commands to the set and when reading results. Depending on the database server, some behaviors (e.g.KeyInfo,SchemaOnly) affect the query executed, so need to be specified before execution (e.g. when the command is added to the set). Other behaviors may only be of interest to the data reader (for example, so it can optimized reading:SequentialAccess,SingleResult,SingleRow) and so only need to be passed at the time of reading. If a reading-specific behavior is passed when a command is added or an execution-level behavior is passed during reading, an exception should be thrown.
Example
using (var set = new SqlDbCommandSet(connection)) {
set.Add(command1);
set.Add(command2, CommandBehavior.SchemaInfo);
set.Add(otherCommandsEnumerable);
using (var setResults = set.Execute()) {
// Get affected row count from first command
var command1AffectedRows = setResults.GetNonQueryResult();
// Move to second command's results
setResults.NextCommandSetResults();
// Get schema details from results
var command2Schema = setResults.GetColumnSchema();
// Loop through remaining commands' results, processing each
while (setResults.NextCommandSetResults()) {
using (var reader = setResults.GetReader()) {
ProcessReader(reader);
}
}
}
}