return inside of using block

[Andrew Robinson]

Are these two equivalent? Is one better than the other? I tend to go with #1
but started wondering....

Thanks,

1:

using (SqlConnection cn = new SqlConnection(DataConnection))
using (SqlCommand cm = new SqlCommand("ItemCount", cn)) {
cm.CommandType = CommandType.StoredProcedure;

cm.Parameters.Add("@Count", SqlDbType.Int);
cm.Parameters["@Count"].Direction = ParameterDirection.Output;

cn.Open();
cm.ExecuteNonQuery();
cn.Close();

return (int)cm.Parameters["@Count"].Value;
}

2:

int count;

using (SqlConnection cn = new SqlConnection(DataConnection))
using (SqlCommand cm = new SqlCommand("ItemCount", cn)) {
cm.CommandType = CommandType.StoredProcedure;

cm.Parameters.Add("@Count", SqlDbType.Int);
cm.Parameters["@Count"].Direction = ParameterDirection.Output;

cn.Open();
cm.ExecuteNonQuery();
cn.Close();

count = (int)cm.Parameters["@Count"].Value;
}

return count;

[Tiberiu Covaci]

Yes. Why? Because using is actually implemented using try/finally

Regards,
Tiberiu Covaci.
"Andrew Robinson" <ne****@nospam.nospam> wrote in message
news:uh**************@TK2MSFTNGP03.phx.gbl...

Are these two equivalent? Is one better than the other? I tend to go with
#1 but started wondering....

Thanks,

1:

using (SqlConnection cn = new SqlConnection(DataConnection))
using (SqlCommand cm = new SqlCommand("ItemCount", cn)) {
cm.CommandType = CommandType.StoredProcedure;

cm.Parameters.Add("@Count", SqlDbType.Int);
cm.Parameters["@Count"].Direction = ParameterDirection.Output;

cn.Open();
cm.ExecuteNonQuery();
cn.Close();

return (int)cm.Parameters["@Count"].Value;
}

2:

int count;

using (SqlConnection cn = new SqlConnection(DataConnection))
using (SqlCommand cm = new SqlCommand("ItemCount", cn)) {
cm.CommandType = CommandType.StoredProcedure;

cm.Parameters.Add("@Count", SqlDbType.Int);
cm.Parameters["@Count"].Direction = ParameterDirection.Output;

cn.Open();
cm.ExecuteNonQuery();
cn.Close();

count = (int)cm.Parameters["@Count"].Value;
}

return count;

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

Andrew,

No, they are not equivalent to each other, technically. While they will
have the same results, it is different, as an exception thrown during return
statement will not cause the command and the connection to clean up. Of
course, in #2, they are already cleaned up because you left the scope of the
using statements anyways.

It also has the effect of keeping open the connection and the command
slightly longer.

As a side note, you don't need to explicitly call Close. It will be
called when Dispose is called on the connection.

Personally, I like the idea of returning while in the using statement
for this particular scenario. It seems more natural to me to access the
members that are dependent on that resource (parameters are from a stored
procedure executed on that connection) when the resource is alive, as
opposed to when it has been disposed already.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Andrew Robinson" <ne****@nospam.nospam> wrote in message
news:uh**************@TK2MSFTNGP03.phx.gbl...

Are these two equivalent? Is one better than the other? I tend to go with
#1 but started wondering....

Thanks,

1:

using (SqlConnection cn = new SqlConnection(DataConnection))
using (SqlCommand cm = new SqlCommand("ItemCount", cn)) {
cm.CommandType = CommandType.StoredProcedure;

cm.Parameters.Add("@Count", SqlDbType.Int);
cm.Parameters["@Count"].Direction = ParameterDirection.Output;

cn.Open();
cm.ExecuteNonQuery();
cn.Close();

return (int)cm.Parameters["@Count"].Value;
}

2:

int count;

using (SqlConnection cn = new SqlConnection(DataConnection))
using (SqlCommand cm = new SqlCommand("ItemCount", cn)) {
cm.CommandType = CommandType.StoredProcedure;

cm.Parameters.Add("@Count", SqlDbType.Int);
cm.Parameters["@Count"].Direction = ParameterDirection.Output;

cn.Open();
cm.ExecuteNonQuery();
cn.Close();

count = (int)cm.Parameters["@Count"].Value;
}

return count;

[Andrew Robinson]

Nicholas,

I hadn't give this any thought in terms of exception handling. If my return
throws an exception, I tend to view this as an extraordinary event that I
currently don't handle and have no plans on handling. So if in that case
resources either get cleaned up or not, I don't think that is really all
that important.

Thanks,

--

Andrew Robinson
"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in
message news:OQ**************@TK2MSFTNGP04.phx.gbl...

Andrew,

