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 |