web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Blogs / Dynamics AX = Denis Macchinetti / AX 2012 - Use Paging featur...

AX 2012 - Use Paging feature in the AIF Document Service

Views (2963)
Denis Macchinetti Profile Picture Denis Macchinetti 16,444
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 PagingSQL 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!

This was originally posted here.

Comments

*This post is locked for comments