when do we use 'sqltransaction' class?

[bnathvbdotnet]

I am aware that we can put bunch of database transactions like 'insert'/'update/delete between begin and commit and in the catch block depending on the seriousness of the error that occured we can rollback.

Is that the right scenario to use sqltransaction class?

Is there any equivalent class for oracle database?

thanks
nath

[Carnifex]

I use sql transactions whenever I am preforming any sort of update on a database which affects more than 1 record, mainly due to system integrity, don't want half the additions/updates going thru, while the other half don't. All or nothing.

[RichardAtherton]

Perhaps I shoukld start this as a new thread,but I found for that I had to have a separate connection for each begintranasaction amd never figured out why.

[Carnifex]

Because an sql transaction is tied to a connection. You should only use being transaction when you want to begin a set of transactions based on a connection. eg

'Start the transaction
myTransaction = myConnection.BeginTransaction
 
'Set the commands to the transaction
 
cmdOne.Transaction = myTransaction
cmdTwo.Transaction = myTransaction
cmdThree.Transaction = myTransaction
 
'Begin executing all the commands
Try
  cmdOne.ExecuteNonQuery
  cmdTwo.ExecuteNonQuery
  cmdThree.ExecuteNonQuery
  myTransaction.Commit
Catch ex As exception
  myTransaction.Rollback
End Try

In the above example this one connection with one transaction for multiple sql commands. The transaction begins at begin transaction, and finishes when either commit or rollback is used.

Doing it so that it's a one to one relationship between a transaction, command, and connection defeats the purpose of sql connections.