Help me understand try-catch

[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.BeginTr ansaction()
'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.BeginTr ansaction()
Try
'do a delete
'do a update
'do an insert
catch ex as exception
myTrans.RollBac k()
throw new exception("ERRO R!",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

[Jared]

If you are calling three distinct types why not create three function one
for Update, Insert, Delete

Friend Function UpdateSQLStuff( ) as MyReturnResult
'do some sql stuff
End Function
Friend Function InsertSQLStuff( ) as MyReturnResult
'do some sql stuff
End Function
Friend Function DeleteSQLStuff( ) as MyReturnResult
'do some sql stuff
End Function

Or - You could add a paramater to your method/function

friend function DoSomeSQLStuff( byval SQLAction as String) as string
Try
'do some stuff
Catch ex as exception
Select Case SQLAction
Case Is = "Upate"
'handle update error
Case Is = "Delete"
'handle delete error
Case Is = "Insert"
'handle insert error
End Select
end try

In any sense, I wouldn't close your database connection without first
finishing your processing, if you know you are going to do an update, insert
and delete in every transaction then close it at the end, otherwise try to
seperate the logic in some way. Select Case? Your choice.
Hope that makes sense.
Jared

"Big D" <[e-mail]> wrote in message
news:#9******** ******@TK2MSFTN GP09.phx.gbl...

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.BeginTr ansaction()
'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.BeginTr ansaction()
Try
'do a delete
'do a update
'do an insert
catch ex as exception
myTrans.RollBac k()
throw new exception("ERRO R!",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

[solex]

D,

If you are using SQL server then you could create named transactions. This
allow you to basically setup multiple roll back points. For instance in
your example you could roll back to the insert if the insert fails but
maintain the delete. The BOL has more detail.

Dan

[Paul E Collins]

"Big D" <[e-mail]> wrote:

[...]
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),

You can customise your message even in a single try block.

try
{
strMsg = "error updating";
// ... update ...
strMsg = "error inserting";
// ... insert ...
}
catch
{
// display strMsg
}

P.

--
www.CL4.org

[Tom Dacon]

In addition to the conventional technique of nesting Try blocks, if you're
using VB.Net you can make use of the righteously cool 'When' feature in the
Catch block:

Create a state variable called, say Step. Increment Step after each
statement in the Try block, along the lines of the following:

try
step = 0
open the connection
step += 1
run the update
end try

and so on

Then write catch blocks like:

catch (ex as SqlException) When step = 1

catch (ex as Exception) When step = 1

catch (ex as SqlException) When step = 2

catch (ex as Exception) When step = 2

etc.

Between nested Try blocks, the When technique in VB.Net, and your own
understanding of what exception are likely to occur, I'd be surprised if you
couldn't figure out a sequence of steps that would handle the situation.

Regards,
Tom Dacon
Dacon Software Consulting

"Big D" <[e-mail]> wrote in message
news:%2******** ********@TK2MSF TNGP09.phx.gbl. ..

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.BeginTr ansaction()
'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.BeginTr ansaction()
Try
'do a delete
'do a update
'do an insert
catch ex as exception
myTrans.RollBac k()
throw new exception("ERRO R!",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