mesa-fab-approval/MesaFabApproval.API/Services/DalService.cs

using System.Data;
using System.Text;

using Dapper;

namespace MesaFabApproval.API.Services;

public interface IDalService {
    Task<IEnumerable<T>> QueryAsync<T>(string sql);
    Task<IEnumerable<T>> QueryAsync<T>(string sql, object paramaters);
    Task<int> ExecuteAsync(string sql);
    Task<int> ExecuteAsync<T>(string sql, T paramaters);
}

public class DalService : IDalService {
    private static readonly int RETRIES = 3;
    private static readonly int BACKOFF_SECONDS_INTERVAL = 30;

    private readonly ILogger<DalService> _logger;
    private readonly IDbConnectionService _dbConnectionService;

    public DalService(IDbConnectionService dbConnectionService, ILogger<DalService> logger) {
        _dbConnectionService = dbConnectionService ??
            throw new ArgumentNullException("IDbConnectionService not injected");
        _logger = logger ??
            throw new ArgumentNullException("ILogger not injected");
    }

    public async Task<IEnumerable<T>> QueryAsync<T>(string sql) {
        if (sql is null) throw new ArgumentNullException("sql cannot be null");

        int remainingRetries = RETRIES;
        bool queryWasSuccessful = false;
        Exception exception = null;
        IEnumerable<T> result = new List<T>();
        while (!queryWasSuccessful && remainingRetries > 0) {
            int backoffSeconds = (RETRIES - remainingRetries--) * BACKOFF_SECONDS_INTERVAL;
            Task.Delay(backoffSeconds * 1000).Wait();

            try {
                _logger.LogInformation($"Attempting to perform query with {sql}. Remaining retries: {remainingRetries}");

                using (IDbConnection conn = _dbConnectionService.GetConnection()) {
                    result = await conn.QueryAsync<T>(sql);
                }

                queryWasSuccessful = true;
            } catch (Exception ex) {
                _logger.LogError($"An exception occurred while attempting to perform a query. Exception: {ex.Message}");
                exception = ex;
            }
        }

        if (!queryWasSuccessful && exception is not null) {
            throw exception;
        }

        return result;
    }

    public async Task<IEnumerable<T>> QueryAsync<T>(string sql, object parameters) {
        if (sql is null) throw new ArgumentNullException("sql cannot be null");
        if (parameters is null) throw new ArgumentNullException("parameters cannot be null");

        StringBuilder logBuilder = new();

        int remainingRetries = RETRIES;
        bool queryWasSuccessful = false;
        Exception exception = null;
        IEnumerable<T> result = new List<T>();
        while (!queryWasSuccessful && remainingRetries > 0) {
            int backoffSeconds = (RETRIES - remainingRetries--) * BACKOFF_SECONDS_INTERVAL;
            Task.Delay(backoffSeconds * 1000).Wait();

            try {
                logBuilder.Clear();
                logBuilder.Append($"Attempting to perform query with {sql} ");
                logBuilder.Append($"and parameters {parameters.ToString()}. ");
                logBuilder.Append($"Remaining retries: {remainingRetries}");
                _logger.LogInformation(logBuilder.ToString());

                using (IDbConnection conn = _dbConnectionService.GetConnection()) {
                    result = await conn.QueryAsync<T>(sql, parameters);
                }

                queryWasSuccessful = true;
            } catch (Exception ex) {
                _logger.LogError($"An exception occurred while attempting to perform a query. Exception: {ex.Message}");
                exception = ex;
            }
        }

        if (!queryWasSuccessful && exception is not null) {
            throw exception;
        }

        return result;
    }

    public async Task<int> ExecuteAsync(string sql) {
        if (sql is null) throw new ArgumentNullException("sql cannot be null");

        int remainingRetries = RETRIES;
        bool queryWasSuccessful = false;
        Exception exception = null;
        int rowsAffected = 0;
        while (!queryWasSuccessful && remainingRetries > 0) {
            int backoffSeconds = (RETRIES - remainingRetries--) * BACKOFF_SECONDS_INTERVAL;
            Task.Delay(backoffSeconds * 1000).Wait();

            try {
                _logger.LogInformation($"Attempting to execute {sql}. Remaining retries: {remainingRetries}");

                using (IDbConnection conn = _dbConnectionService.GetConnection()) {
                    rowsAffected = await conn.ExecuteAsync(sql);
                }

                queryWasSuccessful = true;
            } catch (Exception ex) {
                _logger.LogError($"An exception occurred while attempting to execute a query. Exception: {ex.Message}");
                exception = ex;
            }
        }

        if (!queryWasSuccessful && exception is not null) {
            throw exception;
        }

        return rowsAffected;
    }

    public async Task<int> ExecuteAsync<T>(string sql, T parameters) {
        if (sql is null) throw new ArgumentNullException("sql cannot be null");

        int remainingRetries = RETRIES;
        bool queryWasSuccessful = false;
        Exception exception = null;
        int rowsAffected = 0;
        while (!queryWasSuccessful && remainingRetries > 0) {
            int backoffSeconds = (RETRIES - remainingRetries--) * BACKOFF_SECONDS_INTERVAL;
            Task.Delay(backoffSeconds * 1000).Wait();

            try {
                _logger.LogInformation($"Attempting to execute {sql} with parameters. Remaining retries: {remainingRetries}");

                using (IDbConnection conn = _dbConnectionService.GetConnection()) {
                    rowsAffected = await conn.ExecuteAsync(sql, parameters);
                }

                queryWasSuccessful = true;
            } catch (Exception ex) {
                _logger.LogError($"An exception occurred while attempting to execute a query. Exception: {ex.Message}");
                exception = ex;
            }
        }

        if (!queryWasSuccessful && exception is not null) {
            throw exception;
        }

        return rowsAffected;
    }
}