OracleDataReader returning only last row on pagination query

Question

I'm using the Oracle.ManagedDataAccess to return data from my database, and I really need to page the results because there are lots of registers in this table.

So I'm using the second answer from this post to paging, and it really works when I do run on an Oracle Client.

The Final query looks like this:

select *
from (
    select rownum as rn, a.*
    from (
            Select  u.*
            From users u
            order by u.user_code
         ) a
     )
where   rownum <= :myReturnSize
and     rn > (:myReturnPage-1) * :myReturnSize;

But when I call it from the .Net code below, it returns only the last register of the 100's I asked for.

OracleParameter[] parameters = new OracleParameter[]{
    new OracleParameter("myReturnPage", page), //1
    new OracleParameter("myReturnSize", size)  //100
};

List<User> usersList = new List<User>();

using (OracleConnection conn = new OracleConnection(connString))
{
    using (OracleCommand cmd = new OracleCommand(sbSelect.ToString(), conn))
    {
        conn.Open();
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.AddRange(parameters);

        using (OracleDataReader odr = cmd.ExecuteReader())
        {
            if (!odr.IsClosed && odr.HasRows)
            {
                while (odr.Read())
                {
                    User userToReturn = new User();
                    FillUserEntity(userToReturn, odr);
                    usersList.Add(userToReturn);
                }
            }
        }
    }
}

return usersList.AsQueryable();

Even more bizarre is that when I run this query without pagination in the same method it returns me all registers, more than 723,000.

Any help would be appreciated.

Thanks a lot.

score 3 · Accepted Answer · answered Jan 05 '17 at 11:56

3

By default the ODP.Net set the parameters by position and not by name. So you just need to invert the order when creating the OracleParameter's array, and also set the BindByName property to true, like this:

cmd.BindByName = true;

answered Jan 05 '17 at 11:56

Alan Brambila

46
2

score 1 · Answer 2 · answered Dec 28 '16 at 14:33

1

Oracle tends to prefer stored procedures over direct text (because reasons). I've had more than a few "it works in SQL Developer but not .Net!" situations that were solved by putting it all together in a stored proc within a package on the database side. That also decouples your query from your application, so if the query has to change you don't have to recompile the app. Your app then just makes the same call as before, but to the stored procedure, probably using an OracleDataAdapter.

answered Dec 28 '16 at 14:33

CDove

1,940
10
19

Thank you for your quick reply, but this is not my case, in relation to the procedure creation. If I need to change the query, I'll be through a publish process which makes no difference if I need to recompile the proc or the app. – Elek Guidolin Dec 28 '16 at 14:38
Besides, my other methods that are using parameters in text, are working like a charm, so I think that is not my problem. But thank you for your time anyway. – Elek Guidolin Dec 28 '16 at 14:52

score 1 · Answer 3 · answered Dec 28 '16 at 20:32

1

Can you confirm whether you query giving correct output from Oracle client.?

Problem is with where rownum <= :myReturnSize It will always return the value rownum = :myReturnSize

One possible solution can be

select * from ( select rownum as rnum, a.* from ( Select rownum as rn, u.* From users u order by u.user_code ) a ) where rnum <= :myReturnSize and rn > (:myReturnPage-1) * :myReturn.

answered Dec 28 '16 at 20:32

Subhroneel

112
3

My answer is not formated properly possibly because I am posting from mobile app. – Subhroneel Dec 28 '16 at 20:33
Thank you for your reply. Beginning with this principle, I figured out that for some crazy reason the Oracle.ManagedDataAccess.Client were changing the parameters. I figured, because when I asked, .Net only returned me the last register, and with your answer, returned the first one. So I changed the values and it worked. – Elek Guidolin Dec 29 '16 at 17:12

OracleDataReader returning only last row on pagination query

3 Answers3