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

Advertisements

9 thoughts on “Walkthrough: Calling the Query Service with a Dynamic Query [AX 2012]

  1. Hi,
    I have been using this dynamic query mechanism and it works quite nice.
    Now I need to debug the code within the initialize() method of my AifQueryBuilder descendent class. The problem is that the debugger (Visual Studio) does not hit any of the breakpoints that I set in methods of the MyAifQueryBuilder class or MyAifQueryBuilderArgs classes.
    I have also build a normal Service and debugging of this code (using Visual Studio attached to the AOS service) works as expected.

    Do you know if it is possible to debug any dynamic query code that is running through the standard QueryService?

    Note: I have the same problem with debugging code on an AX Data Set that is running on the Enterprise Portal.

    Regards,
    Chrisjan Pijnenburg

    Like

  2. Hi Shashi,
    It turns out that code inside the AifQueryBuilder / AifQueryBuilderArgs Classes can be debugged in X++ with the AX debugger (using the setup for debugging AOS code).
    I expected this code to run in IL same as all user defined AOS services do.

    Like

  3. Hi, thanks for the article. I followed your tutorial but I cannot find the custom Args class at C# project. I even tried compiling Dynamics with no luck. Do you know what I might be missing? Thanks

    Like

    1. HI Eros, Did you create those classes in X++ ? Did you do a CIL compile and restart the AOS ? Once you do that you will need to update the reference in the C# project. The classes created in X++ needs to flow into the WCF services created by Ax, and then re-read in Visual studio to pick up those changes.
      PM me if it still doesnt work and i can help you out by asking you for more specific errors.

      Like

Leave a Reply / Comment

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s