DapperService.cs

DapperService.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Runtime.Caching;
using System.Threading.Tasks;

// ReSharper disable once CheckNamespace
namespace Dapper {

    /// <summary>
    /// Wrapper for dapper service calls with many convenience functions.
    /// </summary>
    /// <remarks>
    /// Supports sync/async versions of the following methods:
    /// 
    /// Query -> SQL query that returns rows cast to IEnumerable of generic type.
    /// One -> SQL query that returns exactly one of the generic type or throws exception if SQL returns 0 or more than 1.  Strict and preferred.
    /// First -> SQL query that returns the first of the generic type or default of the type (typically null).
    /// Execute -> SQL query that returns the number of rows affected.
    /// ExecuteScalar -> SQL query that returns the first column of the first row returned cast to generic type.
    /// QueryMultiple -> SQL query that returns a grid reader for working with multiple data sets.
    ///
    /// The following pull the value from cache (by a key) if it exists, or execute the specified query and cache, then return its results:
    /// CacheOrQuery
    /// CacheOrOne
    /// CacheOrFirst
    /// CacheOrExecute
    /// CacheOrExecuteScalar
    /// CacheOrQueryMultiple
    /// 
    /// Includes built-in support for reading SQL from enums with values decorated by SqlAttribute and text files for each of the methods.
    /// Convenience functions for each are included that support calling stored procedures 
    /// Cache times are in minutes.
    /// </remarks>
    public abstract class DapperService {
        protected const int DefaultCacheTime = 360;

        protected ICacheProvider CacheProvider { get; }
        protected string ConnectionString { get; }

        protected DapperService(string connectionString, ICacheProvider cacheProvider = null) {
            CacheProvider = cacheProvider ?? DefaultCacheProvider.LazyCache.Value;
            if (connectionString == null) throw new ArgumentNullException(nameof(connectionString), "Connection string must be specified in service constructor.");
            ConnectionString = connectionString;
        }

        protected SqlConnection GetConnection() => new SqlConnection(ConnectionString);

        private TResult Using<TResult>(Func<SqlConnection, TResult> logic) {
            if (logic == null) throw new ArgumentNullException(nameof(logic), $"Parameter {nameof(logic)} must be provided.");
            using (var connection = GetConnection()) { return logic(connection); }
        }

        private async Task<TResult> UsingAsync<TResult>(Func<SqlConnection, Task<TResult>> logicAsync) {
            if (logicAsync == null) throw new ArgumentNullException(nameof(logicAsync), $"Parameter {nameof(logicAsync)} must be provided.");
            using (var connection = GetConnection()) {
                var task = logicAsync(connection);
                if (task == null) throw new NullReferenceException($"{nameof(logicAsync)} must return a task.");
                return await task;
            }
        }

        /// <summary>
        /// Adds a value-less parameter to a parameter object and returns a chainable DynamicParameters object. Useful for output or return parameters.
        /// </summary>
        /// <typeparam name="T">Type (primitive) of parameter to be added.</typeparam>
        /// <param name="param">The parameters object to append on.</param>
        /// <param name="name">The name of the parameter to add.</param>
        /// <param name="direction">The parameter direction.</param>
        /// <returns>The new parameter object.</returns>
        protected DynamicParameters AddParam<T>(object param, string name, ParameterDirection direction = ParameterDirection.Input) {
            var parameters = new DynamicParameters(param);
            parameters.Add(name, dbType: ConvertTypeToDbType<T>(), direction: direction);
            return parameters;
        }

        /// <summary>
        /// Adds a parameter to a parameter object and returns a chainable DynamicParameters object.
        /// </summary>
        /// <typeparam name="T">Type (primitive) of parameter to be added.</typeparam>
        /// <param name="param">The parameters object to append on.</param>
        /// <param name="name">The name of the parameter to add.</param>
        /// <param name="value">The value of the parameter to add.</param>
        /// <param name="direction">The parameter direction.</param>
        /// <returns>The new parameter object.</returns>
        protected DynamicParameters AddParam<T>(object param, string name, T value = default(T), ParameterDirection direction = ParameterDirection.Input) {
            var parameters = new DynamicParameters(param);
            parameters.Add(name, value, ConvertTypeToDbType<T>(), direction);
            return parameters;
        }

        /// <summary>
        /// Wraps sql execution logic with function to get a parameter object, inject it into the sql, and return an output or return parameter from the object.
        /// Use this only if the result of the SQL logic doesn't matter and you just need the return or output parameter back.
        /// </summary>
        /// <typeparam name="T">Type (primitive) of the return or output parameter.</typeparam>
        /// <param name="paramName">Name of the output or return parameter.</param>
        /// <param name="getParam">Logic to build the parameters to be injected.</param>
        /// <param name="sqlLogic">Logic to execute the SQL query, receives the injected parameter.</param>
        /// <returns>The output or return parameter specified.</returns>
        protected T WrapGetParam<T>(string paramName, Func<DynamicParameters> getParam, Action<object> sqlLogic) {
            if (paramName == null) throw new ArgumentNullException(nameof(paramName));
            if (getParam == null) throw new ArgumentNullException(nameof(getParam));
            if (sqlLogic == null) throw new ArgumentNullException(nameof(sqlLogic));
            var param = getParam();
            if (param == null) throw new NullReferenceException($"Argument {nameof(getParam)} must return a non-null parameter object.");
            sqlLogic(param);
            return param.Get<T>(paramName);
        }

        /// <summary>
        /// Wraps async sql execution logic with function to get a parameter object, inject it into the sql, and return an output or return parameter from the object.
        /// Use this only if the result of the SQL logic doesn't matter and you just need the return or output parameter back.
        /// </summary>
        /// <typeparam name="T">Type (primitive) of the return or output parameter.</typeparam>
        /// <param name="paramName">Name of the output or return parameter.</param>
        /// <param name="getParam">Logic to build the parameters to be injected.</param>
        /// <param name="sqlLogicAsync">Logic to execute the async SQL query, receives the injected parameter.</param>
        /// <returns>The output or return parameter specified.</returns>
        protected async Task<T> WrapGetParamAsync<T>(string paramName, Func<DynamicParameters> getParam, Func<object, Task> sqlLogicAsync) {
            if (paramName == null) throw new ArgumentNullException(nameof(paramName));
            if (getParam == null) throw new ArgumentNullException(nameof(getParam));
            if (sqlLogicAsync == null) throw new ArgumentNullException(nameof(sqlLogicAsync));
            var param = getParam();
            if (param == null) throw new NullReferenceException($"Argument {nameof(getParam)} must return a non-null parameter object.");
            await sqlLogicAsync(param);
            return param.Get<T>(paramName);
        }

