SQL 事务+ Try-catch 或 VB.NET 事务 +尝试捕捉？
我正在使用一个连接到数据库的应用程序。如果某些数据库操作失败，我肯定想进行回滚。我只是想知道使用哪种事务+ try-catch 更好，SQL 还是 VB.NET？
在 VB.NET 中，我们可以这样做：
Try
...
Catch ex As Exception
MyTransaction.Rollback
End Try
如果 try catch 在 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()
我总是通过 VB.NET 使用第一个。有什么区别吗？那些是什么？有什么缺点吗？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
差异之一是捕获的错误。
您想要使用的错误很大程度上取决于您的情况。如果 VB.Net try 块所做的只是执行查询并在错误时回滚，那么您可以更好地在 SQL 中执行回滚。这使数据库逻辑保持紧密和本地化。
然而，如果 VB.Net try 块包含额外的业务逻辑，那么将回滚保留在那里可能更有意义。
One of the differences is the errors that are trapped.
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.
从功能上来说，它们是相同的 - vb.net 版本被翻译并作为 SQL 查询运行。
主要区别在于，您可以使用同一连接执行多个 SQL 语句，而不必生成一个包含您想要在事务中完成的所有操作的大型 SQL 查询。
Functionally, they are they same - the vb.net version gets translated and run as sql queries.
The main difference is that you can execute several SQL statements using the same connection without having to generate one large SQL query that contains everything you want done in the transaction.