总结：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 );

cmdCreditCommandType = CommandTypeStoredProcedure;

cmdCreditParametersAdd( new SqlParameter(@AccountNo toAccount) );

cmdCreditParametersAdd( new SqlParameter(@Amount amount ));

SqlCommand cmdDebit = new SqlCommand(Debit conn );

cmdDebitCommandType = CommandTypeStoredProcedure;

cmdDebitParametersAdd( new SqlParameter(@AccountNo fromAccount) );

cmdDebitParametersAdd( new SqlParameter(@Amount amount ));

connOpen();

// Start a new transaction

using ( SqlTransaction trans = connBeginTransaction() )

{

// Associate the two command objects with the same transaction

cmdCreditTransaction = trans;

cmdDebitTransaction = trans;

try

{

cmdCreditExecuteNonQuery();

cmdDebitExecuteNonQuery();

// Both commands (credit and debit) were successful

transCommit();

}

catch( Exception ex )

{

// transaction failed

transRollback();

// log exception details

throw ex;

}

}

}

}

如何使用 TransactSQL 执行事务处理

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

CREATE PROCEDURE MoneyTransfer

@FromAccount char()

@ToAccount char()

@Amount money

AS

BEGIN TRANSACTION

PERFORM DEBIT OPERATION

UPDATE Accounts

SET Balance = Balance @Amount

WHERE AccountNumber = @FromAccount

IF @@RowCount =

BEGIN

RAISERROR(Invalid From Account Number )

GOTO ABORT

END

DECLARE @Balance money

SELECT @Balance = Balance FROM ACCOUNTS

WHERE AccountNumber = @FromAccount

IF @BALANCE <

BEGIN

RAISERROR(Insufficient funds )

GOTO ABORT

END

PERFORM CREDIT OPERATION

UPDATE Accounts

SET Balance = Balance + @Amount

WHERE AccountNumber = @ToAccount

IF @@RowCount =

BEGIN

RAISERROR(Invalid To Account Number )

GOTO ABORT

END

COMMIT TRANSACTION

RETURN

ABORT:

ROLLBACK TRANSACTION

GO

[] [] [] [] [] [] [] [] []