Row count from OracleDataReader

Question

Can any one tell me how to find row count from OracleDataReader in .net 2.0?

Answer 1

An OracleDataReaderobject represents a forward-only, read-only, in-memory result set. Unlike the DataSet, the OracleDataReaderobject stays connected and fetches one row at a time.

So, it does not know how many rows there will be. If you use the data adapter, then you will be able to get a row count since it fetches the rows.

In your case you have to fetch all the rows (if you need to fetch the data only) to get the row count:

OracleDataReader reader = cmd.ExecuteReader();
int rowCount = 0;
while (reader.Read())
{
    // your logic here
    rowCount++;
}

But if you don't need that data, it would be better to reformulate your stored procedure/query to return row count explicitly.

Answer 2

OracleDataReader objReader = cmd.ExecuteReader();
while(cmdReader.Read()) nRegisters++; objReader = cmd.ExecuteReader();

You should re-initialize that objReader because that "pointer" stays in the last position when you want to read it again... and there's no an option to return to the first position of the cursor.

if you need the number of register what you get you should realize a COUNT(*) in the select instead of counting every row, you must be practical about your code:

like if you need the numbers of citizens of a specific city:

BEGIN
OPEN REF_CUR FOR
    SELECT COUNT(*)        AS nRegisters,
           City            AS Var1,
           Country         AS Var2
      FROM Citizens
     WHERE City = 'City';

RETURN;
END;