Creating a Transaction
Creating a Transaction
The two types of transactions are implicit and explicit. Each SQL statement runs in its own implicit transaction. This means if you don't explicitly create a transaction, a transaction is implicitly created for you on a statement-by-statement basis. This ensures that a SQL statement that updates many rows is either completed as a unit or rolled back.
Creating a Transaction Using T-SQL
An explicit transaction is one that you create in your program. You can explicitly create a transaction in T-SQL by using the following script.
SQL: Explicit Transaction
SET XACT_ABORT ON BEGIN TRY BEGIN TRANSACTION --work code here COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION --cleanup code END CATCH
The SQL TRY/CATCH block is used to catch any errors and roll back the transaction. This code sets XACT_ABORT to on, which ensures that all errors under severity level 21 are handled as transaction abort errors. Severity level 21 and higher is considered fatal and stops code execution, which also rolls back the transaction.
The scope of the transaction is limited to the statements in the TRY block, which can include calls to other stored procedures.
Creating a Transaction Using the ADO.NET DbTransaction Object
Another way to create an explicit transaction is to put the transaction logic in your .NET code. The DbConnection object has the BeginTransaction method, which creates a DbTransaction object. The following code snippet shows how this is done.
Visual Basic
Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Dim cnSetting As ConnectionStringSettings = _ ConfigurationManager.ConnectionStrings("NorthwindString") Using cn As New SqlConnection() cn.ConnectionString = cnSetting.ConnectionString cn.Open() Using tran As SqlTransaction = cn.BeginTransaction() Try 'work code here Using cmd As SqlCommand = cn.CreateCommand() cmd.Transaction = tran cmd.CommandText = "SELECT count(*) FROM employees" Dim count As Integer = CInt(cmd.ExecuteScalar()) MessageBox.Show(count.ToString()) End Using 'if we made it this far, commit tran.Commit() Catch xcp As Exception tran.Rollback() 'cleanup code MessageBox.Show(xcp.Message) End Try End Using End Using End Sub 