        #region Sync Wrappers

        public T CacheOrChain<T>(string key, Func<T> operation, int cacheTime = DefaultCacheTime) where T : class
            => CacheProvider.GetWithRefresh(key, operation, cacheTime);

        // Query Start
        protected IEnumerable<T> Query<T>(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
            => Using(connection => connection.Query<T>(sql, param, transaction, buffered, commandTimeout, commandType));


        protected IEnumerable<T> Query<T>(Enum sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
            => Query<T>(sql.GetSql(), param, transaction, buffered, commandTimeout, commandType);

        protected IEnumerable<T> QuerySProc<T>(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null)
            => Query<T>(sql, param, transaction, buffered, commandTimeout, CommandType.StoredProcedure);

        protected IEnumerable<T> QuerySProc<T>(Enum sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null)
            => QuerySProc<T>(sql.GetSql(), param, transaction, buffered, commandTimeout);
        // Query End

        // One Start -> These return the first value and throw if exactly one value is not returned.  Stricter and good for keeping sql efficient.
        protected T One<T>(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
            => Query<T>(sql, param, transaction, buffered, commandTimeout, commandType).Single();

        protected T One<T>(Enum sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
            => Query<T>(sql, param, transaction, buffered, commandTimeout, commandType).Single();

        protected T OneSProc<T>(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null)
            => QuerySProc<T>(sql, param, transaction, buffered, commandTimeout).Single();

        protected T OneSProc<T>(Enum sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null)
            => QuerySProc<T>(sql, param, transaction, buffered, commandTimeout).Single();
        // One End

        // First Start -> These return the first or default value (null typically)
        protected T First<T>(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
            => Query<T>(sql, param, transaction, buffered, commandTimeout, commandType).FirstOrDefault();

        protected T First<T>(Enum sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
            => Query<T>(sql, param, transaction, buffered, commandTimeout, commandType).FirstOrDefault();

        protected T FirstSProc<T>(string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null)
            => QuerySProc<T>(sql, param, transaction, buffered, commandTimeout).FirstOrDefault();

        protected T FirstSProc<T>(Enum sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null)
            => QuerySProc<T>(sql, param, transaction, buffered, commandTimeout).FirstOrDefault();
        // First End

        // CacheOrQuery Start
        protected IEnumerable<T> CacheOrQuery<T>(string key, string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null, int cacheTime = DefaultCacheTime) where T : class
            => CacheProvider.GetWithRefresh(key, () => Query<T>(sql, param, transaction, buffered, commandTimeout, commandType), cacheTime);

        protected IEnumerable<T> CacheOrQuery<T>(string key, Enum sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null, int cacheTime = DefaultCacheTime) where T : class
            => CacheProvider.GetWithRefresh(key, () => Query<T>(sql, param, transaction, buffered, commandTimeout, commandType), cacheTime);

        protected IEnumerable<T> CacheOrQuerySProc<T>(string key, string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, int cacheTime = DefaultCacheTime) where T : class
            => CacheProvider.GetWithRefresh(key, () => QuerySProc<T>(sql, param, transaction, buffered, commandTimeout), cacheTime);

        protected IEnumerable<T> CacheOrQuerySProc<T>(string key, Enum sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, int cacheTime = DefaultCacheTime) where T : class
            => CacheProvider.GetWithRefresh(key, () => QuerySProc<T>(sql, param, transaction, buffered, commandTimeout), cacheTime);
        // CacheOrQuery End

        // CacheOrOne Start
        protected T CacheOrOne<T>(string key, string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null, int cacheTime = DefaultCacheTime) where T : class
            => CacheProvider.GetWithRefresh(key, () => One<T>(sql, param, transaction, buffered, commandTimeout, commandType), cacheTime);

        protected T CacheOrOne<T>(string key, Enum sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null, int cacheTime = DefaultCacheTime) where T : class
            => CacheProvider.GetWithRefresh(key, () => One<T>(sql, param, transaction, buffered, commandTimeout, commandType), cacheTime);

        protected T CacheOrOneSProc<T>(string key, string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, int cacheTime = DefaultCacheTime) where T : class
            => CacheProvider.GetWithRefresh(key, () => OneSProc<T>(sql, param, transaction, buffered, commandTimeout), cacheTime);

        protected T CacheOrOneSProc<T>(string key, Enum sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, int cacheTime = DefaultCacheTime) where T : class
            => CacheProvider.GetWithRefresh(key, () => OneSProc<T>(sql, param, transaction, buffered, commandTimeout), cacheTime);
        // CacheOrOne End

        // CacheOrFirst Start
        protected T CacheOrFirst<T>(string key, string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null, int cacheTime = DefaultCacheTime) where T : class
            => CacheProvider.GetWithRefresh(key, () => First<T>(sql, param, transaction, buffered, commandTimeout, commandType), cacheTime);

        protected T CacheOrFirst<T>(string key, Enum sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null, int cacheTime = DefaultCacheTime) where T : class
            => CacheProvider.GetWithRefresh(key, () => First<T>(sql, param, transaction, buffered, commandTimeout, commandType), cacheTime);

        protected T CacheOrFirstSProc<T>(string key, string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, int cacheTime = DefaultCacheTime) where T : class
            => CacheProvider.GetWithRefresh(key, () => FirstSProc<T>(sql, param, transaction, buffered, commandTimeout), cacheTime);

        protected T CacheOrFirstSProc<T>(string key, Enum sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, int cacheTime = DefaultCacheTime) where T : class
            => CacheProvider.GetWithRefresh(key, () => FirstSProc<T>(sql, param, transaction, buffered, commandTimeout), cacheTime);
        // CacheOrFirst End


        // Execute Start
        protected int Execute(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
            => Using(connection => connection.Execute(sql, param, transaction, commandTimeout, commandType));

        protected int Execute(Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
            => Execute(sql.GetSql(), param, transaction, commandTimeout, commandType);

        protected int ExecuteSProc(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null)
            => Execute(sql, param, transaction, commandTimeout, CommandType.StoredProcedure);

        protected int ExecuteSProc(Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null)
            => ExecuteSProc(sql.GetSql(), param, transaction, commandTimeout);
        // Execute End



        // Execute (with return param) Start
        protected T Execute<T>(string sql, string returnParamName, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null, ParameterDirection returnParamDirection = ParameterDirection.ReturnValue)
            => WrapGetParam<T>(returnParamName, () => AddParam<T>(param, returnParamName, returnParamDirection), p => Execute(sql, p, transaction, commandTimeout, commandType));

        protected T Execute<T>(Enum sql, string returnParamName, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null, ParameterDirection returnParamDirection = ParameterDirection.ReturnValue)
            => Execute<T>(sql.GetSql(), returnParamName, param, transaction, commandTimeout, commandType, returnParamDirection);

        protected T ExecuteSProc<T>(string sql, string returnParamName, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, ParameterDirection returnParamDirection = ParameterDirection.ReturnValue)
            => Execute<T>(sql, returnParamName, param, transaction, commandTimeout, CommandType.StoredProcedure, returnParamDirection);

        protected T ExecuteSProc<T>(Enum sql, string returnParamName, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, ParameterDirection returnParamDirection = ParameterDirection.ReturnValue)
            => Execute<T>(sql.GetSql(), returnParamName, param, transaction, commandTimeout, CommandType.StoredProcedure, returnParamDirection);

        // Execute (with return param) End

        // CacheOrExecute (with return param) Start
        protected T CacheOrExecute<T>(string key, string sql, string returnParamName, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null, ParameterDirection returnParamDirection = ParameterDirection.ReturnValue, int cacheTime = DefaultCacheTime) where T : class
            => CacheProvider.GetWithRefresh(key, () => Execute<T>(sql, returnParamName, param, transaction, commandTimeout, commandType, returnParamDirection), cacheTime);

        protected T CacheOrExecute<T>(string key, Enum sql, string returnParamName, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null, ParameterDirection returnParamDirection = ParameterDirection.ReturnValue, int cacheTime = DefaultCacheTime) where T : class
            => CacheOrExecute<T>(key, sql.GetSql(), returnParamName, param, transaction, commandTimeout, commandType, returnParamDirection, cacheTime);

        protected T CacheOrExecuteSProc<T>(string key, string sql, string returnParamName, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, ParameterDirection returnParamDirection = ParameterDirection.ReturnValue, int cacheTime = DefaultCacheTime) where T : class
            => CacheOrExecute<T>(key, sql, returnParamName, param, transaction, commandTimeout, CommandType.StoredProcedure, returnParamDirection, cacheTime);

        protected T CacheOrExecuteSProc<T>(string key, Enum sql, string returnParamName, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, ParameterDirection returnParamDirection = ParameterDirection.ReturnValue, int cacheTime = DefaultCacheTime) where T : class
            => CacheOrExecute<T>(key, sql.GetSql(), returnParamName, param, transaction, commandTimeout, CommandType.StoredProcedure, returnParamDirection, cacheTime);

        // CacheOrExecute (with return param) End

        // ExecuteScalar Start
        protected T ExecuteScalar<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
            => Using(connection => connection.ExecuteScalar<T>(sql, param, transaction, commandTimeout, commandType));

        protected T ExecuteScalar<T>(Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
            => ExecuteScalar<T>(sql.GetSql(), param, transaction, commandTimeout, commandType);

        protected T ExecuteScalarSProc<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null)
            => ExecuteScalar<T>(sql, param, transaction, commandTimeout, CommandType.StoredProcedure);

        protected T ExecuteScalarSProc<T>(Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null)
            => ExecuteScalarSProc<T>(sql.GetSql(), param, transaction, commandTimeout);

        // ExecuteScalar End

        // CacheOrExecuteScalar Start
        protected T CacheOrExecuteScalar<T>(string key, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null, int cacheTime = DefaultCacheTime) where T : class
            => CacheProvider.GetWithRefresh(key, () => ExecuteScalar<T>(sql, param, transaction, commandTimeout, commandType), cacheTime);

        protected T CacheOrExecuteScalar<T>(string key, Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null, int cacheTime = DefaultCacheTime) where T : class
            => CacheOrExecuteScalar<T>(key, sql.GetSql(), param, transaction, commandTimeout, commandType, cacheTime);

        protected T CacheOrExecuteScalarSProc<T>(string key, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, int cacheTime = DefaultCacheTime) where T : class
            => CacheOrExecuteScalar<T>(key, sql, param, transaction, commandTimeout, CommandType.StoredProcedure, cacheTime);

        protected T CacheOrExecuteScalarSProc<T>(string key, Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, int cacheTime = DefaultCacheTime) where T : class
            => CacheOrExecuteScalarSProc<T>(key, sql.GetSql(), param, transaction, commandTimeout, cacheTime);

        // CacheOrExecuteScalar End

        // QueryMultiple Start
        protected SqlMapper.GridReader QueryMultiple(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
            => Using(connection => connection.QueryMultiple(sql, param, transaction, commandTimeout, commandType));

        protected SqlMapper.GridReader QueryMultiple(Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
            => QueryMultiple(sql.GetSql(), param, transaction, commandTimeout, commandType);

        protected SqlMapper.GridReader QueryMultipleSProc(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null)
            => QueryMultiple(sql, param, transaction, commandTimeout, CommandType.StoredProcedure);

        protected SqlMapper.GridReader QueryMultipleSProc(Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null)
            => QueryMultipleSProc(sql.GetSql(), param, transaction, commandTimeout);

        // QueryMultiple End

        // CacheOrQueryMultiple Start
        protected SqlMapper.GridReader CacheOrQueryMultiple(string key, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null, int cacheTime = DefaultCacheTime)
            => CacheProvider.GetWithRefresh(key, () => QueryMultiple(sql, param, transaction, commandTimeout, commandType), cacheTime);

        protected SqlMapper.GridReader CacheOrQueryMultiple(string key, Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null, int cacheTime = DefaultCacheTime)
            => CacheOrQueryMultiple(key, sql.GetSql(), param, transaction, commandTimeout, commandType, cacheTime);

        protected SqlMapper.GridReader CacheOrQueryMultipleSProc(string key, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, int cacheTime = DefaultCacheTime)
            => CacheOrQueryMultiple(key, sql, param, transaction, commandTimeout, CommandType.StoredProcedure, cacheTime);

        protected SqlMapper.GridReader CacheOrQueryMultipleSProc(string key, Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, int cacheTime = DefaultCacheTime)
            => CacheOrQueryMultipleSProc(key, sql.GetSql(), param, transaction, commandTimeout, cacheTime);

        // CacheOrQueryMultiple End

        #endregion

        #region Async wrappers

        public async Task<T> CacheOrChainAsync<T>(string key, Func<Task<T>> operationAsync, int cacheTime = DefaultCacheTime) where T : class
            => await CacheProvider.GetWithRefreshAsync(key, operationAsync, cacheTime);

        // QueryAsync Start
        protected async Task<IEnumerable<T>> QueryAsync<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
            => await UsingAsync(async connection => await connection.QueryAsync<T>(sql, param, transaction, commandTimeout, commandType));

        protected async Task<IEnumerable<T>> QueryAsync<T>(Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
            => await QueryAsync<T>(sql.GetSql(), param, transaction, commandTimeout, commandType);

        protected async Task<IEnumerable<T>> QuerySProcAsync<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null)
            => await QueryAsync<T>(sql, param, transaction, commandTimeout, CommandType.StoredProcedure);

        protected async Task<IEnumerable<T>> QuerySProcAsync<T>(Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null)
            => await QuerySProcAsync<T>(sql.GetSql(), param, transaction, commandTimeout);
        // QueryAsync End

        // OneAsync Start
        protected async Task<T> OneAsync<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
            => (await QueryAsync<T>(sql, param, transaction, commandTimeout, commandType)).Single();

        protected async Task<T> OneAsync<T>(Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
            => (await QueryAsync<T>(sql, param, transaction, commandTimeout, commandType)).Single();

        protected async Task<T> OneSProcAsync<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null)
            => (await QuerySProcAsync<T>(sql, param, transaction, commandTimeout)).Single();

        protected async Task<T> OneSProcAsync<T>(Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null)
            => (await QuerySProcAsync<T>(sql, param, transaction, commandTimeout)).Single();
        // OneAsync End

        // FirstAsync Start
        protected async Task<T> FirstAsync<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
            => (await QueryAsync<T>(sql, param, transaction, commandTimeout, commandType)).FirstOrDefault();

        protected async Task<T> FirstAsync<T>(Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
            => (await QueryAsync<T>(sql, param, transaction, commandTimeout, commandType)).FirstOrDefault();

        protected async Task<T> FirstSProcAsync<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null)
            => (await QuerySProcAsync<T>(sql, param, transaction, commandTimeout)).FirstOrDefault();

        protected async Task<T> FirstSProcAsync<T>(Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null)
            => (await QuerySProcAsync<T>(sql, param, transaction, commandTimeout)).FirstOrDefault();
        // FirstAsync End

        // CacheOrQueryAsync Start
        protected async Task<IEnumerable<T>> CacheOrQueryAsync<T>(string key, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null, int cacheTime = DefaultCacheTime) where T : class
            => await CacheProvider.GetWithRefreshAsync(key, async () => await QueryAsync<T>(sql, param, transaction, commandTimeout, commandType), cacheTime);

        protected async Task<IEnumerable<T>> CacheOrQueryAsync<T>(string key, Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null, int cacheTime = DefaultCacheTime) where T : class
            => await CacheProvider.GetWithRefreshAsync(key, async () => await QueryAsync<T>(sql, param, transaction, commandTimeout, commandType), cacheTime);

        protected async Task<IEnumerable<T>> CacheOrQuerySProcAsync<T>(string key, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, int cacheTime = DefaultCacheTime) where T : class
            => await CacheProvider.GetWithRefreshAsync(key, async () => await QuerySProcAsync<T>(sql, param, transaction, commandTimeout), cacheTime);

        protected async Task<IEnumerable<T>> CacheOrQuerySProcAsync<T>(string key, Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, int cacheTime = DefaultCacheTime) where T : class
            => await CacheProvider.GetWithRefreshAsync(key, async () => await QuerySProcAsync<T>(sql, param, transaction, commandTimeout), cacheTime);
        // CacheOrQueryAsync End

        // CacheOrOneAsync Start
        protected async Task<T> CacheOrOneAsync<T>(string key, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null, int cacheTime = DefaultCacheTime) where T : class
            => await CacheProvider.GetWithRefresh(key, async () => await OneAsync<T>(sql, param, transaction, commandTimeout, commandType), cacheTime);

        protected async Task<T> CacheOrOneAsync<T>(string key, Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null, int cacheTime = DefaultCacheTime) where T : class
            => await CacheProvider.GetWithRefresh(key, async () => await OneAsync<T>(sql, param, transaction, commandTimeout, commandType), cacheTime);

        protected async Task<T> CacheOrOneSProcAsync<T>(string key, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, int cacheTime = DefaultCacheTime) where T : class
            => await CacheProvider.GetWithRefresh(key, async () => await OneSProcAsync<T>(sql, param, transaction, commandTimeout), cacheTime);

        protected async Task<T> CacheOrOneSProcAsync<T>(string key, Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, int cacheTime = DefaultCacheTime) where T : class
            => await CacheProvider.GetWithRefresh(key, async () => await OneSProcAsync<T>(sql, param, transaction, commandTimeout), cacheTime);
        // CacheOrOneAsync End

        // CacheOrFirstAsync Start
        protected async Task<T> CacheOrFirstAsync<T>(string key, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null, int cacheTime = DefaultCacheTime) where T : class
            => await CacheProvider.GetWithRefresh(key, async () => await FirstAsync<T>(sql, param, transaction, commandTimeout, commandType), cacheTime);

        protected async Task<T> CacheOrFirstAsync<T>(string key, Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null, int cacheTime = DefaultCacheTime) where T : class
            => await CacheProvider.GetWithRefresh(key, async () => await FirstAsync<T>(sql, param, transaction, commandTimeout, commandType), cacheTime);

        protected async Task<T> CacheOrFirstSProcAsync<T>(string key, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, int cacheTime = DefaultCacheTime) where T : class
            => await CacheProvider.GetWithRefresh(key, async () => await FirstSProcAsync<T>(sql, param, transaction, commandTimeout), cacheTime);

        protected async Task<T> CacheOrFirstSProcAsync<T>(string key, Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, int cacheTime = DefaultCacheTime) where T : class
            => await CacheProvider.GetWithRefresh(key, async () => await FirstSProcAsync<T>(sql, param, transaction, commandTimeout), cacheTime);
        // CacheOrFirstAsync End

        // ExecuteAsync Start
        protected async Task<int> ExecuteAsync(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
            => await UsingAsync(async connection => await connection.ExecuteAsync(sql, param, transaction, commandTimeout, commandType));

        protected async Task<int> ExecuteAsync(Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
            => await ExecuteAsync(sql.GetSql(), param, transaction, commandTimeout, commandType);

        protected async Task<int> ExecuteSProcAsync(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null)
            => await ExecuteAsync(sql, param, transaction, commandTimeout, CommandType.StoredProcedure);

        protected async Task<int> ExecuteSProcAsync(Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null)
            => await ExecuteSProcAsync(sql.GetSql(), param, transaction, commandTimeout);
        // ExecuteAsync End

        // ExecuteAsync (with return param) Start
        protected async Task<T> ExecuteAsync<T>(string sql, string returnParamName, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null, ParameterDirection returnParamDirection = ParameterDirection.ReturnValue)
            => await WrapGetParamAsync<T>(returnParamName, () => AddParam<T>(param, returnParamName, returnParamDirection), async p => await ExecuteAsync(sql, p, transaction, commandTimeout, commandType));

        protected async Task<T> ExecuteAsync<T>(Enum sql, string returnParamName, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null, ParameterDirection returnParamDirection = ParameterDirection.ReturnValue)
            => await ExecuteAsync<T>(sql.GetSql(), returnParamName, param, transaction, commandTimeout, commandType, returnParamDirection);

        protected async Task<T> ExecuteSProcAsync<T>(string sql, string returnParamName, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, ParameterDirection returnParamDirection = ParameterDirection.ReturnValue)
            => await ExecuteAsync<T>(sql, returnParamName, param, transaction, commandTimeout, CommandType.StoredProcedure, returnParamDirection);

        protected async Task<T> ExecuteSProcAsync<T>(Enum sql, string returnParamName, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, ParameterDirection returnParamDirection = ParameterDirection.ReturnValue)
            => await ExecuteAsync<T>(sql.GetSql(), returnParamName, param, transaction, commandTimeout, CommandType.StoredProcedure, returnParamDirection);
        // ExecuteAsync (with return param) End

        // CacheOrExecuteAsync (with return param) Start
        protected async Task<T> CacheOrExecuteAsync<T>(string key, string sql, string returnParamName, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null, ParameterDirection returnParamDirection = ParameterDirection.ReturnValue, int cacheTime = DefaultCacheTime) where T : class
            => await CacheProvider.GetWithRefreshAsync(key, async () => await ExecuteAsync<T>(sql, returnParamName, param, transaction, commandTimeout, commandType, returnParamDirection), cacheTime);

        protected async Task<T> CacheOrExecuteAsync<T>(string key, Enum sql, string returnParamName, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null, ParameterDirection returnParamDirection = ParameterDirection.ReturnValue, int cacheTime = DefaultCacheTime) where T : class
            => await CacheOrExecuteAsync<T>(key, sql.GetSql(), returnParamName, param, transaction, commandTimeout, commandType, returnParamDirection, cacheTime);

        protected async Task<T> CacheOrExecuteSProcAsync<T>(string key, string sql, string returnParamName, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, ParameterDirection returnParamDirection = ParameterDirection.ReturnValue, int cacheTime = DefaultCacheTime) where T : class
            => await CacheOrExecuteAsync<T>(key, sql, returnParamName, param, transaction, commandTimeout, CommandType.StoredProcedure, returnParamDirection, cacheTime);

        protected async Task<T> CacheOrExecuteSProcAsync<T>(string key, Enum sql, string returnParamName, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, ParameterDirection returnParamDirection = ParameterDirection.ReturnValue, int cacheTime = DefaultCacheTime) where T : class
            => await CacheOrExecuteAsync<T>(key, sql.GetSql(), returnParamName, param, transaction, commandTimeout, CommandType.StoredProcedure, returnParamDirection, cacheTime);
        // CacheOrExecuteAsync (with return param) End

        // ExecuteScalarAsync Start
        protected async Task<T> ExecuteScalarAsync<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
            => await UsingAsync(async connection => await connection.ExecuteScalarAsync<T>(sql, param, transaction, commandTimeout, commandType));

        protected async Task<T> ExecuteScalarAsync<T>(Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
            => await ExecuteScalarAsync<T>(sql.GetSql(), param, transaction, commandTimeout, commandType);

        protected async Task<T> ExecuteScalarSProcAsync<T>(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null)
            => await ExecuteScalarAsync<T>(sql, param, transaction, commandTimeout, CommandType.StoredProcedure);

        protected async Task<T> ExecuteScalarSProcAsync<T>(Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null)
            => await ExecuteScalarSProcAsync<T>(sql.GetSql(), param, transaction, commandTimeout);
        // ExecuteScalarAsync End

        // CacheOrExecuteScalarAsync Start
        protected async Task<T> CacheOrExecuteScalarAsync<T>(string key, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null, int cacheTime = DefaultCacheTime) where T : class
            => await CacheProvider.GetWithRefreshAsync(key, async () => await ExecuteScalarAsync<T>(sql, param, transaction, commandTimeout, commandType), cacheTime);

        protected async Task<T> CacheOrExecuteScalarAsync<T>(string key, Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null, int cacheTime = DefaultCacheTime) where T : class
            => await CacheOrExecuteScalarAsync<T>(key, sql.GetSql(), param, transaction, commandTimeout, commandType, cacheTime);

        protected async Task<T> CacheOrExecuteScalarSProcAsync<T>(string key, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, int cacheTime = DefaultCacheTime) where T : class
            => await CacheOrExecuteScalarAsync<T>(key, sql, param, transaction, commandTimeout, CommandType.StoredProcedure, cacheTime);

        protected async Task<T> CacheOrExecuteScalarSProcAsync<T>(string key, Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, int cacheTime = DefaultCacheTime) where T : class
            => await CacheOrExecuteScalarSProcAsync<T>(key, sql.GetSql(), param, transaction, commandTimeout, cacheTime);
        // CacheOrExecuteScalarAsync End

        // QueryMultipleAsync Start
        protected async Task<SqlMapper.GridReader> QueryMultipleAsync(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
            => await UsingAsync(async connection => await connection.QueryMultipleAsync(sql, param, transaction, commandTimeout, commandType));

        protected async Task<SqlMapper.GridReader> QueryMultipleAsync(Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
            => await QueryMultipleAsync(sql.GetSql(), param, transaction, commandTimeout, commandType);

        protected async Task<SqlMapper.GridReader> QueryMultipleSProcAsync(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null)
            => await QueryMultipleAsync(sql, param, transaction, commandTimeout, CommandType.StoredProcedure);

        protected async Task<SqlMapper.GridReader> QueryMultipleSProcAsync(Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null)
            => await QueryMultipleSProcAsync(sql.GetSql(), param, transaction, commandTimeout);
        // QueryMultipleAsync End

        // CacheOrQueryMultipleAsync Start
        protected async Task<SqlMapper.GridReader> CacheOrQueryMultipleAsync(string key, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null, int cacheTime = DefaultCacheTime)
            => await CacheProvider.GetWithRefreshAsync(key, async () => await QueryMultipleAsync(sql, param, transaction, commandTimeout, commandType), cacheTime);

        protected async Task<SqlMapper.GridReader> CacheOrQueryMultipleAsync(string key, Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null, int cacheTime = DefaultCacheTime)
            => await CacheOrQueryMultipleAsync(key, sql.GetSql(), param, transaction, commandTimeout, commandType, cacheTime);

        protected async Task<SqlMapper.GridReader> CacheOrQueryMultipleSProcAsync(string key, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, int cacheTime = DefaultCacheTime)
            => await CacheOrQueryMultipleAsync(key, sql, param, transaction, commandTimeout, CommandType.StoredProcedure, cacheTime);

        protected async Task<SqlMapper.GridReader> CacheOrQueryMultipleSProcAsync(string key, Enum sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, int cacheTime = DefaultCacheTime)
            => await CacheOrQueryMultipleSProcAsync(key, sql.GetSql(), param, transaction, commandTimeout, cacheTime);
        // CacheOrQueryMultipleAsync End

        #endregion

        public static Type ConvertDbTypeToType(DbType type) => GetTypeFromTypeCode(ConvertDbTypeToTypeCode(type));
        public static Type GetTypeFromTypeCode(TypeCode typeCode) => typeCode == TypeCode.Empty ? null : Type.GetType("System." + typeCode);

        public static TypeCode ConvertDbTypeToTypeCode(DbType dbType) {
            switch (dbType) {
                case DbType.AnsiString:
                case DbType.AnsiStringFixedLength:
                case DbType.String:
                case DbType.StringFixedLength:
                    return TypeCode.String;
                case DbType.Boolean:
                    return TypeCode.Boolean;
                case DbType.Byte:
                    return TypeCode.Byte;
                case DbType.VarNumeric: // ???
                case DbType.Currency:
                case DbType.Decimal:
                    return TypeCode.Decimal;
                case DbType.Date:
                case DbType.DateTime:
                case DbType.DateTime2: // new Katmai type
                case DbType.Time: // new Katmai type - no TypeCode for TimeSpan
                    return TypeCode.DateTime;
                case DbType.Double:
                    return TypeCode.Double;
                case DbType.Int16:
                    return TypeCode.Int16;
                case DbType.Int32:
                    return TypeCode.Int32;
                case DbType.Int64:
                    return TypeCode.Int64;
                case DbType.SByte:
                    return TypeCode.SByte;
                case DbType.Single:
                    return TypeCode.Single;
                case DbType.UInt16:
                    return TypeCode.UInt16;
                case DbType.UInt32:
                    return TypeCode.UInt32;
                case DbType.UInt64:
                    return TypeCode.UInt64;
                default:
                    return TypeCode.Object;
            }
        }

        public static DbType ConvertTypeToDbType<T>() {
            return ConvertTypeCodeToDbType(Type.GetTypeCode(typeof(T)));
        }

        public static DbType ConvertTypeCodeToDbType(TypeCode typeCode) {
            // no TypeCode equivalent for TimeSpan or DateTimeOffset
            switch (typeCode) {
                case TypeCode.Boolean:
                    return DbType.Boolean;
                case TypeCode.Byte:
                    return DbType.Byte;
                case TypeCode.Char:
                    return DbType.StringFixedLength; // ???
                case TypeCode.DateTime: // Used for Date, DateTime and DateTime2 DbTypes
                    return DbType.DateTime;
                case TypeCode.Decimal:
                    return DbType.Decimal;
                case TypeCode.Double:
                    return DbType.Double;
                case TypeCode.Int16:
                    return DbType.Int16;
                case TypeCode.Int32:
                    return DbType.Int32;
                case TypeCode.Int64:
                    return DbType.Int64;
                case TypeCode.SByte:
                    return DbType.SByte;
                case TypeCode.Single:
                    return DbType.Single;
                case TypeCode.String:
                    return DbType.String;
                case TypeCode.UInt16:
                    return DbType.UInt16;
                case TypeCode.UInt32:
                    return DbType.UInt32;
                case TypeCode.UInt64:
                    return DbType.UInt64;
                default:
                    return DbType.Object;
            }
        }
    }



    /// <summary>
    /// Allows coding inline SQL into an enum value.
    /// </summary>
    public class SqlAttribute : Attribute {
        public SqlAttribute(string sql) { Sql = sql; }
        public string Sql { get; }
    }

    /// <summary>
    /// Helper extension methods for working with enum types.
    /// </summary>
    public static class EnumExtensions {
        /// <summary>
        /// Get the description decorated attribute string from an enum or its ToString() representation.
        /// </summary>
        /// <param name="item">The enum to get the string description of.</param>
        /// <returns>The string description.</returns>
        public static string GetDescription(this Enum item) {
            if (item == null) return string.Empty;
            var t = item.GetType();
            var memberName = item.ToString();
            var memInfo = t.GetMember(memberName);
            if (memInfo.Length <= 0) return memberName;
            var attr = memInfo[0].GetCustomAttribute<DescriptionAttribute>(false);
            return attr?.Description ?? item.ToString();
        }

        public static T FirstValue<T>() => (T)Enum.GetValues(typeof(T)).GetValue(0);

        public static T GetEnum<T>(this string stringValue, T defaultVal) where T : struct {
            var result = defaultVal;
            if (string.IsNullOrWhiteSpace(stringValue)) return result;
            if (Enum.TryParse(stringValue, true, out result) == false)
                result = defaultVal;
            if (Enum.IsDefined(typeof(T), result) == false)
                result = defaultVal;
            return result;
        }

        public static T Parse<T>(this string value) where T : struct {
            if (string.IsNullOrWhiteSpace(value))
                return default(T);
            T outVal;
            var success = Enum.TryParse(value, true, out outVal);
            return success ? outVal : default(T);
        }


        /// <summary>
        /// Get a custom attribute decorator for an enum type.
        /// </summary>
        /// <typeparam name="TAttribute">The custom type attribute to pull.</typeparam>
        /// <param name="enumValue">The enum value to reflect.</param>
        /// <returns>The custom enum type attribute decorator.</returns>
        public static TAttribute GetAttribute<TAttribute>(this Enum enumValue) where TAttribute : Attribute
            => enumValue?.GetType().GetTypeInfo()?.GetDeclaredField(enumValue.ToString()).GetCustomAttribute<TAttribute>();

        public static TAttribute TryGetAttribute<TAttribute>(this Enum enumValue, Func<TAttribute> fallback = null) where TAttribute : Attribute
            => enumValue.TryRef<Enum, TAttribute>(
                value => value.GetAttribute<TAttribute>(),
                (ex, value) => ex == null ? new ArgumentException($"{nameof(enumValue)} must implement {typeof(TAttribute)}.") : (dynamic)fallback ?? ex, throws: true);

        public static string GetSql(this Enum sqlEnumValue, string sqlFallback = null)
            => sqlEnumValue.TryGetAttribute(() => new SqlAttribute(sqlFallback))?.Sql;
    }

    public static class TryExtensions {

        /// <summary>
        /// Inline try-catch-finally block for return <typeparamref name="TResult"/> value types. The input is chained through the try, and optionally through the catch and finally blocks.  By default, will not throw unless an exception is returned from any of the try-catch-finally blocks.
        /// </summary>
        /// <typeparam name="T">The type of the input to flow through try-catch-finally.</typeparam>
        /// <typeparam name="TResult">The type (value) of the try-catch-finally result.</typeparam>
        /// <param name="input">The <typeparamref name="T"/> input to be chained through the try-catch-finally blocks.</param>
        /// <param name="tryLogic">The try logic to execute. Receives <typeparamref name="T"/> input and returns <typeparamref name="TResult"/> result or any exception object which will subsequently be thrown.</param>
        /// <param name="catchLogic">The catch logic to execute. Runs only when try logic throws. Receives the exception and <typeparamref name="T"/> input. Returns <typeparamref name="TResult"/> result or any exception object which will subsequently be thrown.</param>
        /// <param name="finallyLogic">The finally logic to execute.  Receives the resultant <typeparamref name="TResult"/> output from the try or catch logic and returns <typeparamref name="TResult"/> result or any exception which will subsequently be thrown.</param>
        /// <param name="throws">Whether unexpected exceptions should be thrown (default: false).</param>
        /// <returns>The <typeparamref name="TResult"/> result of flowing through the logic blocks or null.</returns>
        public static TResult? TryVal<T, TResult>(this T input, Func<T, dynamic> tryLogic, Func<Exception, T, dynamic> catchLogic = null, Func<T, TResult?, dynamic> finallyLogic = null, bool throws = false) where TResult : struct {
            TResult? result = null;
            try {
                result = ResolveValExceptions<TResult>(tryLogic?.Invoke(input));
            } catch (Exception ex) {
                result = ResolveValExceptions<TResult>(catchLogic?.Invoke(ex, input), ex, throws);
            } finally {
                if (finallyLogic != null) result = ResolveValExceptions<TResult>(finallyLogic(input, result));
            }
            return result;
        }

        /// <summary>
        /// Inline async try-catch-finally block for return <typeparamref name="TResult"/> value types. The input is chained through the try, and optionally through the catch and finally blocks.  By default, will not throw unless an exception is returned from any of the try-catch-finally blocks.
        /// </summary>
        /// <typeparam name="T">The type of the input to flow through async try-catch-finally.</typeparam>
        /// <typeparam name="TResult">The type (value) of the async try-catch-finally result.</typeparam>
        /// <param name="input">The <typeparamref name="T"/> input to be chained through the async try-catch-finally blocks.</param>
        /// <param name="tryLogicAsync">The async try logic to execute. Receives <typeparamref name="T"/> input and returns <typeparamref name="TResult"/> result or any exception object which will subsequently be thrown.</param>
        /// <param name="catchLogicAsync">The async catch logic to execute. Runs only when try logic throws. Receives the exception and <typeparamref name="T"/> input. Returns <typeparamref name="TResult"/> result or any exception object which will subsequently be thrown.</param>
        /// <param name="finallyLogicAsync">The async finally logic to execute.  Receives the resultant <typeparamref name="TResult"/> output from the try or catch logic and returns <typeparamref name="TResult"/> result or any exception which will subsequently be thrown.</param>
        /// <param name="throws">Whether unexpected exceptions should be thrown (default: false).</param>
        /// <returns>The <typeparamref name="TResult"/> result of flowing through the async logic blocks or null.</returns>
        public static async Task<TResult?> TryValAsync<T, TResult>(this T input, Func<T, Task<dynamic>> tryLogicAsync, Func<Exception, T, Task<dynamic>> catchLogicAsync = null, Func<T, TResult?, Task<dynamic>> finallyLogicAsync = null, bool throws = false) where TResult : struct {
            TResult? result = null;
            try {
                var task = tryLogicAsync?.Invoke(input);
                if (task != null) result = ResolveValExceptions<TResult>(await task);
            } catch (Exception ex) {
                var task = catchLogicAsync?.Invoke(ex, input);
                if (task != null) result = ResolveValExceptions<TResult>(await task, ex, throws);
            } finally {
                var task = finallyLogicAsync?.Invoke(input, result);
                if (task != null) result = ResolveValExceptions<TResult>(await task);
            }
            return result;
        }

        private static TResult? ResolveValExceptions<TResult>(dynamic input, Exception exception = null, bool throws = false) where TResult : struct {
            var thrown = input as Exception;
            if (throws && exception != null) throw (thrown == null ? exception : new AggregateException(thrown, exception));
            if (thrown != null) throw thrown;
            return input as TResult?;
        }

        /// <summary>
        /// Inline try-catch-finally block for return <typeparamref name="TResult"/> reference types. The input is chained through the try, and optionally through the catch and finally blocks.  By default, will not throw unless an exception is returned from any of the try-catch-finally blocks.
        /// </summary>
        /// <typeparam name="T">The type of the input to flow through try-catch-finally.</typeparam>
        /// <typeparam name="TResult">The type (reference) of the try-catch-finally result.</typeparam>
        /// <param name="input">The <typeparamref name="T"/> input to be chained through the try-catch-finally blocks.</param>
        /// <param name="tryLogic">The try logic to execute. Receives <typeparamref name="T"/> input and returns <typeparamref name="TResult"/> result or any exception object which will subsequently be thrown.</param>
        /// <param name="catchLogic">The catch logic to execute. Runs only when try logic throws. Receives the exception and <typeparamref name="T"/> input. Returns <typeparamref name="TResult"/> result or any exception object which will subsequently be thrown.</param>
        /// <param name="finallyLogic">The finally logic to execute.  Receives the resultant <typeparamref name="TResult"/> output from the try or catch logic and returns <typeparamref name="TResult"/> result or any exception which will subsequently be thrown.</param>
        /// <param name="throws">Whether unexpected exceptions should be thrown (default: false).</param>
        /// <returns>The <typeparamref name="TResult"/> result of flowing through the logic blocks or null.</returns>
        public static TResult TryRef<T, TResult>(this T input, Func<T, dynamic> tryLogic, Func<Exception, T, dynamic> catchLogic = null, Func<T, TResult, dynamic> finallyLogic = null, bool throws = false) where TResult : class {
            TResult result = null;
            try {
                result = ResolveRefExceptions<TResult>(tryLogic?.Invoke(input));
            } catch (Exception ex) {
                result = ResolveRefExceptions<TResult>(catchLogic?.Invoke(ex, input), ex, throws);
            } finally {
                if (finallyLogic != null) result = ResolveRefExceptions<TResult>(finallyLogic(input, result));
            }
            return result;
        }

        /// <summary>
        /// Inline async try-catch-finally block for return <typeparamref name="TResult"/> reference types. The input is chained through the try, and optionally through the catch and finally blocks.  By default, will not throw unless an exception is returned from any of the try-catch-finally blocks.
        /// </summary>
        /// <typeparam name="T">The type of the input to flow through async try-catch-finally.</typeparam>
        /// <typeparam name="TResult">The type (reference) of the async try-catch-finally result.</typeparam>
        /// <param name="input">The <typeparamref name="T"/> input to be chained through the async try-catch-finally blocks.</param>
        /// <param name="tryLogicAsync">The async try logic to execute. Receives <typeparamref name="T"/> input and returns <typeparamref name="TResult"/> result or any exception object which will subsequently be thrown.</param>
        /// <param name="catchLogicAsync">The async catch logic to execute. Runs only when try logic throws. Receives the exception and <typeparamref name="T"/> input. Returns <typeparamref name="TResult"/> result or any exception object which will subsequently be thrown.</param>
        /// <param name="finallyLogicAsync">The async finally logic to execute.  Receives the resultant <typeparamref name="TResult"/> output from the try or catch logic and returns <typeparamref name="TResult"/> result or any exception which will subsequently be thrown.</param>
        /// <param name="throws">Whether unexpected exceptions should be thrown (default: false).</param>
        /// <returns>The <typeparamref name="TResult"/> result of flowing through the async logic blocks or null.</returns>
        public static async Task<TResult> TryRefAsync<T, TResult>(this T input, Func<T, Task<dynamic>> tryLogicAsync, Func<Exception, T, Task<dynamic>> catchLogicAsync = null, Func<T, TResult, Task<dynamic>> finallyLogicAsync = null, bool throws = false) where TResult : class {
            TResult result = null;
            try {
                var task = tryLogicAsync?.Invoke(input);
                if (task != null) result = ResolveRefExceptions<TResult>(await task);
            } catch (Exception ex) {
                var task = catchLogicAsync?.Invoke(ex, input);
                if (task != null) result = ResolveRefExceptions<TResult>(await task, ex, throws);
            } finally {
                var task = finallyLogicAsync?.Invoke(input, result);
                if (task != null) result = ResolveRefExceptions<TResult>(await task);
            }
            return result;
        }

        private static TResult ResolveRefExceptions<TResult>(dynamic input, Exception exception = null, bool throws = false) where TResult : class {
            var thrown = input as Exception;
            if (throws && exception != null) throw (thrown == null ? exception : new AggregateException(thrown, exception));
            if (thrown != null) throw thrown;
            return input as TResult;
        }
    }

    public interface ICacheProvider {
        object Get(string key);
        T GetWithRefresh<T>(string key, Func<T> refreshFunc, int cacheTime = 360) where T : class;
        T GetWithRefreshVal<T>(string key, Func<T> refreshFunc, int cacheTime = 360) where T : struct;
        Task<T> GetWithRefreshAsync<T>(string key, Func<Task<T>> refreshFuncAsync, int cacheTime = 360) where T : class;
        Task<T> GetWithRefreshValAsync<T>(string key, Func<Task<T>> refreshFuncAsync, int cacheTime = 360) where T : struct;
        void Set(string key, object data, int cacheTime);
        bool IsSet(string key);
        void Invalidate(string key);
    }

    public class DefaultCacheProvider : ICacheProvider {
        public static Lazy<DefaultCacheProvider> LazyCache = new Lazy<DefaultCacheProvider>(() => new DefaultCacheProvider());
        private static ObjectCache Cache => MemoryCache.Default;
        public object Get(string key) => Cache[key];

        public T GetWithRefresh<T>(string key, Func<T> refreshFunc, int cacheTime = 360) where T : class {
            var cached = Get(key) as T;
            return cached ?? Set(key, refreshFunc(), cacheTime);
        }

        public T GetWithRefreshVal<T>(string key, Func<T> refreshFunc, int cacheTime = 360) where T : struct {
            var cached = Get(key);
            return cached as T? ?? SetVal(key, refreshFunc(), cacheTime);
        }

        public async Task<T> GetWithRefreshAsync<T>(string key, Func<Task<T>> refreshFuncAsync, int cacheTime = 360) where T : class {
            var cached = Get(key) as T;
            return cached ?? Set(key, await refreshFuncAsync(), cacheTime);
        }

        public async Task<T> GetWithRefreshValAsync<T>(string key, Func<Task<T>> refreshFunc, int cacheTime = 360) where T : struct {
            var cached = Get(key);
            return cached as T? ?? SetVal(key, await refreshFunc(), cacheTime);
        }

        public T Set<T>(string key, T data, int cacheTime) where T : class {
            if (data == null) return null;
            var policy = new CacheItemPolicy { AbsoluteExpiration = DateTime.Now + TimeSpan.FromMinutes(cacheTime) };
            Cache.Add(new CacheItem(key, data), policy);
            return data;
        }


        public T SetVal<T>(string key, T data, int cacheTime) where T : struct {
            var policy = new CacheItemPolicy { AbsoluteExpiration = DateTime.Now + TimeSpan.FromMinutes(cacheTime) };
            Cache.Add(new CacheItem(key, data), policy);
            return data;
        }

        public void Set(string key, object data, int cacheTime) => Set<object>(key, data, cacheTime);
        public bool IsSet(string key) => (Cache[key] != null);
        public void Invalidate(string key) => Cache.Remove(key);
    }
}