using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Internal;
using Microsoft.EntityFrameworkCore.Storage;
using Microsoft.Extensions.DependencyInjection;
using EasyDevCore.Common;
using System.Data;
using System.Runtime.CompilerServices;
using System.Dynamic;
using System.Collections;
using System.Reflection;
using System.Collections.Concurrent;
using EasyDevCore.Database;
using System.Transactions;

namespace EasyDevCore.Database
{
    /// <summary>
    /// 
    /// </summary>
    /// <typeparam name="TModel">The type of the model.</typeparam>
    /// <typeparam name="TResult">The type of the result.</typeparam>
    /// <param name="models">The models.</param>
    /// <param name="hasNextResult">if set to <c>true</c> [has next result].</param>
    /// <returns></returns>
    public delegate TResult FuncResultList<TModel, TResult>(out IList<TModel> models, out bool hasNextResult);

    /// <summary>
    /// 
    /// </summary>
    /// <typeparam name="TModel">The type of the model.</typeparam>
    /// <typeparam name="TResult">The type of the result.</typeparam>
    /// <param name="models">The models.</param>
    /// <returns></returns>
    public delegate TResult StoreResultList<TModel, TResult>(out IList<TModel> models);
    /// <summary>
    /// 
    /// </summary>
    /// <typeparam name="TModel">The type of the model.</typeparam>
    /// <typeparam name="TResult">The type of the result.</typeparam>
    /// <param name="models">The models.</param>
    /// <returns></returns>
    public delegate TResult StoreResultModel<TModel, TResult>(out TModel models);

    /// <summary>
    /// 
    /// </summary>
    /// <typeparam name="TModel">The type of the model.</typeparam>
    /// <typeparam name="TOut">The type of the out.</typeparam>
    /// <typeparam name="TResult">The type of the result.</typeparam>
    /// <param name="models">The models.</param>
    /// <param name="outVars">The out vars.</param>
    /// <param name="hasNextResult">if set to <c>true</c> [has next result].</param>
    /// <returns></returns>
    public delegate TResult StoreResultList<TModel, TOut, TResult>(out IList<TModel> models, out TOut outVars, out bool hasNextResult);

    /// <summary>
    /// 
    /// </summary>
    public static class EasyDatabaseFacadeExtensions
    {
        private static ConcurrentDictionary<string, Type> _ParameterTypesCache = new();

