c# - Aspire SQL Server connection issues - Stack Overflow

admin管理员组

文章数量:1317894

I've recently switched to using Aspire and I'm noticing some strange behaviors. I'm getting the following error when trying to access the database:

System.InvalidOperationException: BeginExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized. at Microsoft.Data.SqlClient.SqlCommand.<>c.b__195_0(Task1 result) at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke() at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state) --- End of stack trace from previous location --- at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread) --- End of stack trace from previous location --- at Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 434
at CRM.Domain.Common.Repositories.RepositoryBase.GetManyStoredProcedure[TResult](String storedProcedure, Object parameters) in C:\Users\james\source\ERP.Main\ERP.CRM\src\CRM.Domain\Common\Repositories\RepositoryBase.cs:line 14 at CRM.Domain.CustomerAggregate.Commands.GetAllCustomersCommandHandler.HandleAsync(GetAllCustomersCommand command, CancellationToken token) in C:\Users\james\source\ERP.Main\ERP.CRM\src\CRM.Domain\CustomerAggregate\Commands\GetAllCustomersCommandHandler.cs:line 17 at CRM.Domain.Common.CommandHandlerBase`2.Handle(TCommand command, CancellationToken cancellationToken) in C:\Users\james\source\ERP.Main\ERP.CRM\src\CRM.Domain\Common\CommandHandlerBase.cs:line 38 at CRM.Api.Controllers.Customers.CustomersController.GetAllCustomers() in C:\Users\james\source\ERP.Main\ERP.CRM\src\CRM.Api\Controllers\Customers\CustomersController.cs:line 70 at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Logged|12_1(ControllerActionInvoker invoker) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.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.g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Logged|17_1(ResourceInvoker invoker) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Logged|17_1(ResourceInvoker invoker) at Microsoft.AspNetCore.Routing.EndpointMiddleware.g__AwaitRequestTask|7_0(Endpoint endpoint, Task requestTask, ILogger logger) at Microsoft.AspNetCore.ResponseCaching.ResponseCachingMiddleware.Invoke(HttpContext httpContext) at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context) at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext) at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider) at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)

I've added the Aspire.Hosting.SqlServer to the host and Aspire.Microsoft.Data.SqlClient to all of the projects that need it.

var sql = builder
    .AddSqlServer("sql", password)
    .WithLifetime(ContainerLifetime.Persistent)
    .WithDataBindMount(source: @"C:\SqlServer\Data")
    .WithEndpoint(port: 6033, targetPort: 1433, name: "ssms")
    .WithContainerName("sqlserver");
var crmDatabase = sql.AddDatabase("crm-database");
var crm = builder.AddProject<Projects.CRM_Api>("crm-api")
    .WaitFor(sql)
    .WithReference(crmDatabase);

and added this to the projects.

builder.AddSqlServerClient("crm-database");

I've moved from something simular to:

using var connection = new SqlConnection(connectionString);
await connection.Open();
connection.ExecuteAsync(storedPrcedureName, parameters, CommandTypes.StoredProcedure);

to

injecting the SqlConnection into the constructor and doing something simular to:

await sqlConnection.ExecuteAsync(storedPrcedureName, parameters, CommandTypes.StoredProcedure);

Why would I be getting this issue am i missing something?

I've recently switched to using Aspire and I'm noticing some strange behaviors. I'm getting the following error when trying to access the database:

System.InvalidOperationException: BeginExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized. at Microsoft.Data.SqlClient.SqlCommand.<>c.b__195_0(Task1 result) at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke() at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state) --- End of stack trace from previous location --- at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread) --- End of stack trace from previous location --- at Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 434
at CRM.Domain.Common.Repositories.RepositoryBase.GetManyStoredProcedure[TResult](String storedProcedure, Object parameters) in C:\Users\james\source\ERP.Main\ERP.CRM\src\CRM.Domain\Common\Repositories\RepositoryBase.cs:line 14 at CRM.Domain.CustomerAggregate.Commands.GetAllCustomersCommandHandler.HandleAsync(GetAllCustomersCommand command, CancellationToken token) in C:\Users\james\source\ERP.Main\ERP.CRM\src\CRM.Domain\CustomerAggregate\Commands\GetAllCustomersCommandHandler.cs:line 17 at CRM.Domain.Common.CommandHandlerBase`2.Handle(TCommand command, CancellationToken cancellationToken) in C:\Users\james\source\ERP.Main\ERP.CRM\src\CRM.Domain\Common\CommandHandlerBase.cs:line 38 at CRM.Api.Controllers.Customers.CustomersController.GetAllCustomers() in C:\Users\james\source\ERP.Main\ERP.CRM\src\CRM.Api\Controllers\Customers\CustomersController.cs:line 70 at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Logged|12_1(ControllerActionInvoker invoker) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.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.g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Logged|17_1(ResourceInvoker invoker) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Logged|17_1(ResourceInvoker invoker) at Microsoft.AspNetCore.Routing.EndpointMiddleware.g__AwaitRequestTask|7_0(Endpoint endpoint, Task requestTask, ILogger logger) at Microsoft.AspNetCore.ResponseCaching.ResponseCachingMiddleware.Invoke(HttpContext httpContext) at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context) at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext) at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider) at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddlewareImpl.Invoke(HttpContext context)

