TransactionScope() There is already an open DataReader associated with this Command which must be closed first.

Topics: Data Access Application Block
Jun 26, 2010 at 11:47 AM
Edited Jun 29, 2010 at 8:46 PM

I am using TransactionScope and Entity Frame Work library(V 5.0) and I am getting following error.  This only happens on my Test server and not my local box(Windows 7, SQL 2005).

There is already an open DataReader associated with this Command which must be closed first.

using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.Required))
{

 Call to API1 ();
 
 Call to API2 ();
 
 call to API3();
 
 transactionScope.Complete():

}

Test Server:  .net frame work 3.5, Windows 2003, Sql Server 2005, DTC service is running.  Also, under windows comonent, Application Server, Enable network DTC access is checked.

When I test this on my local box,  Windows 7 Sql Server 2005, everything works fine and no errors.

What is the best solution to fix this problem.  If you have any suggestion on database code outlined below please let me know.

The API that contains TransactionScope calls other API's and each one of them does some database work that uses Enterprise Library.  The outline for each database access method is like below.

API1, API2 and API3 all call database access layer method with signature like this
----------------------------------------------------------------------


try
{

 Database db = DatabaseFactory.CreateDatabase();

 using (DbCommand dbCmd = db.GetStoredProcCommand("usp_GetSomething"))
 {
  db.DiscoverParameters(dbCmd);

 using (IDataReader reader = db.ExecuteReader(dbCmd))
 {
     if (reader.Read())
     {
    ... 
     }
  }
 }
}
catch (Exception ex) { }

Jun 28, 2010 at 1:12 AM

Obvious question would be if you didn't miss closing any IDataReader, did you?  And what and where is the exact line of code which throws the exception?  Are you getting this error intermittently or is it consistent?

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com 

Jun 28, 2010 at 3:37 PM

Sarah,

Majority of my IDataReader are enclose in Using statement.  So, they should be closed or dispose at the end.  The error is consistent.

One difference that I see between my Local Dev Box and TEST server is that my Local Dev Box connection string has MultipleActiveResultSet is set to true.  I guess this explains why I am not getting the same error on my Local Dev Box. 

So, now the question is what is the downside in using MultipleActiveResultSet=True on production server.  

Jun 29, 2010 at 6:33 AM

Hi,

Personally, I haven't used MARS yet in any project, though I think you'll have to assess it yourself if your application is taking advantage of the MARS or MultipleActiveResultSet implementation then it's your option whether to modify your application code or continue using MARS. Given this, we're unable to reproduce your problem so it'll be really helpful if you can provide us a sample app that can simulate the problem. Anyway, here's a blog that I found that I'd hope would help you identify the behavior and downside of using MARS in your application http://blogs.msdn.com/b/sqlnativeclient/archive/2006/09/27/774290.aspx.

Gino Terrado
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

 

 

Jul 7, 2010 at 8:37 PM

Sorry I got little side track and busy with other stuff but I was wondering if you used the following outline in couple of methods(API1, API2, API3) would you get the same error as I am getting without MARS.  I think you should and then my question is how do you do the TransactionScope code such as API1, API2, API3.  thanks again. 

 

try
{

 Database db = DatabaseFactory.CreateDatabase();

 using (DbCommand dbCmd = db.GetStoredProcCommand("usp_GetSomething"))
 {
  db.DiscoverParameters(dbCmd);

 using (IDataReader reader = db.ExecuteReader(dbCmd))
 {
     if (reader.Read())
     {
    ... 
     }
  }
 }
}
catch (Exception ex) { }

Jul 8, 2010 at 6:54 AM

Yes, I have already considered this upon trying to reproduce the problem and I'm doing it the same way as you have stated in your previous post but I'm still unable to reproduce it and I'm not getting the error that you have encountered. Though, one difference we have is I'm already using SQL Server 2008. If you can send us a sample app that can simulate the problem that'll be very helpful to us to be able to investigate your issue further. Thanks.

Gino Terrado
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com