SqlConnection.BeginTransaction 方法

  • 参考

定义

命名空间:
System.Data.SqlClient
程序集:
System.Data.SqlClient.dll
程序集:
System.Data.dll
包:
System.Data.SqlClient v4.8.6

开始数据库事务。

重载

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
member this.BeginTransaction : unit -> System.Data.SqlClient.SqlTransaction
Public Function BeginTransaction () As SqlTransaction

返回

SqlTransaction

表示新事务的对象。

例外

SqlException

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

InvalidOperationException

不支持并行事务。

示例

以下示例创建 SqlConnection 和 SqlTransaction。 它还演示如何使用 BeginTransaction、 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数据提供程序正常运行，请避免使用其他事务管理模型，例如SQL Server提供的模型。

注意

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

注意

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

注意

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

有关SQL Server事务的详细信息，请参阅事务 (Transact-SQL) 。

另请参阅

适用于

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
member 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、 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数据提供程序正常运行，请避免使用其他事务管理模型，例如SQL Server提供的模型。

注意

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

有关SQL Server事务的详细信息，请参阅事务 (Transact-SQL) 。

注意

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

另请参阅

适用于

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
member this.BeginTransaction : string -> System.Data.SqlClient.SqlTransaction
Public Function BeginTransaction (transactionName As String) As SqlTransaction

参数

transactionName
String

事务的名称。

返回

SqlTransaction

表示新事务的对象。

例外

SqlException

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

InvalidOperationException

不支持并行事务。

示例

以下示例创建 SqlConnection 和 SqlTransaction。 它还演示如何使用 BeginTransaction、 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 和 方法的 参数的 savePointSave 调用。

必须使用 或 Rollback 方法显式提交或回滚事务Commit。 若要确保SQL Server事务管理模型的.NET Framework数据提供程序正常运行，请避免使用其他事务管理模型，例如SQL Server提供的模型。

有关SQL Server事务的详细信息，请参阅事务 (Transact-SQL) 。

注意

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

另请参阅

适用于

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
member 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、 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 和 方法的 参数的 savePointSave 调用。

必须使用 或 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隔离级别的详细信息，请参阅事务隔离级别。

有关SQL Server事务的详细信息，请参阅事务 (Transact-SQL) 。

注意

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

另请参阅

适用于