No, they are not equivalent to each other, technically. While they
will have the same results, it is different, as an exception thrown during
return statement will not cause the command and the connection to clean
up. Of course, in #2, they are already cleaned up because you left the
scope of the using statements anyways.

It also has the effect of keeping open the connection and the command
slightly longer.

As a side note, you don't need to explicitly call Close. It will be
called when Dispose is called on the connection.

Personally, I like the idea of returning while in the using statement
for this particular scenario. It seems more natural to me to access the
members that are dependent on that resource (parameters are from a stored
procedure executed on that connection) when the resource is alive, as
opposed to when it has been disposed already.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Andrew Robinson" <ne****@nospam.nospam> wrote in message
news:uh**************@TK2MSFTNGP03.phx.gbl...

Are these two equivalent? Is one better than the other? I tend to go with
#1 but started wondering....

Thanks,

1:

using (SqlConnection cn = new SqlConnection(DataConnection))
using (SqlCommand cm = new SqlCommand("ItemCount", cn)) {
cm.CommandType = CommandType.StoredProcedure;

cm.Parameters.Add("@Count", SqlDbType.Int);
cm.Parameters["@Count"].Direction = ParameterDirection.Output;

cn.Open();
cm.ExecuteNonQuery();
cn.Close();

return (int)cm.Parameters["@Count"].Value;
}

2:

int count;

using (SqlConnection cn = new SqlConnection(DataConnection))
using (SqlCommand cm = new SqlCommand("ItemCount", cn)) {
cm.CommandType = CommandType.StoredProcedure;

cm.Parameters.Add("@Count", SqlDbType.Int);
cm.Parameters["@Count"].Direction = ParameterDirection.Output;

cn.Open();
cm.ExecuteNonQuery();
cn.Close();

count = (int)cm.Parameters["@Count"].Value;
}

return count;

[Mythran]

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in
message news:OQ**************@TK2MSFTNGP04.phx.gbl...

Andrew,

No, they are not equivalent to each other, technically. While they
will have the same results, it is different, as an exception thrown during
return statement will not cause the command and the connection to clean
up. Of course, in #2, they are already cleaned up because you left the
scope of the using statements anyways.

It also has the effect of keeping open the connection and the command
slightly longer.

As a side note, you don't need to explicitly call Close. It will be
called when Dispose is called on the connection.

Personally, I like the idea of returning while in the using statement
for this particular scenario. It seems more natural to me to access the
members that are dependent on that resource (parameters are from a stored
procedure executed on that connection) when the resource is alive, as
opposed to when it has been disposed already.

Hope this helps.

Nick, technically speaking, you are slightly off base here. The "using"
statement guarantees the object you are using will be disposed of prior to
exiting the block .. If there is an exception thrown, the using will still
"clean up" or dispose of the object being used :)

ms-help://MS.MSDNQTR.2003FEB.1033/csref/html/vclrfUsingStatement.htm

HTH,
Mythran

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

Mythran,

That's not what I am referring to. Yes, in both cases, the object is
disposed of properly. It's a matter of ^when^ the object is cleaned up.
The order of operations in #1 and #2 are different. Also, if an exception
is thrown, the order in which things happen is different.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Mythran" <ki********@hotmail.comREMOVETRAIL> wrote in message
news:Oc**************@TK2MSFTNGP03.phx.gbl...

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote
in message news:OQ**************@TK2MSFTNGP04.phx.gbl...

Andrew,

No, they are not equivalent to each other, technically. While they
will have the same results, it is different, as an exception thrown
during return statement will not cause the command and the connection to
clean up. Of course, in #2, they are already cleaned up because you left
the scope of the using statements anyways.

It also has the effect of keeping open the connection and the command
slightly longer.

As a side note, you don't need to explicitly call Close. It will be
called when Dispose is called on the connection.

Personally, I like the idea of returning while in the using statement
for this particular scenario. It seems more natural to me to access the
members that are dependent on that resource (parameters are from a stored
procedure executed on that connection) when the resource is alive, as
opposed to when it has been disposed already.

Hope this helps.

Nick, technically speaking, you are slightly off base here. The "using"
statement guarantees the object you are using will be disposed of prior to
exiting the block .. If there is an exception thrown, the using will still
"clean up" or dispose of the object being used :)

ms-help://MS.MSDNQTR.2003FEB.1033/csref/html/vclrfUsingStatement.htm

HTH,
Mythran

[Mythran]

Comments in-line to refer what I'm talking about :)

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in
message news:eh**************@TK2MSFTNGP02.phx.gbl...

Mythran,

That's not what I am referring to. Yes, in both cases, the object is
disposed of properly. It's a matter of ^when^ the object is cleaned up.
The order of operations in #1 and #2 are different. Also, if an exception
is thrown, the order in which things happen is different.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Mythran" <ki********@hotmail.comREMOVETRAIL> wrote in message
news:Oc**************@TK2MSFTNGP03.phx.gbl...

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote
in message news:OQ**************@TK2MSFTNGP04.phx.gbl...

