Dynamics AX - An Introduction to Query Services:

Title: Dynamics AX - An Introduction to Query Services:

Author: Toraj Khavari

Date: March. 25, 2015

Objective: In this wiki article we explore the use of Dynamics AX Query Services, and Microsoft Dynamics AX flexibility, extendibility, with powerful interfaces.

At times, we may have business requirements to query Dynamics AX data bases. Dynamics AX out-of-box supports significant number of queries. Run Dynamics AX. Anywhere in the UI, type <Cntl>D. The Dynamics AX AOT dialogue box opens. Open Queries node. All the available queries are displayed.

This article introduces the Query Service Web Service to consume any query. Consuming the Dynamics AX Query Service is a software language agnostic.  You can use any software language that consumes Web Service.  You can access Dynamic AX queries. I will use. C#.

Article Body:

1-      Create a class liberty.

2-      Add Service Reference, in my case, AOTQueryServiceRef, pointing to Dynamics AX Query Service, URL, an example as follows.

http://<ServerName:PortNumber>/DynamicsAx/Services/QueryService

For correct Query Service URL, and security access, contact the AX Admin team.

3-      In the AOT, identify the Query and table you want to use. In my case, I am using query "DirPartyContacts" table “DirPerson”. Table names are in the AOT, Query, Data Source Node. Identify the columns you are interested. Columns, AKA, fields are identified in AOT.

4-      Update the “using” statements. In my case, I use the following collection.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using Adec.BTSUtility.QueryService.AOTQueryServiceRef;

using System.Runtime.Serialization;

using System.IO;

using System.Data;

5-      Copy and paste the following code in your class.

        #region GetNamePhoneEmailbyPostalAddressRecId

        /// <summary>

        /// Gets the name phone and email by postal address record identifier.

        /// </summary>

        /// <param name="postalAddressRecIdstr">The postal address record id.</param>

        /// <returns></returns>

        public string GetNamePhoneEmailbyPostalAddressRecId(string postalAddressRecIdstr)

        {

            // Refrence http://ax2012aifintegration.blogspot.com/2012_05_01_archive.html and

            // Book Inside Microsoft Dynamics® AX 2012 by The Microsoft Dynamics AX Team

            // Return value

            string resultStr = null;

            try

            {

                // Instantiate Query Client, Query Meta data, and Query Data Source for Meta Data

                QueryServiceClient client = new QueryServiceClient();

                AOTQueryServiceRef.QueryMetadata query = new AOTQueryServiceRef.QueryMetadata();

                query.DataSources = new AOTQueryServiceRef.QueryDataSourceMetadata[1];

                // Identify the Query

                query.Name = "DirPartyContacts";

                // Instantiate Query Data Source for Meta data

                AOTQueryServiceRef.QueryDataSourceMetadata dirPartyContactsDS = new AOTQueryServiceRef.QueryDataSourceMetadata();

                // Identify the query namd and its table

                dirPartyContactsDS.Name = "DirPartyContacts";

                dirPartyContactsDS.Table = "DirPerson";

                dirPartyContactsDS.Enabled = true;

                // Using xpass as a bypass to get all data.

                if (postalAddressRecIdstr.Contains("xpass") != true)

                {

                    // If you are using Range, modify the for loop

                    AOTQueryServiceRef.QueryDataRangeMetadata range = new AOTQueryServiceRef.QueryDataRangeMetadata();

                    range.Enabled = true;

                    range.TableName = "DirPerson";

                    range.FieldName = "RecId";

                    range.Value = postalAddressRecIdstr; // Input string

                }

                query.DataSources[0] = dirPartyContactsDS;

                dirPartyContactsDS.DynamicFieldList = true;

                AOTQueryServiceRef.QueryDataFieldMetadata name, phone, email, recId;

                name = new AOTQueryServiceRef.QueryDataFieldMetadata();

                name.FieldName = "Name";

                name.SelectionField = AOTQueryServiceRef.SelectionField.Database;

                phone = new AOTQueryServiceRef.QueryDataFieldMetadata();

                phone.FieldName = "PrimaryContactPhone";

                phone.SelectionField = AOTQueryServiceRef.SelectionField.Database;

                email = new AOTQueryServiceRef.QueryDataFieldMetadata();

                email.FieldName = "PrimaryContactEmail";

                email.SelectionField = AOTQueryServiceRef.SelectionField.Database;

                recId = new AOTQueryServiceRef.QueryDataFieldMetadata();

                recId.FieldName = "RecId";

                recId.SelectionField = AOTQueryServiceRef.SelectionField.Database;

                // Get all the records

                Paging paging = null;

                DataSet dataset = client.ExecuteQuery(query, ref paging);

                if (dataset.Tables[0].Rows.Count == 0)

                {

                    resultStr = resultStr + string.Format("Querey Result is empty");

                    return resultStr;

                }

                if (postalAddressRecIdstr.Length < 1)

                {

                    resultStr = resultStr + string.Format("Invalid Empty PostalAddressRecId");

                    return resultStr;

                }

                // If you are using Range, modify the for loop

                for (int i = 0; i < dataset.Tables[0].Rows.Count; i++)

                {

                    DataRow datarow = dataset.Tables[0].Rows[i];

                    if (postalAddressRecIdstr.Contains("xpass") == true)

                    {

                        resultStr = resultStr + string.Format("RecID: {0}; Name:{1} ; Phone:{2} ; Email: {3} ;\r\n",

                            datarow[0].ToString(), datarow[1].ToString(), datarow[2].ToString(), datarow[3].ToString());

                    }

                    else

                    {

                        if (datarow[0].ToString() == postalAddressRecIdstr)

                        {

                            resultStr = resultStr + string.Format("{0} {1} {2}",

                                datarow[1].ToString(), datarow[2].ToString(), datarow[3].ToString());

                        }

                    }

                }

                if (resultStr == null)

                {

                    resultStr = resultStr + string.Format("No Match for {0}", postalAddressRecIdstr);

                    return resultStr;

                }

            }

            catch (Exception e)

            {

                resultStr = resultStr + string.Format("Error Message:{0}\n", e.Message);

            }

            return resultStr;

        }

        #endregion

Hints:

-          Modify the code to meet your query name, tables, and fields.

-          If range is not working for you, take a look at AOT Range node in the query. Validate its setup before using it.

-          Data Sources has associated AOT table, Data Dictionary > Table.

-          You can see the table resolute by selecting the Table, right mouse click open.

-          Data Sources in AOT outlines the relational data sources; I like to call them the children relations. The children data sources are available for query, too.

-          The copy and paste removed the indentation. The code is in TFS Toraj.khavari $/Core/Adec.ConsumeQueryService/Adec.BTSUtility.QueryService.

Dynamics AX Query Services brings a whole new dimension to Dynamics AX interface. If AIF Exposed services does not meet your need take a look at Query Services. In addition to Query Services, we have Meta Data Query Services with more flexibility.

Have fun learning and coding. These are great time to take advantage of Service Oriented Architecture.

Cheers Toraj

Version 1.0.2425.01