Transaction Scope

[Guest]

Hi,

I have seen difference approaches in handling transactions on the msdn site.
One approach is that you open a connection and then begin the transaction.
Next you execute command objects using this connection and after you are done
you commit or rollback thereby giving a global scope to the transaction for
all the command objects. Second approach is you open the connection, begin
the transaction and then keep a reference of this transaction and then when
you are executing a command object you assign the transaction object to the
command object. This would mean assigning that same transaction object for
every command object that you want to execute(optional as i might not want to
enlist my transaction for a particular command)
Now the question is are the two approaches similar or in other words is
assigning the transaction object to a command object optional as the
connection already started the transaction ? Which one is a better approach ?
Anybody from Microsoft if you can answer this question.

Thanks!

 

[Sahil Malik [MVP]]

Vish,

Try writing code like this --

Connection.Open
Tran = Connection.BeginTransaction()
Cmd.Transaction = tran
cmd.executenonquery()
cmd2 = new sqlcommand
cmd2.commandtext = "blah blah"
cmd2.executenonquery // note - I didn't specify a transaction on this.
conn.close()

See what happens

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/

 

[Val Mazur \(MVP\)]

Hi,

If you did not specify transaction for the command, then it should be
outside of the transaction. This is the main difference. Opening of the
transaction against connection does not mean that all then commands
participate in it

 

[Guest]

Val,

Is there aother way to do transactions at the connection level without
assigning it to a command object everytime ? What i need to do is start a
transaction and then issue multiple command on the same connection. I saw the
transaction scope object for Framework 2.0 wondering is there is anything
like that in 1.1 ?

Thanks!

 

[Marina]

Nothing like that in 1.1. You need to be able to access the transaction
object, and assign it to the transaction property of the command. As long as
you are passing around the connection, it's not so hard to pass around the
transaction too.

Or you can write on centralized routine to execute all the transactional
queries, that way you don't have to worry about any of that.

 

[Val Mazur \(MVP\)]

Hi,

No, you need to specify transaction using transaction property. I believe if
you did the first step, then specifying transaction is not a big issue in
this case

--
Val Mazur
Microsoft MVP

http://xport.mvps.org