How to pager record

Mar 10, 2011 at 3:56 AM

How to pager record.

public IList GetAll(int pageIndex, int pageSize)
I can complete it by NHibernate like below:
public IList<T> GetAll<T>(int pageIndex, int pageSize) where T : class, new()
            ICriteria criteria = session.CreateCriteria(typeof(T));
            if (!string.IsNullOrEmpty(orderBy))
                criteria.AddOrder(new NHibernate.Criterion.Order(orderBy, descending == false));
            criteria.SetFirstResult(pageIndex * pageSize);
            if (pageSize > 0)

            return criteria.List<T>();


can it provider SetFirstResult and SetMaxResults function

Mar 11, 2011 at 4:51 AM
Edited Mar 11, 2011 at 4:54 AM

Use DBQuery.SelectTopN() Or Select().TopN() to limit the number of results returned.

As SQLServer does not support the capability to page results (I know SQLite and MySQL do) The limits were not implemented.
Because the only option would have been to throw an exception for SQLServer or manually code a workaround for this engine.
There is the Row_Number() OVER (ORDER BY...) option and I'd be intrigued to see an injection of this in a query for SQLServer and wrap an outer Select statement.
See if you can get close. But at the moment only option is to use the for loop with the data reader.
You may find that NHibernate is also doing this but hiding the details - It's at a much higher level than this library.
Jul 14, 2011 at 3:52 PM

guys i've update library for this option if somebody still need  in it     i can share.    i've made just another cause - DBCustomStatement.   SelectRow_Count   Method  use DBCustomStatement to determine experssion:       

    row_number() OVER (ORDER BY [Id_Client] ASC) AS [rownumber]  - 

         ---------  here we use two clauses as Params {  StartCause - OrderField : [Id_Client] ; MiddleCause - rowNumFieldAlias: [rownumber]  } .  

Jul 15, 2011 at 8:46 PM

xidius - send it over and i'll include it.


Jul 17, 2011 at 2:21 PM

ok ican but where to sent    or how  ...   usually guys that lead some project gave me their mail       ...    your mail is -?     

and i also  transferring  this library to the silverlight /(wp7-optional) platforms.    if you want to look at this variant i can send it too    in some time. In this variant i think that  i'l need to breake your  wonderfull incapsulation  to make contracts transferable.     The concept of that transferring is need to compose query on the client(silverlight) and   built  query on the server 

             your mail .. and your comments