Invalid operation. The connection is closed

[wendt88]

Hi, in my application I make 2 api requests, sometimes I got on the second request that message: The connection does not support MultipleActiveResultSets. After adding MultipleActiveResultSets=True in my connection string I got sometimes an other message: Invalid operation. The connection is closed. But always only the second request fails. Is there missing a check if the connection is opened?

My code:

SqlParameter xmlInput = new SqlParameter("@xmlInput", SqlDbType.Xml);
xmlInput.Value = "<SQLP>...</SQLP>";
dbAdapter.Select($"storedProcedureName @{nameof(parameters.xmlInput)}", parameters.xmlInput)

[VitaliyMF]

@wendt88 it seems you use the same shared dbAdapter instance (and the same IDbConnection) from several threads (or asp.net requests) simultaneously. Not every database supports this scenario. Unless you know that you want to use SQL Server MARS (multiple-active-result-sets) feature, you should avoid using shared DB connection.

If this is ASP.NET Core app, ensure that IDbConnection and DbDataAdapter is added with services.AddScoped (example: https://github.com/nreco/data/blob/master/examples/SqliteDemo.WebApi/Data/NRecoDataServiceCollectionsExt.cs ). In classic ASP.NET application ensure that you create new instances of IDbConnection and DbDataAdapter for each request.

p.s. for questions like that I guess StackOverflow is better place - other users will able to find an answer much easier that in github issues. Can you duplicate it on SO with "nreco" tag?..

[wendt88]

I use a ASP.NET Core with MSSQL and the code from your NRecoDataServiceCollectionsExt class. The 2 api calls point to 2 diffrent controllers, calling different stored procedures. The longer call of those usually fails

[VitaliyMF]

'The connection does not support MultipleActiveResultSets' error definitely means that the same connection is used simultaneously; adding 'MultipleActiveResultSets=True' is not a solution until you really need to have 2 active data readers on the same opened DB connection.

It's hard to say where this may happen in your code. AddScoped guarantees that new instance of connection + DbDataAdapter is created for every asp.net request; also you may check that you don't save reference to DbDataAdapter in static field/property.

[VitaliyMF]

one more suggestion: in your code snippet I see that you use synchronous 'Select' method; this error is also possible if you try to execute several async selects in parallel, something like:

var t1 = dbAdapter.SelectAsync(...);  // or Task.Run( ()=>dbAdapter.Select(..) )
var t2 = dbAdapter.SelectAsync(...);
await Task.WhenAll(t1, t2);

Without DB connection that supports MARS parallel queries are not supported. With MARS, you need to manually open and close IDbConnection, something like this:

dbAdapter.Connection.Open();
try {
  var t1 = dbAdapter.SelectAsync(...);
  var t2 = dbAdapter.SelectAsync(...);
  await Task.WhenAll(t1, t2);
} finally {
  dbAdapter.Connection.Close();
}

[wendt88]

thank you for your answer! i was calling a async method to generate sqlparameters value, this method called db async and closed the connection. your workaround worked