CODEWITHSUNDEEP
c#postgresqlnpgsql
OLDMONK
OLDMONK

Reputation: 382

How to pass the parameter to a postgre function and get data using ExecuteReader?

I am trying to retrieve all the columns from a table using ExecuteReader in C# application.Db is postgre.To test i created a console application following a tutorial which did showed how to query using a Function but not with passing parameters.The console application function for test

    static void Main(string[] args)
    {
        // Connect to a PostgreSQL database
        NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;User Id=postgres; " +
            "Password=pes;Database=pmc;");
        conn.Open();

        // Define a query
        NpgsqlCommand command = new NpgsqlCommand("SELECT * from audit.exception_gl_accounts()", conn);

        // Execute the query and obtain a result set
        NpgsqlDataReader dr = command.ExecuteReader();

        // Output rows
        while (dr.Read())
            Console.Write("{0}\t{1} \n", dr[0], dr[1]);

        conn.Close();
    }
}

Here in NpgsqlCommand i sent the query without parameter to the function audit.exception_gl_accounts and it worked well.Now How do i pass a parameter to a function like this

"SELECT * FROM sms.get_accounts_info(@AccountNumber);

I am trying to retrieve all 5 columns using this function and get those objects

    public static string GetAccountInfo(string accountNumber)
    {
        NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;User 
                            Id=postgres; " + "Password=pes;Database=pmc;");
        conn.Open();
        NpgsqlCommand command = new NpgsqlCommand("SELECT * FROM 
                           sms.get_accounts_info(@AccountNumber); ", conn);
        command.Parameters.AddWithValue("@AccountNumber", accountNumber);
        NpgsqlDataReader dr = command.ExecuteReader();
        while (dr.Read())
            Console.Write("{0}\t{1} \n", dr[0], dr[1]);
            return dr.ToString();
    }

Using the second sample code gives this error :

{ "accountNumber": "Npgsql.ForwardsOnlyDataReader", "balance": null, "interestRate": 0, "accountName": null, "accountType": null }

Any Help Appreciated.

DETAILS UPDATED

The Controller

[HttpPost]
[ActionName("info")]
public IHttpActionResult GetAccountInfo([FromBody]AccountInfo 
accountinfo)
 {
accountinfo.accountNumber = BusinessLayer.Api.AccountHolderApi.GetAccountInfo
          (accountinfo.accountNumber);
            return Ok(accountinfo);
 }

Account Info Class

public class AccountInfo
    {
      public string accountNumber { get; set; }
      public string balance { get; set; }
      public int interestRate { get; set; }
      public string accountName { get; set; }
      public string accountType { get; set; }
    }

URI

http://localhost:8080/v1/accounts/info

GetAccountsInfo

CREATE OR REPLACE FUNCTION sms.get_accounts_info(IN account_number_ character varying)
  RETURNS TABLE(account_number character varying, account_name text, product character varying, interest_rate numeric, balance money) AS
$BODY$
BEGIN
    RETURN QUERY(
        SELECT a.account_number,
        c.customer_name,
            p.deposit_product_name, 
            a.interest_rate::numeric, deposit.get_balance(account_number_)
        FROM deposit.account_holders a 
        JOIN core.customers_view  c ON a.customer_id = c.customer_id
        JOIN core.deposit_products p ON a.deposit_product_id = p.deposit_product_id
        WHERE a.account_number = $1
    );
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION sms.get_accounts_info(character varying)
  OWNER TO postgres;

Upvotes: 2

Views: 13074

Answers (1)

Cee McSharpface
Cee McSharpface

Reputation: 8726

Without entity framework, you need to write the code that reads the values from the datareader into an instance of your AccountInfo class:

public static AccountInfo GetAccountInfo(string accountNumber)
{
    AccountInfo result = null;
    using(var conn = new NpgsqlConnection("..."))
    {
        conn.Open();
        using(var command = new NpgsqlCommand("SELECT * FROM sms.get_accounts_info(@AccountNumber); ", conn))
        {
            command.Parameters.AddWithValue("@AccountNumber", accountNumber);
            using(var dr = command.ExecuteReader())
            {
                if(dr.HasRows && dr.Read())
                {
                    result = new AccountInfo { 
                        accountNumber = dr["accountNumber"].ToString(),
                        balance = dr["balance"].ToString(),
                        interestRate = Convert.ToInt32(dr["interestRate"]),
                        accountName = dr["accountName"].ToString()
                    };
                }
            }
        }
    }
    return result;
}

Note that the return type of the function has been changed to AccountInfo, previously string. Also, it is limited to reading just one record, If one call to sms.get_accounts_info could return more than one record, it is a different story. I just assumed that account_number is a primary key in the account_holders table.

Some details need your attention, for example balance is money in database, but string in the class. Also I did not know if and how product (database) and accountType (class) would correspond, so I omitted it.

Database connections, commands, and datareaders are IDisposable and should be wrapped in using blocks.

Upvotes: 5

Related Questions