Using executeScalar() in place of executeReader()RSS

• • Reply
  tarun02

  Member

  33 Points

  153 Posts

  Using executeScalar() in place of executeReader()

  Jun 11, 2017 07:08 PM|tarun02|LINK

  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.

• • Reply
  mgebhard

  All-Star

  35511 Points

  14110 Posts

  Re: Using executeScalar() in place of executeReader()

  Jun 11, 2017 07:19 PM|mgebhard|LINK

  tarun02

  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?  

• • Reply
  tarun02

  Member

  33 Points

  153 Posts

  Re: Using executeScalar() in place of executeReader()

  Jun 11, 2017 07:24 PM|tarun02|LINK

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

• • Reply
  mbanavige

  All-Star

  157124 Points

  13132 Posts

  ASPInsiders

  Moderator

  Re: Using executeScalar() in place of executeReader()

  Jun 11, 2017 07:48 PM|mbanavige|LINK

  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()

  Mike Banavige