WEB开发工程师NET 总结：ADO.NET在开发中的部分使用方法和技巧[8]

总结：ADO.NET在开发中的部分使用方法和技巧[8]  

public void TransferMoney( string toAccount string fromAccount decimal amount ){using ( SqlConnection conn = new SqlConnection( server=(local);Integrated Security=SSPI;database=SimpleBank ) ){SqlCommand cmdCredit = new SqlCommand( Credit conn );cmdCredit CommandType = CommandType StoredProcedure;cmdCredit Parameters Add( new SqlParameter( @AccountNo toAccount) );cmdCredit Parameters Add( new SqlParameter( @Amount amount ));

SqlCommand cmdDebit = new SqlCommand( Debit conn );cmdDebit CommandType = CommandType StoredProcedure;cmdDebit Parameters Add( new SqlParameter( @AccountNo fromAccount) );cmdDebit Parameters Add( new SqlParameter( @Amount amount ));

conn Open();// Start a new transactionusing ( SqlTransaction trans = conn BeginTransaction() ){// Associate the o mand objects with the same transactioncmdCredit Transaction = trans;cmdDebit Transaction = trans;try{cmdCredit ExecuteNonQuery();cmdDebit ExecuteNonQuery();// Both mands (credit and debit) were successfultrans Commit();}catch( Exception ex ){// transaction failedtrans Rollback();// log exception details throw ex;}}}}

　　如何使用 Transact SQL 执行事务处理

　　以下存储过程阐明了如何在 Transact SQL 存储过程内部执行事务性资金转帐操作

CREATE PROCEDURE MoneyTransfer@FromAccount char( ) @ToAccount char( ) @Amount moneyASBEGIN TRANSACTION PERFORM DEBIT OPERATIONUPDATE AccountsSET Balance = Balance @AmountWHERE AccountNumber = @FromAccountIF @@RowCount = BEGINRAISERROR( Invalid From Account Number )GOTO ABORTENDDECLARE @Balance moneySELECT @Balance = Balance FROM ACCOUNTSWHERE AccountNumber = @FromAccountIF @BALANCE < BEGINRAISERROR( Insufficient funds )GOTO ABORTEND PERFORM CREDIT OPERATIONUPDATE Accounts SET Balance = Balance + @Amount WHERE AccountNumber = @ToAccountIF @@RowCount = BEGINRAISERROR( Invalid To Account Number )GOTO ABORTENDMIT TRANSACTIONRETURN ABORT:ROLLBACK TRANSACTIONGO