I'm running into two different problems when using cursors.
First, the sample in the documentation for PgSqlCursor.GetDataReader shows a function which has a named output parameter, p, to let the cursor out of the function. The parameter collection for the command that calls the function is filled in automatically using ParameterCheck.
Now, I wish I didn't have to do that: first, I would really dislike having to name the output parameter for the cursor, I would like to return it as the single return value; second, I would prefer to avoid ParameterCheck (I understand it will cause an additional round trip to the server).
However, I don't seem to be able to use PgSqlCursor this way. Here's how I'm doing it:
Code: Select all
PgSqlCommand comm = new PgSqlCommand("myfunction", conn);
comm.CommandType = CommandType.StoredProcedure;
//comm.ParameterCheck = true; // doesn't work with ParameterCheck either
// Add parameters.
comm.Parameters.Add(new PgSqlParameter("a", PgSqlType.Int));
comm.Parameters.Add(new PgSqlParameter("b", PgSqlType.Int));
comm.Parameters.Add(new PgSqlParameter("return_value", PgSqlType.VarChar)).Direction = ParameterDirection.Output; // doesn't work with ReturnValue either
comm.Parameters["a"].Value = 10;
comm.Parameters["b"].Value = 3;
comm.ExecuteNonQuery();
PgSqlCursor cursor = comm.Parameters["return_value"].PgSqlValue as PgSqlCursor;
using (PgSqlDataReader rd = cursor.GetDataReader()) {
while (rd.Read())
Console.WriteLine(rd.GetValue(0));
}
What am I doing wrong ?
Do I really need to resort to naming the output parameter for the cursor and using ParameterCheck ?
As to the second problem: when the code is exactly like the sample in the documentation, I find that cursor.GetDataReader() actually fetches the entire dataset from the server (GetDataReader takes a lot of time, whereas the while loop does not involve communication).
Now, this kind of beats the purpose of using a cursor, doesn't it?
Can I work around this behaviour?
I already tried something called FetchSize but seems not to be related.
Please help,