private async Task <ExecuteProcedureResult <T> > ExecuteProcedure <T>(SqlQueryResultType resultType, SqlQuery query,
                                                                              Func <IDataReader, T> getQueryDataResult = null, Func <IDictionary <string, object>, T> getQueryOutParamsResult = null, bool getReturnedValue = false)
        {
            try
            {
                using (var con = new SqlConnection(_connectionString))
                {
                    using (var cmd = con.CreateCommand())
                    {
                        if (getReturnedValue)
                        {
                            query.Params.Add(new SqlParameter(ReturnParameterName, SqlDbType.Int)
                            {
                                Direction = ParameterDirection.ReturnValue
                            });
                        }

                        SetupCommand(cmd, query);
                        await con.OpenAsync();

                        var result = new ExecuteProcedureResult <T>();
                        switch (resultType)
                        {
                        case SqlQueryResultType.Data:
                        {
                            var reader = await cmd.ExecuteReaderAsync();

                            result.SelectedResult = getQueryDataResult(reader);

                            reader.Close();

                            break;
                        }

                        case SqlQueryResultType.OutParams:
                        {
                            await cmd.ExecuteNonQueryAsync();

                            var outParams = GetOutputParametrs(cmd.Parameters);

                            result.SelectedResult = getQueryOutParamsResult(outParams);

                            break;
                        }

                        case SqlQueryResultType.None:
                        {
                            await cmd.ExecuteNonQueryAsync();

                            break;
                        }

                        default:
                            throw new NotImplementedException();
                        }

                        if (getReturnedValue)
                        {
                            result.ReturnedCode = GetReturnedValue(cmd.Parameters);
                        }

                        return(result);
                    }
                }
            }
            catch (Exception e)
            {
                SqlException sqlException = e as SqlException;
                if (sqlException != null)
                {
                    //customize this as please
                    switch (sqlException.Number)
                    {
                    case 2627:      // Unique constraint error
                    case 547:       // Constraint check violation
                    case 2601:      // Duplicated key row error
                        throw new DuplicateNameException();

                    case 50000:     //validation exception
                        throw new NotSupportedException(e.Message);

                    case 60001:
                        throw new NotSupportedException(e.Message);

                    default:
                        throw;
                    }
                }
                Console.WriteLine(e);
                throw;
            }
        }
        /// <summary>
        /// Executes and returns the SQL RETURN code.
        /// </summary>
        public async Task <int?> ExecuteAndGetReturnedCodeAsync(SqlQuery query)
        {
            ExecuteProcedureResult <bool> result = await ExecuteProcedure <bool>(SqlQueryResultType.None, query, getReturnedValue : true);

            return(result.ReturnedCode);
        }
        /// <summary>
        /// Returns stored procedure data and returns the SQL RETURN code.
        /// </summary>
        public async Task <ExecuteProcedureResult <T> > ExecuteAndGetReturnedCodeAsync <T>(SqlQuery query, Func <IDataReader, T> getQueryResult)
        {
            ExecuteProcedureResult <T> result = await ExecuteProcedure(SqlQueryResultType.Data, query, getQueryDataResult : getQueryResult, getReturnedValue : true);

            return(result);
        }
        /// <summary>
        /// Returns only stored procedure data.
        /// </summary>
        public async Task <T> ExecuteAsync <T>(SqlQuery query, Func <IDataReader, T> getQueryResult)
        {
            ExecuteProcedureResult <T> result = await ExecuteProcedure(SqlQueryResultType.Data, query, getQueryDataResult : getQueryResult);

            return(result.SelectedResult);
        }
        /// <summary>
        /// Returns stored procedure out parameters and returns the SQL RETURN code.
        /// </summary>
        public async Task <ExecuteProcedureResult <T> > ExecuteAndGetReturnedCodeAsync <T>(SqlQuery query, Func <IDictionary <string, object>, T> getQueryResult)
        {
            ExecuteProcedureResult <T> result = await ExecuteProcedure(SqlQueryResultType.OutParams, query, getQueryOutParamsResult : getQueryResult, getReturnedValue : true);

            return(result);
        }
        /// <summary>
        /// Returns only stored procedure out parameters.
        /// </summary>
        public async Task <T> ExecuteAsync <T>(SqlQuery query, Func <IDictionary <string, object>, T> getQueryResult)
        {
            ExecuteProcedureResult <T> result = await ExecuteProcedure(SqlQueryResultType.OutParams, query, getQueryOutParamsResult : getQueryResult);

            return(result.SelectedResult);
        }