B
Big D
Hi all,
I'm trying to understand the best way to use a try catch with a SQL
transaction. I have a number of sql statements that need to be run, such
as:
'open sqlConnection, get command object, etc.
myTrans.BeginTransaction()
'do a delete
'do a update
'do an insert
myTrans.Commit()
'close connection/dispose of objects
However, I want to be able to roll it back so I do
myTrans.BeginTransaction()
Try
'do a delete
'do a update
'do an insert
catch ex as exception
myTrans.RollBack()
throw new exception("ERROR!",ex)
finally
'close connection/dispose of objects
end try
BUT, what I want to be able to do is have multiple TRYs... one for the
delete, one for the update, one for the insert, (so I can customize the
thrown error message), then within the CATCH for each of those trys, do a
ROLLBACK. What I don't understand though, is that the FINALLY clause of the
TRY/CATCH is ALWAYS executed, so even if no error is encountered, my
sqlconnection would be closed in the first TRY. How is this supposed to be
handled? Should I close the connection/dispose of objects from within the
CATCH in every TRY, or will this be closed/disposed anyway because of the
thrown error?? Does that make sense?
Thanks a bunch,
D
I'm trying to understand the best way to use a try catch with a SQL
transaction. I have a number of sql statements that need to be run, such
as:
'open sqlConnection, get command object, etc.
myTrans.BeginTransaction()
'do a delete
'do a update
'do an insert
myTrans.Commit()
'close connection/dispose of objects
However, I want to be able to roll it back so I do
myTrans.BeginTransaction()
Try
'do a delete
'do a update
'do an insert
catch ex as exception
myTrans.RollBack()
throw new exception("ERROR!",ex)
finally
'close connection/dispose of objects
end try
BUT, what I want to be able to do is have multiple TRYs... one for the
delete, one for the update, one for the insert, (so I can customize the
thrown error message), then within the CATCH for each of those trys, do a
ROLLBACK. What I don't understand though, is that the FINALLY clause of the
TRY/CATCH is ALWAYS executed, so even if no error is encountered, my
sqlconnection would be closed in the first TRY. How is this supposed to be
handled? Should I close the connection/dispose of objects from within the
CATCH in every TRY, or will this be closed/disposed anyway because of the
thrown error?? Does that make sense?
Thanks a bunch,
D