Need Advice: How to Wrap This DB Activity in Try..Catch..Finally 1

[DSect]

Ok - here's the simplified work flow w/ no error handling -
1. Start SQLTransaction
2. Insert Data
3. Grab @@Identity
4. Do A Work Process (Process a credit card)
4a. If Work Fails Rollback Trans - Close Connection - Exit
4b. If Work Passes Go to #5.
5. Insert More Data
5a. If Insert ok , then Commit original transaction - Routine is Finished
5b. If Bad Insert, Rollback original transaction.

So - Here's my confusion:

I need to keep the connection open to be able to CommitTransaction, correct?

If I did it in Try..Catch..Finally..(the wrong way) It would look like this:

Try
Open DB Connection
Start Trans
Execute Command
Catch Execption
Bail out of procedure
Finally
(Cleanup Db & command objects)
End Try

If I put the cleanup in the Finally block, I'll close the connection and not be able to commit the transaction that would follow if successful.

I would think you'd do this (but this is where I also think I'm wrong!):

Try
Open DB Connection
Start Trans
Execute Command
Catch Execption
(Cleanup Db & command objects)
Bail out of procedure
End Try

(This is where I do the other process that succeeds)

Commit original transaction
(Cleanup Db & command objects)

Am I trying to use a "Finally" the wrong way? If I do it like I've seen people use it before, I won't be able to use the open connection to commit tras later on.

Should I put my entire Work Process (Steps 4 to 5b) into the Finally block of My First Db Insert (Steps 1 to 3)?

Like this (reference step #s above):

Try
1.
2.
3.
Catch Execption
Exit Procedure
Finally
4a.
4b.
5a.
5b.
End Try


Thanks for any insight into using Try..Catch..Finally when doing multiple DB operations. I'm thinking I need to nest them to do the entire process??

 

[shatch]

If you are putting the whole thing in a transaction maybe the best thing to do is to put the while lot in the try catch block, then whe whole thing would get rolled back, like below

Try
Open DB Connection
Start Trans
Execute Command
Insert More Data
Commit Transaction
Catch Execption
Rollback Transaction
Finally
(Cleanup Db & command objects)
End Try

I would say that if any part of a inset failed it should raise an exception, hope that helps

 

[DSect]

Hey - There's an idea and it lets me gracefully close my objects after failure.

I was focused more on using the Finally than the Try..

I'd like to hear other opinions also - but your meets my needs.

Thanks!