SQLDataReader and CommandBehaviour.CloseConnection

Question

I have a generic class called "db" which talks directly to the database. And have a method called "ExecuteDataReader" as below:

public SqlDataReader ExecuteDataReader(SqlCommand cmd)
        {
            try
            {
                OpenConnection();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;              

                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                return dr;
            }
            catch (Exception ex)
            {
                Utils.Debug(string.Format("Err in {0}.{1} : {2}\nSQL : {3}", this.GetType(), "ExecuteDataReader", ex.Message, cmd.CommandText));
                return null;
            }            
        }

Then, I execute the resource intensive query which loops through 10000 parent records and 20000 child records to update in the database. And then I got the following error:

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

To solve these problems, I have to call dr.Close() explicitly after the execution.

static void ProcessAssessmentCriteria(string UnitReference)
        {
            SqlCommand cmd = new SqlCommand("TRACKING.DBTool_GetUniqueAssessmentCriteriaByUnitReference");
            cmd.Parameters.Add("@UnitReference", SqlDbType.VarChar, 20).Value = UnitReference;

            SqlDataReader dr = db.ExecuteDataReader(cmd);

            if (dr.HasRows)
            {
                while (dr.Read())
                {                    
                    ProcessDetailAssessmentCriteria(UnitReference, dr["AssessmentRefNumber"].ToString());
                    Console.WriteLine("---------------");
                }
            }

            dr.Close();
        }

Due to my knowledge, the CommandBehaviour.CloseConnection() automatically close the connection. But it seems that it does not close now. Could you please enlighten me? Thanks.

2GDev · Accepted Answer · 2011-08-11 10:03:00Z

3

CommandBehavior.CloseConnection from MSDN

When the command is executed, the associated Connection object is closed when the associated DataReader object is closed.

So only when you close the DataReader the connection is Closed.

answered Aug 11, 2011 at 10:03

2GDev

2,4661 gold badge20 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TTCG Over a year ago

oh yeah. I misunderstood it. :)

Collectives™ on Stack Overflow

SQLDataReader and CommandBehaviour.CloseConnection

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related