Search code examples
npgsql

NPGSQL CURSOR MOVE ALL getting affected rows (or row count)

I am trying to get the "rows affected" count from the following query using npgsql:

DECLARE cursor SCROLL CURSOR FOR SELECT * FROM public."table";
MOVE ALL IN cursor;

Using PgAdmin's SQL Editor, running this query gives: "Query returned successfully: 5736 rows affected, 31 msec execution time."

Using npgsql:

var transaction = conn.BeginTransaction();
NpgsqlCommand command = new NpgsqlCommand("DECLARE cursor SCROLL CURSOR FOR SELECT * FROM public.\"PARTIJ\"; MOVE ALL IN cursor", conn);
var count = command.ExecuteNonQuery();
// I valided here the cursor did move to end of result -- so cursor is working.
transaction.Commit();

I was expecting 5736, but count equals -1. Can I get the same rows affected count as PgAdmin does using npgsql?

Solution

  • This is probably happening because you're trying to get the affected row count of a multistatement command - your first statement creates the cursor, and the second one actually moves it (although I'm not sure what "rows affected" would mean when simply moving a cursor, as opposed to fetching). Try to send your statements in two different commands and get the affected row of the second.

    All that aside, any particular reason for using cursors here and not just doing SELECT COUNT(*) FROM public."table"?