1

From time to time we receive the following database connection error from PetaPoco in an ASP.NET MVC 4 app:

There is already an open DataReader associated with this Command which must be closed first.;
System.Data;    at System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command)...

It seems like this happens as we get more load to the system.

Some suggestions we found as we researched were:

  • Do a PetaPoco Fetch instead of a Query
  • Add MultipleActiveResultSets=True to our connection string

Can someone with PetaPoco experience verify that these suggestions would help? Any other suggestions to avoid the Exception would be appreciated.

Update 06/10/2013 We changed the Query to a Fetch and we have seen some improvement however we still sometimes see the error.

Does anyone know what drawbacks changing the connection string to MultipleActiveResultSets=True might have?

Ken Burkhardt
  • 3,528
  • 6
  • 33
  • 45
  • how did you solve this issue? It is also happened to me. I am using Castle Windsor with PerWebRequest lifestyle (I think it is similar to Structuremap HttpContextScoped) – ensecoz May 20 '13 at 08:46
  • we tried changing the Query to a Fetch and had some good results but still sometimes see the problem. How about you? – Ken Burkhardt Jun 10 '13 at 19:17

1 Answers1

2

Be sure that you are creating the PetaPoco DB per request (not a static).

See: how to create a DAL using petapoco

Update 06/10/2013 All Fetch methods calls the Query method (see the source)

enter image description here

So changing one for the other has no effect on the error.

The drawbacks are listed on the MSDN and includes warnings with:

  • Statement Interleaving
  • Session Cache
  • Thread Safety
  • Connection Pooling
  • Parallel Execution

I have tried it personally and didn't got any drawbacks (depends on your app), but didn't get rid of the errors also.

The only thing that you can do to remove the error, it's follow your request code to find where in the code the statement is called twice, and then use other DB connection in that function.

Also, you can catch the error and then create a new db connection and try with that new one.

Sorry but not magic bullet here.

Community
  • 1
  • 1
Eduardo Molteni
  • 38,786
  • 23
  • 141
  • 206
  • yes we are creating the PetaPoco DB per request using StructureMap HttpContextScoped. Is that the best practice or should it be transient? Would MultipleActiveResultSets=True help us? – Ken Burkhardt Apr 29 '13 at 13:46
  • Sorry, I don't know StructureMap. If you are getting the error it's because there two or more queries accessing the same results in the same connection at the same time. – Eduardo Molteni Apr 29 '13 at 15:59