使用ExecuteScalar()代替ExecuteReader()

Question

我正在学习Web，并在做一个小项目(我知道这个问题与Web无关，只是为了达到目的)。因此，我有一个名为get_balance(string accountNumber)的函数，它返回余额：

-- Function: deposit.get_balance(character varying)
-- DROP FUNCTION deposit.get_balance(character varying);

CREATE OR REPLACE FUNCTION deposit.get_balance
     (deposit_account_number_ character varying)
RETURNS money AS
$BODY$
DECLARE _account_number_id bigint;
BEGIN

SELECT account_number_id INTO _account_number_id FROM 
deposit.account_holders WHERE account_number = $1;
RETURN(
SELECT 
SUM(COALESCE(credit,'0')) - SUM(COALESCE(debit, '0'))
FROM deposit.transaction_view
WHERE account_number_id = _account_number_id
);
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION deposit.get_balance(character varying)
OWNER TO postgres;

因为这是一个Web项目，所以我有一个AccountsController

[HttpPost]
[ActionName("balance")]
public IHttpActionResult GetBalance([FromBody]AccountBalance account)
{
    account = BusinessLayer.Api.AccountHolderApi.GetBalance(account.AccountNumber);
    return Ok(account);
}

业务层：

public static AccountBalance GetBalance(string AccountNumber)
{
    return DatabaseLayer.Api.AccountHolderApi.GetBalance(AccountNumber);
}

Db层：

public static AccountBalance GetBalance(string AccountNumber)
{
    AccountBalance result = null;

    using (var conn = new NpgsqlConnection("Server=localhost;UserId = postgres; " + "Password = pes; Database = pmc; "))
    {
        conn.Open();

        using (var command = new NpgsqlCommand("SELECT * FROM deposit.get_balance(@AccountNumber);", conn))
        {
            command.Parameters.AddWithValue("@AccountNumber", AccountNumber);

            using (var dr = command.ExecuteReader())
            {
                if(dr.HasRows && dr.Read())
                {
                    result = new AccountBalance
                    {
                        AccountNumber = AccountNumber,
                        Balance = dr["get_balance"].ToString(),
                    };
                }
            }
        }

        return result;
    }
}

从现在起，一切正常，我得到了预期的结果。

{
    "accountNumber": "RD-0000050",
    "balance": "1100"
}

关于ExecuteScalar，我学到的是，它只返回查询第一行第一列的值。我的查询只返回一个列，并且我尝试使用ExecuteScalar进行一些tuts，但是遗憾的是，没有做到这一点。如何在这个场景中使用ExecuteScalar？任何详细的链接，解释都是有帮助的。

Answer 1

下面是如何使用ExecuteScalar而不是ExecuteReader

using (var command = new NpgsqlCommand("SELECT get_balance FROM deposit.get_balance(@AccountNumber);", conn)) {
    command.Parameters.AddWithValue("@AccountNumber", AccountNumber);
    var balanceObj = command.ExecuteScalar();
    string balance = balanceObj != DBNull.Value ? balanceObj.ToString() : "<EMPTY>";
    return new AccountBalance {
        AccountNumber
    ,   Balance = balance
    };
}
return null;