        private static string GetExecStoreProcedureString(string storeProcedure, IEnumerable<EasyDbParameter> parameters)
        {
            return
@"--" + string.Join(",", parameters.OfType<EasyDbParameter>().Where(p => p.ParameterName.In("ApplicationID", "UserID", "HostName", "ScreenID")).Select(p => $@"{p.ParameterName}={p.Value}")) +
@"
EXEC @RETURN_VALUE = " + storeProcedure + " " + string.Join(",", parameters.OfType<EasyDbParameter>().Where(p => !p.ParameterName.In("RETURN_VALUE", "RETURN_ROWS_EFFECTED")).Select(p => $"@{p.ParameterName} = @{p.ParameterName}"
                 + (p.Direction.In(ParameterDirection.Output, ParameterDirection.InputOutput) ? " OUTPUT" : "")));
        }

        /// <summary>
        /// Synchronizes the output parameters.
        /// </summary>
        /// <param name="inParams">The in parameters.</param>
        /// <param name="outParams">The out parameters.</param>
        private static void SyncOutputParameters(object inParams, IEnumerable<EasyDbParameter> outParams)
        {
            IEnumerable<EasyDbParameter> fixInParams = null;
            if (inParams == outParams) return;

            if (inParams is object[] && ((object[])inParams).Length == 1 && ((object[])inParams)[0] is EasyDbParameterCollection)
            {
                fixInParams = (EasyDbParameterCollection)((object[])inParams)[0];
            }

            if (inParams is IEnumerable<EasyDbParameter>)
            {
                fixInParams = (IEnumerable<EasyDbParameter>)inParams;
            }

            if (fixInParams == null) return;

            var synchList = from pIn in fixInParams
                            join pOut in outParams on pIn.ParameterName equals pOut.ParameterName
                            where pIn.Direction.In(ParameterDirection.Output, ParameterDirection.InputOutput, ParameterDirection.ReturnValue)
                            select new { pIn, pOut.Value };
            synchList.ForEach(v => v.pIn.Value = v.Value);
        }

        /// <summary>
        /// Creates the parameter.
        /// </summary>
        /// <param name="database">The database.</param>
        /// <param name="parameter">The parameter.</param>
        /// <returns></returns>
        public static EasyDbParameter CreateParameter(this DatabaseFacade database, EasyDbParameter parameter)
        {
            if (!_ParameterTypesCache.TryGetValue(database.ProviderName, out Type type))
            {
                var command = database.GetDbConnection().CreateCommand();
                type = command.CreateParameter().GetType();
                _ParameterTypesCache.TryAdd(database.ProviderName, type);
            }
            EasyDbParameter param = (EasyDbParameter)type.CreateInstance(parameter.ParameterName, parameter.Value ?? DBNull.Value);
            param.Direction = parameter.Direction;

            param.Scale = parameter.Scale != 0 ? parameter.Scale : param.Scale;
            param.Precision = parameter.Precision != 0 ? parameter.Precision : param.Precision;
            param.Size = parameter.Precision != 0 ? parameter.Precision : param.Precision;


            if (param.Value is DateTime && ((DateTime)param.Value).Date <= DateTime.Parse("1900/01/01"))
            {
                param.Value = DateTime.Parse("1900/01/01");
            }
            if (param.Direction == ParameterDirection.InputOutput && param.Value.GetType() == typeof(decimal))
            {
                param.Precision = 28;
                param.Scale = 8;
            }
            if (param.DbType.In(DbType.String, DbType.AnsiString, DbType.StringFixedLength, DbType.AnsiStringFixedLength) && param.Direction.In(ParameterDirection.InputOutput, ParameterDirection.Output))
            {
                param.Size = -1;
            }
            return param;
        }

        /// <summary>
        /// Converts the parameters.
        /// </summary>
        /// <param name="database">The database.The database.</param>
        /// <param name="parameters">The parameters.The parameters.</param>
        /// <returns>
        ///   <br />
        /// </returns>
        private static IEnumerable<EasyDbParameter> ConvertParameters(DatabaseFacade database, EasyDbParameterCollection parameters)
        {
            IEnumerable<EasyDbParameter> result = null;
            if (parameters != null && parameters.Any() && !(parameters.First() is EasyDbParameter))
            {
                result = parameters;
                foreach (var p in parameters.Where(dp => !dp.Direction.In(ParameterDirection.InputOutput, ParameterDirection.Output) && (dp.Value == null || dp.Value == DBNull.Value)).ToArray())
                {
                    parameters.Remove(p.ParameterName);
                }
            }
            else
            {
                result = new EasyDbParameterCollection();
                if (parameters != null)
                {
                    foreach (EasyDbParameter param in parameters)
                    {
                        if (param.Direction.In(ParameterDirection.InputOutput, ParameterDirection.Output) || param.Value != DBNull.Value && param.Value != null)
                        {
                            ((EasyDbParameterCollection)result).Add(database.CreateParameter(param));
                        }
                    }
                }
            }

            return result;
        }

        /// <summary>
        /// Gets the context.
        /// </summary>
        /// <param name="database">The database.</param>
        /// <returns></returns>
        public static DbContext GetContext(this DatabaseFacade database)
        {
            object internalSet = database
                .GetType()
                .GetField("_internalSet", BindingFlags.NonPublic | BindingFlags.Instance)
                .GetValue(database);
            object internalContext = internalSet
                .GetType()
                .BaseType
                .GetField("_internalContext", BindingFlags.NonPublic | BindingFlags.Instance)
                .GetValue(internalSet);
            return (DbContext)internalContext
                .GetType()
                .GetProperty("Owner", BindingFlags.Instance | BindingFlags.Public)
                .GetValue(internalContext, null);
        }


        /// <summary>
        /// Executes the non query asynchronous.
        /// </summary>
        /// <param name="database">The database.</param>
        /// <param name="cancellationToken">The cancellation token.</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static Task<int> ExecuteNonQueryAsync(this DatabaseFacade database,
                                                        CancellationToken cancellationToken,
                                                        string sql,
                                                        params object[] parameters)
        {
            var fixParams = ConvertParameters(database, ParameterUtils.GetParameters(false, parameters));
            var concurrencyDetector = database.GetService<IConcurrencyDetector>();
            using (concurrencyDetector.EnterCriticalSection())
            {
                var rawSqlCommand = database.GetService<IRawSqlCommandBuilder>().Build(sql, fixParams);
                var paramObject = new RelationalCommandParameterObject(database.GetService<IRelationalConnection>(), rawSqlCommand.ParameterValues, null, null, null);
                return rawSqlCommand.RelationalCommand.ExecuteNonQueryAsync(paramObject, cancellationToken).ContinueWith(t =>
                {
                    SyncOutputParameters(parameters, fixParams);
                    return t.Result;
                });
            }
        }

        /// <summary>
        /// Executes the non query asynchronous.
        /// </summary>
        /// <param name="database">The database.</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static Task<int> ExecuteNonQueryAsync(this DatabaseFacade database,
                                                       string sql,
                                                       params object[] parameters)
        {
            return database.ExecuteNonQueryAsync(CancellationToken.None, sql, parameters);
        }

        /// <summary>
        /// Executes the non query.
        /// </summary>
        /// <param name="database">The database.</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(this DatabaseFacade database, string sql, params object[] parameters)
        {
            var fixParams = ConvertParameters(database, ParameterUtils.GetParameters(false, parameters));
            var concurrencyDetector = database.GetService<IConcurrencyDetector>();
            using (concurrencyDetector.EnterCriticalSection())
            {
                var rawSqlCommand = database.GetService<IRawSqlCommandBuilder>().Build(sql, fixParams);
                var paramObject = new RelationalCommandParameterObject(database.GetService<IRelationalConnection>(), rawSqlCommand.ParameterValues, null, null, null);
                var result = rawSqlCommand.RelationalCommand.ExecuteNonQuery(paramObject);
                SyncOutputParameters(parameters, fixParams);
                return result;
            }
        }

        /// <summary>
        /// Executes the scalar.
        /// </summary>
        /// <param name="database">The database.</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static object ExecuteScalar(this DatabaseFacade database, string sql, params object[] parameters)
        {
            var fixParams = ConvertParameters(database, ParameterUtils.GetParameters(false, parameters));
            var concurrencyDetector = database.GetService<IConcurrencyDetector>();
            using (concurrencyDetector.EnterCriticalSection())
            {
                var rawSqlCommand = database.GetService<IRawSqlCommandBuilder>().Build(sql, fixParams);
                var paramObject = new RelationalCommandParameterObject(database.GetService<IRelationalConnection>(), rawSqlCommand.ParameterValues, null, null, null);
                var result = rawSqlCommand.RelationalCommand.ExecuteScalar(paramObject);
                SyncOutputParameters(parameters, fixParams);
                return result;
            }
        }

        /// <summary>
        /// Executes the scalar asynchronous.
        /// </summary>
        /// <param name="database">The database.</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static Task<object> ExecuteScalarAsync(this DatabaseFacade database, string sql, params object[] parameters)
        {
            var fixParams = ConvertParameters(database, ParameterUtils.GetParameters(false, parameters));
            var concurrencyDetector = database.GetService<IConcurrencyDetector>();
            using (concurrencyDetector.EnterCriticalSection())
            {
                var rawSqlCommand = database.GetService<IRawSqlCommandBuilder>().Build(sql, fixParams);
                var paramObject = new RelationalCommandParameterObject(database.GetService<IRelationalConnection>(), rawSqlCommand.ParameterValues, null, null, null);
                var result = rawSqlCommand.RelationalCommand.ExecuteScalarAsync(paramObject);
                SyncOutputParameters(parameters, fixParams);
                return result;
            }
        }

        /// <summary>
        /// Executes the reader.
        /// </summary>
        /// <param name="database">The database facade.</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static RelationalDataReader ExecuteReader(this DatabaseFacade database, string sql, params object[] parameters)
        {
            var fixParams = ConvertParameters(database, ParameterUtils.GetParameters(false, parameters));
            var concurrencyDetector = database.GetService<IConcurrencyDetector>();
            using (concurrencyDetector.EnterCriticalSection())
            {
                var rawSqlCommand = database.GetService<IRawSqlCommandBuilder>().Build(sql, fixParams);
                var paramObject = new RelationalCommandParameterObject(database.GetService<IRelationalConnection>(), rawSqlCommand.ParameterValues, null, null, null);
                var result = rawSqlCommand.RelationalCommand.ExecuteReader(paramObject);
                SyncOutputParameters(parameters, fixParams);
                return result;
            }
        }

        /// <summary>
        /// Executes the reader asynchronous.
        /// </summary>
        /// <param name="database">The database facade.</param>
        /// <param name="cancellationToken">The cancellation token.</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static Task<RelationalDataReader> ExecuteReaderAsync(this DatabaseFacade database,
                                                                        CancellationToken cancellationToken,
                                                                        string sql,
                                                                        params object[] parameters)
        {
            var fixParams = ConvertParameters(database, ParameterUtils.GetParameters(false, parameters));
            var concurrencyDetector = database.GetService<IConcurrencyDetector>();
            using (concurrencyDetector.EnterCriticalSection())
            {
                var rawSqlCommand = database.GetService<IRawSqlCommandBuilder>().Build(sql, fixParams);
                var paramObject = new RelationalCommandParameterObject(database.GetService<IRelationalConnection>(), rawSqlCommand.ParameterValues, null, null, null);
                return rawSqlCommand.RelationalCommand.ExecuteReaderAsync(paramObject, cancellationToken).ContinueWith(t =>
                {
                    SyncOutputParameters(parameters, fixParams);
                    return t.Result;
                });
            }
        }

        /// <summary>
        /// Executes the reader asynchronous.
        /// </summary>
        /// <param name="database">The database.</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static Task<RelationalDataReader> ExecuteReaderAsync(this DatabaseFacade database,
                                                                        string sql,
                                                                        params object[] parameters)
        {
            return database.ExecuteReaderAsync(CancellationToken.None, sql, parameters);
        }

        /// <summary>
        /// Fetches the result.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="reader">The reader.</param>
        /// <returns></returns>
        public static FuncResultList<T, RelationalDataReader> FetchResult<T>(this RelationalDataReader reader) where T : class, new()
        {
            List<T> list = new();
            var mapper = new DbDataReaderMapper<T>(reader);
            mapper.Map(row => list.Add(row));
            var nextResult = reader.DbDataReader.NextResult();
            if (!nextResult) reader.DbDataReader.Close();
            return (out IList<T> models, out bool hasNextResult) =>
            {
                models = list;
                hasNextResult = nextResult;
                return reader;
            };
        }

        /// <summary>
        /// Fetches the result json.
        /// </summary>
        /// <param name="reader">The reader.</param>
        /// <returns></returns>
        public static StoreResultModel<string, RelationalDataReader> FetchResultJson(this RelationalDataReader reader)
        {
            var json = reader.DbDataReader.FetchToJson();
            var nextResult = reader.DbDataReader.NextResult();
            if (!nextResult) reader.DbDataReader.Close();
            return (out string models) =>
            {
                models = json;
                return reader;
            };
        }

        /// <summary>
        /// Fetches the result dynamic.
        /// </summary>
        /// <param name="reader">The reader.</param>
        /// <returns></returns>
        /// <remarks></remarks>
        public static FuncResultList<dynamic, RelationalDataReader> FetchResultDynamic(this RelationalDataReader reader)
        {
            var list = reader.DbDataReader.FetchToDynamic();
            var nextResult = reader.DbDataReader.NextResult();
            if (!nextResult) reader.DbDataReader.Close();

            return (out IList<dynamic> models, out bool hasNextResult) =>
            {
                models = list;
                hasNextResult = nextResult;
                return reader;
            };
        }

        /// <summary>
        /// Fetches the result asynchronous.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="reader">The reader.</param>
        /// <param name="cancellationToken">The cancellation token.</param>
        /// <returns></returns>
        public static async Task<FuncResultList<T, RelationalDataReader>> FetchResultAsync<T>(this RelationalDataReader reader,
                                                                                                 CancellationToken cancellationToken) where T : class, new()
        {
            List<T> list = new();
            var mapper = new DbDataReaderMapper<T>(reader);
            await mapper.MapAsync(row => list.Add(row), cancellationToken).ConfigureAwait(false);
            bool nextResult = await reader.DbDataReader.NextResultAsync(cancellationToken).ConfigureAwait(false);
            if (!nextResult) await reader.DbDataReader.CloseAsync();
            return (out IList<T> models, out bool hasNextResult) =>
            {
                models = list;
                hasNextResult = nextResult;
                return reader;
            };
        }

        /// <summary>
        /// Fetches the result asynchronous.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="reader">The reader.</param>
        /// <returns></returns>
        public static async Task<FuncResultList<T, RelationalDataReader>> FetchResultAsync<T>(this RelationalDataReader reader) where T : class, new()
        {
            return await reader.FetchResultAsync<T>(CancellationToken.None).ConfigureAwait(false);
        }

        /// <summary>
        /// Fetches the result.
        /// </summary>
        /// <typeparam name="TResult">The type of the result.</typeparam>
        /// <param name="reader">The reader.</param>
        /// <param name="selector">The selector.</param>
        /// <returns></returns>
        public static FuncResultList<TResult, RelationalDataReader> FetchResult<TResult>(this RelationalDataReader reader,
                                                                                            Func<dynamic, TResult> selector)
        {
            List<TResult> list = new();
            DataRecordDynamic wrapper = new(reader.DbDataReader);
            while (reader.Read())
            {
                var item = selector(wrapper);
                list.Add(item);
            }
            bool nextResult = reader.DbDataReader.NextResult();
            return (out IList<TResult> models, out bool hasNextResult) =>
            {
                models = list;
                hasNextResult = nextResult;
                return reader;
            };
        }

        /// <summary>
        /// Fetches the result asynchronous.
        /// </summary>
        /// <typeparam name="TResult">The type of the result.</typeparam>
        /// <param name="reader">The reader.</param>
        /// <param name="selector">The selector.</param>
        /// <param name="cancellationToken">The cancellation token.</param>
        /// <returns></returns>
        public static async Task<FuncResultList<TResult, RelationalDataReader>> FetchResultAsync<TResult>(this RelationalDataReader reader,
                                                                                                            Func<dynamic, TResult> selector,
                                                                                                            CancellationToken cancellationToken)
        {
            List<TResult> list = new();
            DataRecordDynamic wrapper = new(reader.DbDataReader);
            while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
            {
                var item = selector(wrapper);
                list.Add(item);
            }
            bool nextResult = await reader.DbDataReader.NextResultAsync(cancellationToken).ConfigureAwait(false);
            return (out IList<TResult> models, out bool hasNextResult) =>
            {
                models = list;
                hasNextResult = nextResult;
                return reader;
            };
        }

        /// <summary>
        /// Fetches the result asynchronous.
        /// </summary>
        /// <typeparam name="TResult">The type of the result.</typeparam>
        /// <param name="reader">The reader.</param>
        /// <param name="selector">The selector.</param>
        /// <returns></returns>
        public static async Task<FuncResultList<TResult, RelationalDataReader>> FetchResultAsync<TResult>(this RelationalDataReader reader,
                                                                                                            Func<dynamic, TResult> selector)
        {
            return await reader.FetchResultAsync(selector, CancellationToken.None).ConfigureAwait(false);
        }

        /// <summary>
        /// Fetches the SQL with multi result set.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="database">The database.</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static FuncResultList<T, RelationalDataReader> FetchSql<T>(this DatabaseFacade database,
                                                                            string sql,
                                                                            params object[] parameters) where T : class, new()
        {
            var reader = database.ExecuteReader(sql, parameters);
            return reader.FetchResult<T>();
        }

        /// <summary>
        /// Fetches the SQL to dynamic.
        /// </summary>
        /// <param name="database">The database.</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static FuncResultList<dynamic, RelationalDataReader> FetchSqlDynamic(this DatabaseFacade database,
                                                                            string sql,
                                                                            params object[] parameters)
        {
            var reader = database.ExecuteReader(sql, parameters);
            return reader.FetchResultDynamic();
        }

        /// <summary>
        /// Fetches the SQL json.
        /// </summary>
        /// <param name="database">The database.</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static StoreResultModel<string, RelationalDataReader> FetchSqlJson(this DatabaseFacade database,
                                                                            string sql,
                                                                            params object[] parameters)
        {
            var reader = database.ExecuteReader(sql, parameters);
            return reader.FetchResultJson();
        }

        /// <summary>
        /// Fetches the SQL asynchronous.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="database">The database.</param>
        /// <param name="cancellationToken">The cancellation token.</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static async Task<FuncResultList<T, RelationalDataReader>> FetchSqlAsync<T>(this DatabaseFacade database,
                                                                                            CancellationToken cancellationToken,
                                                                                            string sql,
                                                                                            params object[] parameters) where T : class, new()
        {
            var reader = await database.ExecuteReaderAsync(cancellationToken, sql, parameters).ConfigureAwait(false);
            return await FetchResultAsync<T>(reader, cancellationToken).ConfigureAwait(false);
        }

        /// <summary>
        /// Fetches the SQL asynchronous.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="database">The database.</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static async Task<FuncResultList<T, RelationalDataReader>> FetchSqlAsync<T>(this DatabaseFacade database,
                                                                                                string sql,
                                                                                                params object[] parameters) where T : class, new()
        {
            return await database.FetchSqlAsync<T>(CancellationToken.None, sql, parameters).ConfigureAwait(false);
        }

        /// <summary>
        /// Fetches the SQL.
        /// </summary>
        /// <typeparam name="TResult">The type of the result.</typeparam>
        /// <param name="database">The database.</param>
        /// <param name="selector">The selector.</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static FuncResultList<TResult, RelationalDataReader> FetchSql<TResult>(this DatabaseFacade database,
                                                                                            Func<dynamic, TResult> selector,
                                                                                            string sql,
                                                                                            params object[] parameters)
        {
            var reader = database.ExecuteReader(sql, parameters);
            return reader.FetchResult(selector);
        }

        /// <summary>
        /// Fetches the SQL asynchronous.
        /// </summary>
        /// <typeparam name="TResult">The type of the result.</typeparam>
        /// <param name="database">The database.</param>
        /// <param name="selector">The selector.</param>
        /// <param name="cancellationToken">The cancellation token.</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static async Task<FuncResultList<TResult, RelationalDataReader>> FetchSqlAsync<TResult>(this DatabaseFacade database,
                                                                                                           Func<dynamic, TResult> selector,
                                                                                                           CancellationToken cancellationToken,
                                                                                                           string sql,
                                                                                                           params object[] parameters)
        {
            var reader = await database.ExecuteReaderAsync(cancellationToken, sql, parameters).ConfigureAwait(false);
            return await FetchResultAsync(reader, selector, cancellationToken).ConfigureAwait(false);
        }

        /// <summary>
        /// Fetches the SQL asynchronous.
        /// </summary>
        /// <typeparam name="TResult">The type of the result.</typeparam>
        /// <param name="database">The database.</param>
        /// <param name="selector">The selector.</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static async Task<FuncResultList<TResult, RelationalDataReader>> FetchSqlAsync<TResult>(this DatabaseFacade database,
                                                                                                           Func<dynamic, TResult> selector,
                                                                                                           string sql,
                                                                                                           params object[] parameters)
        {
            return await database.FetchSqlAsync(selector, CancellationToken.None, sql, parameters).ConfigureAwait(false);
        }

        /// <summary>
        /// Selects the SQL.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="database">The database.</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static IList<T> SelectSql<T>(this DatabaseFacade database,
                                                string sql,
                                                params object[] parameters) where T : class, new()
        {
            var reader = database.ExecuteReader(sql, parameters);
            bool hasNextResult = false;
            reader.FetchResult<T>()(out var list, out hasNextResult);
            return list;
        }

        /// <summary>
        /// Selects the SQL asynchronous.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="database">The database.</param>
        /// <param name="cancellationToken">The cancellation token.</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static async Task<IList<T>> SelectSqlAsync<T>(this DatabaseFacade database,
                                                                   CancellationToken cancellationToken,
                                                                   string sql,
                                                                   params object[] parameters) where T : class, new()
        {
            var reader = await database.ExecuteReaderAsync(cancellationToken, sql, parameters).ConfigureAwait(false);
            bool hasNextResult = false;
            (await FetchResultAsync<T>(reader, cancellationToken).ConfigureAwait(false))(out var list, out hasNextResult);
            return list;
        }

        /// <summary>
        /// Selects the SQL asynchronous.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="database">The database.</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static async Task<IList<T>> SelectSqlAsync<T>(this DatabaseFacade database,
                                                                  string sql,
                                                                  params object[] parameters) where T : class, new()
        {
            return await database.SelectSqlAsync<T>(CancellationToken.None, sql, parameters).ConfigureAwait(false);
        }

        /// <summary>
        /// Selects the SQL.
        /// </summary>
        /// <typeparam name="TResult">The type of the result.</typeparam>
        /// <param name="database">The database.</param>
        /// <param name="selector">The selector.</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static IList<TResult> SelectSql<TResult>(this DatabaseFacade database,
                                                            Func<dynamic, TResult> selector,
                                                            string sql,
                                                            params object[] parameters)
        {
            var reader = database.ExecuteReader(sql, parameters);
            bool hasNextResult = false;
            reader.FetchResult(selector)(out var list, out hasNextResult);
            return list;
        }

        /// <summary>
        /// Selects the SQL asynchronous.
        /// </summary>
        /// <typeparam name="TResult">The type of the result.</typeparam>
        /// <param name="database">The database.</param>
        /// <param name="cancellationToken">The cancellation token.</param>
        /// <param name="selector">The selector.</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static async Task<IList<TResult>> SelectSqlAsync<TResult>(this DatabaseFacade database,
                                                                            Func<dynamic, TResult> selector,
                                                                            CancellationToken cancellationToken,
                                                                            string sql,
                                                                            params object[] parameters)
        {
            var reader = await database.ExecuteReaderAsync(cancellationToken, sql, parameters).ConfigureAwait(false);
            bool hasNextResult = false;
            (await FetchResultAsync(reader, selector, cancellationToken).ConfigureAwait(false))(out var list, out hasNextResult);
            return list;
        }

        /// <summary>
        /// Selects the SQL asynchronous.
        /// </summary>
        /// <typeparam name="TResult">The type of the result.</typeparam>
        /// <param name="database">The database.</param>
        /// <param name="selector">The selector.</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static async Task<IList<TResult>> SelectSqlAsync<TResult>(this DatabaseFacade database,
                                                                            Func<dynamic, TResult> selector,
                                                                            string sql,
                                                                            params object[] parameters)
        {
            return await database.SelectSqlAsync(selector, CancellationToken.None, sql, parameters).ConfigureAwait(false);
        }

        /// <summary>
        /// Selects the SQL to dataset and output dictionary.
        /// </summary>
        /// <param name="database">The database.</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static StoreResultModel<DataSet, Dictionary<string, object>> SelectSqlToDataSetOutput(this DatabaseFacade database,
                                                                                            string sql,
                                                                                            params object[] parameters)
        {
            var fixParams = ConvertParameters(database, ParameterUtils.GetParameters(false, parameters));
            var reader = database.ExecuteReader(sql, fixParams);
            var data = reader.FetchToDataSet();
            var result = ParameterUtils.GatherOutputParameters(data.Tables.Count, fixParams);
            result.ForEach(p => data.ExtendedProperties[p.Key] = p.Value);
            return (out DataSet models) =>
            {
                models = data;
                return result;
            };
        }

        /// <summary>
        /// Selects the SQL to data set output asynchronous.
        /// </summary>
        /// <param name="database">The database.</param>
        /// <param name="cancellationToken">The cancellation token.</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static async Task<StoreResultModel<DataSet, Dictionary<string, object>>> SelectSqlToDataSetOutputAsync(this DatabaseFacade database,
                                                                                            CancellationToken cancellationToken,
                                                                                            string sql,
                                                                                            params object[] parameters)
        {
            var fixParams = ParameterUtils.GetParameters(false, parameters);
            var reader = await database.ExecuteReaderAsync(cancellationToken, sql, parameters).ConfigureAwait(false);
            var data = await reader.FetchToDataSetAsync();
            var result = ParameterUtils.GatherOutputParameters(data.Tables.Count, fixParams);
            return (out DataSet models) =>
            {
                models = data;
                return result;
            };
        }

        /// <summary>
        /// Selects the SQL to data set asynchronous.
        /// </summary>
        /// <param name="database">The database.</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static DataSet SelectSqlToDataSet(this DatabaseFacade database,
                                                                   string sql,
                                                                   params object[] parameters)
        {
            var fixParams = ConvertParameters(database, ParameterUtils.GetParameters(false, parameters));
            var reader = database.ExecuteReader(sql, fixParams);
            var dataSet = reader.FetchToDataSet();
            var result = ParameterUtils.GatherOutputParameters(dataSet.Tables.Count, fixParams);
            result.ForEach(p => dataSet.ExtendedProperties[p.Key] = p.Value);
            return dataSet;
        }

        /// <summary>
        /// Selects the SQL to data set asynchronous.
        /// </summary>
        /// <param name="database">The database.</param>
        /// <param name="cancellationToken">The cancellation token.</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static async Task<DataSet> SelectSqlToDataSetAsync(this DatabaseFacade database,
                                                                   CancellationToken cancellationToken,
                                                                   string sql,
                                                                   params object[] parameters)
        {
            var fixParams = ConvertParameters(database, ParameterUtils.GetParameters(false, parameters));
            var reader = await database.ExecuteReaderAsync(cancellationToken, sql, parameters).ConfigureAwait(false);
            var dataSet = await reader.FetchToDataSetAsync();
            var result = ParameterUtils.GatherOutputParameters(dataSet.Tables.Count, fixParams);
            result.ForEach(p => dataSet.ExtendedProperties[p.Key] = p.Value);
            return dataSet;
        }

        /// <summary>
        /// Executes the store procedure.
        /// </summary>
        /// <param name="database">The database.</param>
        /// <param name="storeProcedure">The store procedure.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static Dictionary<string, object> ExecStoreProcedure(this DatabaseFacade database,
                                                                        string storeProcedure,
                                                                        params object[] parameters)
        {
            var fixParams = ConvertParameters(database, ParameterUtils.GetParameters(false, parameters));
            string queryString = GetExecStoreProcedureString(storeProcedure, fixParams);
            int rowsAffected = database.ExecuteNonQuery(queryString, fixParams);
            var result = ParameterUtils.GatherOutputParameters(rowsAffected, fixParams);
            return result;
        }

        /// <summary>
        /// Executes the store procedure asynchronous.
        /// </summary>
        /// <param name="database">The database.</param>
        /// <param name="cancellationToken">The cancellation token.</param>
        /// <param name="storeProcedure">The store procedure.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static async Task<Dictionary<string, object>> ExecStoreProcedureAsync(this DatabaseFacade database,
                                                                                        CancellationToken cancellationToken,
                                                                                        string storeProcedure,
                                                                                        params object[] parameters)
        {
            var fixParams = ConvertParameters(database, ParameterUtils.GetParameters(false, parameters));
            string queryString = GetExecStoreProcedureString(storeProcedure, fixParams);
            int rowsAffected = await database.ExecuteNonQueryAsync(queryString, cancellationToken, fixParams).ConfigureAwait(false);
            var result = ParameterUtils.GatherOutputParameters(rowsAffected, fixParams);
            return result;
        }

        /// <summary>
        /// Executes the store procedure asynchronous.
        /// </summary>
        /// <param name="database">The database.</param>
        /// <param name="storeProcedure">The store procedure.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static async Task<Dictionary<string, object>> ExecStoreProcedureAsync(this DatabaseFacade database,
                                                                                        string storeProcedure,
                                                                                        params object[] parameters)
        {
            return await database.ExecStoreProcedureAsync(CancellationToken.None, storeProcedure, storeProcedure, parameters).ConfigureAwait(false);
        }

        /// <summary>
        /// Stores the procedure get data set.
        /// </summary>
        /// <param name="database">The database.</param>
        /// <param name="storeProcedure">The store procedure.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static DataSet StoreProcedureGetDataSet(this DatabaseFacade database,
                                                                                            string storeProcedure,
                                                                                            params object[] parameters)
        {
            var fixParams = ParameterUtils.GetParameters(false, parameters);
            string queryString = GetExecStoreProcedureString(storeProcedure, fixParams);
            var reader = database.ExecuteReader(queryString, fixParams);
            var dataSet = reader.FetchToDataSet(parameters: fixParams);
            return dataSet;
        }

        /// <summary>
        /// Stores the procedure get.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="database">The database.</param>
        /// <param name="storeProcedure">The store procedure.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static StoreResultList<T, Dictionary<string, object>> StoreProcedureGet<T>(this DatabaseFacade database,
                                                                                            string storeProcedure,
                                                                                            params object[] parameters) where T : class, new()
        {
            var fixParams = ConvertParameters(database, ParameterUtils.GetParameters(false, parameters));
            string queryString = GetExecStoreProcedureString(storeProcedure, fixParams);
            var list = database.SelectSql<T>(queryString, fixParams);
            var result = ParameterUtils.GatherOutputParameters(list.Count, fixParams);
            return (out IList<T> models) =>
            {
                models = list;
                return result;
            };
        }

        /// <summary>
        /// Stores the procedure get dynamic.
        /// </summary>
        /// <param name="database">The database.</param>
        /// <param name="storeProcedure">The store procedure.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        /// <remarks></remarks>
        public static StoreResultList<dynamic, Dictionary<string, object>> StoreProcedureGet(this DatabaseFacade database,
                                                                                            string storeProcedure,
                                                                                            params object[] parameters)
        {
            var fixParams = ConvertParameters(database, ParameterUtils.GetParameters(false, parameters));
            string queryString = GetExecStoreProcedureString(storeProcedure, fixParams);
            var reader = database.ExecuteReader(queryString, fixParams);
            var list = reader.FetchToDynamic();
            var result = ParameterUtils.GatherOutputParameters(list.Count, fixParams);
            return (out IList<dynamic> models) =>
            {
                models = list;
                return result;
            };
        }

        /// <summary>
        /// Stores the procedure get asynchronous.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="database">The database.</param>
        /// <param name="cancellationToken">The cancellation token.</param>
        /// <param name="storeProcedure">The store procedure.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static async Task<StoreResultList<T, Dictionary<string, object>>> StoreProcedureGetAsync<T>(this DatabaseFacade database,
                                                                                                                CancellationToken cancellationToken,
                                                                                                                string storeProcedure,
                                                                                                                params object[] parameters) where T : class, new()
        {
            var fixParams = ConvertParameters(database, ParameterUtils.GetParameters(false, parameters));
            string queryString = GetExecStoreProcedureString(storeProcedure, fixParams);
            var list = await database.SelectSqlAsync<T>(cancellationToken, queryString, fixParams).ConfigureAwait(false);
            var result = ParameterUtils.GatherOutputParameters(list.Count, fixParams);
            return (out IList<T> models) =>
            {
                models = list;
                return result;
            };
        }

        /// <summary>
        /// Stores the procedure get asynchronous.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="database">The database.</param>
        /// <param name="storeProcedure">The store procedure.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static async Task<StoreResultList<T, Dictionary<string, object>>> StoreProcedureGetAsync<T>(this DatabaseFacade database,
                                                                                                                string storeProcedure,
                                                                                                                params object[] parameters) where T : class, new()
        {
            return await database.StoreProcedureGetAsync<T>(CancellationToken.None, storeProcedure, parameters).ConfigureAwait(false);
        }

        /// <summary>
        /// Stores the procedure get.
        /// </summary>
        /// <typeparam name="TResult">The type of the result.</typeparam>
        /// <param name="database">The database.</param>
        /// <param name="selector">The selector.</param>
        /// <param name="storeProcedure">The store procedure.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static StoreResultList<TResult, Dictionary<string, object>> StoreProcedureGet<TResult>(this DatabaseFacade database,
                                                                                                        Func<dynamic, TResult> selector,
                                                                                                        string storeProcedure,
                                                                                                        params object[] parameters)
        {
            var fixParams = ConvertParameters(database, ParameterUtils.GetParameters(false, parameters));
            string queryString = GetExecStoreProcedureString(storeProcedure, fixParams);
            var list = database.SelectSql(selector, queryString, fixParams);
            var result = ParameterUtils.GatherOutputParameters(list.Count, fixParams);
            return (out IList<TResult> models) =>
            {
                models = list;
                return result;
            };
        }

        /// <summary>
        /// Stores the procedure get asynchronous.
        /// </summary>
        /// <typeparam name="TResult">The type of the result.</typeparam>
        /// <param name="database">The database.</param>
        /// <param name="selector">The selector.</param>
        /// <param name="cancellationToken">The cancellation token.</param>
        /// <param name="storeProcedure">The store procedure.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static async Task<StoreResultList<TResult, Dictionary<string, object>>> StoreProcedureGetAsync<TResult>(this DatabaseFacade database,
                                                                                                                        Func<dynamic, TResult> selector,
                                                                                                                        CancellationToken cancellationToken,
                                                                                                                        string storeProcedure,
                                                                                                                        params object[] parameters)
        {
            var fixParams = ConvertParameters(database, ParameterUtils.GetParameters(false, parameters));
            string queryString = GetExecStoreProcedureString(storeProcedure, fixParams);
            var list = await database.SelectSqlAsync(selector, cancellationToken, queryString, fixParams).ConfigureAwait(false);
            var result = ParameterUtils.GatherOutputParameters(list.Count, fixParams);
            return (out IList<TResult> models) =>
            {
                models = list;
                return result;
            };
        }

        /// <summary>
        /// Stores the procedure get asynchronous.
        /// </summary>
        /// <typeparam name="TResult">The type of the result.</typeparam>
        /// <param name="database">The database.</param>
        /// <param name="selector">The selector.</param>
        /// <param name="storeProcedure">The store procedure.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static async Task<StoreResultList<TResult, Dictionary<string, object>>> StoreProcedureGetAsync<TResult>(this DatabaseFacade database,
                                                                                                                        Func<dynamic, TResult> selector,
                                                                                                                        string storeProcedure,
                                                                                                                        params object[] parameters)
        {
            return await database.StoreProcedureGetAsync(selector, storeProcedure, parameters).ConfigureAwait(false);
        }

        /// <summary>
        /// Fetches the store procedure.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="database">The database.</param>
        /// <param name="storeProcedure">The store procedure.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static StoreResultList<T, Dictionary<string, object>, RelationalDataReader> FetchStoreProcedure<T>(this DatabaseFacade database,
                                                                                                                   string storeProcedure,
                                                                                                                   params object[] parameters) where T : class, new()
        {
            var fixParams = ConvertParameters(database, ParameterUtils.GetParameters(false, parameters));
            string queryString = GetExecStoreProcedureString(storeProcedure, fixParams);
            bool nextResult = false;
            var reader = database.FetchSql<T>(queryString, fixParams)(out var list, out nextResult);
            var result = ParameterUtils.GatherOutputParameters(list.Count, fixParams);
            return (out IList<T> models, out Dictionary<string, object> outVars, out bool hasNextResult) =>
            {
                models = list;
                outVars = result;
                hasNextResult = nextResult;
                return reader;
            };
        }

        /// <summary>
        /// Fetches the store procedure.
        /// </summary>
        /// <param name="database">The database.</param>
        /// <param name="storeProcedure">The store procedure.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        /// <remarks></remarks>
        public static StoreResultList<dynamic, Dictionary<string, object>, RelationalDataReader> FetchStoreProcedureDynamic(this DatabaseFacade database,
                                                                                                                   string storeProcedure,
                                                                                                                   params object[] parameters)
        {
            var fixParams = ConvertParameters(database, ParameterUtils.GetParameters(false, parameters));
            string queryString = GetExecStoreProcedureString(storeProcedure, fixParams);
            bool nextResult = false;
            var reader = database.FetchSqlDynamic(queryString, fixParams)(out var list, out nextResult);
            var result = ParameterUtils.GatherOutputParameters(list.Count, fixParams);
            return (out IList<dynamic> models, out Dictionary<string, object> outVars, out bool hasNextResult) =>
            {
                models = list;
                outVars = result;
                hasNextResult = nextResult;
                return reader;
            };
        }

        /// <summary>
        /// Fetches the store procedure json.
        /// </summary>
        /// <param name="database">The database.</param>
        /// <param name="storeProcedure">The store procedure.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static string FetchStoreProcedureJson(this DatabaseFacade database,
                                                                                                                   string storeProcedure,
                                                                                                                   params object[] parameters)
        {
            var fixParams = ConvertParameters(database, ParameterUtils.GetParameters(false, parameters));
            string queryString = GetExecStoreProcedureString(storeProcedure, fixParams);
            var reader = database.FetchSqlJson(queryString, fixParams)(out string data);
            return data;
        }

        /// <summary>
        /// Fetches the store procedure asynchronous.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="database">The database.</param>
        /// <param name="cancellationToken">The cancellation token.</param>
        /// <param name="storeProcedure">The store procedure.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static async Task<StoreResultList<T, Dictionary<string, object>, RelationalDataReader>> FetchStoreProcedureAsync<T>(this DatabaseFacade database,
                                                                                                                                        CancellationToken cancellationToken,
                                                                                                                                       string storeProcedure,
                                                                                                                                       params object[] parameters) where T : class, new()
        {
            var fixParams = ConvertParameters(database, ParameterUtils.GetParameters(false, parameters));
            string queryString = GetExecStoreProcedureString(storeProcedure, fixParams);
            bool nextResult = false;
            var reader = (await database.FetchSqlAsync<T>(cancellationToken, queryString, fixParams).ConfigureAwait(false))(out var list, out nextResult);
            var result = ParameterUtils.GatherOutputParameters(list.Count, fixParams);
            return (out IList<T> models, out Dictionary<string, object> outVars, out bool hasNextResult) =>
            {
                models = list;
                outVars = result;
                hasNextResult = nextResult;
                return reader;
            };
        }

        /// <summary>
        /// Fetches the store procedure asynchronous.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="database">The database.</param>
        /// <param name="storeProcedure">The store procedure.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static async Task<StoreResultList<T, Dictionary<string, object>, RelationalDataReader>> FetchStoreProcedureAsync<T>(this DatabaseFacade database,
                                                                                                                                       string storeProcedure,
                                                                                                                                       params object[] parameters) where T : class, new()
        {
            return await database.FetchStoreProcedureAsync<T>(CancellationToken.None, storeProcedure, parameters).ConfigureAwait(false);
        }

        /// <summary>
        /// Fetches the store procedure.
        /// </summary>
        /// <typeparam name="TResult">The type of the result.</typeparam>
        /// <param name="database">The database.</param>
        /// <param name="selector">The selector.</param>
        /// <param name="storeProcedure">The store procedure.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static StoreResultList<TResult, Dictionary<string, object>, RelationalDataReader> FetchStoreProcedure<TResult>(this DatabaseFacade database,
                                                                                                                                Func<dynamic, TResult> selector,
                                                                                                                                string storeProcedure,
                                                                                                                                params object[] parameters)
        {
            var fixParams = ConvertParameters(database, ParameterUtils.GetParameters(false, parameters));
            string queryString = GetExecStoreProcedureString(storeProcedure, fixParams);
            bool nextResult = false;
            var reader = database.FetchSql(selector, queryString, fixParams)(out var list, out nextResult);
            var result = ParameterUtils.GatherOutputParameters(list.Count, fixParams);
            return (out IList<TResult> models, out Dictionary<string, object> outVars, out bool hasNextResult) =>
            {
                models = list;
                outVars = result;
                hasNextResult = nextResult;
                return reader;
            };
        }

        /// <summary>
        /// Fetches the store procedure asynchronous.
        /// </summary>
        /// <typeparam name="TResult">The type of the result.</typeparam>
        /// <param name="database">The database.</param>
        /// <param name="selector">The selector.</param>
        /// <param name="cancellationToken">The cancellation token.</param>
        /// <param name="storeProcedure">The store procedure.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static async Task<StoreResultList<TResult, Dictionary<string, object>, RelationalDataReader>> FetchStoreProcedureAsync<TResult>(this DatabaseFacade database,
                                                                                                                                                Func<dynamic, TResult> selector,
                                                                                                                                                CancellationToken cancellationToken,
                                                                                                                                                string storeProcedure,
                                                                                                                                                params object[] parameters)
        {
            var fixParams = ConvertParameters(database, ParameterUtils.GetParameters(false, parameters));
            string queryString = GetExecStoreProcedureString(storeProcedure, fixParams);
            bool nextResult = false;
            var reader = (await database.FetchSqlAsync(selector, cancellationToken, queryString, fixParams).ConfigureAwait(false))(out var list, out nextResult);
            var result = ParameterUtils.GatherOutputParameters(list.Count, fixParams);
            return (out IList<TResult> models, out Dictionary<string, object> outVars, out bool hasNextResult) =>
            {
                models = list;
                outVars = result;
                hasNextResult = nextResult;
                return reader;
            };
        }
    }
}