cmdDebit.Transaction = trans;
try { cmdCredit.ExecuteNonQuery();
cmdDebit.ExecuteNonQuery(); // Both commands (credit and debit) were successful trans.Commit(); }
catch( Exception ex ) {
// transaction failed trans.Rollback();
// log exception details . . .
throw ex; } } } }
如何使用 Transact-SQL 执行事务处理
以下存储过程阐明了如何在 Transact-SQL 存储过程内部执行事务性资金转帐操作。
CREATE PROCEDURE MoneyTransfer
@FromAccount char(20),
@ToAccount char(20),
@Amount money AS BEGIN TRANSACTION -- PERFORM DEBIT OperaTION UPDATE Accounts SET Balance = Balance -
@Amount WHERE AccountNumber = @FromAccount IF RowCount = 0 BEGIN RAISERROR('Invalid From Account Number', 11, 1)
GOTO ABORT END DECLARE
@Balance money SELECT @Balance = Balance FROM ACCOUNTS
WHERE AccountNumber = @FromAccount IF @BALANCE
0 BEGIN RAISERROR('Insufficient funds', 11, 1)
GOTO ABORT END -- PERFORM CREDIT OPERATION UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountNumber = @ToAccount
IF RowCount = 0 BEGIN RAISERROR('Invalid To Account Number', 11, 1) GOTO ABORT END COMMIT TRANSACTION RETURN 0 ABORT: ROLLBACK TRANSACTION GO
该存储过程使用 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 operation Debit debit = new Debit();
debit.DebitAccount( fromAccount, amount );
// Perform the credit operation Credit credit = new Credit();
credit.CreditAccount( toAccount, amount ); }
catch( SqlException sqlex ) {
// Handle and log exception details
// Wrap and propagate the exception throw new TransferException( "Transfer Failure", sqlex ); } } }
[Transaction(TransactionOption.Required)]
public class Credit : ServicedComponent { [AutoComplete]
public void CreditAccount( string account, decimal amount ) { try {
using( 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 ));
conn.Open();
cmd.ExecuteNonQuery(); } } }
catch( SqlException sqlex ){
// Log exception details here throw;
// Propagate exception } } [Transaction(TransactionOption.Required)]
public class Debit : ServicedComponent { public void DebitAccount( string account, decimal amount ) {
try { using( 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 )); conn.Open(); cmd.ExecuteNonQuery(); } }
catch (SqlException sqlex) {
// Log exception details here throw;
// Propagate exception back to caller } } }
-
4/4 首页 上一页 2 3 4 |
