ASP.NET MVC -Working with Oracle and Dapper, using pagination

Question

I had to change sql server for oracle.

So I redid the query.

My query/Method on sql server with dapper was like this and worked perfectly :

   public Pagination<User> ShowAllUsers(string name, int pageSize, int pageNumber)
        {
            var cn = DbContext.Database.Connection;

            var sql = @"SELECT * FROM USERS" +
                  "WHERE (@Name IS NULL OR Nome LIKE @Name+ '%') " +
                  "ORDER BY [Nome] " +
                  "OFFSET " + pageSize * (pageNumber - 1) + " ROWS " +
                  "FETCH NEXT " + pageSize + " ROWS ONLY " +
                  " " +
                  "SELECT COUNT(Id) FROM USERS" +
                  "WHERE (@Nome IS NULL OR Name LIKE @Name + '%') ";

            var multi = cn.QueryMultiple(sql, new { Name = name});
            var users= multi.Read<User>();
            var total = multi.Read<int>().FirstOrDefault();
        }

And next, is my query with Oracle/Method,

  public Pagination<Barco> ShowAllUsers(string name, int pageSize, int pageNumber)
        {
            var cn = DbContext.Database.Connection;

            var sql = @"SELECT * 
                        FROM (
                            SELECT ROWNUM rnum, b.* 
                                FROM (
                                SELECT * 
                                    FROM USERS
                                    WHERE (:Name IS NULL OR USERS.NAME LIKE :Name + '%')
                                ) b
                        ) WHERE RNUM between :pageSize * (:pageNumber - 1) + 1 and(:pageSize * :pageNumber) ";

            var multi = cn.QueryMultiple(sql, new { Name = name});
            var users = multi.Read<User>();
            var total = multi.Read<int>().FirstOrDefault();
         }

When changing the query to pl/sql format, I am getting the following error:

Invalid parameter binding Parameter Name: pageSize

How could I adjust this query to work with dapper?

And where did you pass pageSize (and pageNumber) to the query? new { Name = name} => new { Name = name, pageSize, pageNumber} — Selvin
– Selvin, Commented Dec 4, 2019 at 15:41
@Selvin Your observation is important, I forgot to observe the passage of the parameters. When modifying, I am getting the error from Dapper Reader 'Disposed Exception. "The reader has been disposed; this can happen after all data has been consumed Object name: 'Dapper.SqlMapper + GridReader''. " So I changed the variable "multi" to cn.Query, but now I have an error in the users and total variables. Any ideas? — Jhensen
– Jhensen, Commented Dec 4, 2019 at 18:02

nick gowdy · Accepted Answer · 2019-12-04 15:43:03Z

0

Could it be because you're not passing your pageSize as a parameter?

var multi = cn.QueryMultiple(sql, new { Name = name, PageSize = pageSize});

answered Dec 4, 2019 at 15:43

nick gowdy

6,54127 gold badges96 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Piotr L Over a year ago

pageNumber parameter is missing as well

nick gowdy Over a year ago

@PiotrŁazarczyk good spot. His original code for sql server is also missing those parameters.

Selvin Over a year ago

His original code for sql server is also missing those parameters. No, it's not - as he is not using those variables as parameter but he is adding it directly to the sql code

nick gowdy Over a year ago

@Selvin you're right, he is using concatenation to build his query. This is slightly off topic but it would improve his query to use all those variables as parameters like he has with name.

Selvin Over a year ago

not sure if it's possible with OFFSET and FETCH NEXT in MSSQL

|

Collectives™ on Stack Overflow

ASP.NET MVC -Working with Oracle and Dapper, using pagination

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related