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

i have looked materials in www.npgsql.org, couldn't find how solve 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 returns single result set (cursor)     create or replace function show_cities() returns refcursor $$     declare       ref refcursor;     begin       open ref select city, state 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" , it's cursor fetched not data, there way convert data austin, houston, los angeles... ?

there posts on internet this, i'm not sure i'm doing wrong.

• npgsql : ver3.0.3
• c# : vs2012

(added) have found happening @ npgsql ver3.x, while in ver2.x working fine code. there change in usage fetching cursor ?

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

npgsql 2.x had feature whereby automatically "dereferenced" cursors returned functions. feature dropped npgsql 3.0; mentioned in our migration nodes 3.0, , discussion in this issue. since cursor returned , isn't dereferenced, npgsql returns cursor name (unnamed portal 1); can fetch results query sending fetch etc.

however, mentioned, wrapping single select in function doesn't make sense. if need write function returns single resultset, make return setof or table instead of cursor: create function ... returns table (column_name column_type [, ...]). apart being simpler , cleaner, more efficient, query results returned directly (dereferencing cursor involves database roundtrip).

see postgresql docs more info on how define function returning table.