Paging and Query Service Results

Applies To: Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

The Microsoft Dynamics AX query service supports paging so that you can manage the amount of data returned by a query. Paging provides a mechanism to stop the query in order to limit the amount of data per request. This paging mechanism also allows for the query to restart from the position where it was stopped.

This topic describes the different types of paging that are available and shows you how to implement them when you call the query service. The two types of paging are as follows:

• Position-based paging – You supply a start position and the record limit for each query data source. The start position is a numeric value that specifies the record position (in the results) from which you want to start paging.

• Value-based paging – You provide a bookmark and the record limit for each query data source. The bookmark is an identifier that is derived from the value of the record.

When you call the query service, you pass the paging parameters to the service by using one of the paging classes exposed by the query service.

Position-Based Paging

Position-based paging is the simplest form of paging in which you specify a starting position and a record limit. In the dataset returned from the query service, there is a table for each data source in the query. By using position-based paging, no table returned from a query request will contain more than the number of records specified in the record limit.

The following code shows you how to use position-based paging when you call the query service by using the CustTable static query. The Paging object is passed by reference so, after you make the first call to the ExecuteStaticQuery method, the starting position value is returned to the client. The starting position value will be the position of the last record returned plus one.

In this example, each table in the dataset can have no more than 10 records. In order to get all of the records, the code must call the query service to get the next page of data until the top-level dataset table no longer contains any rows.

static void Main(string[] args)
        {

            QueryServiceClient client = new QueryServiceClient();

            int i = 0;
            DataSet dataSet;
            Paging paging = new PositionBasedPaging() { StartingPosition = 1, NumberOfRecordsToFetch = 10 };

            do
            {
                // Call the CustTable query using the query service.
                dataSet = client.ExecuteStaticQuery("CustTable", ref paging);

                // Code to perform operations on the data returned.
                Console.WriteLine("Query service call: " + i.ToString());
                Console.WriteLine("Number of Records in CustTable: " + dataSet.Tables[0].Rows.Count.ToString());
                Console.WriteLine("Number of Records in CustTrans: " + dataSet.Tables[1].Rows.Count.ToString());
                Console.WriteLine("Number of Records in CustTransOpen: " + dataSet.Tables[2].Rows.Count.ToString());
                i++;
            }
            // Check if the last call returned any data. If so, then this is the next page of data.
            while (dataSet.Tables[0].Rows.Count > 0);

            Console.ReadLine();
        }

Value-Based Paging

When you use value-based paging, you specify a bookmark that defines where the data page starts and you specify a record limit.

There are three different methods for using value-based paging:

• Standard value-based paging – You specify a bookmark and a record limit.

• Top-level value-based paging – You specify a bookmark and a record limit that is applied only to the top-level data source in the query.

• Advanced value-based paging – You specify a bookmark and a record limit for each query data source

Standard Value-Based Paging

When you use value-based paging, you must specify a bookmark and a record limit. If you pass no bookmark, the paging will start at the beginning of the result set. In the dataset returned from the query service, there is a table for each query data source. By using standard value-based paging, no table returned from a query request will contain more than the number of records specified in the record limit.

The following code shows you how to use standard value-based paging when you call the query service by using a static query. In value-based paging, you use the bookmark to check if there is any more data returned from the query. For the first call to the ExecuteStaticQuery method, you set no bookmark value. The Paging object is passed by reference so after you make the first call to the ExecuteStaticQuery method, the Paging object contains the bookmark of the first record in the next page of data. If there are more records to be returned, the bookmark returned to the client is not NULL. If there are no more records to be returned, the bookmark is NULL.

static void Main(string[] args)
        {

            QueryServiceClient client = new QueryServiceClient();

            int i = 0;
            DataSet dataSet;
            Paging paging = new ValueBasedPaging() { RecordLimit = 10 };

            do
            {
                // Call the CustTable query using the query service.
                dataSet = client.ExecuteStaticQuery("CustTable", ref paging);

                // Code to perform operations on the data returned.
                Console.WriteLine("Query service call: " + i.ToString());
                Console.WriteLine("Number of Records in CustTable: " + dataSet.Tables[0].Rows.Count.ToString());
                Console.WriteLine("Number of Records in CustTrans: " + dataSet.Tables[1].Rows.Count.ToString());
                Console.WriteLine("Number of Records in CustTransOpen: " + dataSet.Tables[2].Rows.Count.ToString());
                i++;
            }
            // Check if the returned bookmark is NULL. If so, there is no more data to be returned.
            while (((ValueBasedPaging)paging).Bookmark != null);

            Console.ReadLine();

        }

By default, if you pass a NULL Paging object to the query service, it will use standard value-based paging. After you call the query, if there is more data to be returned, the query service will return a Paging object to you with a bookmark so that you can retrieve the next page of data.

Top-Level Value-Based Paging

When you use top-level value-based paging, you must specify a bookmark, a record limit, and a Boolean value for the LimitTopLevelDataSourcesOnly parameter. If you pass no bookmark, the paging will start at the beginning of the result set.

