Npgsql passing an array of parameters

Question

I am new in using Npgsql and I tried to make an helper in my asp.net project so that I can call it conveniently in my controllers method.

npgsqlqueryhelper

  public DataSet ExecuteQueryWithParams(string commandText, params NpgsqlParameter[] parameters)
  {
     using (var connection = npgsqlcon.GetnpgsqlConnection())
     using (NpgsqlCommand command = new NpgsqlCommand(commandText, connection))
     {
          DataSet ds = new DataSet();
          command.Parameters.AddRange(parameters);
          command.CommandTimeout = 5000;
          NpgsqlDataAdapter da = new NpgsqlDataAdapter(command);
          da.Fill(ds);
          connection.Close();
          return ds;
     }
  }

My Controller Method

   List<rollingPAR> rollingparlist = new List<rollingPAR>();
   npgsqlhelper = new npgsqlQueryHelper();
   NpgsqlParameter[] parameterList = {
                new NpgsqlParameter("@lid", r.lid),
                new NpgsqlParameter("@posting_date", r.date_end)
   };
   var table = npgsqlhelper.ExecuteQueryWithParams("SELECT ln.get_payment_status()", parameterList).Tables[0];
   rollingparlist = table.AsEnumerable().Select(row => new rollingPAR
    {
        get_payment_status = row.Field<int>("get_payment_status")
    }).ToList();

As I tried to run my program, I always encountered an error saying that function ln.get_payment_status() does not exist but when I tried to supply the parameters directly on the query

(e.g var table = npgsqlhelper.ExecuteQueryWithParams("SELECT ln.get_payment_status(1231,'06-18-2019')", parameterList).Tables[0];)

It gives me the data that I need. I don't know what is my mistake and I'm stuck here since yesterday. Can anyone help me with this? TIA

Answer 1

The parameter place holders are not automatically included in the function call. Try adding them:

var table = npgsqlhelper.ExecuteQueryWithParams("SELECT ln.get_payment_status(@lid,@posting_date)", parameterList).Tables[0];