Export (0) Print
Expand All

SqlConnection.BeginTransaction Method

Starts a database transaction.

Namespace:  System.Data.SqlClient
Assembly:  System.Data (in System.Data.dll)

'Declaration
Public Function BeginTransaction As SqlTransaction

Return Value

Type: System.Data.SqlClient.SqlTransaction
An object representing the new transaction.

ExceptionCondition
SqlException

Parallel transactions are not allowed when using Multiple Active Result Sets (MARS).

InvalidOperationException

Parallel transactions are not supported.

This command maps to the SQL Server implementation of BEGIN TRANSACTION.

You must explicitly commit or roll back the transaction using the Commit or Rollback method. To make sure that the .NET Framework Data Provider for SQL Server transaction management model performs correctly, avoid using other transaction management models, such as the one provided by SQL Server.

NoteNote

If you do not specify an isolation level, the default isolation level is used. To specify an isolation level with the BeginTransaction method, use the overload that takes the iso parameter (BeginTransaction). The isolation level set for a transaction persists after the transaction is completed and until the connection is closed or disposed. Setting the isolation level to Snapshot in a database where the snapshot isolation level is not enabled does not throw an exception. The transaction will complete using the default isolation level.

Caution noteCaution

If a transaction is started and a level 16 or higher error occurs on the server, the transaction will not be rolled back until the Read method is invoked. No exception is thrown on ExecuteReader.

Caution noteCaution

When your query returns a large amount of data and calls BeginTransaction, a SqlException is thrown because SQL Server 2005 does not allow parallel transactions when using MARS. To avoid this problem, always associate a transaction with the command, the connection, or both before any readers are open.

For more information on SQL Server transactions, see "Explicit Transactions" and "Coding Efficient Transactions" in SQL Server 2005 Books Online.

The following example creates a SqlConnection and a SqlTransaction. It also demonstrates how to use the BeginTransaction, a Commit, and Rollback methods.


Private Sub ExecuteSqlTransaction(ByVal connectionString As String)
    Using connection As New SqlConnection(connectionString)
        connection.Open()

        Dim command As SqlCommand = connection.CreateCommand()
        Dim transaction As SqlTransaction

        ' Start a local transaction
        transaction = connection.BeginTransaction("SampleTransaction")

        ' Must assign both transaction object and connection
        ' to Command object for a pending local transaction.
        command.Connection = connection
        command.Transaction = transaction

        Try
            command.CommandText = _
              "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')"
            command.ExecuteNonQuery()
            command.CommandText = _
              "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')"

            command.ExecuteNonQuery()

            ' Attempt to commit the transaction.
            transaction.Commit()
            Console.WriteLine("Both records are written to database.")

        Catch ex As Exception
            Console.WriteLine("Commit Exception Type: {0}", ex.GetType())
            Console.WriteLine("  Message: {0}", ex.Message)

            ' Attempt to roll back the transaction.
            Try
                transaction.Rollback()

            Catch ex2 As Exception
                ' This catch block will handle any errors that may have occurred
                ' on the server that would cause the rollback to fail, such as
                ' a closed connection.
                Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType())
                Console.WriteLine("  Message: {0}", ex2.Message)
            End Try
        End Try
    End Using
End Sub


.NET Framework

Supported in: 4, 3.5, 3.0, 2.0, 1.1, 1.0

.NET Framework Client Profile

Supported in: 4, 3.5 SP1

Windows 7, Windows Vista SP1 or later, Windows XP SP3, Windows XP SP2 x64 Edition, Windows Server 2008 (Server Core not supported), Windows Server 2008 R2 (Server Core supported with SP1 or later), Windows Server 2003 SP2

The .NET Framework does not support all versions of every platform. For a list of the supported versions, see .NET Framework System Requirements.

Community Additions

ADD
Show:
© 2014 Microsoft