如何利用Transact-SQL执行事务

下列存储过程说明了如何在Transact-SQL过程内执行事务的支金转移操作。

CREATE PROCEDURE MoneyTransfer@FromAccount char(20),@ToAccount char(20),@Amount moneyASBEGIN TRANSACTION-- PERFORM DEBIT OPERATIONUPDATE AccountsSET Balance = Balance - @AmountWHERE AccountNumber = @FromAccountIF @@RowCount = 0BEGINRAISERROR('Invalid From Account Number', 11, 1)GOTO ABORTENDDECLARE @Balance moneySELECT @Balance = Balance FROM ACCOUNTSWHERE AccountNumber = @FromAccountIF @BALANCE < 0BEGINRAISERROR('Insufficient funds', 11, 1)GOTO ABORTEND-- PERFORM CREDIT OPERATIONUPDATE AccountsSET Balance = Balance + @AmountWHERE AccountNumber = @ToAccountIF @@RowCount = 0BEGINRAISERROR('Invalid To Account Number', 11, 1)GOTO ABORTENDCOMMIT TRANSACTIONRETURN 0ABORT:ROLLBACK TRANSACTIONGO

该存储过程使用BEGIN TRANSACTION， COMMIT TRANSACTION，和ROLLBACK TRANSACTION状态手工控制事务。

如何编码事务性的.NET类

下述例子是三种服务性的NET类，它们配置或用于自动事务。每个类都带有Transaction属性，它的值将决定是否启动新事务流或者对象是否共享即时调用程序的数据流。这些元素一起工作来执行银行支金转移。Transfer类配置有RequiresNew事务属性，而Debit和Credit类配置有Required属性。这样，在运行的时候三个对象共享同一个事务。

using System;using System.EnterpriseServices;[Transaction(TransactionOption.RequiresNew)]public class Transfer : ServicedComponent{[AutoComplete]public void Transfer( string toAccount,string fromAccount, decimal amount ){try{// Perform the debit operationDebit debit = new Debit();debit.DebitAccount( fromAccount, amount );// Perform the credit operationCredit credit = new Credit();credit.CreditAccount( toAccount, amount );}catch( SqlException sqlex ){// Handle and log exception details// Wrap and propagate the exceptionthrow new TransferException( "Transfer Failure", sqlex );}}}[Transaction(TransactionOption.Required)]public class Credit : ServicedComponent{[AutoComplete]public void CreditAccount( string account, decimal amount ){SqlConnection conn = new SqlConnection("Server=(local); Integrated Security=SSPI"; database="SimpleBank");SqlCommand cmd = new SqlCommand("Credit", conn );cmd.CommandType = CommandType.StoredProcedure;cmd.Parameters.Add( new SqlParameter("@AccountNo", account) );cmd.Parameters.Add( new SqlParameter("@Amount", amount ));try{conn.Open();cmd.ExecuteNonQuery();}catch (SqlException sqlex){// Log exception details herethrow; // Propagate exception}}}[Transaction(TransactionOption.Required)]public class Debit : ServicedComponent{public void DebitAccount( string account, decimal amount ){SqlConnection conn = new SqlConnection("Server=(local); Integrated Security=SSPI"; database="SimpleBank");SqlCommand cmd = new SqlCommand("Debit", conn );cmd.CommandType = CommandType.StoredProcedure;cmd.Parameters.Add( new SqlParameter("@AccountNo", account) );cmd.Parameters.Add( new SqlParameter("@Amount", amount ));try{conn.Open();cmd.ExecuteNonQuery();}catch (SqlException sqlex){// Log exception details herethrow; // Propagate exception back to caller}}}