I've added the Aspire.Hosting.SqlServer to the host and Aspire.Microsoft.Data.SqlClient to all of the projects that need it.

var sql = builder
    .AddSqlServer("sql", password)
    .WithLifetime(ContainerLifetime.Persistent)
    .WithDataBindMount(source: @"C:\SqlServer\Data")
    .WithEndpoint(port: 6033, targetPort: 1433, name: "ssms")
    .WithContainerName("sqlserver");
var crmDatabase = sql.AddDatabase("crm-database");
var crm = builder.AddProject<Projects.CRM_Api>("crm-api")
    .WaitFor(sql)
    .WithReference(crmDatabase);

and added this to the projects.

builder.AddSqlServerClient("crm-database");

I've moved from something simular to:

using var connection = new SqlConnection(connectionString);
await connection.Open();
connection.ExecuteAsync(storedPrcedureName, parameters, CommandTypes.StoredProcedure);

to

injecting the SqlConnection into the constructor and doing something simular to:

await sqlConnection.ExecuteAsync(storedPrcedureName, parameters, CommandTypes.StoredProcedure);

Why would I be getting this issue am i missing something?

• c#
• dapper
• sqlclient

Share Improve this question Follow edited Jan 22 at 15:34 Panagiotis Kanavos 132k1616 gold badges203203 silver badges265265 bronze badges asked Jan 22 at 14:56 3xGuy3xGuy 2,56922 gold badges3535 silver badges5656 bronze badges 2

• .NET Aspire doesn't manage database connections. The error comes from custom application classes like CommandHandlerBase.Handle and the custom RepositoryBase class. It complains that no transaction was passed to the SqlCommand/Dapper's ExecuteAsync even though there's a pending transaction. That wasn't done by Aspire – Panagiotis Kanavos Commented Jan 22 at 15:06
• Somewhere in RepositoryBase or one of the other application classes a call is made to SqlConnection.BeginTransaction. The code in RepositoryBase.GetManyStoredProcedure must pass it as the transaction: parameter in calls to Dapper's QueryAsync – Panagiotis Kanavos Commented Jan 22 at 15:15

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

This isn't related to Aspire. Aspire doesn't change how database connections work.

The error and stack trace show that a call is made to Dapper's QueryAsync without passing the active transaction to the method:

System.InvalidOperationException: BeginExecuteReader requires the command 
to have a transaction when the connection assigned to the command is in a 
pending local transaction. The Transaction property of the command has not been initialized. 
   at ...
   at Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 434
   at CRM.Domain.Common.Repositories.RepositoryBase.GetManyStoredProcedure[TResult](String storedProcedure, Object parameters) ) in C:\Users\james\source\ERP.Main\ERP.CRM\src\CRM.Domain\Common\Repositories\RepositoryBase.cs:line 14 

This shows the problem is in CRM.Domain.Common.Repositories.RepositoryBase.GetManyStoredProcedure, line 14

本文标签： cAspire SQL Server connection issuesStack Overflow