c# - how can I get cursor data with calling stored procedure in npgsql

Question

I have looked into materials in www.npgsql.org, but couldn't find how to solve my problem...

Table, PostgreSQL

[City], [State]
"Austin", "TX"
"Houston", "TX"
"Los Angeles", "CA"
"San Diego", "CA"
"San Fransisco";"CA"
"St.Louis", "MO"

Function (stored procedure), PostgreSQL

-- Procedure that returns a single result set (cursor) 
   CREATE OR REPLACE FUNCTION show_cities() RETURNS refcursor AS $$
    DECLARE
      ref refcursor;
    BEGIN
      OPEN ref FOR SELECT city, state FROM cities;
      RETURN ref;                                 
    END;
    $$ LANGUAGE plpgsql;

Code, C#

using (NpgsqlConnection conn = new NpgsqlConnection(ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString))
{
    conn.Open();
    using (NpgsqlTransaction tran = conn.BeginTransaction())
    {
        using (var command = new NpgsqlCommand("show_cities", conn))
        {
            command.Transaction = tran;
            command.CommandType = CommandType.StoredProcedure;
            NpgsqlDataReader dr = command.ExecuteReader();

            while (dr.Read())
                str += dr.GetValue(0);

            dr.Close();
        }
        tran.Commit();
    }
}

This returns "unnamed portal 1" and it's a cursor to be fetched not data, Is there any way to convert this to data like Austin, Houston, Los Angeles... ?

There are some posts over internet about this, but I'm not sure what I'm doing wrong.

• npgsql : ver3.0.3
• c# : vs2012

(added) I have found this is happening at npgsql ver3.x, while in ver2.x it is working fine with my code. Is there any change in usage for fetching cursor ?

(reference) http://www.sqlines.com/postgresql/npgsql_cs_result_sets

Answer 1

With Shay's help, I figured out how we can fetch cursor in v3.x after removal of "dereferenced" feature. I think there are not much of good example on this, I hope this might help people save time to search example.

You can do this in npgsql ver3.x

1. CommandType.StoredProcedure (cursor name not defined)

conn.Open();
NpgsqlTransaction tran = conn.BeginTransaction();

NpgsqlCommand command = new NpgsqlCommand("show_cities", conn);
command.CommandType = CommandType.StoredProcedure;
command.ExecuteNonQuery();

command.CommandText = "fetch all in "<unnamed portal 1>"";
command.CommandType = CommandType.Text;

NpgsqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
    // do what you want with data, convert this to json or...
    Console.WriteLine(dr[0]);
}
dr.Close();

tran.Commit();
conn.Close();

2. CommandType.StoredProcedure (cursor name defined)

conn.Open();
NpgsqlTransaction tran = conn.BeginTransaction();

NpgsqlCommand command = new NpgsqlCommand("select show_cities(@ref)", conn);
command.CommandType = CommandType.Text;
NpgsqlParameter p = new NpgsqlParameter();
p.ParameterName = "@ref";
p.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Refcursor;
p.Direction = ParameterDirection.InputOutput;
p.Value = "ref";
command.Parameters.Add(p);
command.ExecuteNonQuery();

command.CommandText = "fetch all in "ref"";
command.CommandType = CommandType.Text;

NpgsqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
    // do what you want with data, convert this to json or...
    Console.WriteLine(dr[0]);
}
dr.Close();

tran.Commit();
conn.Close();

3. CommandType.Text (cursor name defined)

conn.Open();
NpgsqlTransaction tran = conn.BeginTransaction();

NpgsqlCommand command = new NpgsqlCommand("select show_cities(@ref)", conn);
command.CommandType = CommandType.Text;
NpgsqlParameter p = new NpgsqlParameter();
p.ParameterName = "@ref";
p.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Refcursor;
p.Direction = ParameterDirection.InputOutput;
p.Value = "ref";
command.Parameters.Add(p);
command.ExecuteNonQuery();

command.CommandText = "fetch all in "ref"";
command.CommandType = CommandType.Text;

NpgsqlDataReader dr = command.ExecuteReader();
while (dr.Read())
{
    // do what you want with data, convert this to json or...
    Console.WriteLine(dr[0]);
}
dr.Close();

tran.Commit();
conn.Close();

(reference for v2.x) http://www.sqlines.com/postgresql/npgsql_cs_result_sets

This is example for easy understanding, so if you want your function to return single result set, then instead of returning cursor, please consider it to return table as @Shay suggested or create a view not a function as @CeOnSql suggested.

Thanks !