This SqlTransaction has completed; it is no longer usable #4746

10 Answer(s)

• 

  0

  maliming created one year ago

  Support Team Fullstack Developer

  hi

  Can I reproduce the problem locally?

  Save Cancel
• 

  0

  bhasinp created one year ago

  Hi maliming,

  Let me give you some sample code for this.

  The method, GetOrgUnitUsers is present under EfCoreUserRepository:EfCoreRepository<motrDbContext, IdentityUser>, IEfCoreUserRepository

  So when I call GetOrgUnitUsers from my API endpoint (ApplicationService), I get the response from SP and everything at code level works well, but as soon as I return the response from the API, in browser I get the above error response.

  public async Task<List<WorkGroupUser>> GetOrgUnitUsers(string OrgUnitIds, CancellationToken cancellationToken = default)
  {
      await EnsureConnectionOpenAsync(cancellationToken);
  
  
      using (var command = CreateCommand("GetOrgUnitUsers", CommandType.StoredProcedure, new SqlParameter("OrgUnitIds", OrgUnitIds)))
      {
  
          using (var dataReader = ((await command).ExecuteReaderAsync(cancellationToken)))
          {
              var result = new List<WorkGroupUser>();
              var reader = (await dataReader);
  
  
              while (await reader.ReadAsync(cancellationToken))
              {
                  result.Add(new WorkGroupUser
                  {
                      OrgUnitId = reader["OrgUnitId"].ToString(),
                      Id = reader["Id"].ToString(),
                      Name = reader["Name"].ToString(),
                      Surname = reader["Surname"].ToString(),
                      DisplayName = reader["DisplayName"].ToString(),
                      Email = reader["Email"].ToString()
                  });
  
  
              }
              await CloseConnectionAsync();
  
              return result;
          }
      }
  
  }
  

  Helper Methods

  private async Task<DbCommand> CreateCommand(string commandText, CommandType commandType, params SqlParameter[] parameters)
  {
          var context = await GetDbContextAsync();
          var command = context.Database.GetDbConnection().CreateCommand();
  
          command.CommandText = commandText;
          command.CommandType = commandType;
          command.Transaction = context.Database.CurrentTransaction?.GetDbTransaction();
  
          foreach (var parameter in parameters)
          {
              command.Parameters.Add(parameter);
          }
  
          return command;
      }
  
      private async Task EnsureConnectionOpenAsync(CancellationToken cancellationToken = default)
      {
          var connection = (await GetDbContextAsync()).Database.GetDbConnection();
  
          if (connection.State != ConnectionState.Open)
          {
              await connection.OpenAsync(cancellationToken);
          }
      }
      private async Task CloseConnectionAsync()
      {
          var connection = (await GetDbContextAsync()).Database.GetDbConnection();
  
          if (connection.State != ConnectionState.Closed)
          {
              await connection.CloseAsync();
          }
      }
      ```
  

  Save Cancel
• 

  0

  maliming created one year ago

  Support Team Fullstack Developer

  hi I think you don't need to call CloseConnectionAsync

  We have a sample.

  https://github.com/abpframework/abp-samples/blob/master/StoredProcedureDemo/src/StoredProcedureDemo.EntityFrameworkCore/EntityFrameworkCore/Users/AppUserRepository.cs#L16

  Save Cancel
• 

  0

  bhasinp created one year ago

  Hi If I remove CloseConnectionAsync on next database call after fetching data from SP, I get following error

  {"error":{"code":null,"message":"There is already an open DataReader associated with this Connection which must be closed first.","details":"InvalidOperationException: There is already an open DataReader associated with this Connection which must be closed first.\r\nSTACK TRACE: at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__188_0(Task1 result)\r\n at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke()\r\n at System.Threading.Tasks.Task.<>c.<.cctor>b__272_0(Object obj)\r\n at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)\r\n--- End of stack trace from previous location ---\r\n at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)\r\n at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)\r\n--- End of stack trace from previous location ---\r\n at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)\r\n at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)\r\n at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)\r\n at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func4 operation, Func4 verifySucceeded, CancellationToken cancellationToken)\r\n at Microsoft.EntityFrameworkCore.Query.Internal.SplitQueryingEnumerable1.AsyncEnumerator.MoveNextAsync()\r\n at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken cancellationToken)\r\n at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken cancellationToken)\r\n at Volo.Abp.SettingManagement.EntityFrameworkCore.EfCoreSettingRepository.GetListAsync(String providerName, String providerKey, CancellationToken cancellationToken)\r\n at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo)\r\n at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapterWithReturnValue1.ProceedAsync()\r\n at Volo.Abp.Uow.UnitOfWorkInterceptor.InterceptAsync(IAbpMethodInvocation invocation)\r\n at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter1.InterceptAsync[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo, Func3 proceed)\r\n at Volo.Abp.SettingManagement.SettingManagementStore.SetCacheItemsAsync(String providerName, String providerKey, String currentName, SettingCacheItem currentCacheItem)\r\n at Volo.Abp.SettingManagement.SettingManagementStore.GetCacheItemAsync(String name, String providerName, String providerKey)\r\n at Volo.Abp.SettingManagement.SettingManagementStore.GetOrNullAsync(String name, String providerName, String providerKey)\r\n at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo)\r\n at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapterWithReturnValue1.ProceedAsync()\r\n at Volo.Abp.Uow.UnitOfWorkInterceptor.InterceptAsync(IAbpMethodInvocation invocation)\r\n at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter1.InterceptAsync[TResult](IInvocation invocation, IInvocationProceedInfo proceedInfo, Func3 proceed)\r\n at Volo.Abp.SettingManagement.SettingManagementProvider.GetOrNullAsync(SettingDefinition setting, String providerKey)\r\n at Volo.Abp.SettingManagement.SettingManager.GetOrNullInternalAsync(String name, String providerName, String providerKey, Boolean fallback)\r\n at motr.SharedService.CommunicationService.SendCommunication(SendEmailViewModel model) in D:\Teams\Work\Initto\mot-r-V2\aspnet-core\src\motr.Application\SharedService\CommunicationService.cs:line 47\r\n at motr.Messages.MessageExtnAppService.BroadcastMessageAsync(SendBroadcastMessageViewModel input) in D:\Teams\Work\Initto\mot-r-V2\aspnet-core\src\motr.Application\Messages\MessageExtnAppService.cs:line 92\r\n at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous(IInvocation invocation, IInvocationProceedInfo proceedInfo)\r\n at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapter.ProceedAsync()\r\n at Volo.Abp.GlobalFeatures.GlobalFeatureInterceptor.InterceptAsync(IAbpMethodInvocation invocation)\r\n at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter1.InterceptAsync(IInvocation invocation, IInvocationProceedInfo proceedInfo, Func3 proceed)\r\n at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous(IInvocation invocation, IInvocationProceedInfo proceedInfo)\r\n at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapter.ProceedAsync()\r\n at Volo.Abp.Auditing.AuditingInterceptor.InterceptAsync(IAbpMethodInvocation invocation)\r\n at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter1.InterceptAsync(IInvocation invocation, IInvocationProceedInfo proceedInfo, Func3 proceed)\r\n at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous(IInvocation invocation, IInvocationProceedInfo proceedInfo)\r\n at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapter.ProceedAsync()\r\n at Volo.Abp.Validation.ValidationInterceptor.InterceptAsync(IAbpMethodInvocation invocation)\r\n at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter1.InterceptAsync(IInvocation invocation, IInvocationProceedInfo proceedInfo, Func3 proceed)\r\n at Castle.DynamicProxy.AsyncInterceptorBase.ProceedAsynchronous(IInvocation invocation, IInvocationProceedInfo proceedInfo)\r\n at Volo.Abp.Castle.DynamicProxy.CastleAbpMethodInvocationAdapter.ProceedAsync()\r\n at Volo.Abp.Uow.UnitOfWorkInterceptor.InterceptAsync(IAbpMethodInvocation invocation)\r\n at Volo.Abp.Castle.DynamicProxy.CastleAsyncAbpInterceptorAdapter1.InterceptAsync(IInvocation invocation, IInvocationProceedInfo proceedInfo, Func3 proceed)\r\n at lambda_method4651(Closure , Object )\r\n at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)\r\n at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)\r\n at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.

  Save Cancel
• 

  0

  maliming created one year ago

  Support Team Fullstack Developer

  hi

  Can you try the code that I shared?

  https://github.com/abpframework/abp-samples/blob/master/StoredProcedureDemo/src/StoredProcedureDemo.EntityFrameworkCore/EntityFrameworkCore/Users/AppUserRepository.cs#L16

  Save Cancel
• 

  0

  bhasinp created one year ago

  Hi,

  I tried above link code and the errors are same, no change in output.

  Thanks

  Save Cancel
• 

  0

  maliming created one year ago

  Support Team Fullstack Developer

  hi

  Please share a sample project with me, and I will check it. Thanks. liming.ma@volosoft.com

  Save Cancel
• 

  0

  maliming created one year ago

  Support Team Fullstack Developer

  hi

  

  public async Task<List<string>> GetOrgUnitUsers(CancellationToken cancellationToken = default)
  {
      await EnsureConnectionOpenAsync(cancellationToken);
      using (var command = await CreateCommand("GetOrgUnitUsers", CommandType.StoredProcedure, new SqlParameter("OrgUnitIds", "1")))
      {
          using (var dataReader = await command.ExecuteReaderAsync(cancellationToken))
          {
              var result = new List<string>();
  
              while (await dataReader.ReadAsync(cancellationToken))
              {
                  result.Add(dataReader["Id"].ToString());
  
              }
              return result;
          }
      }
  }
  

  Save Cancel
• 

  0

  bhasinp created one year ago

  You reviewed this method ?

  public async Task<List<string>> GetOrgUnitsAsync() { try { var result = await _userExtAppService.GetOrgUnitsAsync(); var rr= await _efCoreUserRepository.GetOrgUnitUsers(); // I'm getting one error here return result; } catch(Exception exp) { return new List<string>() { "Failed" }; } }

  Save Cancel
• 

  0

  maliming created one year ago

  Support Team Fullstack Developer

  hi

  It works the same.

  

  Save Cancel