Sunday, December 20, 2015

AX 2012 - Use Paging feature in the AIF Document Service

Hi All
As you know, the Document Service doesn't support the Paging, only Query Service.
Take a look here
 
Below the trick to enable this feature also for the Document Service:
1-      Take a look to the standard class AxdPricelist, prepareForQuery method
2-      Practically, the service use a temporary table that it’s filled every time the service is called
3-      So, the idea is use a temp table and fill it with the proper data coming from a “direct” query to SQL
4-      The Temp Table must have two extra fields, like PageNumber and RowNumber
5-      So, in the prepareForQuery method execute a query directly to SQL using Paging SQL feature like :
a.       Select Field1, Field2, Fieldn from Table\View Order By Field Desc OFFSET ((' + PageNumber + ' - 1) *' + RowsPage + ') ROWS FETCH NEXT ' + RowsPage + ' ROWS ONLY
6-      From my side I used the Statement class and fill the temp table like :
    ResultSet = statement.executeQuery(SQL);
 
    while ( ResultSet.next() )
    {
        TempTable.clear();
 
        TempTable.Field1                = ResultSet.getXX(1);
        TempTable.Field2                = ResultSet.getXX(2);
        …..
 
        TempTable.PageNumber  = PageNumber;
        TempTable.RowsPage    = RowsPage;
 
        TempTable.insert();
    }
 
7-   Before to execute the query you have to retrieve the “range” fields, so PageNumber and RowNumber like :
    Qbr1 = _query.dataSourceNo(1).findRange( fieldNum(TempTable, PageNumber ) );
    Qbr2 = _query.dataSourceNo(1).findRange( fieldNum(TempTable, RowsPage   ) );
 
8-      In a complex queries, we have to create an AX View and use it in the above query.
9-      I have used the OFFSET clause. This feature is available for the SQL Server 2012 and above version. Otherwise you have to use the ROW_NUMBER clause.
Stay Tuned!

No comments: