42809 Error On Executing PostgreSQL Stored Procedure From Asp.Net C# Application

Question

I am using PostgreSQL pgadmin4 (4.16v) with ASP.NET application. I have created a procedure as defined below:

CREATE OR REPLACE PROCEDURE public.usp_bind(
    )
LANGUAGE 'plpgsql'

AS $BODY$
BEGIN
      select district_id,district_name from district_master order by district_name;
END;
$BODY$;

From asp.net application I have called above procedure, code as below:

NpgsqlConnection conn = new NpgsqlConnection();
        NpgsqlDataAdapter da = new NpgsqlDataAdapter();
        NpgsqlCommand cmd = new NpgsqlCommand();
        DataSet ds = new DataSet();
        public string dbconstr = dbConnectstr.Connectionstring();

        public DataSet getDatafunction(string procedure_, [Optional] string flag_)
        {
            using (conn = new NpgsqlConnection(dbconstr))
            {
                //conn.Open();
                using (da = new NpgsqlDataAdapter())
                {

                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    da.SelectCommand.CommandText = "CALL usp_bind";
                    da.SelectCommand.Connection = conn;


                    using (ds = new DataSet())
                    {
                        da.Fill(ds);
                    }
                }
                //conn.Close();
            }
            return ds;
        }

It's giving me an error as - 42809: 'usp_bind' is a procedure.

I would have called it using a CALL method too but did't worked. What is the exact way to call a procedure from ASP.NET application?

Answer 1

Don't set CommandType.StoredProcedure on your command.

Unfortunately, stored procedures are new, and CommandType.StoredProcedure was already used to invoke functions, and changing that would be a major breaking change at this point.