Gets an
PgSqlDataReader for the specified cursor.
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