I create stored procedure on postgree sql as below
CREATE OR REPLACE PROCEDURE app_sec."app_perm_sp_pkg$get_all_tenants"( IN p_tenant_cur refcursor, OUT p_status integer, OUT p_status_desc text)
my issue how to get data on c# when calling sp above it give me error {"42883: function app_perm_sp_pkg$get_all_tenants(refcursor, integer, text) does not exist"} on cmd.executenonquery()
public ResponseStatus GetTenantList() { ResponseStatus response = new ResponseStatus(); try { DataSet ds = new DataSet(); string cmdString = ""; OpenDB(); string sqlstr = @" DO $$ DECLARE p_tenant_cur REFCURSOR; p_status INTEGER; p_status_desc TEXT; BEGIN -- Call the stored procedure to initialize the cursor PERFORM app_perm_sp_pkg$get_all_tenants(p_tenant_cur, p_status, p_status_desc); END $$;"; using (var command = new NpgsqlCommand(sqlstr, postcon)) { command.ExecuteNonQuery(); // Execute the DO block to set up the cursor } // Now fetch the results from the cursor string fetchSql = "FETCH ALL FROM p_tenant_cur;"; using (var fetchCommand = new NpgsqlCommand(fetchSql, postcon)) { using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter(fetchCommand)) { adapter.TableMappings.Add("Table1", "p_tenant_cur"); adapter.Fill(ds); } } response.ds = ds; CloseDB(); } catch (Exception ex) { } return response; }
i check sp exist or not by using c# it is exist because result not return null
string checkProcSql = @" SELECT proname FROM pg_proc JOIN pg_namespace ON pg_proc.pronamespace = pg_namespace.oid WHERE proname = 'app_perm_sp_pkg$get_all_tenants' AND nspname = 'app_sec'; "; using (var checkCommand = new NpgsqlCommand(checkProcSql, postcon)) { var result = checkCommand.ExecuteScalar(); if (result == null) { throw new Exception("Stored procedure does not exist."); } }