C# Npgsql read cursor from procedure output parameter

Question

I'm migration an application from a Oracle DB to a Postgres DB. There are many procedures implemented that returns via output parameter a RefCursor. Just like this:

        string schema = server.SERVER_SCHEMA;
        string connStr = modelUtils.GetRemoteConn(server, false);

        OracleConnection conn = GetConnection(connStr);
        OracleCommand cmd = GetCommand(conn);

        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = schema + ".ProcedureName";
        cmd.Parameters.Add("p_flow", OracleDbType.Varchar2, ParameterDirection.Input).Value = flowKey;
        OracleParameter outCursor = cmd.Parameters.Add("p_cursor", OracleDbType.RefCursor, ParameterDirection.Output);
        cmd.ExecuteNonQuery();
        OracleRefCursor dataCursor = (OracleRefCursor)outCursor.Value;

        OracleDataAdapter myAdapter = new OracleDataAdapter("", conn);
        myAdapter.Fill(tableData, dataCursor);

Please notice thant I've to grab the parameter outCursor, cast as OracleRefCursor and set it to DataTable named "tableData" via DataAdapter.

To do the same but using Npgsql this is my approach:

        string schema = server.SERVER_SCHEMA;
        string connStr = modelUtils.GetRemoteConn(server, false);

        NpgsqlConnection conn = GetConnection(connStr);
        NpgsqlCommand cmd = GetCommand(conn);

        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = schema + ".ProcedureName";
        cmd.Parameters.Add("p_flow", NpgsqlDbType.Varchar).Value = flowKey;
        NpgsqlParameter outCursor = cmd.Parameters.Add(new NpgsqlParameter("p_cursor", NpgsqlDbType.Refcursor) { Direction = ParameterDirection.Output });
        cmd.ExecuteNonQuery();
        var dataCursor = (Refcursor)outCursor.Value;

        NpgsqlDataAdapter myAdapter = new NpgsqlDataAdapter("", conn);
        myAdapter.Fill(tableData, dataCursor);

But unfortunately seems that there is no equivalent in Npgsql for Refcursor

Any ideias how can I get arround this?

Thank you.

Answer 1

To everyone who needs to do the same, I recommend reading this: https://stackoverflow.com/a/47970680/2229993

Nonetheless this is how I solved this issue:

NpgsqlConnection conn = GetConnection(connStr);
NpgsqlCommand cmd = new NpgsqlCommand("CALL show_citiesProc('op');FETCH ALL IN \"op\";", conn);
NpgsqlDataAdapter myAdapter = new NpgsqlDataAdapter(cmd);
myAdapter.Fill(tableData);
myAdapter.Dispose();