Using statement doesn't seem to dispose my object

Question

I'm having some trouble. I have a SqlCommand in a using block. Usually this works fine. This time however, the code exits the using block but the command does not get disposed. It has me stumped.

using (SqlCommand transferCommand = new SqlCommand(strHeaderStoredProcedure, connection))
{
    transferCommand.CommandType = System.Data.CommandType.StoredProcedure;
    transferCommand.Parameters.Add("@InvtTransferID", SqlDbType.UniqueIdentifier).Value = InvtTransferID;
    SqlDataReader transferReader = transferCommand.ExecuteReader();
    while (transferReader.Read())
    {
        //do my stuff, this all works fine
    }
}


using (SqlCommand transferCommand = new SqlCommand(strDetailsStoredProcedure, connection))
{
    transferCommand.CommandType = System.Data.CommandType.StoredProcedure;
    transferCommand.Parameters.Add("@InvtTransferID",     SqlDbType.UniqueIdentifier).Value = InvtTransferID;
    SqlDataReader transferReader = transferCommand.ExecuteReader(); <-- Error happens here.
}

I'm trying to reuse my connection to run a bunch of stored procedures. It is the strangest thing. I have some code on another form that is virtually identical and it runs fine, disposing the command and allowing me to make another. Any ideas what I'm doing wrong? (I'm trying to make this code look nice in the post but it seems to be beyond my meager power...so sorry for the messy code.)

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

Can you provide the actual exception or error message that you're getting? — StriplingWarrior
– StriplingWarrior, Commented May 29, 2014 at 23:02
The error text is: There is already an open DataReader associated with this Command which must be closed first. However, the datareader is usually closed when the command is disposed. — user3525898
– user3525898, Commented May 29, 2014 at 23:08

Gusman · Accepted Answer · 2014-05-29 23:08:45Z

6

What you have is an open SqlDataReader (transferReader), Close and Dispose the datareader or use a using() for it.

I assume the command is not being released because the reader is still open.

answered May 29, 2014 at 23:08

Gusman

15.2k2 gold badges38 silver badges53 bronze badges

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

3 Comments

user3525898 Over a year ago

Well, you're right. Putting the SqlDataReader in a using statement fixed my problem right up. That is odd though, I thought a using would ALWAYS dispose of the object in the using statement as well as any objects created within its scope regardless of any other conditions. I guess this is a wrong assumption?

Gusman Over a year ago

Using() cannot guarantee the object destruction, as you stated guarantees the Dispose call, but you don't know what that Dispose does internally, per example supose you have a class with a thread and in your Dispose call you do Thread.Abort, the object is disposed but the thread is still alive until the thread is finished internally or the ThreadAbortException is raised which can happen some time after the Abort call.

user3525898 Over a year ago

TIL using statement is not a magic bullet. Thanks for taking the time to explain it to me.

Andrei · Accepted Answer · 2014-05-29 23:32:57Z

1

As a bit of extra info, here is what the compiler does behind the scenes when you write an using statement.

using(IDisposable x)
{
  //some work here
}

Turns into

IDisposable x = //some IDisposable

try
{
    //do work  here
}
finally
{
  x.Dispose()
}

As Gusman mentioned , using cannot guarantee the object destruction, it just calls the object's dispose method. It's the IDisposable's reponsability to clean up any used resources

I hope this helps

answered May 29, 2014 at 23:32

Andrei

3413 silver badges12 bronze badges

1 Comment

user3525898 Over a year ago

Yeah, that makes perfect sense now. It is odd though. I've probably used this construct scores of times now and this is the first time I've had a problem. So I wasn't suspecting the SqlDataReader since it had always worked fine before. I guess it just goes to show just because something works doesn't mean it is right.

Collectives™ on Stack Overflow

Using statement doesn't seem to dispose my object

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related