using store procedure in custom repository not working #10231

Hi team,

I want to use store procedure in my project. and i followed the tutorial step by step: https://aspnetboilerplate.com/Pages/Documents/Articles/Using-Stored-Procedures,-User-Defined-Functions-and-Views/index.html.

when i run my project and i get the error:

InvalidOperationException: The connection does not support MultipleActiveResultSets. STACK TRACE: at Microsoft.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command) at Microsoft.Data.SqlClient.SqlInternalTransaction.Rollback() at Microsoft.Data.SqlClient.SqlTransaction.Dispose(Boolean disposing) at Microsoft.EntityFrameworkCore.Storage.RelationalTransaction.Dispose() at Abp.EntityFrameworkCore.Uow.DbContextEfCoreTransactionStrategy.Dispose(IIocResolver iocResolver) at Abp.EntityFrameworkCore.Uow.EfCoreUnitOfWork.DisposeUow() at Abp.Domain.Uow.UnitOfWorkBase.Dispose() at Abp.AspNetCore.Mvc.Uow.AbpUowActionFilter.OnActionExecutionAsync(ActionExecutingContext context, ActionExecutionDelegate next) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync() --- End of stack trace from previous location --- at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextExceptionFilterAsync>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)

So i tried to set MultipleActiveResultSets=true in the connection string and i get another error:

SqlException: The transaction operation cannot be performed because there are pending requests working on this transaction. STACK TRACE: at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at Microsoft.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at Microsoft.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest) at Microsoft.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest) at Microsoft.Data.SqlClient.SqlInternalTransaction.Commit() at Microsoft.Data.SqlClient.SqlTransaction.Commit() at Microsoft.EntityFrameworkCore.Storage.RelationalTransaction.Commit() at Abp.EntityFrameworkCore.Uow.DbContextEfCoreTransactionStrategy.Commit() at Abp.EntityFrameworkCore.Uow.EfCoreUnitOfWork.CommitTransaction() at Abp.EntityFrameworkCore.Uow.EfCoreUnitOfWork.CompleteUowAsync() at Abp.Domain.Uow.UnitOfWorkBase.CompleteAsync() at Abp.AspNetCore.Mvc.Uow.AbpUowActionFilter.OnActionExecutionAsync(ActionExecutingContext context, ActionExecutionDelegate next) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync() --- End of stack trace from previous location --- at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextExceptionFilterAsync>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted

below is my code:

public class DashboardRepository : DynasysSolutionRepositoryBase<Company, long>, IDashboardRepository { private readonly IActiveTransactionProvider _transactionProvider; public DashboardRepository(IDbContextProvider<DynasysSolutionDbContext> dbContextProvider, IActiveTransactionProvider transactionProvider) : base(dbContextProvider) { _transactionProvider = transactionProvider; }

    public async Task<List<GetSupplierStatusOutput>> GetSupplierStatus(int tenantId)
    {

        EnsureConnectionOpen();
        using (var command = CreateCommand("Exec SP_GetSupplierStatisticByStatus @tenantId", CommandType.StoredProcedure, new SqlParameter("@tenantId", tenantId)))
        {
            using (var dataReader = await command.ExecuteReaderAsync())
            {
                List<GetSupplierStatusOutput> list = new List<GetSupplierStatusOutput>();

                while (dataReader.Read())
                {
                    list.Add(new GetSupplierStatusOutput()
                    {
                        Name = dataReader["Status"].ToString(),
                        Count = Convert.ToInt32(dataReader["NumsOfSupplier"])
                    });
                }
                return list;
            }
        }

    }

    private DbCommand CreateCommand(string commandText, CommandType commandType, params SqlParameter[] parameters)
    {
        var command = GetConnection().CreateCommand();

        command.CommandText = commandText;
        command.CommandType = commandType;
        command.Transaction = GetActiveTransaction();
        foreach (var parameter in parameters)
        {
            command.Parameters.Add(parameter);
        }

        return command;
    }

    private void EnsureConnectionOpen()
    {
        var connection = GetConnection();

        if (connection.State != ConnectionState.Open)
        {
            connection.Open();
        }
    }

    private DbTransaction GetActiveTransaction()
    {
        return (DbTransaction)_transactionProvider.GetActiveTransaction(new ActiveTransactionProviderArgs
        {
            {"ContextType", typeof(DynasysSolutionDbContext) },
            {"MultiTenancySide", MultiTenancySide }
        });
    }