Andrew,

No, they are not equivalent to each other, technically. While they
will have the same results, it is different, as an exception thrown
during return statement will not cause the command and the connection to
clean up. Of course, in #2, they are already cleaned up because you
left the scope of the using statements anyways.

-----------
You said "While they will have the same results, it is different, as an
exception thrown during return statement will not cause the command and
connection to clean up.". Hmm, if an exception is thrown during the return
statement inside the using block, they will be cleaned up before the
exception is "bubbled up" the stack (in #1). In #2, the command and
connection are already cleaned up prior to the exception so no worries there
anyways.
It also has the effect of keeping open the connection and the command
slightly longer.

-----------
Unless you are doing something with the result besides returning it, it's
not keeping the command or connection open any longer in either one. If
anything, #2 is holding it open a little longer while it stores the result
into the var (insignificant timing though). In #1, it is returning the
value, which in this case, calls dispose immediately before exiting the
method, therefore cleaning up the command and connection.
As a side note, you don't need to explicitly call Close. It will be
called when Dispose is called on the connection.

-----------
Aye, when using the "using" statement, you don't have to Close ... Dispose
calls close... but here's the kicker! If you are using the "using"
statement on a SqlConnection object, you are effectively doing away with
connection pooling (someone PLEASE correct me if I'm wrong here). When you
call Dispose on a SqlConnection object, you are disposing of the object, not
just closing it. If you call Close, you are releasing it back into the
pool.
Personally, I like the idea of returning while in the using statement
for this particular scenario. It seems more natural to me to access the
members that are dependent on that resource (parameters are from a
stored procedure executed on that connection) when the resource is
alive, as opposed to when it has been disposed already.

Hope this helps.

-----------
Aye, I feel the same way about returning while in the using statement.

HTH :)

Mythran

[Göran Andersson]

It's not the connection object that is pooled, it's the connection. When
the Close or Dispose methods are called, the connection is released to
the pool.

Calling the Dispose method doesn't dispose of an object, it lets the
object dispose of the unmanaged resources that it uses.

Mythran wrote:

Aye, when using the "using" statement, you don't have to Close ...
Dispose calls close... but here's the kicker! If you are using the
"using" statement on a SqlConnection object, you are effectively doing
away with connection pooling (someone PLEASE correct me if I'm wrong
here). When you call Dispose on a SqlConnection object, you are
disposing of the object, not just closing it. If you call Close, you
are releasing it back into the pool.

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

Mythran,

See inline:

-----------
You said "While they will have the same results, it is different, as an
exception thrown during return statement will not cause the command and
connection to clean up.". Hmm, if an exception is thrown during the
return statement inside the using block, they will be cleaned up before
the exception is "bubbled up" the stack (in #1). In #2, the command and
connection are already cleaned up prior to the exception so no worries
there anyways.
Right, there are no worries, but there is a difference. Compile each
code sample, and check the resulting IL. They are different.
Unless you are doing something with the result besides returning it, it's
not keeping the command or connection open any longer in either one. If
anything, #2 is holding it open a little longer while it stores the result
into the var (insignificant timing though). In #1, it is returning the
value, which in this case, calls dispose immediately before exiting the
method, therefore cleaning up the command and connection.
Actually, it is keeping it open longer. When the return statement is in
the "using" block, the expression "(int)cm.Parameters["@Count"].Value" is
evaluated before it is placed on the stack to be returned. Then the scope
is exited, and the connection/command is closed.

In #2, the connection and command are closed, then the expression
"(int)cm.Parameters["@Count"].Value" is evaluated (executed). Again, same
result, but different ways of going about it. The connection/command are
disposed of either way, but there is a difference in how it is obtained.

As a side note, you don't need to explicitly call Close. It will be
called when Dispose is called on the connection.

-----------
Aye, when using the "using" statement, you don't have to Close ... Dispose
calls close... but here's the kicker! If you are using the "using"
statement on a SqlConnection object, you are effectively doing away with
connection pooling (someone PLEASE correct me if I'm wrong here). When
you call Dispose on a SqlConnection object, you are disposing of the
object, not just closing it. If you call Close, you are releasing it back
into the pool.
That is not true. For more information, see:

http://msdn2.microsoft.com/en-US/library/8xx3tyca.aspx

Basically, Close and Dispose do the same thing (there are slight
differences in the code, but they have the same effect).
Personally, I like the idea of returning while in the using
statement for this particular scenario. It seems more natural to me to
access the members that are dependent on that resource (parameters are
from a stored procedure executed on that connection) when the resource
is alive, as opposed to when it has been disposed already.

Hope this helps.

-----------
Aye, I feel the same way about returning while in the using statement.

HTH :)

Mythran

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com