SqlConnection.BeginTransaction 方法

定義

命名空間:: System.Data.SqlClient

組件:: System.Data.dll

重要

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

啟動資料庫交易。

多載

名稱	Description
BeginTransaction()	啟動資料庫交易。
BeginTransaction(IsolationLevel)	啟動指定的隔離層級資料庫交易。
BeginTransaction(String)	啟動一個以指定交易名稱的資料庫交易。
BeginTransaction(IsolationLevel, String)	啟動一個指定的隔離層級和交易名稱的資料庫交易。

BeginTransaction()

啟動資料庫交易。

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

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

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

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

Public Function BeginTransaction () As SqlTransaction

傳回

SqlTransaction

一個代表新交易的物件。

例外狀況

SqlException

使用多重主動結果集（MARS）時不允許平行交易。

InvalidOperationException

不支援平行交易。

範例

以下範例會產生 a 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

備註

此指令對應至 SQL Server 的 BEGIN TRANSACTION 實作。

你必須明確使用 Commit or Rollback 方法來提交或回滾交易。為確保 .NET Framework Data Provider for SQL Server 的交易管理模型能正常運作，請避免使用其他交易管理模型，例如 SQL Server 所提供的。

Note

若未指定隔離等級，則使用預設隔離等級。若要用方法 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);

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

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

Public Function BeginTransaction (iso As IsolationLevel) As SqlTransaction

參數

iso: IsolationLevel

交易應在的隔離層級。

傳回

SqlTransaction

一個代表新交易的物件。

例外狀況

SqlException

使用多重主動結果集（MARS）時不允許平行交易。

InvalidOperationException

不支援平行交易。

範例

以下範例會產生 a 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

備註

此指令對應至 SQL Server 的 BEGIN TRANSACTION 實作。

Note

交易提交或回滾後，該交易的隔離層級會持續適用於所有處於自動提交模式（SQL Server 預設）的後續指令。這可能導致意想不到的結果，例如可重複讀取的隔離層級持續存在，導致其他使用者無法進入該列。若要將隔離層級重設為預設值（READ COMMITTED），請執行 Transact-SQL SET TRANSACTION ISOLATION LEVEL READ COMMITTED（已提交）語句，或呼叫 SqlConnection.BeginTransaction，接著立即呼叫 SqlTransaction.Commit。欲了解更多隔離層級SQL Server，請參見交易隔離層。

欲了解更多SQL Server交易資訊，請參見交易（Transact-SQL）。

注意事項

另請參閱

適用於

BeginTransaction(String)

啟動一個以指定交易名稱的資料庫交易。

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

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

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

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

Public Function BeginTransaction (transactionName As String) As SqlTransaction

參數

transactionName: String

交易名稱。

傳回

SqlTransaction

一個代表新交易的物件。

例外狀況

SqlException

使用多重主動結果集（MARS）時不允許平行交易。

InvalidOperationException

不支援平行交易。

範例

以下範例會產生 a 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

備註

此指令對應至 SQL Server 的 BEGIN TRANSACTION 實作。

參數長度 transactionName 不得超過 32 個字元;否則會拋出例外。

參數中的transactionName值可用於後續對方法的呼叫Rollback Save及savePoint參數。

欲了解更多SQL Server交易資訊，請參見交易（Transact-SQL）。

注意事項

另請參閱

適用於

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

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

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

不支援平行交易。

範例

以下範例會產生 a 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

備註

此指令對應至 SQL Server 的 BEGIN TRANSACTION 實作。

參數中的transactionName值可用於後續對方法的呼叫Rollback Save及savePoint參數。

你必須明確使用 Commit or Rollback 方法來提交或回滾交易。為確保 SQL Server 的交易管理模型能正確運作，請避免使用其他交易管理模型，例如 SQL Server 所提供的。

Note

欲了解更多SQL Server交易資訊，請參見交易（Transact-SQL）。

注意事項

另請參閱

適用於

意見反應

此頁面對您有幫助嗎？