locked
Using executeScalar() in place of executeReader() RRS feed

  • Question

  • User411771660 posted

    I am learning webapi and working on a small project.(i know this question is not related to webapi, but just to get to the scene).So i have this function called get_balance(string accountNumber) which returns the balance.

    -- 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;

    Since this is a Webapi project i have the controller as AccountsController

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

    The business Layer

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

    And the Db Layer

    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;
        }
    }

    Everthing is working as of now and I am getting the expected result

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

    What I have learned about executeScalar is that it only returns the value from the first column of the first row of your query. And my query returns only one column and i did tried using executeScalar going through some tuts but alas, didn't get that working. How do I use executeScalar in this scenario? Any detailed link, explanation would be helpful.

    Sunday, June 11, 2017 7:08 PM

Answers

  • User-158764254 posted

    perhaps you took this line of code from the above sample and tried to convert it to use ExecuteScalar?

     using (var dr = command.ExecuteReader())

    ExecuteScalar returns an object which does not implement iDisposable so you cannot use its result as part of a using block.

    Try it like this when using ExecuteScalar:

     var myScalarObject = command.ExecuteScalar()

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, June 11, 2017 7:48 PM

All replies

  • User475983607 posted

    and i did tried using executeScalar going through some tuts but alas, didn't get that working.

    Can you clarify your statement?  What is not working?  Did you receive an error? Can you post the code that did not work?  Have you tried using the Visual Studio debugger to step through your code?  What does ExecuteScalar return?  

    Sunday, June 11, 2017 7:19 PM
  • User411771660 posted

    i am getting this sire- 'object': type used in a using statement must be implicitly convertible to 'System.IDisposable'

    Sunday, June 11, 2017 7:24 PM
  • User-158764254 posted

    perhaps you took this line of code from the above sample and tried to convert it to use ExecuteScalar?

     using (var dr = command.ExecuteReader())

    ExecuteScalar returns an object which does not implement iDisposable so you cannot use its result as part of a using block.

    Try it like this when using ExecuteScalar:

     var myScalarObject = command.ExecuteScalar()

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, June 11, 2017 7:48 PM