ExpressiveCommandBase.cs

namespace Open.Database.Extensions;

/// <summary>
/// Base class for developing expressive commands.
/// Includes methods for use with IDbConnection and IDbCommand 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 partial class ExpressiveCommandBase<TConnection, TCommand, TReader, TDbType, TThis>
		: IExecuteCommand<TCommand>, IExecuteReader<TReader>
		where TConnection : class, IDbConnection
		where TCommand : class, IDbCommand
		where TReader : class, IDataReader
		where TDbType : struct
		where TThis : ExpressiveCommandBase<TConnection, TCommand, TReader, TDbType, TThis>
{
	/// <summary>
	/// Utility for simplifying param concatenation.
	/// </summary>
	/// <typeparam name="T">The type of the enumerable.</typeparam>
	/// <param name="first">The first value.</param>
	/// <param name="remaining">The remaining values.</param>
	protected static IEnumerable<T> Concat<T>(T first, ICollection<T> remaining)
		=> CoreExtensions.Concat(first, remaining);

	/// <summary>
	/// The connection provider to used to acquire connections.
	/// </summary>
	protected IDbConnectionPool<TConnection> ConnectionProvider { get; }

	/// <summary>
	/// The transaction to execute commands on if not using a connection factory.
	/// </summary>
	protected IDbTransaction? Transaction { get; }

	/// <summary>Constructs a <see cref="ExpressiveCommandBase{TConnection, TCommand, TReader, TDbType, TThis}"/>.</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 ExpressiveCommandBase(
		IDbConnectionPool<TConnection> connectionPool,
		CommandType type,
		string command,
		IEnumerable<Param>? @params)
	{
		ConnectionProvider = connectionPool ?? throw new ArgumentNullException(nameof(connectionPool));
		Command = command ?? throw new ArgumentNullException(nameof(command));
		if (string.IsNullOrWhiteSpace(command)) throw new ArgumentException("Cannot be null or whitespace.", nameof(command));
		Contract.EndContractBlock();

		Type = type;
		Params = @params?.ToList() ?? [];
		Timeout = CommandTimeout.DEFAULT_SECONDS;
	}

	/// <summary>Constructs a <see cref="ExpressiveCommandBase{TConnection, TCommand, TReader, TDbType, TThis}"/>.</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 ExpressiveCommandBase(
		IDbConnectionFactory<TConnection> connFactory,
		CommandType type,
		string command,
		IEnumerable<Param>? @params)
		: this((connFactory ?? throw new ArgumentNullException(nameof(connFactory))).AsPool(), type, command, @params)
	{
	}

	/// <inheritdoc cref="ExpressiveCommandBase(IDbConnectionFactory{TConnection}, CommandType, string, IEnumerable{Param}?)"/>
	protected ExpressiveCommandBase(
		Func<TConnection> connFactory,
		CommandType type,
		string command,
		IEnumerable<Param>? @params)
		: this((connFactory ?? throw new ArgumentNullException(nameof(connFactory))).AsPool(), type, command, @params)
	{
	}

	/// <summary>Constructs a <see cref="ExpressiveCommandBase{TConnection, TCommand, TReader, TDbType, TThis}"/>.</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 ExpressiveCommandBase(
		TConnection connection,
		IDbTransaction? transaction,
		CommandType type,
		string command,
		IEnumerable<Param>? @params)
		: this(DbConnectionProvider.Create(connection), type, command, @params)
		=> Transaction = transaction;

	/// <summary>Constructs a <see cref="ExpressiveCommandBase{TConnection, TCommand, TReader, TDbType, TThis}"/>.</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 ExpressiveCommandBase(
		TConnection connection,
		CommandType type,
		string command,
		IEnumerable<Param>? @params)
		: this(connection, null, type, command, @params)
	{
	}

	/// <summary>Constructs a <see cref="ExpressiveCommandBase{TConnection, TCommand, TReader, TDbType, TThis}"/>.</summary>
	/// <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 ExpressiveCommandBase(
		IDbTransaction transaction,
		CommandType type,
		string command,
		IEnumerable<Param>? @params)
		: this(
			(TConnection)(transaction ?? throw new ArgumentNullException(nameof(transaction))).Connection!,
			transaction, type, command, @params)
	{
	}

	/// <summary>
	/// The command text or procedure name to use.
	/// </summary>
	public string Command { get; set; }

	/// <summary>
	/// The command type.
	/// </summary>
	public CommandType Type { get; set; }

	/// <summary>
	/// The list of params to apply to the command before execution.
	/// </summary>
	public List<Param> Params { get; }

	/// <summary>
	/// The command timeout value.
	/// </summary>
	public ushort Timeout { get; set; }

	/// <summary>
	/// Creates the expected command type from the connection provided.
	/// </summary>
	/// <param name="connection">The connection to create the command from.</param>
	/// <returns>The new command to use.</returns>
	protected TCommand PrepareCommand(TConnection connection)
	{
		IDbCommand cmd = connection.CreateCommand(Type, Command, Timeout);
		if (cmd is not TCommand c)
			throw new InvalidCastException($"Actual command type ({cmd.GetType()}) is not compatible with expected command type ({typeof(TCommand)}).");
		if (Transaction != null)
			c.Transaction = Transaction;
		AddParams(c);
		return c;
	}

	/// <summary>
	/// The optional cancellation token to use with supported methods.
	/// </summary>
	public CancellationToken CancellationToken { get; set; } = CancellationToken.None;

	CancellationToken IExecuteReader.CancellationToken => CancellationToken;

	/// <summary>
	/// Sets the cancellation token.
	/// </summary>
	public TThis UseCancellationToken(CancellationToken token)
	{
		CancellationToken = token;
		return (TThis)this;
	}

	#region AddParam

	/// <summary>
	/// Shortcut to add a parameter to the params list.
	/// </summary>
	protected TThis AddParam(Param param)
	{
		Params.Add(param);
		return (TThis)this;
	}

	/// <summary>
	/// Adds a parameter to the params list.
	/// </summary>
	/// <param name="name">The name of the parameter.</param>
	/// <param name="value">The value of the parameter.</param>
	/// <param name="type">The database type of the parameter.</param>
	/// <param name="direction">The direction of the parameter.</param>
	/// <returns>This instance for use in method chaining.</returns>
	/// <exception cref="ArgumentNullException"><paramref name="name"/> is null.</exception>
	/// <exception cref="ArgumentException"><paramref name="name"/> is blank.</exception>
	public TThis AddParam(string name, object value, TDbType type, ParameterDirection direction = ParameterDirection.Input)
		=> AddParam(new(name, value, type, direction));

	/// <inheritdoc cref="AddParam(string, object, TDbType, ParameterDirection)"/>
	public TThis AddParam(string name, object? value, ParameterDirection direction = ParameterDirection.Input)
		=> AddParam(new(name, value, null, direction));

	/// <inheritdoc cref="AddParam(string, object, TDbType, ParameterDirection)"/>
	public TThis AddParam<T>(string name, T? value, TDbType? type = null, ParameterDirection direction = ParameterDirection.Input)
		where T : struct
		=> AddParam(new(name, value, type, direction));

	/// <inheritdoc cref="AddParam(string, object, TDbType, ParameterDirection)"/>
	public TThis AddParam(string name, string? value, TDbType? type = null, ParameterDirection direction = ParameterDirection.Input)
		=> AddParam(new(name, value, type, direction));

	/// <inheritdoc cref="AddParam(string, object, TDbType, ParameterDirection)"/>
	public TThis AddParam(string name, TDbType type, ParameterDirection direction = ParameterDirection.Input)
		=> AddParam(new(name, null, type, direction));

	/// <inheritdoc cref="AddParam(string, object, TDbType, ParameterDirection)"/>
	public TThis AddParam(string name, ParameterDirection direction = ParameterDirection.Input)
		=> AddParam(new(name, null, null, direction));

	/// <summary>
	/// Adds a return parameter to the params list.
	/// </summary>
	/// <inheritdoc cref="AddParam(string, object, TDbType, ParameterDirection)"/>
	public TThis AddReturnParam(string name, TDbType type)
		=> AddParam(new(name, null, type, ParameterDirection.ReturnValue));

	/// <inheritdoc cref="AddReturnParam(string, TDbType)"/>
	public TThis AddReturnParam(string name)
		=> AddParam(new(name, null, null, ParameterDirection.ReturnValue));

	/// <summary>
	/// Conditionally adds a parameter to the params list.
	/// </summary>
	/// <param name="condition">The condition to add the param by.  Only adds if true.</param>
	/// <param name="name">The name of the parameter.</param>
	/// <param name="value">The value of the parameter.</param>
	/// <param name="type">The database type of the parameter.</param>
	/// <param name="direction">The direction of the parameter.</param>
	/// <inheritdoc cref="AddParam(string, object, TDbType, ParameterDirection)"/>
	public TThis AddParamIf(bool condition, string name, object value, TDbType type, ParameterDirection direction = ParameterDirection.Input)
		=> condition ? AddParam(name, value, type, direction) : (TThis)this;

	/// <inheritdoc cref="AddParamIf(bool, string, object, TDbType, ParameterDirection)"/>
	public TThis AddParamIf<T>(bool condition, string name, T? value, ParameterDirection direction = ParameterDirection.Input)
		where T : struct
		=> condition ? AddParam(name, value, null, direction) : (TThis)this;

	/// <inheritdoc cref="AddParamIf(bool, string, object, TDbType, ParameterDirection)"/>
	public TThis AddParamIf(bool condition, string name, object? value, ParameterDirection direction = ParameterDirection.Input)
		=> condition ? AddParam(name, value, direction) : (TThis)this;

	/// <inheritdoc cref="AddParamIf(bool, string, object, TDbType, ParameterDirection)"/>
	public TThis AddParamIf<T>(bool condition, string name, T? value, TDbType? type, ParameterDirection direction = ParameterDirection.Input)
		where T : struct
		=> condition ? AddParam(name, value, type, direction) : (TThis)this;

	/// <inheritdoc cref="AddParamIf(bool, string, object, TDbType, ParameterDirection)"/>
	public TThis AddParamIf(bool condition, string name, TDbType type, ParameterDirection direction = ParameterDirection.Input)
		=> condition ? AddParam(name, type, direction) : (TThis)this;

	/// <inheritdoc cref="AddParamIf(bool, string, object, TDbType, ParameterDirection)"/>
	public TThis AddParamIf(bool condition, string name, ParameterDirection direction = ParameterDirection.Input)
		=> condition ? AddParam(name, direction) : (TThis)this;

	/// <summary>
	/// Handles adding the list of parameters to a new command.
	/// </summary>
	/// <param name="command">The command to add parameters to.</param>
	protected abstract void AddParams(TCommand command);
	#endregion

	/// <summary>
	/// Sets the timeout value.
	/// </summary>
	/// <param name="seconds">The number of seconds to wait before the connection times out.</param>
	/// <returns>This instance for use in method chaining.</returns>
	public TThis SetTimeout(ushort seconds)
	{
		Timeout = seconds;
		return (TThis)this;
	}

	/// <inheritdoc />
	public void Execute(Action<TCommand> action)
	{
		if (action is null) throw new ArgumentNullException(nameof(action));
		Contract.EndContractBlock();

		// Open MUST occur before command creation as some DbCommands require it.
		ConnectionProvider.Open((conn, _) =>
		{
			using TCommand cmd = PrepareCommand(conn);
			action(cmd);
		});
	}

	/// <inheritdoc />
	public T Execute<T>(Func<TCommand, T> transform)
	{
		if (transform is null) throw new ArgumentNullException(nameof(transform));
		Contract.EndContractBlock();

		// Open MUST occur before command creation as some DbCommands require it.
		return ConnectionProvider.Open((conn, _) =>
		{
			using TCommand cmd = PrepareCommand(conn);
			return transform(cmd);
		});
	}

	/// <inheritdoc />
	public virtual ValueTask ExecuteAsync(Func<TCommand, ValueTask> handler)
	{
		if (handler is null) throw new ArgumentNullException(nameof(handler));
		Contract.EndContractBlock();

		CancellationToken.ThrowIfCancellationRequested(); // Since cancelled awaited tasks throw, we will follow the same pattern here.

		// Open MUST occur before command creation as some DbCommands require it.
		return ConnectionProvider.OpenAsync(async (conn, _) =>
		{
			using TCommand cmd = PrepareCommand(conn);
			await handler(cmd).ConfigureAwait(false);
		});
	}

	/// <inheritdoc />
	public virtual ValueTask<T> ExecuteAsync<T>(Func<TCommand, ValueTask<T>> transform)
	{
		if (transform is null) throw new ArgumentNullException(nameof(transform));
		Contract.EndContractBlock();

		CancellationToken.ThrowIfCancellationRequested(); // Since cancelled awaited tasks throw, we will follow the same pattern here.

		// Open MUST occur before command creation as some DbCommands require it.
		return ConnectionProvider.OpenAsync(async (conn, _) =>
		{
			using TCommand cmd = PrepareCommand(conn);
			return await transform(cmd).ConfigureAwait(false);
		});
	}

	void IExecuteCommand.Execute(Action<IDbCommand> action)
		=> Execute(command => action(command));

	T IExecuteCommand.Execute<T>(Func<IDbCommand, T> transform)
		=> Execute(command => transform(command));

	ValueTask IExecuteCommand.ExecuteAsync(Func<IDbCommand, ValueTask> handler)
		=> ExecuteAsync(command => handler(command));

	ValueTask<T> IExecuteCommand.ExecuteAsync<T>(Func<IDbCommand, ValueTask<T>> transform)
		=> ExecuteAsync(command => transform(command));

	/// <summary>
	/// Validates and properly acquires the expected type of the reader.
	/// </summary>
	/// <typeparam name="TActual">The actual type of the reader.</typeparam>
	/// <param name="reader">The reader to cast.</param>
	/// <returns>The expected reader.</returns>
	protected static TReader EnsureReaderType<TActual>(TActual reader)
		where TActual : IDataReader
		=> reader is TReader r ? r : throw new InvalidCastException($"Expected reader type of ({typeof(TReader)}).  Actual: ({reader.GetType()})");

	/// <inheritdoc />
	public void ExecuteReader(Action<TReader> handler, CommandBehavior behavior = CommandBehavior.Default)
	{
		if (handler is null) throw new ArgumentNullException(nameof(handler));
		Contract.EndContractBlock();

		// Open MUST occur before command creation as some DbCommands require it.
		ConnectionProvider.Open((conn, state) =>
		{
			if (state == ConnectionState.Closed) behavior |= CommandBehavior.CloseConnection;
			using TCommand cmd = PrepareCommand(conn);
			cmd.ExecuteReader(reader => handler(EnsureReaderType(reader)), behavior);
		});
	}

	/// <inheritdoc />
	public T ExecuteReader<T>(Func<TReader, T> transform, CommandBehavior behavior = CommandBehavior.Default)
	{
		if (transform is null) throw new ArgumentNullException(nameof(transform));
		Contract.EndContractBlock();

		return ConnectionProvider.Open((conn, state) =>
		{
			// Open MUST occur before command creation as some DbCommands require it.
			if (state == ConnectionState.Closed) behavior |= CommandBehavior.CloseConnection;
			using TCommand cmd = PrepareCommand(conn);
			return cmd.ExecuteReader(reader => transform(EnsureReaderType(reader)), behavior);
		});
	}

	/// <inheritdoc />
	public ValueTask ExecuteReaderAsync(Action<TReader> handler, CommandBehavior behavior = CommandBehavior.Default)
	{
		return ConnectionProvider.OpenAsync(async (conn, state) =>
		{
			// Open MUST occur before command creation as some DbCommands require it.
			if (state == ConnectionState.Closed) behavior |= CommandBehavior.CloseConnection;
			using TCommand cmd = PrepareCommand(conn);
			await cmd.ExecuteReaderAsync(ExecuteReaderAsyncCore, behavior, CancellationToken).ConfigureAwait(false);
		});

		ValueTask ExecuteReaderAsyncCore(IDataReader reader)
		{
			handler(EnsureReaderType(reader));
			return new ValueTask();
		}
	}

	/// <inheritdoc />
	public ValueTask<T> ExecuteReaderAsync<T>(Func<TReader, T> handler, CommandBehavior behavior = CommandBehavior.Default)
	{
		return ConnectionProvider.OpenAsync(async (conn, state) =>
		{
			// Open MUST occur before command creation as some DbCommands require it.
			if (state == ConnectionState.Closed) behavior |= CommandBehavior.CloseConnection;
			using TCommand cmd = PrepareCommand(conn);
			return await cmd.ExecuteReaderAsync(ExecuteReaderAsyncCore, behavior, CancellationToken).ConfigureAwait(false);
		});

		ValueTask<T> ExecuteReaderAsyncCore(IDataReader reader)
			=> new(handler(EnsureReaderType(reader)));
	}

	/// <inheritdoc />
	public ValueTask ExecuteReaderAsync(Func<TReader, ValueTask> handler, CommandBehavior behavior = CommandBehavior.Default)
		=> ConnectionProvider.OpenAsync(async (conn, state) =>
		{
			// Open MUST occur before command creation as some DbCommands require it.
			if (state == ConnectionState.Closed) behavior |= CommandBehavior.CloseConnection;
			using TCommand cmd = PrepareCommand(conn);
			await cmd.ExecuteReaderAsync(reader => handler(EnsureReaderType(reader)), behavior, CancellationToken).ConfigureAwait(false);
		});

	/// <inheritdoc />
	public ValueTask<T> ExecuteReaderAsync<T>(Func<TReader, ValueTask<T>> handler, CommandBehavior behavior = CommandBehavior.Default)
		=> ConnectionProvider.OpenAsync(async (conn, state) =>
		{
			// Open MUST occur before command creation as some DbCommands require it.
			if (state == ConnectionState.Closed) behavior |= CommandBehavior.CloseConnection;
			using TCommand cmd = PrepareCommand(conn);
			return await cmd.ExecuteReaderAsync(reader => handler(EnsureReaderType(reader)), behavior, CancellationToken).ConfigureAwait(false);
		});

	void IExecuteReader.ExecuteReader(Action<IDataReader> handler, CommandBehavior behavior)
		=> ExecuteReader(reader => handler(reader), behavior);

	T IExecuteReader.ExecuteReader<T>(Func<IDataReader, T> transform, CommandBehavior behavior)
		=> ExecuteReader(reader => transform(reader), behavior);

	ValueTask IExecuteReader.ExecuteReaderAsync(Func<IDataReader, ValueTask> handler, CommandBehavior behavior)
		=> ExecuteReaderAsync(reader => handler(reader), behavior);

	ValueTask<T> IExecuteReader.ExecuteReaderAsync<T>(Func<IDataReader, ValueTask<T>> transform, CommandBehavior behavior)
		=> ExecuteReaderAsync(reader => transform(reader), behavior);

	/// <summary>
	/// Calls ExecuteNonQuery on the underlying command but sets up a return parameter and returns that value.
	/// </summary>
	/// <returns>The value from the return parameter.</returns>
	public object? ExecuteReturn()
		// Open MUST occur before command creation as some DbCommands require it.
		=> ConnectionProvider.Open((conn, _) =>
		{
			using TCommand cmd = PrepareCommand(conn);
			IDbDataParameter returnParameter = cmd.AddReturnParameter();
			cmd.ExecuteNonQuery();
			return returnParameter.Value;
		});

	/// <summary>
	/// Calls ExecuteNonQuery on the underlying command but sets up a return parameter and returns that value.
	/// </summary>
	/// <returns>The value from the return parameter.</returns>
	public T ExecuteReturn<T>()
		=> (T)ExecuteReturn()!;

	/// <summary>
	/// Calls ExecuteNonQueryAsync on the underlying command but sets up a return parameter and returns that value.
	/// </summary>
	/// <returns>The value from the return parameter.</returns>
	public ValueTask<object?> ExecuteReturnAsync()
	{
		CancellationToken.ThrowIfCancellationRequested();

		// Open MUST occur before command creation as some DbCommands require it.
		return ConnectionProvider.OpenAsync(async (conn, _) =>
		{
			using TCommand cmd = PrepareCommand(conn);
			IDbDataParameter returnParameter = cmd.AddReturnParameter();

			if (cmd is DbCommand dbCommand)
				await dbCommand.ExecuteNonQueryAsync(CancellationToken).ConfigureAwait(false);
			else
				cmd.ExecuteNonQuery();

			return returnParameter.Value;
		})!;
	}

	/// <summary>
	/// Calls ExecuteNonQueryAsync on the underlying command but sets up a return parameter and returns that value.
	/// </summary>
	/// <returns>The value from the return parameter.</returns>
	public async ValueTask<T> ExecuteReturnAsync<T>()
		=> (T)(await ExecuteReturnAsync().ConfigureAwait(false))!;

	/// <summary>
	/// Calls ExecuteNonQuery on the underlying command.
	/// </summary>
	/// <returns>The integer response from the method. (Records updated.)</returns>
	public int ExecuteNonQuery()
		=> Execute(command => command.ExecuteNonQuery());

	/// <summary>
	/// Calls ExecuteScalar on the underlying command.
	/// </summary>
	/// <returns>The value returned from the method.</returns>
	public object? ExecuteScalar()
		=> Execute(command => command.ExecuteScalar());

	/// <summary>
	/// Calls ExecuteScalar on the underlying command.
	/// </summary>
	/// <typeparam name="T">The type expected.</typeparam>
	/// <returns>The value returned from the method.</returns>
	public T ExecuteScalar<T>()
		=> (T)ExecuteScalar()!;

	/// <summary>
	/// Calls ExecuteScalar on the underlying command.
	/// </summary>
	/// <typeparam name="T">The type expected.</typeparam>
	/// <returns>The value returned from the method.</returns>
	public T ExecuteScalar<T>(Func<object?, T> transform)
	{
		if (transform is null) throw new ArgumentNullException(nameof(transform));
		Contract.EndContractBlock();

		return transform(ExecuteScalar());
	}
}