SqlCommand Disposing Connection Object

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

Guest

I have the following VB.NET code:

....
Dim cmd As New SqlCommand

Try
With cmd
.Connection = New SqlConnection(...)
.CommandText = "sp..."
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@year", SqlDbType.Int).Value = intYear
.Parameters.Add("@quarter", SqlDbType.Int).Value = intQuarter

cmd.Connection.Open()
cmd.ExecuteNonQuery()
End With

Finally
If Not cmd Is Nothing Then cmd.Dispose()
End Try
....

My question is, does the connection object get disposed and closed with the
SqlCommand's dispose?

I do the same kind of thing, except with a SqlDataAdapter object. Does the
connection object get disposed and closed with the SqlDataAdapter's dispose?

Thanks,

J.J.
 
Just calling Dispose on the command does not automatically dispose the
connection. Remember that in the SqlClient library, the only unmanaged
resource is the SqlConnection. As lon as you use Close in a finally block
(or Using block in C# or the new new VB.NET you'll be fine). As far as
Adapters go, they are allocated and handled just like other resources.

HTH,

Bill
 
So the following finally would be correct?

....
Finally
If Not cmd Is Nothing Then
cmd.Dispose()
cmd.Connection.Dispose()
End Try
....

Thanks,

J.J.
 
JJsolo,

There is no need in this instructions to dispose even the command. It goes
automaticly out of scope, there is at the end no references anymore in it
outside these methods, therefore the GC will release it.

However I would make it more like this.


\\\
Try
dim conn as new connection(connectionstring)
conn.open
Try
Dim cmd As New SqlCommand
With cmd
.CommandText = "sp..."
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@year", SqlDbType.Int).Value = intYear
.Parameters.Add("@quarter", SqlDbType.Int).Value =
intQuarter
cmd.ExecuteNonQuery()
End With
Catch ex as exception
'actions with errors in the execute
End try
Catch ex as exceptions
'actions with errors in the connection
Finally
conn.Close
End Try

I hope this helps,

Cor
 
Is the reason that I do not need to dispose the command object because it is
managed resources? I was under the assumption a command object was unmanaged.

J.J.
 
jjsolo,

There is almost never a reason to use the dispose. That command is in 20% of
all classes while probably for 80% of the most used. It is just because that
all system.data classes derive from system.components, which inherits
IDisposable.

This is the same that 100% of all classes have gettype, because that is in
Object, from which all classes derive from.

Use the dispose there were is written on MSDN (not on all kind of websites)
were it should be used. I assume that you have nowhere in your program
label.dispose either (although that does even more than command.dispose).

I hope this helps,

Cor
 
We were just under the impression that the SqlCommand object was one of those
unmanaged resources like the SqlConnection that needed to be disposed.

Then when using a SqlDataAdapter, I only need to make sure the SqlCommand
gets disposed, not the Adapter itself?
 
jjsolo.

In my opinion does not need any object from the system.data class to be
disposed. They all implement IDisposable as you saw.

There was never nice documentation on MSDN, however in my opinon does this
show if nice and short after the syntax part

http://msdn2.microsoft.com/en-us/library/aax125c9

I hope this gives the ideas you want.

Cor
 
Cor Ligthert said:
jjsolo,

There is almost never a reason to use the dispose. That command is in 20%
of all classes while probably for 80% of the most used. It is just because
that all system.data classes derive from system.components, which inherits
IDisposable.

This is the same that 100% of all classes have gettype, because that is in
Object, from which all classes derive from.

Use the dispose there were is written on MSDN (not on all kind of
websites) were it should be used. I assume that you have nowhere in your
program label.dispose either (although that does even more than
command.dispose).
Click to expand...

This is one interpretation.
The another, more safe interpretation would be: Nobody knows for sure which
classes need disposing and furthermore, what is even worse, nobody knows
whether the disposing will be required in the future.
For example, you might have a class which implements IDisposable but does
not need disposing. In the next version it might require disposing - there
is no guarantee that it won't. And your program that worked will start to
act strangely when using next version of such class.
Thus, to be on the safe side, always make sure you dispose everything that
implements IDisposable.
 
Yes the connection is the only unmanaged resource, in that it will return it
to the connection pool if pooling is no. Make sure you close it in a
finally block though if you're using VB.NET 1.x. In C# you can use the
using statement or in VB.NET 2.0 you can use the new using statement.
 
No, you don't need to dispose the command - you need to do Finally
connection.Close ' Or dispose

End Try

The command is a managed resource so you don't need to explicitly call
dispose on the command
 
Cor - don't you mean with the exception of Sqlconnections or IDBConnections?
I know you mentioned System.Data but I'm guessing you also reference
SqlClient as well. Since connection objects are unmanaged (and also due
to their size, they are probably the least likely to get cleaned up - or
among the least likely) you need to be very careful that you handle them
properly. But are you saying that you don't need to dispose of connections?
I would agree that the main thing with them is ensuring they are closed as
soon as you are done with them, and opening them as late as possible before
you need them, but I don't know that they don't need cleaned up b/c of
IDisposable.
 
Miha,

See that link I gave to jjsolo, I have for ever wanted that there would be
something about that. A lot discussions and more. In that I see it for the
first time in a very simple way easy described.

In this we had a lot of discussions (very friendly) about connection
disposing. I did not see the reason however Angel wrote there were some
things beneath the cover for connection pooling. Therefore I started to use
dispose on that place. Than there were messages from a colleague of him (I
can get the name you know him I am sure you know who I mean) who told that
the close was more than enough. I thought I don't stop to do what Angel had
said. Than there was somebody I thought Bill Vaughn (I am not completely
sure) who said that as well and told why. I thought what he says make sense
for me and now I use the close again.

This is the page about dispose, it tells in my opinion exactly how I have
forever interpreted the dispose. However that of course not if a dispose
method is overloaded.

http://msdn2.microsoft.com/en-us/library/aax125c9


Cor
 
Everything that implements IDisposable. Note that component derived class
created by windows forms designer are added to adequate collection and are
automatically disposed.
 
Back
Top