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.

Bruno Ferreira · Accepted Answer · 2022-04-11 13:01:44Z

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();

answered Apr 11, 2022 at 13:01

Bruno Ferreira

4847 silver badges17 bronze badges

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

Collectives™ on Stack Overflow

C# Npgsql read cursor from procedure output parameter

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related