SqlConnection.BeginTransaction 方法

參考

定義

命名空間:: System.Data.SqlClient

組件:: System.Data.SqlClient.dll

組件:: System.Data.dll

套件:: System.Data.SqlClient v4.8.6

重要

部分資訊涉及發行前產品，在發行之前可能會有大幅修改。 Microsoft 對此處提供的資訊，不做任何明確或隱含的瑕疵擔保。

開始資料庫交易。

多載

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

使用 Multiple Active Result Sets (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 實作。

您必須使用 Commit 或 Rollback 方法明確認可或復原交易。若要確定 .NET Framework SQL Server 事務管理模型的數據提供者會正確執行，請避免使用其他交易管理模型，例如 SQL Server 所提供的模型。

注意

如果您未指定隔離等級，則會使用預設隔離等級。若要使用 BeginTransaction 方法指定隔離等級，請使用採用 iso 參數 () BeginTransaction 的多載。交易完成之後，交易所設定的隔離等級會持續存在，直到關閉或處置連接為止。在未啟用快照集隔離等級的資料庫中，將隔離等級設定為快照集，並不會擲回例外狀況。交易會使用預設隔離等級完成。

警告

如果交易已啟動，且伺服器上發生層級 16 或更高版本的錯誤，則除非叫用方法，否則 Read 不會回復交易。 ExecuteReader 上不會擲回任何例外狀況。

警告

當您的查詢傳回大量數據和呼叫 BeginTransaction時，會擲回，SqlException因為使用MARS時 SQL Server 不允許平行交易。若要避免這個問題，請在開啟任何讀取器之前，一律將交易與命令、連接或兩者產生關聯。

如需 SQL Server 交易的詳細資訊，請參閱 Transact-SQL (交易) 。

另請參閱

交易和並行存取
在 ADO.NET 中聯機到數據源
SQL Server and 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

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

Public Function BeginTransaction (iso As IsolationLevel) As SqlTransaction

參數

iso: IsolationLevel

應該在其下執行異動的隔離等級。

傳回

SqlTransaction

代表新異動的物件。

例外狀況

SqlException

使用 Multiple Active Result Sets (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 實作。

注意

認可或回復交易之後，交易的隔離等級會針對處於自動認可模式的所有後續命令保存 (SQL Server 預設) 。這會產生非預期的結果，例如 REPEATABLE READ 的隔離等級，並鎖定其他使用者離開數據列。若要將隔離等級重設為預設 (READ COMMITTED) ，請執行 Transact-SQL SET TRANSACTION ISOLATION LEVEL READ COMMITTED 語句，或立即呼叫 SqlConnection.BeginTransaction 後面 SqlTransaction.Commit接著。如需 SQL Server 隔離等級的詳細資訊，請參閱交易隔離等級。

如需 SQL Server 交易的詳細資訊，請參閱 Transact-SQL (交易) 。

警告

另請參閱

交易 (ADO.NET)
連接資料來源 (ADO.NET)
使用 .NET Framework Data Provider for SQL Server
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

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

Public Function BeginTransaction (transactionName As String) As SqlTransaction

參數

transactionName: String

交易的名稱。

傳回

SqlTransaction

代表新異動的物件。

例外狀況

SqlException

使用 Multiple Active Result Sets (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 方法的 savePoint 和參數中 Save 。

如需 SQL Server 交易的詳細資訊，請參閱 Transact-SQL (交易) 。

警告

另請參閱

交易 (ADO.NET)
連接資料來源 (ADO.NET)
使用 .NET Framework Data Provider for SQL Server
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

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

使用 Multiple Active Result Sets (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 方法的 savePoint 和參數中 Save 。

您必須使用 Commit 或 Rollback 方法明確認可或復原交易。若要確定 SQL Server 事務管理模型正確執行，請避免使用其他交易管理模型，例如 SQL Server 所提供的模型。

注意

如需 SQL Server 交易的詳細資訊，請參閱 Transact-SQL (交易) 。

警告

另請參閱

交易 (ADO.NET)
連接資料來源 (ADO.NET)
使用 .NET Framework Data Provider for SQL Server
ADO.NET 概觀 \(部分機器翻譯\)

適用於

SqlConnection.BeginTransaction 方法

定義

多載

BeginTransaction()

傳回

例外狀況

範例

備註

另請參閱

適用於

BeginTransaction(IsolationLevel)

參數

傳回

例外狀況

範例

備註

另請參閱

適用於

BeginTransaction(String)

參數

傳回

例外狀況

範例

備註

另請參閱

適用於

BeginTransaction(IsolationLevel, String)

參數

傳回

例外狀況

範例

備註

另請參閱

適用於

意見反應

其他資源