ApplicationContextExtension.cs

using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using Microsoft.Data.SqlClient;
using PLXS.EmailInvoice.Database.DbContext;

namespace Extensions
{
    public static class ApplicationContextExtension
    {
        public static async IAsyncEnumerable<T> ExecuteProcedureAsync<T>(
            this IApplicationContext context,
            string procedure,
            params SqlParameter[] parameters
        ) where T : class, new()
        {
            await using var connection = context.GetConnection();

            await connection.OpenAsync();

            var command = SqlCommand(procedure, parameters, connection);

            await using var reader = await command.ExecuteReaderAsync();

            await foreach (var result in reader.MapperAsync<T>())
            {
                yield return result;
            }
        }

        public static async Task ExecuteNonProcedureAsync(
            this IApplicationContext context,
            string procedure,
            params SqlParameter[] parameters
        )
        {
            await using var connection = context.GetConnection();

            await connection.OpenAsync();

            var command = SqlCommand(procedure, parameters, connection);

            await command.ExecuteNonQueryAsync();
        }

        private static SqlCommand SqlCommand(string procedure, SqlParameter[] parameters, SqlConnection connection)
        {
            var command = new SqlCommand(procedure, connection) { CommandType = CommandType.StoredProcedure };

            if (HasParameters(parameters)) command.Parameters.AddRange(parameters);

            return command;
        }

        private static bool HasParameters(IReadOnlyCollection<SqlParameter> parameters) => parameters.Count > 0;
    }
}