dotConnect for PostgreSQL Documentation
Devart.Data.PostgreSql Namespace / PgSqlCursor Class
Members Example

In This Topic
    PgSqlCursor Class
    In This Topic
    Represents a PostgreSQL REF CURSOR and value of cursor field.
    Syntax
    'Declaration
     
    Public Class PgSqlCursor 
    public class PgSqlCursor 
    Remarks

    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.

    Example
    This example demonstrates usage of PgSqlCursor class. To execute this code you have to create the following function on the server:
    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
    Inheritance Hierarchy

    System.Object
       Devart.Data.PostgreSql.PgSqlCursor

    Requirements

    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

    See Also