Invalid attempt to call Read when reader is closed error when call ExecuteScalar method

Question

I have an ASP.NET application that need to load data fro excel file. The file contains about 20K records. The app reads the data from the file and loop through each record, makes calculations and validations and then insert each record into a DB. Everything works as expected, till the Insert method throws the exception. The error is thrown after 10 - 11 minutes of running. Note: All loading process is running into a Transaction scope that is defined in the following way:

 using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, TimeSpan.MaxValue))

All the time SQLConnection is opened - I have ensure this using an SQL Profiler. In order to work with DB we using an Microsoft.Practices.EnterpriseLibrary.Data.Database object. This is an Insert method:

public bool InsertInspectionRide(InspectionRideBE be)
    {
        bool result = false;
        try
        {
            using (System.Data.Common.DbCommand cmd = db.GetStoredProcCommand("InsertInspectionRide",
                be.param1, be.param2))
            {

                cmd.CommandTimeout = 60000000;


                be.IdRide = Convert.ToInt32(db.ExecuteScalar(cmd));

                result = be.IdRide > 0;
            }
        }
        catch (Exception ex)
        {
            if (ExceptionPolicy.HandleException(ex, "DAL"))
            {
                throw;
            }
        }
        return result;
    }

This is an error:

    06/28/2016 10:27:14
Type : System.Exception, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
Message : 
Source : 
Help link : 
Data : System.Collections.ListDictionaryInternal
TargetSite : 
Stack Trace : The stack trace is unavailable.
Additional Info:

MachineName : XXX
TimeStamp : 6/28/2016 7:27:14 AM
FullName : Microsoft.Practices.EnterpriseLibrary.ExceptionHandling, Version=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
AppDomainName : /XXX-1-131115702788886173
ThreadIdentity : XXX
WindowsIdentity : XXXX
    Inner Exception
    ---------------
    Type : System.InvalidOperationException, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
    Message : Invalid attempt to call Read when reader is closed.
    Source : System.Data
    Help link : 
    Data : System.Collections.ListDictionaryInternal
    TargetSite : Boolean ReadInternal(Boolean)
    Stack Trace :    at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
       at System.Data.SqlClient.SqlDataReader.Read()
       at System.Data.SqlClient.SqlCommand.CompleteExecuteScalar(SqlDataReader ds, Boolean returnSqlValue)
       at System.Data.SqlClient.SqlCommand.ExecuteScalar()
       at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteScalar(IDbCommand command)
       at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteScalar(DbCommand command)
       at EnforcementService.DataAccess.InspectionRideDAL.InsertInspectionRide(InspectionRideBE be)

I have search info about this error and the main idea is that the connection is closed, but I can't figure out why?

I would appreciate any help or advice

Add `Debug.Print(myConn.State)`. You will find that the connection is closed. Now trace backwards. this piece of code likely is innocent. — usr, Jul 03 '16 at 14:45
Hi! I understand that at the moment the exception is thrown the connection is closed. I need to understand the reason of closing. Thanks — Lev Z, Jul 03 '16 at 15:17

score 0 · Answer 1 · edited May 23 '17 at 12:22

Ok, I have found the source of the problem. It's a transaction timeout. System.Transaction has MaxTimeout property and it's default value is 10 minutes. Using the code or app/web config you can only reduce it's value. In order to increase it, you have to configure it in machine.config file by adding following block in the END of configuration section of the file.

<system.transactions>
 <machineSettings maxTimeout="01:00:00" />
</system.transactions>
</configuration>

Here is some relevant articles: Override the System.Transactions default timeout of 10 minutes in the code.

maxTimeout value from Machine.Config is not picked up by C# winform app

Invalid attempt to call Read when reader is closed error when call ExecuteScalar method

1 Answers1