In the dataset returned from the query service, there is a table for each query data source. In top-level value-based paging, the record limit applies only to the parent data source in the query. So the parent table in the dataset will contain no more than the number of records specified in the record limit. The maximum record limit is applied to the child data sources in the query. Therefore, if you call the CustTable query and pass in a record limit of 10, the dataset returned will contain a CustTable table that contains no more than 10 records, but the CustTrans and CustTransOpen tables will contain all the records that are returned for those data sources by the query up to the maximum record limit of 25,000.

The following code shows you how to use top-level value-based paging when you call the query service by using a static query. In top-level value-based paging, you use the bookmark to check if there is any more data returned from the query. For the first call to the ExecuteStaticQuery method, you set no bookmark value. The Paging object is passed by reference so after you make the first call to the ExecuteStaticQuery method, the Paging object contains the bookmark of the first record in the next page of data. If there are more records to be returned, the bookmark returned to the client is not NULL. If there are no more records to be returned, the bookmark is NULL.

static void Main(string[] args)
        {
            QueryServiceClient client = new QueryServiceClient();

            int i = 0;
            DataSet dataSet;
            Paging paging = new TopLevelValueBasedPaging() { RecordLimit = 25, LimitTopLevelDataSourcesOnly = true };

            do
            {
                // Call the CustTable query using the query service.
                dataSet = client.ExecuteStaticQuery("CustTable", ref paging);

                // Code to perform operations on the data returned.
                Console.WriteLine("Query service call: " + i.ToString());
                Console.WriteLine("Number of Records in CustTable: " + dataSet.Tables[0].Rows.Count.ToString());
                Console.WriteLine("Number of Records in CustTrans: " + dataSet.Tables[1].Rows.Count.ToString());
                Console.WriteLine("Number of Records in CustTransOpen: " + dataSet.Tables[2].Rows.Count.ToString());
                i++;
            }
            // Check if the last call returned any data. If so, then this is the next page of data.
            while (((ValueBasedPaging)paging).Bookmark != null);

            Console.ReadLine();
        }

Advanced Value-Based Paging

Advanced value-based paging enables you to control the record limit for each data source in a query. When you use advanced value-based paging, you can specify a bookmark and a record limit for each query data source. Any data source for which a record limit is not specified will return up to the maximum record limit of 25,000.

In the dataset returned from the query service, there is a table for each query data source. In advanced value-based paging, the record limits are applied for each data source in the query. If you do not provide a record limit for query data source, the maximum record limit is applied. If you run the following code, the query service returns a dataset that contains a CustTable table that contains up to 2 records, a CustTrans table that contains up to 10 records, and a CustTransOpen table that contains up to 10 records.

The following code shows you how to use advanced value-based paging when you call the query service by using a static query. In advanced value-based paging, you use the bookmark to check if there is any more data returned from the query. For the first call to the ExecuteStaticQuery method, you set no bookmark value. The Paging object is passed by reference so after you make the first call to the ExecuteStaticQuery method, the Paging object contains the bookmark of the first record in the next page of data. If there are more records to be returned, the bookmark returned to the client is not NULL. If there are no more records to be returned, the bookmark is NULL.

static void Main(string[] args)
        {
            QueryServiceClient client = new QueryServiceClient();

            int i = 0;
            DataSet dataSet;
            AdvancedValueBasedPaging advPaging = new AdvancedValueBasedPaging();
            Paging paging = advPaging;

            advPaging.RecordLimits = new DataSourceRecordLimit[] {
                new DataSourceRecordLimit() { DataSourceName = "CustTable", RecordLimit = 2 },
                new DataSourceRecordLimit() { DataSourceName = "CustTrans", RecordLimit = 10 },
                new DataSourceRecordLimit() { DataSourceName = "CustTransOpen", RecordLimit = 10 }};

            do
            {
                // Call the CustTable query using the query service.
                dataSet = client.ExecuteStaticQuery("CustTable", ref paging);

                // Code to perform operations on the data returned.
                Console.WriteLine("Query service call: " + i.ToString());
                Console.WriteLine("Number of Records in CustTable: " + dataSet.Tables[0].Rows.Count.ToString());
                Console.WriteLine("Number of Records in CustTrans: " + dataSet.Tables[1].Rows.Count.ToString());
                Console.WriteLine("Number of Records in CustTransOpen: " + dataSet.Tables[2].Rows.Count.ToString());
                i++;
            }
            // Check if the last call returned any data. If so, then this is the next page of data.
            while (((AdvancedValueBasedPaging)paging).Bookmark != null);

            Console.ReadLine();
        }

It is not required to set a record limit for each query data source. However, the query will stop processing as soon as a record limit on any data source is reached.

See also

Handling Large Datasets Returned by the Query Service

Streaming and Query Service Results

Walkthrough: Calling the Query Service with a Static Query