Walkthrough: Calling the Query Service with a Dynamic Query [AX 2012]

Ax2012 exposes the query service, and makes the queries available directly by a developer. these queries are static, Dynamic or User defined. Query Service [AX 2012]

Static queries are the simplest ones, all you need to do is to pass the query name, and you get the dataset in return. (Walkthrough: Calling the Query Service with a Static Query [AX 2012]) Works like a charm, except when you may want to filter on it (Lets say we want all items that are BOM types). You could filter the dataset in c# or your language of choice, but what if there are over 2000 items ?

Thats where Dynamic queries come in. There is a bit more work involved in it, but its not too much.

Firstly we need to create 2 classes. One which holds the query, and we add our ranges to it, and the second class which is a DataContract / Argument class which contains the parameters we define for the filtering. (or any special process for that matter)

In this example we will look into using the Query InventTable

1. Create the DataContract / Arguments class

We will create a class called MyInventTableQueryBuilderArgs which extends AifQueryBuilderArgs Class, and will be decorated with the DataContractAttribute, and the parmMethods with the DataMemberAttribute. This tehn exposes this class to the QueryService

[DataContractAttribute]
public class MyInventTableQueryBuilderArgs extends AifQueryBuilderArgs
{
ItemType itemType;
}
ItemType is what we will use as a filter. In order for it to be available in the QueryService, is to create a parm method
[DataMemberAttribute]
public ItemType parmItemType(ItemType _itemType = itemType)
{
itemType = _itemType;
return itemType;
}

2. Create the QueryBuilder class
Create a class called MyInventTableQueryBuilder which extends the AifQueryBuilder class. Add the MyInventTableQueryBuilderArgs as a property of the class, and use the setArgs to populate this object.
public class MyInventTableQueryBuilder extends AifQueryBuilder
{
MyInventTableQueryBuilderArgs args;
}
//Used internally to access the args object
private MyInventTableQueryBuilderArgs getArgs()
{
return args;
}

This class also requires the initialize method to be overridden, and this is where we will populate the query ranges using the args object

public void setArgs(AifQueryBuilderArgs _args)
{
if(!_args
|| classIdGet(_args) != classNum(MyInventTableQueryBuilderArgs))
{
throw error("@SYS95118");
}
args = _args;
}

[SysEntryPointAttribute]
public void initialize()
{
QueryBuildDataSource qbds;
query = new Query(queryStr(InventTable));

qbds = query.dataSourceTable(tableNum(InventTable));

SysQuery::findOrCreateRange(qbds, fieldNum(InventTable, ItemType)).value(SysQuery::value(this.getArgs().parmItemType()));

queryRun = new QueryRun(query);
}
Compile the 2 classes and then run the incremental compile

Now for the better half – Visual studio

Create a project of your choice (I am using a Console application for simplicity), and in the language of your choice (I am choosing c#)
Add a Web service reference (Right click reference and choose Add Service reference)
In the URL enter the following URL (replace with the server where Ax is installed. The port 8101 can be replaced based on the installation port if different
http://AOSserverName:8101/DynamicsAx/Services/QueryService
also in the same screen (after clicking GO), enter the namespace as MyQueryService

In the Main Method of the Program class, write the following code:

static void Main(string[] args)
{
int records = 0;
using (MyQueryService.QueryServiceClient client = new MyQueryService.QueryServiceClient())
{
MyQueryService.Paging paging = null;

MyQueryService.MyInventTableQueryBuilderArgs queryArgs = new MyQueryService.MyInventTableQueryBuilderArgs();
queryArgs.parmItemType = MyQueryService.ItemType.Item;

DataSet dataset = client.ExecuteDynamicQuery(“MyInventTableQueryBuilder”, queryArgs, ref paging);
if (dataset.Tables.Contains(“InventTable”))
{
DataTable datatable = dataset.Tables[“InventTable”];
foreach (DataRow row in datatable.Rows)
{
records++;
string itemId = (string)row[“ItemId”];
Byte itemType = (Byte)row[“ItemType”];
string dataAreaId = (string)row[“DataAreaId”];
Console.WriteLine(“Item: {0}, Type: {1}, Company: {2}”, itemId, itemType, dataAreaId);
}
}
}
Console.WriteLine(“Records: {0}”, records);
}

The code should now be good to run. For my installation and data, i get the following output:

Item: Office-Chair, Type: 0, Company: ceu
Item: Pack-Ribbon, Type: 0, Company: ceu
Item: SCPS_CSCL, Type: 0, Company: ceu
Item: SCPS_CSCR, Type: 0, Company: ceu
Item: SCPS_PSCL, Type: 0, Company: ceu
Item: SCPS_PSCR, Type: 0, Company: ceu
Item: SCPS_UPSCL, Type: 0, Company: ceu
Item: SCPS_UPSCR, Type: 0, Company: ceu
Item: WEE B1190, Type: 0, Company: ceu
Item: WMFlat32, Type: 0, Company: ceu
Item: WMFlat45, Type: 0, Company: ceu
Item: Work-Shirt, Type: 0, Company: ceu
Records: 298
Press any key to continue . . .

NOTE: If you do get an error regarding the message size being too big, then edit the app.config to make the
maxReceivedMessageSize="9999999" in the netTcpBinding element