ahmed salah

ahmed salah

  • 1.1k
  • 654
  • 41.6k

How to get data from stored procedure created on postgree sql ?

Sep 16 2024 8:14 PM

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.");
                    }
                }

 


Answers (1)