Dynamics Ax custom WCF service with paging support

Hi all,

Lately I’ve been busy developing WCF services to communicate with .NET web applications. All of these web services are custom-made and are using .NET data contracts so that every application uses the same contracts. Due to the high amount of data and performance we had to implement some kind of paging. I had no clue that Ax even has paging support but it does and it does this with properties on the QueryRun objects.

For example purposes I’ve made a service which uses .NET request and response contracts. I prefer this way over X++ data contracts because this is more reusable and flexible on the client side. The code is self-explanatory to me but you can always pose questions of course. 😉

The request contract:

[DataContract]
public class ItemListRequest
{
    [DataMember]
    public long StartingPosition { get; set; }
 
    [DataMember]
    public long NumberOfRecordsToFetch { get; set; }
}

The response contract:

[DataContract]
[KnownType(typeof(Item))]
public class ItemListResponse
{
    [DataMember]
    public int TotalNumberOfRecords { get; set; }
 
    [DataMember]
    public ArrayList Items { get; set; }
}
 
[DataContract]
public class Item
{
    [DataMember]
    public string Id { get; set; }
 
    [DataMember]
    public string Name { get; set; }
}

The service implementation:

[SysEntryPointAttribute]
public Blog.WCFPaging.DataContracts.ItemListResponse getItems(Blog.WCFPaging.DataContracts.ItemListRequest  _request)
{
    Blog.WCFPaging.DataContracts.Item               item;
    System.Collections.ArrayList                    itemList    = new System.Collections.ArrayList();
    Blog.WCFPaging.DataContracts.ItemListResponse   response    = new Blog.WCFPaging.DataContracts.ItemListResponse();
 
    QueryRun        queryRun    = new QueryRun(queryStr(InventTable));
    InventTable     inventTable;
    ;
 
    if(     CLRInterop::getAnyTypeForObject(_request.get_StartingPosition()) > 0
        &&  CLRInterop::getAnyTypeForObject(_request.get_NumberOfRecordsToFetch()) > 0)
    {
        response.set_TotalNumberOfRecords(QueryRun::getQueryRowCount(queryRun.query(), maxInt()));
 
        queryRun.enablePositionPaging(true);
        queryRun.addPageRange(_request.get_StartingPosition(), _request.get_NumberOfRecordsToFetch());
 
        // At least one order by field should be declared when using paging
        SysQuery::findOrCreateDataSource(queryRun.query(), tableNum(InventTable)).addOrderByField(fieldNum(InventTable, ItemId));
    }
 
    while(queryRun.next())
    {
        inventTable = queryRun.get(tableNum(InventTable));
 
        item        = new Blog.WCFPaging.DataContracts.Item();
        item.set_Id(inventTable.ItemId);
        item.set_Name(inventTable.NameAlias);
        itemList.Add(item);
    }
 
    response.set_Items(itemList);
 
    return response;
}

Calling the service from a .NET application:

int pageSize = 10;
 
using (var client = new BLOGPagingServiceClient())
{
    BLOGPagingServiceGetItemsResponse response = null;
 
    var request = new ItemListRequest() { StartingPosition = 1, NumberOfRecordsToFetch = pageSize };
 
    do
    {
        response = client.getItems(new BLOGPagingServiceGetItemsRequest()
        {
            CallContext = new CallContext(),
            _request = request
        });
 
        foreach (Item item in response.response.Items)
        {
            Console.WriteLine(String.Format("{0, -10} - {1}", item.Id, item.Name));
        }
 
        Console.WriteLine("-----");
        request.StartingPosition += pageSize;
    }
    while (response.response.Items.Count > 0);
}

Paging on a QueryRun is implement since Ax 2009, more info on paging: http://msdn.microsoft.com/nl-be/library/aa623755(v=ax.50).aspx

I wonder why this isn’t implemented in the AIF services or is it? If anyone knows please leave a comment about it. 😉

4 thoughts on “Dynamics Ax custom WCF service with paging support”

  1. Interesting article :). I had to make modifications to standard AIF so that a sorting field could be passed in, so that i can handle the 1000 row limit. I could use this in that and make it better 🙂

    1. Hi Shashi,

      That would be a way better option, I’ve dived into this with SQL tracing and I’ve noticed that the paging is handled on the SQL server witch I hoped for. 😉 So on large tables this would be a very good performance win. 🙂

      Kind regards,

      Kevin

      1. Managed to do a test with this in x++, seems that the paging works across all records in a query. i.e. if the paging records is set to 100, and the query involves a join of 2 tables, then 100 includes the records retrieved from both the tables. Do you know how this is handled within SQL server? Makes it hard if you want to be paging the top most record (unless pagingByValue is used?)

        1. Hi Shashi,

          Sorry for the late reply, I’ve been busy lately.
          That is correct only 100 results will be returned regardless of the tables that are joined. I have no experience with value based paging as this works for us as desired.

          Kind regards,

          Kevin

Leave a Reply to Shashi Sadasivan Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.