總結：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

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

From:http://tw.wingwit.com/Article/program/net/201311/15086.html