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

JGH · Accepted Answer · 2019-11-27 12:38:40Z

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

answered Nov 27, 2019 at 12:38

JGH

18.2k5 gold badges40 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

pryxen · Accepted Answer · 2019-11-28 00:40:33Z

With the help of Sir @JGH, it turns out that my query is missing the parameter placeholders but after I edit it, I encountered an error regarding the datatype between the asp.net datetime and postgresql date so I added this code to remove the error.

parameterList[1].NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Date;

So here is now the new code:

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

Thank you sir @JGH

Collectives™ on Stack Overflow

Npgsql passing an array of parameters

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related