Get records from AX with pagination [AX 2009]

Question

I am trying to get a certain number of records at a time from AX. I want to perform something equivalent to:

SELECT * FROM (SELECT *, ROW_NUMBER() AS ROWNO
FROM TableName) 
    AS TableName WHERE ROWNO > startIndex 
AND ROWNO <= endIndex;

Currently, I am fetching all records from AX (using .net business connector) :

axRecord.ExecuteStmt("select * from %1");
i = 0;
while(axRecord.Found)
{
 if(i<startIndex)
 {
  i++;
  continue;
 }
 // Perform operations
 i++;
 if(i==endIndex)
 {
   break;
 }
}

Is there is a better way to do this using Business connector only? Kindly help

Answer 1

In the internal SQL syntax of X++ there is some keywords (firstOnly, firstOnly10, firstOnly100, firstOnly1000) to limit how many rows will be fetched. It can be combined with RecId field to manually fetch groups of rows simulating pagination:

select firstonly10 inventTable // only fetch 10 rows
    index hint ItemIdx
    where inventTable.RecId > lastRecIdFetched // save last recId for each page
       && inventTable.itemId == itemId;

You have complete syntax reference of select statement on MSDN:

http://msdn.microsoft.com/en-us/library/aa656402.aspx