C# OleDbDataReader

Question

如何改变这一点：

public void Charge(string bankaccountnumber, decimal moneyamount)
    {
       foreach (Bankaccount bankaccountInProcess in bankaccount)
        {
            if (bankaccountnumber == bankaccountInProcess.Bankaccountnumber)

                bankaccountInProcess.ChargeFromBankAccount(moneyamount);

        }

        return;
}

使用OleDbDataReader而不是那个foreach？

当被要求输入要从银行账户中提取的金额，然后从BalanceAmountAvailableForWithdrawal (以及WholeAccountBalance)中提取的金额时，在控制台中输入金额。但是access文件中没有moneyamount列，只有WholeAccountBalance和BalanceAmountAvailableForWithdrawal currency列。

以下是我如何在类的不同部分中更改它的示例：

public bool IdentifyUser(string bankAccountnumber, int passWord)
    {


        connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source= C:\\asd.accdb");
            connection.Open();


        OleDbCommand getOne = new OleDbCommand();
        getOne.Connection = connection;
        getOne.CommandText = "SELECT accounNum, pinCode FROM Account WHERE accountNum = '" +
             bankAccountnumber +"'";
        OleDbDataReader readOne = readOne.ExecuteReader();
        if (readOne.Read())
        {
            int GottenPassWord = (int)readOne["pinCode"];
            if (GottenPassWord == passWord)

            return true;
        }

        else
        {
            Console.WriteLine("Account was not found");            
        }
        connection.Close();
        return false;

    }

到目前为止，我的想法是：

public void Charge(string bankaccountnumber, decimal moneyamount)
    {

        connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source= C:\\asd.accdb");
            connection.Open();

        OleDbCommand getOne = new OleDbCommand();
        getOne.Connection = connection;
        getOne.CommandText = "SELECT accounNum, WholeAccountBalance, BalanceAmountAvailableForWithdrawal FROM Account WHERE accounNum = '" +
             bankaccountnumber + "'";
        OleDbDataReader readOne = readOne.ExecuteReader();
        if (readOne.Read())
        {
        // i dont know what to put here to make this do the same thing as the foreach did

        return;

        }

        else
        {
            Console.WriteLine("Charging did not work");

            return;

        }
}

Answer 1

如果您的Access表Account有3列：accounNum、WholeAccountBalance和BalanceAmountAvailableForWithdrawal，那么您基本上应该这样做(我也对其进行了一些更正和更新)。只要检查它，因为我没有测试它。

public void Charge(string bankaccountnumber, decimal moneyamount)
{
    using (OleDbConnection connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source= C:\\asd.accdb"))
    {
        connection.Open();

        using (OleDbCommand cmd = new OleDbCommand())
        {
            cmd.Connection = connection;
            cmd.CommandText = "SELECT accounNum, WholeAccountBalance, BalanceAmountAvailableForWithdrawal FROM Account WHERE accounNum = @accNum";
            cmd.Parameters.AddWithValue("@accNum", bankaccountnumber);

            using (OleDbDataReader reader = cmd.ExecuteReader())
            {
                if (reader.Read())  //assuming accounNum is an unique field (primary key)
                {
                    decimal balanceAmountAvailableForWithdrawal = Convert.ToDecimal(reader["BalanceAmountAvailableForWithdrawal"]);
                    if (balanceAmountAvailableForWithdrawal >= moneyamount)   //check if you can withdraw money
                    {
                        cmd.CommandText = @"UPDATE Account 
                                            SET WholeAccountBalance = WholeAccountBalance - @moneyamount, 
                                                BalanceAmountAvailableForWithdrawal = BalanceAmountAvailableForWithdrawal - @moneyamount 
                                            WHERE accounNum = @accNum";

                        cmd.Parameters.AddWithValue("@moneyamount", moneyamount);

                        int result = cmd.ExecuteNonQuery();

                        if (result == 1)
                            Console.WriteLine("Money withdrawn sucessfully.");
                        else
                            Console.WriteLine("Error while withdrawing, operation has failed.");
                    }
                    else
                    {
                        Console.WriteLine("Not enough money to withdrawn requested ammount.");
                    }
                }
                else
                {
                    Console.WriteLine("Charging did not work");
                }
            }
        }
    }
}