CODEWITHSUNDEEP
c#ado.netdisposesqlcommandusing-statement
user3525898
user3525898

Reputation: 68

Using statement doesn't seem to dispose my object

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.

Upvotes: 1

Views: 293

Answers (2)

Andrei
Andrei

Reputation: 341

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

Upvotes: 1

Gusman
Gusman

Reputation: 15161

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.

Upvotes: 6

Related Questions