SqlConnection.BeginTransaction 方法

定义

命名空间:

System.Data.SqlClient

程序集:

System.Data.dll

启动数据库事务。

重载

名称	说明
BeginTransaction()	启动数据库事务。
BeginTransaction(IsolationLevel)	使用指定的隔离级别启动数据库事务。
BeginTransaction(String)	使用指定的事务名称启动数据库事务。
BeginTransaction(IsolationLevel, String)	使用指定的隔离级别和事务名称启动数据库事务。

BeginTransaction()

启动数据库事务。

public:
 System::Data::SqlClient::SqlTransaction ^ BeginTransaction();

public System.Data.SqlClient.SqlTransaction BeginTransaction();

override this.BeginTransaction : unit -> System.Data.SqlClient.SqlTransaction

Public Function BeginTransaction () As SqlTransaction

返回

SqlTransaction

表示新事务的对象。

例外

SqlException

使用多个活动结果集（MARS）时，不允许并行事务。

InvalidOperationException

不支持并行事务。

示例

以下示例创建一个 SqlConnection 和一个 SqlTransaction。 它还演示了如何使用 BeginTransaction、a Commit和 Rollback 方法。

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

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

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()

        ' 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

注解

此命令映射到 BEGIN TRANSACTION 的SQL Server实现。

必须使用或Rollback方法显式提交或回滚事务Commit。 若要确保SQL Server事务管理模型的 .NET Framework Data Provider能够正确执行，请避免使用其他事务管理模型，例如SQL Server提供的模型。

注释

如果未指定隔离级别，则使用默认隔离级别。 若要使用BeginTransaction该方法指定隔离级别，请使用采用参数 （BeginTransaction） 的iso重载。 事务完成之后，事务设置的隔离级别将一直保留，直到连接关闭或释放为止。 将隔离级别设置为未启用快照隔离级别的数据库中的 快照 不会引发异常。 事务将使用默认隔离级别完成。

注意

如果事务已启动，并且服务器上出现级别 16 或更高版本的错误，则在调用该方法之前 Read ，不会回滚该事务。 ExecuteReader 上不会引发异常。

注意

当查询返回大量数据并调用 BeginTransaction 时，会引发 SqlException，因为使用 MARS 时SQL Server不允许并行事务。 若要避免此问题，请在打开任何读取器之前始终将事务与命令、连接或两者相关联。

有关SQL Server事务的详细信息，请参阅 Transactions （Transact-SQL）。

另请参阅

• 事务和并发性
• 在 ADO.NET 中连接到数据源
• SQL Server 和 ADO.NET
• ADO.NET 概述

BeginTransaction(IsolationLevel)

使用指定的隔离级别启动数据库事务。

public:
 System::Data::SqlClient::SqlTransaction ^ BeginTransaction(System::Data::IsolationLevel iso);

public System.Data.SqlClient.SqlTransaction BeginTransaction(System.Data.IsolationLevel iso);

override this.BeginTransaction : System.Data.IsolationLevel -> System.Data.SqlClient.SqlTransaction

Public Function BeginTransaction (iso As IsolationLevel) As SqlTransaction

参数

iso

IsolationLevel

事务应在其下运行的隔离级别。

返回

SqlTransaction

表示新事务的对象。

例外

SqlException

使用多个活动结果集（MARS）时，不允许并行事务。

InvalidOperationException

不支持并行事务。

示例

以下示例创建一个 SqlConnection 和一个 SqlTransaction。 它还演示了如何使用 BeginTransaction、a Commit和 Rollback 方法。

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

        // 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.");
        }
    }
}

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(IsolationLevel.ReadCommitted)
        ' 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 e As Exception
            Try
                transaction.Rollback()
            Catch ex As SqlException
                If Not transaction.Connection Is Nothing Then
                    Console.WriteLine("An exception of type " & ex.GetType().ToString() & _
                      " was encountered while attempting to roll back the transaction.")
                End If
            End Try

            Console.WriteLine("An exception of type " & e.GetType().ToString() & _
              "was encountered while inserting the data.")
            Console.WriteLine("Neither record was written to database.")
        End Try
    End Using
End Sub

注解

此命令映射到 BEGIN TRANSACTION 的SQL Server实现。

必须使用或Rollback方法显式提交或回滚事务Commit。 若要确保SQL Server事务管理模型的 .NET Framework Data Provider能够正确执行，请避免使用其他事务管理模型，例如SQL Server提供的模型。

注释

提交或回滚事务后，事务的隔离级别将保留为处于自动提交模式的所有后续命令（SQL Server默认值）。 这可能会导致意外结果，例如 REPEATABLE READ 的隔离级别持久化并将其他用户锁定在行之外。 若要将隔离级别重置为默认值（READ COMMITTED），请执行 Transact-SQL SET TRANSACTION ISOLATION LEVEL READ COMMITTED 语句，或调用紧跟 SqlTransaction.Commit 后跟 SqlConnection.BeginTransaction。 有关SQL Server隔离级别的详细信息，请参阅 Transaction 隔离级别。

有关SQL Server事务的详细信息，请参阅 Transactions （Transact-SQL）。

注意

当查询返回大量数据并调用 BeginTransaction 时，会引发 SqlException，因为使用 MARS 时SQL Server不允许并行事务。 若要避免此问题，请在打开任何读取器之前始终将事务与命令、连接或两者相关联。

