BeginTransaction Method
Collapse the table of content
Expand the table of content

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.

Exception Condition
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.

System_CAPS_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.

System_CAPS_cautionCaution

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.

System_CAPS_cautionCaution

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);
            }
        }
    }
}

Universal Windows Platform
Available since 10
.NET Framework
Available since 1.1
Return to top
Show:
© 2016 Microsoft