Export (0) Print
Expand All

SqlConnection.BeginTransaction Method

Starts a database transaction.

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

public SqlTransaction BeginTransaction()

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 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 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 static void ExecuteSqlTransaction(string connectionString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        SqlCommand command = connection.CreateCommand();
        SqlTransaction transaction;

        // 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 (Exception ex)
        {
            Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
            Console.WriteLine("  Message: {0}", ex.Message);

            // Attempt to roll back the transaction. 
            try
            {
                transaction.Rollback();
            }
            catch (Exception ex2)
            {
                // 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);
            }
        }
    }
}

.NET Framework

Supported in: 4.5.2, 4.5.1, 4.5, 4, 3.5, 3.0, 2.0, 1.1, 1.0

.NET Framework Client Profile

Supported in: 4, 3.5 SP1

Windows 8.1, Windows Server 2012 R2, Windows 8, Windows Server 2012, Windows 7, Windows Vista SP2, Windows Server 2008 (Server Core Role not supported), Windows Server 2008 R2 (Server Core Role supported with SP1 or later; Itanium not supported)

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

Show:
© 2014 Microsoft