Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

.Net: Does my connection get closed via Dispose in this example?

Tags:

c#

ado.net

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?

like image 923
John Avatar asked Dec 23 '11 20:12

John


People also ask

Does dispose close connection C#?

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.

What happens if Dispose is not called?

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.

Do I need to dispose SqlConnection?

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.


1 Answers

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.

like image 60
James Michael Hare Avatar answered Nov 04 '22 22:11

James Michael Hare