Dispose() and transactions

[Daniel Billingsley]

Does this look right? I want to be a good boy and dispose of everything and
I'm trying to make sure I have the lifecycles right to support the
transactional part.

using (SqlConnection cn = new SqlConnection("blah blah")
{
cn.open();
using (SqlTransaction tran = cn.BeginTransaction())
using (SqlCommand cm = new SqlCommand())
{
cm.Connection = cn;
cm.Transaction = tran;
try
{
// do some stuff
cm.ExecutNonQuery();
tran.Commit();
}
catch(Exception ex)
{
tran.RollBack();
throw ex;
}
}

[Nick Harris]

umm... how about adding a finally{} block to your try block to close your
connection and set it to null. Right now your not disposing of anything,
your letting GC do it when your ref's are out of scope.

Hope this helps.

Nick Harris, MCP, CNA, MCSD
Director of Software Services
http://www.VizSoft.net
"Daniel Billingsley" <db**********@NO.durcon.SPAAMM.com> wrote in message
news:uF**************@tk2msftngp13.phx.gbl...

Does this look right? I want to be a good boy and dispose of everything and I'm trying to make sure I have the lifecycles right to support the
transactional part.

using (SqlConnection cn = new SqlConnection("blah blah")
{
cn.open();
using (SqlTransaction tran = cn.BeginTransaction())
using (SqlCommand cm = new SqlCommand())
{
cm.Connection = cn;
cm.Transaction = tran;
try
{
// do some stuff
cm.ExecutNonQuery();
tran.Commit();
}
catch(Exception ex)
{
tran.RollBack();
throw ex;
}
}

[Alan Pretre]

"Daniel Billingsley" <db**********@NO.durcon.SPAAMM.com> wrote in message
news:uF**************@tk2msftngp13.phx.gbl...

Does this look right? I want to be a good boy and dispose of everything and I'm trying to make sure I have the lifecycles right to support the
transactional part.

Daniel, this is almost exactly how I do it. Mine is structured a little bit
differently. Also you don't need the ex on the throw. I took your example
and redid it based on the way that I do it.

using (SqlConnection cn = new SqlConnection("blah blah") {
cn.open();
using (SqlTransaction tran = cn.BeginTransaction()) {
try {
using (SqlCommand cm = new SqlCommand()) {
cm.Connection = cn;
cm.Transaction = tran;
// do some stuff
cm.ExecutNonQuery();
tran.Commit();
}
} catch {
tran.RollBack();
throw;
}
}
}

-- Alan

[Jon Skeet [C# MVP]]

Nick Harris <nh*****@VizSoft.net> wrote:

umm... how about adding a finally{} block to your try block to close your
connection and set it to null. Right now your not disposing of anything,
your letting GC do it when your ref's are out of scope.

No - note the "using" blocks, which *do* perform a Dispose implicitly.
The connection, transaction and command are all being disposed. Setting
the variables to null would be unnecessary, too, as they'd fall out of
scope anyway.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Jon Skeet [C# MVP]]

Alan Pretre <no@spam> wrote:

"Daniel Billingsley" <db**********@NO.durcon.SPAAMM.com> wrote in message
news:uF**************@tk2msftngp13.phx.gbl...

Does this look right? I want to be a good boy and dispose of everything

and

I'm trying to make sure I have the lifecycles right to support the
transactional part.

Daniel, this is almost exactly how I do it. Mine is structured a little bit
differently. Also you don't need the ex on the throw. I took your example
and redid it based on the way that I do it.

<snip>

Just as a minor point, I can't see from the docs what happens to a
transaction that hasn't been either committed or rolled back but is
then disposed. It would make sense to my mind for any such transaction
to be rolled back - which would also make the code a lot simpler (you
wouldn't need the extra try block) - what am I missing?

(I suspect this is actually what it does, but as the documentation
doesn't state that, it would be wrong to assume it.)

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Alan Pretre]

"Jon Skeet [C# MVP]" <sk***@pobox.com> wrote in message
news:MP************************@msnews.microsoft.c om...

Just as a minor point, I can't see from the docs what happens to a
transaction that hasn't been either committed or rolled back but is
then disposed. It would make sense to my mind for any such transaction
to be rolled back - which would also make the code a lot simpler (you
wouldn't need the extra try block)
I'm not sure about the answer to your hypothesis. But at least in my
snippet all of the code paths result in a Commit() or a Rollback(), which
seems to make intent clear.
what am I missing?

I don't think you're missing anything... would you post your version of the
snippet without the try/catch...

-- Alan

[Jon Skeet [C# MVP]]

Alan Pretre <no@spam> wrote:

"Jon Skeet [C# MVP]" <sk***@pobox.com> wrote in message
news:MP************************@msnews.microsoft.c om...

Just as a minor point, I can't see from the docs what happens to a
transaction that hasn't been either committed or rolled back but is
then disposed. It would make sense to my mind for any such transaction
to be rolled back - which would also make the code a lot simpler (you
wouldn't need the extra try block)

I'm not sure about the answer to your hypothesis. But at least in my
snippet all of the code paths result in a Commit() or a Rollback(), which
seems to make intent clear.

That's true.

what am I missing?

I don't think you're missing anything... would you post your version of the
snippet without the try/catch...

Sure (although I can't bring myself to use your bracing style, I'm
afraid :)

using (SqlConnection cn = new SqlConnection("blah blah"))
{
cn.Open();
using (SqlTransaction tran = cn.BeginTransaction())
{
using (SqlCommand cm = new SqlCommand())
{
cm.Connection = cn;
cm.Transaction = tran;
// do some stuff
cm.ExecuteNonQuery();
tran.Commit();
}
}
}

If an exception is thrown, the command gets disposed, then the
transaction gets disposed, then the connection gets disposed. When the
transaction is disposed, it could check whether or not it had been
committed, and roll back if not.

I don't know for sure, not having done much database work in .NET, but
I presume that the tran.Commit() could actually be called *after* the
using block with the SqlCommand? (For many transactions you'd want one
command after another, wouldn't you?) If so, I'd put it that way so
that it was more logical: do everything with the command, and then
commit the transaction after you've finished with the command.

The above looks like cleaner code to me, but does rely on the reader
knowing that Dispose would perform that rollback. I suspect that if
SqlTransaction were documented to have that behaviour, it would become
sufficiently ingrained as an idiom that it wouldn't be a problem.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Alan Pretre]

"Jon Skeet [C# MVP]" <sk***@pobox.com> wrote in message
news:MP************************@msnews.microsoft.c om...

I don't know for sure, not having done much database work in .NET, but
I presume that the tran.Commit() could actually be called *after* the
using block with the SqlCommand? (For many transactions you'd want one
command after another, wouldn't you?) If so, I'd put it that way so
that it was more logical: do everything with the command, and then
commit the transaction after you've finished with the command.

Agreed. The SqlTransaction object has no dependency on the SqlCommand
object.

So we are left with either (although I can't bring myself to use your
bracing style, I'm
afraid :) :

using (SqlConnection cn = new SqlConnection("blah blah") {
cn.open();
using (SqlTransaction tran = cn.BeginTransaction()) {
try {
using (SqlCommand cm = new SqlCommand()) {
cm.Connection = cn;
cm.Transaction = tran;
// do some stuff
cm.ExecutNonQuery();
}
tran.Commit();
} catch {
tran.RollBack();
throw;
}
}
}

OR

using (SqlConnection cn = new SqlConnection("blah blah") {
cn.open();
using (SqlTransaction tran = cn.BeginTransaction()) {
using (SqlCommand cm = new SqlCommand()) {
cm.Connection = cn;
cm.Transaction = tran;
// do some stuff
cm.ExecutNonQuery();
}
tran.Commit();
}
}

-- Alan

[Mickey Williams]

"Jon Skeet [C# MVP]" <sk***@pobox.com> wrote in message
news:MP************************@msnews.microsoft.c om...

Just as a minor point, I can't see from the docs what happens to a
transaction that hasn't been either committed or rolled back but is
then disposed.

If the transaction is disposed before a Commit(), it's the same as an
implicit vote for a rollback. An explicit call to Commit() is required to
preserve the work performed under the transaction.

--
Mickey Williams
Author, "Microsoft Visual C# .NET Core Reference", MS Press
www.servergeek.com

[Jon Skeet [C# MVP]]

<"Mickey Williams" <my first name at servergeek.com>> wrote:

Just as a minor point, I can't see from the docs what happens to a
transaction that hasn't been either committed or rolled back but is
then disposed.

If the transaction is disposed before a Commit(), it's the same as an
implicit vote for a rollback. An explicit call to Commit() is required to
preserve the work performed under the transaction.

That was I expected - but do you know whether it's actually
*documented* anywhere? It's the only thing that makes any sense, IMO,
but that's not the same as it being worth depending on. Maybe it'll be
documented in Whidbey...

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Daniel Billingsley]

This is one of the things I wanted to verify. You're implying the command
objects don't need to be around for the transaction to commit or rollback
and my code was based on thinking they did. But I think your thinking seems
more logical now that I look at it again. I guess I wasn't sure if a
transaction object is encapsulated with all the information it needs to
commit or rollback (without the commands), but then that would be good OO
and make more sense, eh?

That would certainly have to be the case if you reused the command, changing
the CommandText and doing another ExecuteNonQuery, for example, wouldn't it?
I assume that's permitted.

"Jon Skeet [C# MVP]" <sk***@pobox.com> wrote in message
news:MP************************@msnews.microsoft.c om...

<snip>
I don't know for sure, not having done much database work in .NET, but
I presume that the tran.Commit() could actually be called *after* the
using block with the SqlCommand? (For many transactions you'd want one
command after another, wouldn't you?) If so, I'd put it that way so
that it was more logical: do everything with the command, and then
commit the transaction after you've finished with the command.

[Daniel Billingsley]

Speaking of bracing style, you don't like my

using (SqlTransaction tran = cn.BeginTransaction())
using (SqlCommand cm = new SqlCommand())
{

code? Maybe I'm too simple-minded, but removing a level of indentation
makes things easier to follow for me. Would you find this code hard to
read, or is it just a tom-a-to/tom-ah-to kind of thing? <Realizing that
analogy may lose something going across the ocean as you may pronounce them
the same...lol>

"Jon Skeet [C# MVP]" <sk***@pobox.com> wrote in message
news:MP************************@msnews.microsoft.c om...

using (SqlConnection cn = new SqlConnection("blah blah"))
{
cn.Open();
using (SqlTransaction tran = cn.BeginTransaction())
{
using (SqlCommand cm = new SqlCommand())
{
cm.Connection = cn;
cm.Transaction = tran;
// do some stuff
cm.ExecuteNonQuery();
tran.Commit();
}
}
}

[Alan Pretre]

"Daniel Billingsley" <db**********@NO.durcon.SPAAMM.com> wrote in message
news:OY**************@TK2MSFTNGP09.phx.gbl...

Speaking of bracing style, you don't like my

using (SqlTransaction tran = cn.BeginTransaction())
using (SqlCommand cm = new SqlCommand())
{

code?

Hey I'm a K&R guy. I'm not in vogue right now. Sooner or later the fad
will come back to my way again. ;-)

-- Alan

[Jon Skeet [C# MVP]]

Daniel Billingsley <db**********@NO.durcon.SPAAMM.com> wrote:

Speaking of bracing style, you don't like my

using (SqlTransaction tran = cn.BeginTransaction())
using (SqlCommand cm = new SqlCommand())
{

code?
Maybe I'm too simple-minded, but removing a level of indentation
makes things easier to follow for me.
But you haven't *really* removed a level of indentation - you've just
not shown it, if you see what I mean. You could do the same everywhere
and just not bother indenting at all, but it wouldn't help at all.

The bracing style I was talking about was the "brace at the end" style
though.
Would you find this code hard to read, or is it just a tom-a-to/tom-ah-to
kind of thing?

I would find it "odd" to read in the same way that I'd find:

if (x==y)
Foo();

hard to read - it doesn't show the indentation I'd expect to see,
namely that the Foo() part is effectively encompassed by the if (x==y)
part.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Daniel Billingsley]

Actually, I was talking more to Jon who used my style except for this
section. You're stuff is, well, just ugly. LOL.

"Alan Pretre" <no@spam> wrote in message
news:ek**************@tk2msftngp13.phx.gbl...

"Daniel Billingsley" <db**********@NO.durcon.SPAAMM.com> wrote in message
news:OY**************@TK2MSFTNGP09.phx.gbl...

Speaking of bracing style, you don't like my

using (SqlTransaction tran = cn.BeginTransaction())
using (SqlCommand cm = new SqlCommand())
{

code?

Hey I'm a K&R guy. I'm not in vogue right now. Sooner or later the fad
will come back to my way again. ;-)

-- Alan

[Daniel Billingsley]

"Jon Skeet [C# MVP]" <sk***@pobox.com> wrote in message
news:MP************************@msnews.microsoft.c om...

Daniel Billingsley <db**********@NO.durcon.SPAAMM.com> wrote:

But you haven't *really* removed a level of indentation - you've just
not shown it, if you see what I mean.
Oh yes, of course, I realize that.
I would find it "odd" to read in the same way that I'd find:

if (x==y)
Foo();

hard to read - it doesn't show the indentation I'd expect to see,
namely that the Foo() part is effectively encompassed by the if (x==y)
part.

Hmm. Interesting. I think I actually saw that example somewhere, but can't
find it. The Help shows them nested with indentation but no brackets.
using()
using()
...
using()
statement;

Since I'm one that would generally lean towards using brackets even for a
one-line statement for the sake of clarity...
if (x==y)
{
DoSomething();
}

I guess I'm being inconsistent. I was thinking this was a generally
accepted exception since I remembered seeing the example, and I would think
nested using() statements would be fairly common.

Guess I thought wrong. :)

[Jon Skeet [C# MVP]]

Daniel Billingsley <db**********@NO.durcon.SPAAMM.com> wrote:

Hmm. Interesting. I think I actually saw that example somewhere, but can't
find it. The Help shows them nested with indentation but no brackets.
using()
using()
...
using()
statement;
Indented with no brackets is certainly better than not indented with no
brackets, but I'd prefer indented *with* brackets :)
Since I'm one that would generally lean towards using brackets even for a
one-line statement for the sake of clarity...
if (x==y)
{
DoSomething();
}
I'm hypocritical on that one - I think it's a good idea, but I'm often
too lazy to do it, unfortunately :(
I guess I'm being inconsistent. I was thinking this was a generally
accepted exception since I remembered seeing the example, and I would think
nested using() statements would be fairly common.
I'm sure they're fairly common (especially for cases like this) but
that doesn't mean it's a good idea to miss the braces out - IMO, of
course.
Guess I thought wrong. :)

Not necessarily - plenty of people disagree with me on all kinds of
matters, so I wouldn't be surprised if this is another one :)

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too