Transactions in ADO.NET

Until now, you have used only T-SQL to manage transactions, but transactions can also be managed directly through ADO.NET. Transactions in ADO.NET are managed through the SQLTransaction class, which can be found in the System.Data.SqlClient namespace. For every transaction, an isolation level can be provided through the IsolationLevel Enumerators namespace.

The following example shows how a transaction can be defined through ADO.NET. The transaction is rolled back on any error and committed when no error occurs. This code is included in the sample files as Chapter10.sln. To run the sample, build and execute the solution in Visual Studio, then make sure the Output window is visible in Visual Studio while you click the two buttons on the form.

Private Sub TryCommand(ByVal cmd As String)     Dim connectionString As String = _       "Data Source=.\SQLExpress;Initial Catalog=AdventureWorks;" + _       "Integrated Security=True;"     Using connection As New SqlConnection(connectionString)         connection.Open()         Dim command As SqlCommand = connection.CreateCommand()         Dim transaction As SqlTransaction         Dim iso As IsolationLevel = IsolationLevel.RepeatableRead         'Start a local transaction and define the Isolation Level         transaction = connection.BeginTransaction(iso)         'Assign the transaction and connection object         'to the Command object         command.Connection = connection         command.Transaction = transaction         Try             command.CommandText = cmd             command.ExecuteNonQuery()             ' Attempt to commit the transaction.             transaction.Commit()             Console.WriteLine("Transaction succeeded.")         Catch ex As Exception             Console.WriteLine("Commit Exception Type: {0}", ex.GetType())             Console.WriteLine("  Message: {0}", ex.Message)             ' Try to roll back the transaction.             Try                 transaction.Rollback()             Catch ex2 As Exception                 'When the connection was already closed                 'the Rollback doesn't work anymore                 Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType())                 Console.WriteLine("  Message: {0}", ex2.Message)             End Try         End Try     End Using End Sub Private Sub btnTryInvalidCmd_Click(ByVal sender As System.Object, _     ByVal e As System.EventArgs) Handles btnSetInvalidCmd.Click     TryCommand("This_is_an_invalid_sql_command") End Sub Private Sub btnTryValidCmd_Click(ByVal sender As System.Object, _     ByVal e As System.EventArgs) Handles btnSetValidCmd.Click     TryCommand("SELECT * FROM dbo.orders") End Sub