Connection.Close or End Using or Both

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I know that a connection is not closed when it goes out of scope so I would
usually code a Try...Finally block. But does Using...End Using achieve the
same?

i.e. is the following code overkill?

Using _connection As New SqlClient.SqlConnection
_connection.Open()
Try

Finally
_connection.Close()
End Try
End Using
 
Hi Dick,

Yes, when you use using the connection.Dispose is called in "finally" block
generated by using.
Dispose does a little more (almost irrelevant) - it clears connectionstring
in addition to invoking Close() in case of SqlServer. Other connections
might behave differently.
Note that what connection does in Dispose might change in future.
 
Thanks for your quick response Miha. So, for SQL Server at least, is there a
recomendation: Try...Finally or Using...End Using?

Miha Markic said:
Hi Dick,

Yes, when you use using the connection.Dispose is called in "finally" block
generated by using.
Dispose does a little more (almost irrelevant) - it clears connectionstring
in addition to invoking Close() in case of SqlServer. Other connections
might behave differently.
Note that what connection does in Dispose might change in future.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Dick said:
I know that a connection is not closed when it goes out of scope so I would
usually code a Try...Finally block. But does Using...End Using achieve the
same?

i.e. is the following code overkill?

Using _connection As New SqlClient.SqlConnection
_connection.Open()
Try

Finally
_connection.Close()
End Try
End Using
Click to expand...
Click to expand...
 
Hi Dick,

Yes, when you use using the connection.Dispose is called in "finally" block
generated by using.
Dispose does a little more (almost irrelevant) - it clears connectionstring
in addition to invoking Close() in case of SqlServer. Other connections
might behave differently.
Note that what connection does in Dispose might change in future.
Click to expand...

Miha,

Does the clearing of the connection string mean that one must reset the
connection string to use the connection again? That seems to me a poor design.
Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com
 
Good point Otis. Does this mean the connection can't go back in the pool? I
guess this must be so because a pooled connection whose connection info is
missing isn't very useful!
 
I always say: whenever a class implement IDisposable do use Dispose (which
"using" does).
As for your question: using is just syntactic sugar and simply translates to
try/finally IOW it doesn't matter - it is always try/finally.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Dick said:
Thanks for your quick response Miha. So, for SQL Server at least, is there
a
recomendation: Try...Finally or Using...End Using?

Miha Markic said:
Hi Dick,

Yes, when you use using the connection.Dispose is called in "finally"
block
generated by using.
Dispose does a little more (almost irrelevant) - it clears
connectionstring
in addition to invoking Close() in case of SqlServer. Other connections
might behave differently.
Note that what connection does in Dispose might change in future.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Dick said:
I know that a connection is not closed when it goes out of scope so I
would
usually code a Try...Finally block. But does Using...End Using achieve
the
same?

i.e. is the following code overkill?

Using _connection As New SqlClient.SqlConnection
_connection.Open()
Try

Finally
_connection.Close()
End Try
End Using
Click to expand...
Click to expand...
Click to expand...
 
Hi Otis,

Sure, what else do you except from disposed object (it might be even
non-usable at all)?
In the case of connection you can always use
using (SqlConnection conn = new SqlConnection(..))
{
}
as there is negligent overhead when creating new instance.
 
Dick,

Only physicall connection go to the pool, not .net instances.
IOW SqlConnection is .net instance which uses physicall connection in the
background.
 
Back
Top