ExpressiveDbCommandBase.cs

namespace Open.Database.Extensions;

/// <summary>
/// An base class for executing commands on a database using best practices and simplified expressive syntax.
/// Includes methods for use with DbConnection and DbCommand types.
/// </summary>
/// <typeparam name="TConnection">The type of the connection to be used.</typeparam>
/// <typeparam name="TCommand">The type of the commands generated by the connection.</typeparam>
/// <typeparam name="TReader">The type of reader created by the command.</typeparam>
/// <typeparam name="TDbType">The DB type enum to use for parameters.</typeparam>
/// <typeparam name="TThis">The type of this class in order to facilitate proper expressive notation.</typeparam>
public abstract class ExpressiveDbCommandBase<TConnection, TCommand, TReader, TDbType, TThis>
	: ExpressiveCommandBase<TConnection, TCommand, TReader, TDbType, TThis>, IExecuteReaderAsync<TReader>
	where TConnection : DbConnection
	where TCommand : DbCommand
	where TReader : DbDataReader
	where TDbType : struct
	where TThis : ExpressiveDbCommandBase<TConnection, TCommand, TReader, TDbType, TThis>
{
	/// <summary>Constructs a an expressive command.</summary>
	/// <param name="connectionPool">The pool to acquire connections from.</param>
	/// <param name="type">The command type.</param>
	/// <param name="command">The SQL command.</param>
	/// <param name="params">The list of params</param>
	protected ExpressiveDbCommandBase(
		IDbConnectionPool<TConnection> connectionPool,
		CommandType type,
		string command,
		IEnumerable<Param>? @params = null)
		: base(connectionPool, type, command, @params)
	{
	}

	/// <summary>Constructs a an expressive command.</summary>
	/// <param name="connFactory">The factory to generate connections from.</param>
	/// <param name="type">The command type.</param>
	/// <param name="command">The SQL command.</param>
	/// <param name="params">The list of params</param>
	protected ExpressiveDbCommandBase(
		IDbConnectionFactory<TConnection> connFactory,
		CommandType type,
		string command,
		IEnumerable<Param>? @params = null)
		: base(connFactory, type, command, @params)
	{
	}

	/// <inheritdoc cref="ExpressiveDbCommandBase(IDbConnectionFactory{TConnection}, CommandType, string, IEnumerable{ExpressiveCommandBase{TConnection, TCommand, TReader, TDbType, TThis}.Param}?)"/>
	protected ExpressiveDbCommandBase(
		Func<TConnection> connFactory,
		CommandType type,
		string command,
		IEnumerable<Param>? @params = null)
		: base(connFactory, type, command, @params)
	{
	}

	/// <summary>Constructs a an expressive command.</summary>
	/// <param name="connection">The connection to execute the command on.</param>
	/// <param name="transaction">The optional transaction to execute the command on.</param>
	/// <param name="type">The command type.</param>
	/// <param name="command">The SQL command.</param>
	/// <param name="params">The list of params</param>
	protected ExpressiveDbCommandBase(
		TConnection connection,
		IDbTransaction? transaction,
		CommandType type,
		string command,
		IEnumerable<Param>? @params = null)
		: base(connection, transaction, type, command, @params)
	{
	}

	/// <summary>Constructs a an expressive command.</summary>
	/// <param name="connection">The connection to execute the command on.</param>
	/// <param name="type">The command type.</param>
	/// <param name="command">The SQL command.</param>
	/// <param name="params">The list of params</param>
	protected ExpressiveDbCommandBase(
		TConnection connection,
		CommandType type,
		string command,
		IEnumerable<Param>? @params = null)
		: base(connection, type, command, @params)
	{
	}

	/// <summary>Constructs a an expressive command.</summary>
	/// <param name="transaction">The transaction to execute the command on.</param>
	/// <param name="type">The command type.</param>
	/// <param name="command">The SQL command.</param>
	/// <param name="params">The list of params</param>
	protected ExpressiveDbCommandBase(
		IDbTransaction transaction,
		CommandType type,
		string command,
		IEnumerable<Param>? @params = null)
		: base(transaction, type, command, @params)
	{
	}

	/// <summary>
	/// By default (false), for async methods, the underlying iteration operation for a reader will be .Read() whenever possible.  If set to true, .ReadAsync() will be used.
	/// Using .ReadAsync() can introduce unexpected latency and additional CPU overhead.
	/// This should only be set to true if there is a clear reason why and should be profiled before and after.
	/// </summary>
	public bool UseAsyncRead { get; set; }

	/// <summary>
	/// Sets the UseAsyncRead value.
	/// </summary>
	public TThis EnableAsyncRead(bool value = true)
	{
		UseAsyncRead = value;
		return (TThis)this;
	}

	/// <summary>
	/// Calls ExecuteNonQueryAsync on the underlying command.
	/// </summary>
	/// <returns>The integer response from the method.</returns>
	public ValueTask<int> ExecuteNonQueryAsync()
		=> ExecuteAsync(command => new ValueTask<int>(command.ExecuteNonQueryAsync(CancellationToken)));

	/// <summary>
	/// Calls ExecuteScalarAsync on the underlying command.
	/// </summary>
	/// <returns>The value returned from the method.</returns>
	public ValueTask<object?> ExecuteScalarAsync()
		=> ExecuteAsync(command => new ValueTask<object?>(command.ExecuteScalarAsync(CancellationToken)));

	/// <summary>
	/// Asynchronously executes scalar on the underlying command.
	/// </summary>
	/// <typeparam name="T">The type expected.</typeparam>
	/// <param name="transform">The transform function for the result.</param>
	/// <returns>The value returned from the method.</returns>
	public async ValueTask<T> ExecuteScalarAsync<T>(Func<object?, T> transform)
	{
		if (transform is null) throw new ArgumentNullException(nameof(transform));
		Contract.EndContractBlock();

		return transform(await ExecuteScalarAsync().ConfigureAwait(false));
	}

	/// <summary>
	/// Asynchronously executes scalar on the underlying command and casts to the expected type.
	/// </summary>
	/// <typeparam name="T">The type expected.</typeparam>
	/// <returns>The value returned from the method.</returns>
	public async ValueTask<T> ExecuteScalarAsync<T>()
		=> (T)(await ExecuteScalarAsync().ConfigureAwait(false))!;

	/// <summary>
	/// Asynchronously executes scalar on the underlying command.
	/// </summary>
	/// <typeparam name="T">The type expected.</typeparam>
	/// <param name="transform">The transform function (task) for the result.</param>
	/// <returns>The value returned from the method.</returns>
	public async ValueTask<T> ExecuteScalarAsync<T>(Func<object?, ValueTask<T>> transform)
	{
		if (transform is null) throw new ArgumentNullException(nameof(transform));
		Contract.EndContractBlock();

		return await transform(await ExecuteScalarAsync().ConfigureAwait(false)).ConfigureAwait(false);
	}

	/// <summary>
	/// Asynchronously iterates a IDataReader and returns the each result until the count is met.
	/// </summary>
	/// <typeparam name="T">The return type of the transform function.</typeparam>
	/// <param name="transform">The transform function to process each IDataRecord.</param>
	/// <param name="count">The maximum number of records before complete.</param>
	/// <param name="behavior">The behavior to use with the data reader.</param>
	/// <returns>The value from the transform.</returns>
	public ValueTask<IList<T>> TakeAsync<T>(Func<IDataRecord, T> transform, int count, CommandBehavior behavior = CommandBehavior.Default)
	{
		if (transform is null) throw new ArgumentNullException(nameof(transform));
		if (count < 0) throw new ArgumentOutOfRangeException(nameof(count), count, "Cannot be negative.");
		Contract.EndContractBlock();

		return count == 0
			? new ValueTask<IList<T>>(Array.Empty<T>())
			: TakeAsyncCore();

		async ValueTask<IList<T>> TakeAsyncCore()
		{
			var results = new List<T>();
			await this.IterateReaderWhileAsync(record =>
			{
				results.Add(transform(record));
				return results.Count < count;
			}, behavior).ConfigureAwait(false);
			return results;
		}
	}

	/// <summary>
	/// Reads the first column from every record and returns the results as a list..
	/// <see cref="DBNull"/> values are converted to null.
	/// </summary>
	/// <returns>The list of transformed records.</returns>
	public ValueTask<IEnumerable<object?>> FirstOrdinalResultsAsync()
		=> ExecuteReaderAsync(reader => reader.FirstOrdinalResultsAsync(UseAsyncRead, CancellationToken), CommandBehavior.SequentialAccess | CommandBehavior.SingleResult);

	/// <summary>
	/// Reads the first column from every record..
	/// <see cref="DBNull"/> values are converted to null.
	/// </summary>
	/// <returns>The enumerable of casted values.</returns>
	public ValueTask<IEnumerable<T0>> FirstOrdinalResultsAsync<T0>()
		=> ExecuteReaderAsync(reader => reader.FirstOrdinalResultsAsync<T0>(UseAsyncRead, CancellationToken), CommandBehavior.SequentialAccess | CommandBehavior.SingleResult);

	/// <summary>
	/// Asynchronously iterates all records within the current result set using an IDataReader and returns the desired results.
	/// </summary>
	/// <param name="n">The first ordinal to include in the request to the reader for each record.</param>
	/// <param name="others">The remaining ordinals to request from the reader for each record.</param>
	/// <returns>The QueryResult that contains all the results and the column mappings.</returns>
#if NET8_0_OR_GREATER
	public ValueTask<QueryResultQueue<object[]>> RetrieveAsync(int n, params IEnumerable<int> others)
		=> RetrieveAsync(others.Prepend(n));
#else
	public ValueTask<QueryResultQueue<object[]>> RetrieveAsync(int n, params int[] others)
		=> RetrieveAsync(Concat(n, others));
#endif

	/// <summary>
	/// Iterates all records within the current result set using an IDataReader and returns the desired results.
	/// </summary>
	/// <param name="c">The first column name to include in the request to the reader for each record.</param>
	/// <param name="others">The remaining column names to request from the reader for each record.</param>
	/// <returns>The QueryResult that contains all the results and the column mappings.</returns>
#if NET8_0_OR_GREATER
	public ValueTask<QueryResultQueue<object[]>> RetrieveAsync(string c, params IEnumerable<string> others)
		=> RetrieveAsync(others.Prepend(c));
#else
	public ValueTask<QueryResultQueue<object[]>> RetrieveAsync(string c, params string[] others)
		=> RetrieveAsync(Concat(c, others));
#endif

	/// <summary>
	/// Asynchronously returns all records via a transform function.
	/// </summary>
	/// <param name="transform">The desired column names.</param>
	/// <param name="behavior">The behavior to use with the data reader.</param>
	/// <returns>A task containing the list of results.</returns>
	public async ValueTask<List<T>> ToListAsync<T>(Func<IDataRecord, T> transform, CommandBehavior behavior = CommandBehavior.Default)
	{
		var results = new List<T>();
		await this.IterateReaderAsync(record => results.Add(transform(record)), behavior).ConfigureAwait(false);
		return results;
	}

	/// <summary>
	/// Asynchronously returns all records and iteratively attempts to map the fields to type T.
	/// </summary>
	/// <typeparam name="T">The model type to map the values to (using reflection).</typeparam>
	/// <param name="fieldMappingOverrides">An override map of field names to column names where the keys are the property names, and values are the column names.</param>
	/// <returns>A task containing the list of results.</returns>
	public ValueTask<IEnumerable<T>> ResultsAsync<T>(IEnumerable<KeyValuePair<string, string?>>? fieldMappingOverrides) where T : new()
		=> ResultsAsync<T>(fieldMappingOverrides?.Select(kvp => (kvp.Key, kvp.Value)));

	/// <summary>
	/// Asynchronously returns all records and iteratively attempts to map the fields to type T.
	/// </summary>
	/// <typeparam name="T">The model type to map the values to (using reflection).</typeparam>
	/// <param name="fieldMappingOverrides">An override map of field names to column names where the keys are the property names, and values are the column names.</param>
	/// <returns>A task containing the list of results.</returns>
	public ValueTask<IEnumerable<T>> ResultsAsync<T>(params (string Field, string? Column)[] fieldMappingOverrides) where T : new()
		=> ResultsAsync<T>(fieldMappingOverrides as IEnumerable<(string Field, string? Column)>);

	/// <summary>
	/// Asynchronously iterates all records within the first result set using an IDataReader and returns the results.
	/// </summary>
	/// <returns>The QueryResult that contains all the results and the column mappings.</returns>
	public ValueTask<QueryResultQueue<object[]>> RetrieveAsync()
		=> ExecuteReaderAsync(reader => reader.RetrieveAsync(useReadAsync: UseAsyncRead), CommandBehavior.SingleResult);

	/// <summary>
	/// Asynchronously iterates all records within the current result set using an IDataReader and returns the desired results.
	/// </summary>
	/// <param name="ordinals">The ordinals to request from the reader for each record.</param>
	/// <returns>The QueryResult that contains all the results and the column mappings.</returns>
	public ValueTask<QueryResultQueue<object[]>> RetrieveAsync(IEnumerable<int> ordinals)
		=> ExecuteReaderAsync(reader => reader.RetrieveAsync(ordinals, useReadAsync: UseAsyncRead), CommandBehavior.SingleResult);

	/// <summary>
	/// Iterates all records within the first result set using an IDataReader and returns the desired results as a list of Dictionaries containing only the specified column values.
	/// </summary>
	/// <param name="columnNames">The column names to select.</param>
	/// <param name="normalizeColumnOrder">Orders the results arrays by ordinal.</param>
	/// <returns>The QueryResult that contains all the results and the column mappings.</returns>
	public ValueTask<QueryResultQueue<object[]>> RetrieveAsync(IEnumerable<string> columnNames, bool normalizeColumnOrder = false)
		=> ExecuteReaderAsync(reader => reader.RetrieveAsync(columnNames, normalizeColumnOrder, useReadAsync: UseAsyncRead));

	/// <summary>
	/// Asynchronously returns all records and iteratively attempts to map the fields to type T.
	/// </summary>
	/// <typeparam name="T">The model type to map the values to (using reflection).</typeparam>
	/// <param name="fieldMappingOverrides">An override map of field names to column names where the keys are the property names, and values are the column names.</param>
	/// <returns>A task containing the list of results.</returns>
	public ValueTask<IEnumerable<T>> ResultsAsync<T>(IEnumerable<(string Field, string? Column)>? fieldMappingOverrides)
		where T : new()
		=> ExecuteReaderAsync(reader => reader.ResultsBufferedAsync<T>(fieldMappingOverrides, useReadAsync: UseAsyncRead));
}