Here's a simplified version of my example:
using (DbCommand cmd = new SqlCommand("myProcedure", (SqlConnection)DataAccessHelper.CreateDatabase().CreateConnection()) { CommandType = CommandType.StoredProcedure })
{
cmd.Connection.Open();
using(IDataReader dr = cmd.ExecuteReader())
doWork(dr);
}
When the command is disposed, is the connection closed? Or would I need to have that first using statement be for the connection, and then create the command in the closure?
Net documentation for the OleDbCommand, Close() closes the connection and returns it to the connection pool, and Dispose() closes the connection and REMOVES it from the connection pool.
Implement a finalizer to free resources when Dispose is not called. By default, the garbage collector automatically calls an object's finalizer before reclaiming its memory. However, if the Dispose method has been called, it is typically unnecessary for the garbage collector to call the disposed object's finalizer.
While there may be many instances (like on SqlConnection) where you call Disponse() on some object and it simply calls Close() on it's connection or closes a file handle, it's almost always your best bet to call Dispose()! unless you plan on reusing the object in the very near future.
If you want the reader to close the connection, you can use the overload of ExecuteReader():
...
using (IDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
...
By default, disposing a reader does not release the connection. - see MSDN for more info...
To address the question of Close()
vs Dispose()
, the MSDN states:
If the DbConnection goes out of scope, it is not closed. Therefore, you must explicitly close the connection by calling Close or Dispose, which are functionally equivalent.
Thus a self-closing connection need not be disposed necessarily. The main difference is that a closed connection can be re-opened, but a disposed connection cannot. The main additional work that Dispose()
does is set internals to null
which won't have much of an effect since the connection is passing out of scope anyway.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With