[引]ASP.NET 中 事务处理(SqlTransaction)示例

下面的示例创建一个 SqlConnection 和一个 SqlTransaction。

此示例还演示如何使用 BeginTransaction、Commit 和 Rollback 等方法。

出现任何错误时事务都会回滚。

Try/Catch 错误处理用于处理尝试提交或回滚事务时的所有错误。

--------------

Visual Basic

--------------

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

mandText = _

"Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')"

command.ExecuteNonQuery()

mandText = _

"Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')"

command.ExecuteNonQuery()

' Attempt to commit the transaction.

mit()

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

---------

C#

---------

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

{

mandText =

"Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";

command.ExecuteNonQuery();

mandText =

"Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";

command.ExecuteNonQuery();

// Attempt to commit the transaction.

mit();

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

}

}

}

}