ABP执行原生sql

using Abp.Data;
using Abp.EntityFrameworkCore;
using HY.OLAP.Report;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using MyProject.Core.HY.OLAP;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Threading.Tasks;
using static HY.OLAP.Report.供应商明细账PagedDto;

namespace MyProject.EntityFrameworkCore.Repositories
{

    /// <summary>
    /// LYF
    /// </summary>在ABP种执行原生sql和存储过程
    /// <typeparam name="TEntity"></typeparam>
    /// <typeparam name="TPrimaryKey"></typeparam>
    ///<remarks>
    ///We will create a custom repository to do some basic operations on User entity using stored procedure, view and user defined function. To implement a custom repository,
    ///just derive from your application specific base repository class.
    ///</remarks>
    public abstract class SqlHelperRepository<TEntity, TPrimaryKey> : HYProjectRepositoryBase<Vip佣金计提单, string>, IVip佣金计提单Repository
    {
        private readonly IActiveTransactionProvider _transactionProvider;

        private readonly IDbContextProvider<SecondDbContext> _dbContextProvider;


        /// <summary>
        /// 说明：ABP中使用了依赖注入容器Castle.Windsor，因此构造函数中的注入对象由容器自动注入
        /// </summary>
        /// <param name="dbContextProvider">dbContextProvider对象由容器Castle.Windsor自动注入</param>
        /// <param name="transactionProvider"></param>
        public SqlHelperRepository(IDbContextProvider<SecondDbContext> dbContextProvider, IActiveTransactionProvider transactionProvider)
            : base(dbContextProvider)
        {
            _transactionProvider = transactionProvider;
            _dbContextProvider = dbContextProvider;
        }

        public DbCommand CreateCommand(string commandText, CommandType commandType, params SqlParameter[] parameters)
        {
            EnsureConnectionOpen();
            var command = _dbContextProvider.GetDbContext().Database.GetDbConnection().CreateCommand();
            command.CommandText = commandText;
            command.CommandType = commandType;
            command.Transaction = GetActiveTransaction();
            foreach (var parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            return command;
        }

        /// <summary>
        /// 执行给定的命令
        /// </summary>
        /// <param name="sql">命令字符串</param>
        /// <param name="parameters">要应用于命令字符串的参数</param>
        /// <returns>执行命令后由数据库返回的结果</returns>
        public async Task<int> Execute(string sql, params object[] parameters)
        {
            ///DbSet提供了四个方法用来执行原生的sql查询
            /（二 ）执行字符串插值的字符串，用C#6 新语法，用$
            ///C#6.0新语法：
            //  3 FromSQLIntepolated($"selct * from where x={var}");
            //  4 FromSQLIntepolatedAsync($"selct * from where x={var}");
            //执行非查询类SQL
            //一、执行非字符串插值字符串
            //    Context.Database.ExecuteSQLRaw()
            //    Context.Database.ExecuteSQLRawAsync();
            //二、执行字符串插值字符串
            //    Context.Database.ExecuteSQLInterpolated()
            // Context.Database.ExecuteSQLInterpolatedAsync()


            return await _dbContextProvider.GetDbContext().Database.ExecuteSqlRawAsync(sql, parameters);
        }

        /// <summary>
        /// 执行命令返回集合
        /// </summary>
        /// <param name="sql">命令字符串</param>
        /// <param name="parameters">参数</param>
        /// <returns>结果集</returns>
        public async Task<IQueryable> ExecuteDataTable(string sql, params object[] parameters)
        {
            IQueryable dt = _dbContextProvider.GetDbContext().Vip客户档案s.FromSqlRaw(sql, parameters);
            return await Task.FromResult(dt);
        }

            /// <summary>
        /// 
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<List<VIP佣金余额表OutDto>> GetVIP佣金余额表(VipCommissionPagedDto input)
        {
            await EnsureConnectionOpenAsync();

            SqlParameter[] parameters = {

             new SqlParameter("@AddDateStart", SqlDbType.DateTime),
             new SqlParameter("@AddDateEnd", SqlDbType.DateTime)
            };

            parameters[0].Value = input.AddDateStart;
            parameters[1].Value = input.AddDateEnd;


            using (var command = CreateCommand("GetVipYongJinShengYuMoney", CommandType.StoredProcedure, parameters))
            {
                using (var dataReader = await command.ExecuteReaderAsync())
                {
                    var result = new List<VIP佣金余额表OutDto>();

                    while (dataReader.Read())
                    {
                        VIP佣金余额表OutDto vIP = new VIP佣金余额表OutDto()
                        {
                            Name = dataReader["Name"].ToString(),
                            QC = (decimal)dataReader["QC"],
                            BQFS = (decimal)dataReader["BQFS"],
                            BQFK = (decimal)dataReader["BQFK"],
                            JY = (decimal)dataReader["JY"]
                        };
                        result.Add(vIP);
                    }
                    return result;
                }
            }
        }
        /// <summary>
        /// User Defined Function
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<List<VIP佣金余额表OutDto>> GetVIP佣金余额表2(VipCommissionPagedDto input)
        {
            await EnsureConnectionOpenAsync();

            using (var command = CreateCommand("SELECT dbo.GetUsernameById(@id)", CommandType.Text, new SqlParameter("@id", input.AddDateEnd)))
            {
                var username = (await command.ExecuteScalarAsync()).ToString();
                return new List<VIP佣金余额表OutDto>();
            }
        }
        /// <summary>
        /// Here is another example that sends a parameter to a stored procedure to delete a user:
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        //public async Task DeleteUser(EntityDto input)
        //{
        //    await _dbContextProvider.GetDbContext()(
        //        "EXEC DeleteUserById @id",
        //        default(CancellationToken),
        //        new SqlParameter("id", input.Id)
        //    );
        //}

        /// <summary>
        /// You can call a view like that:
        /// </summary>
        /// <returns></returns>
        public async Task<List<string>> GetAdminUsernames()
        {
            await EnsureConnectionOpenAsync();
            using (var command = CreateCommand("SELECT * FROM dbo.UserAdminView", CommandType.Text))
            {
                using (var dataReader = await command.ExecuteReaderAsync())
                {
                    var result = new List<string>();
                    while (dataReader.Read())
                    {
                        result.Add(dataReader["UserName"].ToString());
                    }
                    return result;
                }
            }
        }

     


        public void EnsureConnectionOpen()
        {
            var connection = _dbContextProvider.GetDbContext().Database.GetDbConnection();
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
        }
        private async Task EnsureConnectionOpenAsync()
        {
            var connection = _dbContextProvider.GetDbContext().Database.GetDbConnection();

            if (connection.State != ConnectionState.Open)
            {
                await connection.OpenAsync();
            }
        }
        private DbTransaction GetActiveTransaction()
        {
            return (DbTransaction)_transactionProvider.GetActiveTransaction(new ActiveTransactionProviderArgs
            {
                {"ContextType", typeof(SecondDbContext) },
                {"MultiTenancySide", MultiTenancySide }
            });
        }



    }
}