我正在处理一个连接到数据库的应用程序。如果某些数据库操作失败,我肯定要进行回滚操作。我只是在想,使用SQL还是VB.NET中的事务+try-catch更好呢?
在VB.NET中可这样做:
Try
...
Catch ex As Exception
MyTransaction.Rollback
End Try
如果在 SQL 中使用 try catch:
...
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()
我通常使用VB.NET来实现这个功能,但是其他方法有什么区别?它们之间存在什么不同?是否存在任何缺点?