Hi, I would like to be able to perform paging-style queries where I specify the start row-number and end row-number rather than specifying a page number and count. Is there any mechanism available to implement such behaviour in LLGLBEN? Since the logic that generates the paging query (calculating start and end row numbers from the given page-number and -size) is implemented in the DQEs I'm not sure I can just override something and supply different code.

Would somehow modifying the ADO.NET query before it's executed be the way to go?

Could you give an example why you want to have startrow-endrow instead of pageno/pagesize? (as paging is used mostly for chopping up a large resultset, so it doesn't matter for the user if s/he browses to page 3 or to rows 20-30)

Otis wrote:

Could you give an example

Yep. I am making changes to the underlying data as I iterate over it which will exclude the edited records from subsequent retrieval using the same query filtering predicates (ignoring paging).

Here is an example (page size 5):

TABLE contents: ID PROCESSED 1 false 2 false 3 false 4 false 5 false 6 false 7 false 8 false 9 false 10 false

query: "get records from TABLE where PROCESSED=false"

Get page 1: result set {1,2,3,4,5} edit 3 records - setting PROCESSED=true.

Get page 2: result set {9, 10} NB: not {6,7,8,9,10} due to editing of records in first page.

If I keep track of how many records I edit, I can ask for blocks of records by start and end row that avoids the skipping of records seen above.

**BTW, as an implementation of this idea, I'm currently trying to intercept the collection fetching query and modify the values of __rownumStart and __rowNumEnd parameters.**

Well, I have been successful in implementing this by intercepting the select query in OnFetchEntityCollection, and changing the values of (MSSQL) @__rownumStart and @__rownumEnd, or :rownumEnd__ and :rownumStart__.

I noticed that it looks like the parameter labels in the Oracle DQE are the wrong way round

No doubt when it comes to porting to a new LLBLGEN, this hack will stop working.

That's indeed the way it should work The parameters are correct, oracle chokes on parameters starting with ___ so I appended the underscores at the end.

Otis wrote:

The parameters are correct, oracle chokes on parameters starting with ___ so I appended the underscores at the end.

No, I wasnt talking about the underscores. I meant that in the oracle DQE, you need to set the startrow parameter to the larger of the two values (end row index) and the endrow to the smaller (start row index):

IDbDataParameter rowNoStart =
       (IDbDataParameter)selectQuery.Parameters[":rownoStart__"];
IDbDataParameter rowNoEnd = 
       (IDbDataParameter)selectQuery.Parameters[":rownoEnd__"];

// Swapped:
rowNoStart.Value = _endRow;
rowNoEnd.Value = _startRow;                 

Whereas in the SQLServer DQE you set them as you'd expect (with zero-based counting for that database type).

No biggy, as they say!

What a blunder

I placed the markers wrong in the query construction. Silly thing. I'll fix this in v2, so you won't run into probs now, or do you want me to fix it pronto in 1.0.2005.1 as well, so you wont into problems later?