学步园

CREATE Procedure DebitAccount
(
    @AccountID    int,
    @Amount    money
)
AS

INSERT INTO 
    Debits ([AccountID],[Amount])
VALUES
    (@AccountID,@Amount)


GO

 

CREATE Procedure CreditAccount
(
    @AccountID    int,
    @Amount        money
)
AS

INSERT INTO 
    Credits ([AccountID],[Amount])
VALUES
    (@AccountID,@Amount)


GO

  public bool Transfer(int transactionAmount, int sourceAccount, int destinationAccount)
        {
            bool result = false;
            
            // Create the Database object, using the default database service. The
            // default database service is determined through configuration.
            Database db = DatabaseFactory.CreateDatabase();

            // Two operations, one to credit an account, and one to debit another
            // account.
            string sqlCommand = "CreditAccount";
            DBCommandWrapper creditCommandWrapper = db.GetStoredProcCommandWrapper(sqlCommand);

            creditCommandWrapper.AddInParameter("@AccountID", DbType.Int32, sourceAccount);
            creditCommandWrapper.AddInParameter("@Amount", DbType.Int32, transactionAmount);

            sqlCommand = "DebitAccount";
            DBCommandWrapper debitCommandWrapper = db.GetStoredProcCommandWrapper(sqlCommand);

            debitCommandWrapper.AddInParameter("@AccountID", DbType.Int32, destinationAccount);
            debitCommandWrapper.AddInParameter("@Amount", DbType.Int32, transactionAmount);

            using (IDbConnection connection = db.GetConnection())
            {
                connection.Open();
                IDbTransaction transaction = connection.BeginTransaction();

                try
                {
                    // Credit the first account
                    db.ExecuteNonQuery(creditCommandWrapper, transaction);
                    // Debit the second account
                    db.ExecuteNonQuery(debitCommandWrapper, transaction);

                    // Commit the transaction
                    transaction.Commit();
                    
                    result = true;
                }
                catch
                {
                    // Rollback transaction 
                    transaction.Rollback();
                }
                connection.Close();
                
                return result;
            }