Hi, I need to implement custom DataGrid paging: http://dotnetjunkies.com/Tutorial/EA868 ... F967F.dcik

Basically, idea is called a stored proc and retrieve only those records you need on "Current" page, thereby reducing processing and data transfer. The stored procedure takes as parameters "PageNumber" and "PageSize" to compute the records to return.

Normally, without stored proc:

o_session = SetupFESRCConnection(...)
criteria = o_session.CreateCriteria(GetType(mylib.to.Company))
...
conjunc = New NHibernate.Expression.Conjunction
criteria.Add(conjunc)
disjunc = New NHibernate.Expression.Disjunction
conjunc.Add(disjunc)
disjunc.Add(Expression.EqExpression.Eq("CompanyCode", companyCode))
company_list = criteria.List()

But how can I specify criteria "I just want page 3 data given page size = 10 records per page"? However, can I retrieve via stored procedure using NHibernate?

Thanks in advance.

use
criteria.SetFirstResult(recordtostartfrom)
.SetMaxResults(10)

Hmmm... And with Query API it would be something like:
Query.SetFirstResult(10)
Query.SetMaxResults(5)
... for 5 rows from 10th to 14th, right?

But it looks like it does a "select top" just by making 10+5: "select top 15 ..." So when you paged to the last page you get the whole set of your data again. Can't believe this is to get "5 rows from 10th to 14th"...
Am I missing something (again :) )?

I haven't inspected the SQL generated, but I've been using query.SetFirstResult() and query.SetMaxResult() quite happily for some time. It gets the right rows.

Hey Balagan,

The issue here is that some SQL servers don't support paging (notably MSSQL), so the NH developers have made the dialect for those DBs only retrieve the rows up to the last one you want and then NH simply instantiates the objects for the page you're after. It's a workaround, but it's due to flaws in the DB, not NH.

If you use a DB that supports paging you'll get the right SQL and only the rows you want.

Cheers,

Symon.