Export (0) Print
Expand All

SqlConnection.BeginTransaction Method (IsolationLevel, String)

Starts a database transaction with the specified isolation level and transaction name.

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

public SqlTransaction BeginTransaction(
	IsolationLevel iso,
	string transactionName
)

Parameters

iso
Type: System.Data.IsolationLevel
The isolation level under which the transaction should run.
transactionName
Type: System.String
The name of the transaction.

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.

The value in the transactionName parameter can be used in later calls to Rollback and in the savePoint parameter of the Save method.

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

After a transaction is committed or rolled back, the isolation level of the transaction persists for all subsequent commands that are in autocommit mode (the Microsoft SQL Server default). This can produce unexpected results, such as an isolation level of REPEATABLE READ persisting and locking other users out of a row. To reset the isolation level to the default (READ COMMITTED), execute the Transact-SQL SET TRANSACTION ISOLATION LEVEL READ COMMITTED statement, or call SqlConnection.BeginTransaction followed immediately by SqlTransaction.Commit. For more information on SQL Server isolation levels, see "Isolation Levels in the Database Engine" in SQL Server 2005 Books Online.

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

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.

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(
            IsolationLevel.ReadCommitted, "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();
            transaction.Commit();
            Console.WriteLine("Both records are written to database.");
        }
        catch (Exception e)
        {
            try
            {
                transaction.Rollback();
            }
            catch (SqlException ex)
            {
                if (transaction.Connection != null)
                {
                    Console.WriteLine("An exception of type " + ex.GetType() +
                        " was encountered while attempting to roll back the transaction.");
                }
            }

            Console.WriteLine("An exception of type " + e.GetType() +
                " was encountered while inserting the data.");
            Console.WriteLine("Neither record was written to database.");
        }
    }
}


.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