另请参阅

• Transactions （ADO.NET）
• 连接到数据源（ADO.NET）
• 使用适用于 SQL Server 的 .NET Framework 数据提供程序
• ADO.NET 概述

BeginTransaction(String)

使用指定的事务名称启动数据库事务。

public:
 System::Data::SqlClient::SqlTransaction ^ BeginTransaction(System::String ^ transactionName);

public System.Data.SqlClient.SqlTransaction BeginTransaction(string transactionName);

override this.BeginTransaction : string -> System.Data.SqlClient.SqlTransaction

Public Function BeginTransaction (transactionName As String) As SqlTransaction

参数

transactionName

String

事务的名称。

返回

SqlTransaction

表示新事务的对象。

例外

SqlException

使用多个活动结果集（MARS）时，不允许并行事务。

InvalidOperationException

不支持并行事务。

示例

以下示例创建一个 SqlConnection 和一个 SqlTransaction。 它还演示了如何使用 BeginTransaction、a Commit和 Rollback 方法。

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("SampleTransaction");
            }
            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);
            }
        }
    }
}

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

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

            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

注解

此命令映射到 BEGIN TRANSACTION 的SQL Server实现。

参数的 transactionName 长度不得超过 32 个字符;否则将引发异常。

参数中的transactionName值可以在以后调用Rollback方法时和方法的参数Save中使用savePoint。

必须使用或Rollback方法显式提交或回滚事务Commit。 若要确保SQL Server事务管理模型的 .NET Framework Data Provider能够正确执行，请避免使用其他事务管理模型，例如SQL Server提供的模型。

有关SQL Server事务的详细信息，请参阅 Transactions （Transact-SQL）。

注意

当查询返回大量数据并调用 BeginTransaction 时，会引发 SqlException，因为使用 MARS 时SQL Server不允许并行事务。 若要避免此问题，请在打开任何读取器之前始终将事务与命令、连接或两者相关联。

另请参阅

• Transactions （ADO.NET）
• 连接到数据源（ADO.NET）
• 使用适用于 SQL Server 的 .NET Framework 数据提供程序
• ADO.NET 概述

BeginTransaction(IsolationLevel, String)

使用指定的隔离级别和事务名称启动数据库事务。

public:
 System::Data::SqlClient::SqlTransaction ^ BeginTransaction(System::Data::IsolationLevel iso, System::String ^ transactionName);

public System.Data.SqlClient.SqlTransaction BeginTransaction(System.Data.IsolationLevel iso, string transactionName);

override this.BeginTransaction : System.Data.IsolationLevel * string -> System.Data.SqlClient.SqlTransaction

Public Function BeginTransaction (iso As IsolationLevel, transactionName As String) As SqlTransaction

参数

iso

IsolationLevel

事务应在其下运行的隔离级别。

transactionName

String

事务的名称。

返回

SqlTransaction

表示新事务的对象。

例外

SqlException

使用多个活动结果集（MARS）时，不允许并行事务。

InvalidOperationException

不支持并行事务。

示例

以下示例创建一个 SqlConnection 和一个 SqlTransaction。 它还演示了如何使用 BeginTransaction、a Commit和 Rollback 方法。

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("SampleTransaction");
            }
            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.");
        }
    }
}

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( _
          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 e As Exception
            Try
                transaction.Rollback("SampleTransaction")
            Catch ex As SqlException
                If Not transaction.Connection Is Nothing Then
                    Console.WriteLine("An exception of type " & ex.GetType().ToString() & _
                      " was encountered while attempting to roll back the transaction.")
                End If
            End Try

            Console.WriteLine("An exception of type " & e.GetType().ToString() & _
              "was encountered while inserting the data.")
            Console.WriteLine("Neither record was written to database.")
        End Try
    End Using
End Sub

注解

此命令映射到 BEGIN TRANSACTION 的SQL Server实现。

参数中的transactionName值可以在以后调用Rollback方法时和方法的参数Save中使用savePoint。

必须使用或Rollback方法显式提交或回滚事务Commit。 为了确保SQL Server事务管理模型能够正确执行，请避免使用其他事务管理模型，例如SQL Server提供的模型。

注释

提交或回滚事务后，事务的隔离级别将保留为处于自动提交模式的所有后续命令（SQL Server默认值）。 这可能会导致意外结果，例如 REPEATABLE READ 的隔离级别持久化并将其他用户锁定在行之外。 若要将隔离级别重置为默认值（READ COMMITTED），请执行 Transact-SQL SET TRANSACTION ISOLATION LEVEL READ COMMITTED 语句，或调用紧跟 SqlTransaction.Commit 后跟 SqlConnection.BeginTransaction。 有关SQL Server隔离级别的详细信息，请参阅 Transaction 隔离级别。

有关SQL Server事务的详细信息，请参阅 Transactions （Transact-SQL）。

注意

当查询返回大量数据并调用 BeginTransaction 时，会引发 SqlException，因为使用 MARS 时SQL Server不允许并行事务。 若要避免此问题，请在打开任何读取器之前始终将事务与命令、连接或两者相关联。

另请参阅

• Transactions （ADO.NET）
• 连接到数据源（ADO.NET）
• 使用适用于 SQL Server 的 .NET Framework 数据提供程序
• ADO.NET 概述