469,647 Members | 1,677 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,647 developers. It's quick & easy.

Should I Dispose() an SqlCommand object?

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
Nov 15 '05 #1
10 17344
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.

--
- Nicholas Paldino [.NET/C# MVP]
- nick(d0t)paldino=At-exisconsulting'dot|com

On Mon, 6 Oct 2003 12:57:18 +0200, Henrik Dahl <Th**********************@inet.uni2.dk> wrote:
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

Nov 15 '05 #2
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,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Henrik Dahl" <Th**********************@inet.uni2.dk> wrote in message
news:ev*************@TK2MSFTNGP11.phx.gbl...
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

Nov 15 '05 #3
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 )" <ignacio.machin AT dot.state.fl.us> wrote
in message news:uw**************@TK2MSFTNGP12.phx.gbl...
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,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Henrik Dahl" <Th**********************@inet.uni2.dk> wrote in message
news:ev*************@TK2MSFTNGP11.phx.gbl...
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


Nov 15 '05 #4
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,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Henrik Dahl" <Th**********************@inet.uni2.dk> wrote in message
news:eO**************@TK2MSFTNGP09.phx.gbl...
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 )" <ignacio.machin AT dot.state.fl.us> wrote in message news:uw**************@TK2MSFTNGP12.phx.gbl...
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,

--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Henrik Dahl" <Th**********************@inet.uni2.dk> wrote in message
news:ev*************@TK2MSFTNGP11.phx.gbl...
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



Nov 15 '05 #5
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

"Nicholas Paldino [.NET/C# MVP]" <ni**************@exisconsulting.com> wrote
in message news:op**************@msnews.microsoft.com...
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.

--
- Nicholas Paldino [.NET/C# MVP]
- nick(d0t)paldino=At-exisconsulting'dot|com

On Mon, 6 Oct 2003 12:57:18 +0200, Henrik Dahl <Th**********************@inet.uni2.dk> wrote:
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


Nov 15 '05 #6
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.
"Henrik Dahl" <Th**********************@inet.uni2.dk> wrote in message
news:ev*************@TK2MSFTNGP11.phx.gbl...
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

Nov 15 '05 #7
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

"Angel Saenz-Badillos[MS]" <an*****@online.microsoft.com> wrote in message
news:uv**************@TK2MSFTNGP12.phx.gbl...
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.
"Henrik Dahl" <Th**********************@inet.uni2.dk> wrote in message
news:ev*************@TK2MSFTNGP11.phx.gbl...
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


Nov 15 '05 #8
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.
"Henrik Dahl" <Th**********************@inet.uni2.dk> wrote in message
news:OQ**************@TK2MSFTNGP11.phx.gbl...
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

"Angel Saenz-Badillos[MS]" <an*****@online.microsoft.com> wrote in message
news:uv**************@TK2MSFTNGP12.phx.gbl...
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.
"Henrik Dahl" <Th**********************@inet.uni2.dk> wrote in message
news:ev*************@TK2MSFTNGP11.phx.gbl...
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



Nov 15 '05 #9
Angel,

Thank you very much for your precise answer.
Henrik Dahl

"Angel Saenz-Badillos[MS]" <an*****@online.microsoft.com> wrote in message
news:OB**************@TK2MSFTNGP10.phx.gbl...
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.
"Henrik Dahl" <Th**********************@inet.uni2.dk> wrote in message
news:OQ**************@TK2MSFTNGP11.phx.gbl...
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

"Angel Saenz-Badillos[MS]" <an*****@online.microsoft.com> wrote in message news:uv**************@TK2MSFTNGP12.phx.gbl...
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.
"Henrik Dahl" <Th**********************@inet.uni2.dk> wrote in message
news:ev*************@TK2MSFTNGP11.phx.gbl...
> 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
>
>



Nov 15 '05 #10
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.
"Henrik Dahl" <Th**********************@inet.uni2.dk> wrote in message
news:uv**************@tk2msftngp13.phx.gbl...
Angel,

Thank you very much for your precise answer.
Henrik Dahl

"Angel Saenz-Badillos[MS]" <an*****@online.microsoft.com> wrote in message
news:OB**************@TK2MSFTNGP10.phx.gbl...
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.
"Henrik Dahl" <Th**********************@inet.uni2.dk> wrote in message
news:OQ**************@TK2MSFTNGP11.phx.gbl...
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

"Angel Saenz-Badillos[MS]" <an*****@online.microsoft.com> wrote in message news:uv**************@TK2MSFTNGP12.phx.gbl...
> 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.
> "Henrik Dahl" <Th**********************@inet.uni2.dk> wrote in message > news:ev*************@TK2MSFTNGP11.phx.gbl...
> > 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
> >
> >
>
>



Nov 15 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

16 posts views Thread by Daniel Billingsley | last post: by
3 posts views Thread by bozzzza | last post: by
17 posts views Thread by Bob Lehmann | last post: by
5 posts views Thread by ypul | last post: by
156 posts views Thread by Dennis | last post: by
1 post views Thread by Agnes | last post: by
2 posts views Thread by Michael Carr | last post: by
5 posts views Thread by cj | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.