SQL Transaction + Try-catch or VB.NET Transaction + Try-catch?

Question

I'm working with an application which connects to a database. I definitely want to do a rollback if some database operations failed. I'm just wondering if which transaction + try-catch is better to use, the SQL or the VB.NET?

In VB.NET we can do something like:

Try

...

Catch ex As Exception
    MyTransaction.Rollback
End Try

If try catch is in SQL:

...
MyCommand.CommandText = _
"USE AdventureWorks2008R2; " & _
"GO " & _
"BEGIN TRANSACTION; " & _
"BEGIN TRY " & _
"    ... " & _
"END TRY " & _
"BEGIN CATCH " & _
"    SELECT  " & _
"        ERROR_NUMBER() AS ErrorNumber " & _
"        ,ERROR_SEVERITY() AS ErrorSeverity " & _
"        ,ERROR_STATE() AS ErrorState " & _
"        ,ERROR_PROCEDURE() AS ErrorProcedure " & _
"        ,ERROR_LINE() AS ErrorLine " & _
"        ,ERROR_MESSAGE() AS ErrorMessage; " & _
"    IF @@TRANCOUNT > 0 " & _
"        ROLLBACK TRANSACTION; " & _
"END CATCH; " & _
"IF @@TRANCOUNT > 0 " & _
"    COMMIT TRANSACTION; " & _
"GO " & _
MyCommand.ExecuteNonQuery()

I always use the first one, thru VB.NET. Is there any difference? What are those? Any drawbacks?

JaredPar JaredPar · Accepted Answer · 2011-08-12T07:37:39

One of the differences is the errors that are trapped.

The SQL catch will catch errors that occur in the SQL query
The VB.Net catch will catch errors that occur in VB.Net. This includes both programming and many query errors which propagate through .Net APIs

Which you want to use is highly dependent upon your situation. If all the VB.Net try block does is execute the query and roll back on errors then you're much better served doing the rollback in SQL. This keeps the database logic tight and local.

However if the VB.Net try block includes say additional business logic then it probably makes more sense to keep the rollback there.

SQL Transaction + Try-catch or VB.NET Transaction + Try-catch?

2 Answers