title: SQL Transaction + Try-catch or VB.NET Transaction + Try-catch?
body: <p>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?</p>
  
  <p>In VB.NET we can do something like:</p>
  
  <pre><code>Try
  
  ...
  
  Catch ex As Exception
      MyTransaction.Rollback
  End Try
  </code></pre>
  
  <p>If try catch is in SQL:</p>
  
  <pre><code>...
  MyCommand.CommandText = _
  "USE AdventureWorks2008R2; " &amp; _
  "GO " &amp; _
  "BEGIN TRANSACTION; " &amp; _
  "BEGIN TRY " &amp; _
  "    ... " &amp; _
  "END TRY " &amp; _
  "BEGIN CATCH " &amp; _
  "    SELECT  " &amp; _
  "        ERROR_NUMBER() AS ErrorNumber " &amp; _
  "        ,ERROR_SEVERITY() AS ErrorSeverity " &amp; _
  "        ,ERROR_STATE() AS ErrorState " &amp; _
  "        ,ERROR_PROCEDURE() AS ErrorProcedure " &amp; _
  "        ,ERROR_LINE() AS ErrorLine " &amp; _
  "        ,ERROR_MESSAGE() AS ErrorMessage; " &amp; _
  "    IF @@TRANCOUNT &gt; 0 " &amp; _
  "        ROLLBACK TRANSACTION; " &amp; _
  "END CATCH; " &amp; _
  "IF @@TRANCOUNT &gt; 0 " &amp; _
  "    COMMIT TRANSACTION; " &amp; _
  "GO " &amp; _
  MyCommand.ExecuteNonQuery()
  </code></pre>
  
  <p>I always use the first one, thru VB.NET. Is there any difference? What are those? Any drawbacks?</p>