You can get cursor as a parameter value by Value property or by calling PgSqlDataReader.GetPgSqlCursor method of the PgSqlDataReader object.
You can use cursor to obtain PgSqlDataReader object and read data from it, or retrieve its name. The PgSqlDataReader can be retrieved only once.
Note that to retrieve a result set to which the cursor is pointing, you need a transaction to be opened on your connection. The reason is that the PostgreSQL portal which is created for the cursor lives inside a transaction only. If no transaction is opened explicitly, a single transaction implicitly starts for each statement being executed. In this case, the portal which was created on the stored procedure execution, will be unavailable after this operation is over.
CREATE OR REPLACE FUNCTION refcursorfunc(OUT p refcursor) AS $BODY$ DECLARE v_refcursor refcursor; BEGIN OPEN v_refcursor FOR SELECT deptno FROM dept; p := v_refcursor; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE;
PgSqlCommand cmd = new PgSqlCommand("refcursorfunc"); cmd.Connection = myConnection; cmd.CommandType = CommandType.StoredProcedure; cmd.ParameterCheck = true; myConnection.Open(); PgSqlTransaction t = myConnection.BeginTransaction(); try { cmd.ExecuteNonQuery(); PgSqlCursor cursor = cmd.Parameters["p"].PgSqlValue as PgSqlCursor; using (PgSqlDataReader rd = cursor.GetDataReader()) { while (rd.Read()) Console.WriteLine(rd.GetValue(0)); } } finally { t.Commit(); myConnection.Close(); }
Dim cmd As New PgSqlCommand("refcursorfunc") cmd.Connection = (Me.myConnection) cmd.CommandType = CommandType.StoredProcedure cmd.ParameterCheck = True Me.myConnection.Open() Dim t As PgSqlTransaction = Me.myConnection.BeginTransaction Try cmd.ExecuteNonQuery() Dim cursor As PgSqlCursor = TryCast(cmd.Parameters("p").PgSqlValue, PgSqlCursor) Using rd As PgSqlDataReader = cursor.GetDataReader Do While rd.Read Console.WriteLine(rd.GetValue(0)) Loop End Using Finally t.Commit() Me.myConnection.Close() End Try
System.Object
Devart.Data.PostgreSql.PgSqlCursor
Target Platforms: Windows 7, Windows Vista SP1 or later, Windows XP SP3, Windows Server 2008 (Server Core not supported), Windows Server 2008 R2 (Server Core supported with SP1 or later), Windows Server 2003 SP2