Should I Dispose() an SqlCommand object?

[Henrik Dahl]

Hello!

After I've finished using an instance of the SqlCommand class, should I then
invoke Dispose() on the instance. I suppose so, as there is a Dispose
method, but what does it actually release?

I would basically prefer to skip invoking Dispose() as this will free me
from determining when the usage actually has finished.


Best regards,

Henrik Dahl

 

[=?iso-8859-1?Q?Fredrik_Norm=E9n_www.NSQAURED2.net?]

You don't need to call Dispose of the command object.
But be sure that you close the connection that the
command object uses and release the rest of its resources.

Dispose will release all of the resources that the
SqlCommand uses. You don't need to call the Dispose but
it's recommanded if you want tell the Garbage Collection
that this object is free to remove from the memory and
let other object reuse the memory. This could increase
the performance of your application.

If you don't call Dispose the object and all its
resources will be left in the memory until the GC wants
to relase memory for other objects, GC must first
finalize the SqlCommand object before the memory could be
reused by other objects.

You can read about it on this site:
http://msdn.microsoft.com/library/default.asp?
url=/library/en-
us/cpguide/html/cpconimplementingdisposemethod.asp

/Fredrik Normén NSQAURED2
http://www.nsquared2.net

 

[Nicholas Paldino [.NET/C# MVP]]

Henrik,

This is a bad design, in my opinion. Generally speaking, with DB operations, at least with the model in .NET (disconnected recordsets), you should open your connection, perform your operation, and get out. You can recreate the command. Also, do you really want to be passing around commands to your database to someone on the outside that might use it in an improper manner?

So, given all that, yes, you should always call Dispose. It doesn't really matter what it disposes, the implication through the implementation (say that 10 times fast) of IDispose is that there is a resource the class manages which should be disposed of in a timely manner as opposed to waiting for a GC.

Hope this helps.

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi Henrik,

I'm not very sure that you need to call Dispose() on a SqlCommand , the
Dispose() of SqlCommand is inherited from Component ( according to MSDN )
therefore it does not perform any DB related op.
You should definely close the connection , regarding the SqlCommand this
is what the ADO.NET guide says:

"Although you can repeatedly use the same SqlCommand object to execute the
same command multiple times, do not reuse the same SqlCommand object to
execute different commands. "


Hope this help,

 

[Henrik Dahl]

Hello Ignacio,

Interesting, also because on the SqlCommand overview page it reads:
"You can reset the CommandText property and reuse the SqlCommand object.
However, you must close the SqlDataReader before you can execute a new or
previous command.".


Best regards,

Henrik Dahl

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

Of course you can do it, but it seems that it's not advised, it's like
maintaining open a connection during the live of a application, is doable ,
but not very wise.

The DataReader keeps the connection closed in a way that nobody can reuse
it until it gets close, that's way SqlCommand.ExecuteReader has a overload
that receive a CommandBehavior that you can set it to CloseConnection and it
will automatically close the connection once you close the reader.


Cheers,

 

[Henrik Dahl]

Nicholas,

Obviously I generally agree with you completely. In this particular case I
find it a bit surprising that I've seen many examples of using SqlCommand
objects in documentation from Microsoft, but I've never seen one which
actually Disposes the SqlCommand object afterwards, have you?


Best regards,

Henrik Dahl

Henrik,

This is a bad design, in my opinion. Generally speaking, with DB

operations, at least with the model in .NET (disconnected recordsets), you
should open your connection, perform your operation, and get out. You can
recreate the command. Also, do you really want to be passing around
commands to your database to someone on the outside that might use it in an
improper manner?

So, given all that, yes, you should always call Dispose. It doesn't

really matter what it disposes, the implication through the implementation
(say that 10 times fast) of IDispose is that there is a resource the class
manages which should be disposed of in a timely manner as opposed to waiting
for a GC.

Hope this helps.

 

[Angel Saenz-Badillos[MS]]

You should call Dispose on any object that implements IDisposable, this is
very easy to do with C# and the "using" keyword, in other .net languages you
can use the try finally construct to ensure that Dispose gets called. In
general adhering to this basic principle will greatly reduce stress related
problems with your code and may increase performance as it makes cleaning up
smarter.

Something else to think about is that allthough currently the Command may
not be doing anything meaningfull in its dispose method, there is no
guarantee that this will be true for future releases of the framework.
Calling dispose for all disposable objects will greatly enhance the lifetime
of your code.

Hope this helped,

 

[Henrik Dahl]

Angel,

Yes, that's obvious, but how may it then be that there are many, many
examples from Microsoft which do not invoke the Dispose() method of
SqlCommand objects, even examples which include Disposing e.g. the
SqlConnection object?

A question:
In one of the books from Microsoft Press it's stressed that you should
Close(), not Dispose(), an SqlConnection object because Dispose() will close
the connection to MS SQL Server whereas Close() lets the connection to go
back to the pool, i.e. closes the logical connection. In the ".NET Data
Access Architecture Guide" it's claimed that both Close() and Dispose() let
the SqlConnection to go back to the pool. Which is correct?


Best regards,

Henrik Dahl

You should call Dispose on any object that implements IDisposable, this is
very easy to do with C# and the "using" keyword, in other .net languages you
can use the try finally construct to ensure that Dispose gets called. In
general adhering to this basic principle will greatly reduce stress related
problems with your code and may increase performance as it makes cleaning up
smarter.

Something else to think about is that allthough currently the Command may
not be doing anything meaningfull in its dispose method, there is no
guarantee that this will be true for future releases of the framework.
Calling dispose for all disposable objects will greatly enhance the lifetime
of your code.

Hope this helped,
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

Hello!

After I've finished using an instance of the SqlCommand class, should I then
invoke Dispose() on the instance. I suppose so, as there is a Dispose
method, but what does it actually release?

I would basically prefer to skip invoking Dispose() as this will free me
from determining when the usage actually has finished.


Best regards,

Henrik Dahl

 

[Angel Saenz-Badillos[MS]]

The reason that the samples in the microsoft documentation do not show calls
to Dispose for the SqlCommand object is that, as it has been stated in this
thread, this does nothing. Looking at the ildasm of SqlCommand shows that it
does not override dispose and just inherits it from the base. The person
writing the sample knew this and coded accordingly. I have written some of
the examples in the docs myself and have done the same thing.

The problem is that as the next version of the urt rolls along there are no
guarantees that dispose will keep doing nothing, it is also tedious to
figure out exactly what objects you have to dispose at all costs
(SqlConnection!) and which you can ignore completelly. The general rule of
"if it implements idisposable, dispose it" will always work.

I am familiar with the Quote you mention. This information was based on
outdated information, for Beta 1 the SqlConnection did not implement a
completelly managed pooling solution, instead it relied on Enterprise
services object pooling. There where a number of problems with this
implementation but it was always meant to be temporary, the documentation
was added to the Beta documentation to work arround some issues with Dispose
and it was only fixed by v1.1! (auch, my fault)

Calling Dispose on the SqlConnection does not close the connection to sql
server, the only thing that Dispose does internally is to set the connection
string to null and to call Close if the connection has not already been
closed. The recommended usage (for the same reasons as I am recomending
always calling dispose) is to call using Sqlconnection (which automatically
disposes) and to call connection.Close(), there is no perf penalty for
calling both.

Hope this helps
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

Angel,

Yes, that's obvious, but how may it then be that there are many, many
examples from Microsoft which do not invoke the Dispose() method of
SqlCommand objects, even examples which include Disposing e.g. the
SqlConnection object?

A question:
In one of the books from Microsoft Press it's stressed that you should
Close(), not Dispose(), an SqlConnection object because Dispose() will close
the connection to MS SQL Server whereas Close() lets the connection to go
back to the pool, i.e. closes the logical connection. In the ".NET Data
Access Architecture Guide" it's claimed that both Close() and Dispose() let
the SqlConnection to go back to the pool. Which is correct?


Best regards,

Henrik Dahl

You should call Dispose on any object that implements IDisposable, this is
very easy to do with C# and the "using" keyword, in other .net languages you
can use the try finally construct to ensure that Dispose gets called. In
general adhering to this basic principle will greatly reduce stress related
problems with your code and may increase performance as it makes

cleaning

up
smarter.

Something else to think about is that allthough currently the Command may
not be doing anything meaningfull in its dispose method, there is no
guarantee that this will be true for future releases of the framework.
Calling dispose for all disposable objects will greatly enhance the lifetime
of your code.

Hope this helped,
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

Hello!

After I've finished using an instance of the SqlCommand class, should

I
then

invoke Dispose() on the instance. I suppose so, as there is a Dispose
method, but what does it actually release?

I would basically prefer to skip invoking Dispose() as this will free me
from determining when the usage actually has finished.


Best regards,

Henrik Dahl

 

[Henrik Dahl]

Angel,

Thank you very much for your precise answer.


Henrik Dahl

The reason that the samples in the microsoft documentation do not show calls
to Dispose for the SqlCommand object is that, as it has been stated in this
thread, this does nothing. Looking at the ildasm of SqlCommand shows that it
does not override dispose and just inherits it from the base. The person
writing the sample knew this and coded accordingly. I have written some of
the examples in the docs myself and have done the same thing.

The problem is that as the next version of the urt rolls along there are no
guarantees that dispose will keep doing nothing, it is also tedious to
figure out exactly what objects you have to dispose at all costs
(SqlConnection!) and which you can ignore completelly. The general rule of
"if it implements idisposable, dispose it" will always work.

I am familiar with the Quote you mention. This information was based on
outdated information, for Beta 1 the SqlConnection did not implement a
completelly managed pooling solution, instead it relied on Enterprise
services object pooling. There where a number of problems with this
implementation but it was always meant to be temporary, the documentation
was added to the Beta documentation to work arround some issues with Dispose
and it was only fixed by v1.1! (auch, my fault)

Calling Dispose on the SqlConnection does not close the connection to sql
server, the only thing that Dispose does internally is to set the connection
string to null and to call Close if the connection has not already been
closed. The recommended usage (for the same reasons as I am recomending
always calling dispose) is to call using Sqlconnection (which automatically
disposes) and to call connection.Close(), there is no perf penalty for
calling both.

Hope this helps
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

Angel,

Yes, that's obvious, but how may it then be that there are many, many
examples from Microsoft which do not invoke the Dispose() method of
SqlCommand objects, even examples which include Disposing e.g. the
SqlConnection object?

A question:
In one of the books from Microsoft Press it's stressed that you should
Close(), not Dispose(), an SqlConnection object because Dispose() will close
the connection to MS SQL Server whereas Close() lets the connection to go
back to the pool, i.e. closes the logical connection. In the ".NET Data
Access Architecture Guide" it's claimed that both Close() and Dispose() let
the SqlConnection to go back to the pool. Which is correct?


Best regards,

Henrik Dahl

this

is

very easy to do with C# and the "using" keyword, in other .net

languages
you

can use the try finally construct to ensure that Dispose gets called. In
general adhering to this basic principle will greatly reduce stress related
problems with your code and may increase performance as it makes

cleaning

up
smarter.

Something else to think about is that allthough currently the Command may
not be doing anything meaningfull in its dispose method, there is no
guarantee that this will be true for future releases of the framework.
Calling dispose for all disposable objects will greatly enhance the lifetime
of your code.

Hope this helped,
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
Hello!

After I've finished using an instance of the SqlCommand class,

should

I

free

me

 

[Angel Saenz-Badillos[MS]]

I apologize if my answer seems brusque, I was trying to convey that
disposing Idisposable objects is always a good thing, it is by no means
necesary.

Objects that do not require dispose to be called on today should continue to
work as such for existing code, we will treat any changes in this behavior
as a bug. My hope is that we can avoid any such bugs going forward but
realistically there may be instances where this does not happen, there may
also be cases where new features that use an object that does not require
dispose today may require or benefit from the object being disposed in the
future, this may make things even more confusing.

I guess my question is what kind of examples would you be interested to see
in the documentation that ships with the next version of the URT? How do you
rate your experience with the current batch of samples and how much would it
help if they were to be rewritten? One of the biggest problems with samples
is that VB.NET does not support the "using" keyword, so equivalent C# and
VB.NET samples become less clear, would this be a concern when looking at
doc samples?

looking forward to any doc related feedback
Thanks,
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

Angel,

Thank you very much for your precise answer.


Henrik Dahl

The reason that the samples in the microsoft documentation do not show calls
to Dispose for the SqlCommand object is that, as it has been stated in this
thread, this does nothing. Looking at the ildasm of SqlCommand shows

that

it
does not override dispose and just inherits it from the base. The person
writing the sample knew this and coded accordingly. I have written some of
the examples in the docs myself and have done the same thing.

The problem is that as the next version of the urt rolls along there are no
guarantees that dispose will keep doing nothing, it is also tedious to
figure out exactly what objects you have to dispose at all costs
(SqlConnection!) and which you can ignore completelly. The general rule of
"if it implements idisposable, dispose it" will always work.

I am familiar with the Quote you mention. This information was based on
outdated information, for Beta 1 the SqlConnection did not implement a
completelly managed pooling solution, instead it relied on Enterprise
services object pooling. There where a number of problems with this
implementation but it was always meant to be temporary, the documentation
was added to the Beta documentation to work arround some issues with Dispose
and it was only fixed by v1.1! (auch, my fault)

Calling Dispose on the SqlConnection does not close the connection to sql
server, the only thing that Dispose does internally is to set the connection
string to null and to call Close if the connection has not already been
closed. The recommended usage (for the same reasons as I am recomending
always calling dispose) is to call using Sqlconnection (which automatically
disposes) and to call connection.Close(), there is no perf penalty for
calling both.

Hope this helps
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

Angel,

Yes, that's obvious, but how may it then be that there are many, many
examples from Microsoft which do not invoke the Dispose() method of
SqlCommand objects, even examples which include Disposing e.g. the
SqlConnection object?

A question:
In one of the books from Microsoft Press it's stressed that you should
Close(), not Dispose(), an SqlConnection object because Dispose() will close
the connection to MS SQL Server whereas Close() lets the connection to go
back to the pool, i.e. closes the logical connection. In the ".NET Data
Access Architecture Guide" it's claimed that both Close() and

Dispose()
let

the SqlConnection to go back to the pool. Which is correct?


Best regards,

Henrik Dahl

You should call Dispose on any object that implements IDisposable,

this

is
very easy to do with C# and the "using" keyword, in other .net languages
you
can use the try finally construct to ensure that Dispose gets

called.

In

general adhering to this basic principle will greatly reduce stress
related
problems with your code and may increase performance as it makes cleaning
up
smarter.

Something else to think about is that allthough currently the

Command
may

not be doing anything meaningfull in its dispose method, there is no
guarantee that this will be true for future releases of the framework.
Calling dispose for all disposable objects will greatly enhance the
lifetime
of your code.

Hope this helped,
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.
Please do not send email directly to this alias. This alias is for
newsgroup
purposes only.
Hello!

After I've finished using an instance of the SqlCommand class,

should

I
then
invoke Dispose() on the instance. I suppose so, as there is a Dispose
method, but what does it actually release?

I would basically prefer to skip invoking Dispose() as this will

free

me
from determining when the usage actually has finished.


Best regards,

Henrik Dahl