Dynamic AX Query Services in BizTalk Functoid:

Author: Toraj Khavari

Date: March. 31, 2015

Objective: You may at times face querying Microsoft Dynamics AX (AX) data for BizTalk mapping. There are a few methods to implement BizTalk solutions to query AX data in BizTalk. This article is written to implement a C# in-line code, using BizTalk Script Functoid with signed assembly to Query AX data, and employing Dynamic AX Services. The assembly is Global Assembly Cache (GAC) registered and environments configurable.

AX has a powerful Application Interface Frame (AIF). Querying data from AX is very straight forward. For an introduction to AX Query, please refer to the following articles.

Dynamics AX - An Introduction to Query Services - http://torajkhavari.blogspot.com/2015/03/dynamics-ax-introduction-to-query.html

Article Body:

1-      Create a C# class library project, add a service reference for the DynamicsAx/Services/QueryService, sign the library project, and version the project’s assembly. Versioning the project assembly will assist you and the production support staff. For more details about assembly version, refer to the following article.

How to Version Solution Assemblies and why should we do them - http://torajkhavari.blogspot.com/2015/03/how-to-version-solution-assemblies-and.html

In my case, the business requirements were to get the following components from the AX’s “PurchLine” table. The query requires Purchase Order or PurchID, ItemID and Quantity.

• InventDimId
• InventTransRefId
• InventTransId

Project class name space is: Adec.BTSUtility.PurchLineMetaDataQueryService

Class name is: AOTPurchLineMetaDataQuery

Method name is: GetInventDimIdInventTransRefIdInventTransId

To save runtime, the method will return all three values with special characters delimiters. BizTalk can extract the three values during runtime keying on special characters.

Copy and paste the following code in the project “GetInventDimIdInventTransRefIdInventTransId” method.

#region GetInventDimIdAndInventTransRefId

/// <summary>

/// Gets the invent dim identifier, invent trans reference identifier and invent trans identifier.

/// </summary>

/// <param name="po">The Purchase Order.</param>

/// <param name="itemID">The item identifier.</param>

/// <param name="quanity">The quantity.</param>

/// <returns>String InventDimId followed by a marker, InventTransRefId followed by a marker, and InventTransId followed by a marker</returns>

public string GetInventDimIdInventTransRefIdInventTransId(string po, string itemID, string quantity)

{

    // Refrences:

    // http://blogs.msdn.com/b/axsupport/archive/2012/02/17/calling-the-query-service.aspx

    // https://team.a-dec.com/sites/SolDev/Developer%20Wiki/Dynamics%20AX%20An%20Introduction%20to%20Query%20Services.aspx

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

    string strResult, tempStrInventDimId, tempStrInventTransRefId, tempStrInventTransId, eventLogStr = null;

    /// Eventlog status

    eventLogStr = System.String.Format("Start Invent Query for PO={0}, ItemID={1}, Quanity={2}. In memory purchaseOrder is {3}. ",

                                                        po, itemID, quantity, purchaseOrder);

    eventLogStr = eventLogStr + System.String.Format("Class {0} a member of namespace {1}", "GetInventDimIdAndInventTransRefId", "Adec.BTSUtility.PurchLineMetaDataQueryService");

    System.Diagnostics.EventLog.WriteEntry("VendorsEDI", eventLogStr, System.Diagnostics.EventLogEntryType.Information, 1000);

    eventLogStr = "";

    /// always catch exception and error. Report problem.

    try

    {

        /// Always save the latest purchase order that is passed.

        if (System.String.IsNullOrEmpty(purchaseOrder))

        {

            purchaseOrder = po;

        }

        if (System.String.IsNullOrEmpty(po) == false)

        {

            if (po.Length > 0)

            {

                purchaseOrder = po;

            }

        }

        if (quantity.Contains(".00") == false)

        {

            quantity = quantity + ".00";

        }

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

        QueryServiceClient client = new QueryServiceClient();

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

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

        // Identify the Query

        query.Name = "PurchLine";

        // Instantiate Query Data Source for Meta data

        AxQueryServiceReference.QueryDataSourceMetadata queryDataSourceMD = new AxQueryServiceReference.QueryDataSourceMetadata();

        // Identify the query name and its table

        queryDataSourceMD.Name = "PurchLine";

        queryDataSourceMD.Table = "PurchLine";

        queryDataSourceMD.Enabled = true;

        // Identify the ranges.

        AxQueryServiceReference.QueryDataRangeMetadata rangePO = new AxQueryServiceReference.QueryDataRangeMetadata();

        rangePO.Enabled = true;

        rangePO.TableName = "PurchLine";

        rangePO.FieldName = "PurchId";

        rangePO.Value = purchaseOrder;

        rangePO.Enabled = true;

        AxQueryServiceReference.QueryDataRangeMetadata rangeItemID = new AxQueryServiceReference.QueryDataRangeMetadata();

        rangeItemID.TableName = "PurchLine";

        rangeItemID.FieldName = "ItemId";

        rangeItemID.Value = itemID;

        rangeItemID.Enabled = true;

        AxQueryServiceReference.QueryDataRangeMetadata rangeQuanity = new AxQueryServiceReference.QueryDataRangeMetadata();

        rangeQuanity.TableName = "PurchLine";

        rangeQuanity.FieldName = "QtyOrdered";

        rangeQuanity.Value = quantity;

        rangeQuanity.Enabled = true;

        // Set the ranges

        queryDataSourceMD.Ranges = new AxQueryServiceReference.QueryRangeMetadata[3];

        queryDataSourceMD.Ranges[0] = rangePO;

        queryDataSourceMD.Ranges[1] = rangeItemID;

        queryDataSourceMD.Ranges[2] = rangeQuanity;

               

        query.DataSources[0] = queryDataSourceMD;

        queryDataSourceMD.DynamicFieldList = true;

        // Get all the records

        Paging paging = null;

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

        strResult = "";

        // Always return the 1st item found

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

        {

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

            tempStrInventDimId = datarow.Field<string>("InventDimId"); // Use this for BizTalk WMSJournalTrans.InventDimId

            tempStrInventTransRefId = datarow.Field<string>("PurchID"); // Use this value for BizTalk Map InventTransRefId

            tempStrInventTransId = datarow.Field<string>("InventTransId"); // Use this for BizTalk WMSJournalTrans.InventTransId

            // "<" Begining of the InventTransRefId, ">" for the beginning of StrInventTransRefId, and ";" at the end of InventTransId.

            strResult = strResult + string.Format("{0}<{1}>{2};", tempStrInventDimId, tempStrInventTransRefId, tempStrInventTransId);

        }

        else

        {

            strResult = strResult + string.Format("No Match for PO={0}, ItemID={1}, Quanity={2}, in class {3}",

                                                    purchaseOrder, itemID, quantity, "GetInventDimIdAndInventTransRefId");

            System.Diagnostics.EventLog.WriteEntry("VendorsEDI", strResult, System.Diagnostics.EventLogEntryType.Warning, 1001);

            return strResult;

        }

    }

    catch (Exception e)

    {

        strResult = string.Format("Exception thrown in the {0} method. A member of {1} class in the {2} namespace. ", "GetInventDimIdAndInventTransRefId",

                                    "AOTPurchLineMetaDataQuery", "Adec.BTSUtility.PurchLineMetaDataQueryService");

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

               

        // Log exception

        System.Diagnostics.EventLog.WriteEntry("VendorsEDI", strResult, System.Diagnostics.EventLogEntryType.Error, 1009);

    }

    // Log complete

    eventLogStr = System.String.Format("End Invent Query for PO={0}, ItemID={1}, Quanity={2}. In memory purchaseOrder is {3}. ",

                                            po, itemID, quantity, purchaseOrder);

    eventLogStr = eventLogStr + System.String.Format("Class {0} a member of namespace {1}", "GetInventDimIdAndInventTransRefId", "Adec.BTSUtility.PurchLineMetaDataQueryService");

    System.Diagnostics.EventLog.WriteEntry("VendorsEDI", eventLogStr, System.Diagnostics.EventLogEntryType.Information, 1000);

           

    return strResult;

}

#endregion GetInventDimIdAndInventTransRefId

2-      Copy the Adec.BTSUtility.PurchLineMetaDataQueryService.dll and its configuration file to the BizTalk server. In my case, I copied them in the BizTalk Server C:\Adec\Adec.BTSUtility.PurchLineMetaDataQueryService.

3-      In the BizTalk server Click Start, Type cmd, or click All Programs, and then click Accessories. Right-click Command prompt, and then click Run as administrator. If the User Account Control dialog box appears, confirm that the action it displays is what you want, and then click Continue.

4-      Navigate to the gacutil.exe folder. In my case as follows.

cd C:\Program Files (x86)\Microsoft SDKs\Windows\v8.0A\bin\NETFX 4.0 Tools

5-      Register the dll with GAC.

gacutil.exe /if "C:\Adec\Adec.BTSUtility.PurchLineMetaDataQueryService\Adec.BTSUtility.PurchLineMetaDataQueryService.dll"

6-      Verify the GAC registration. In A-dec server, its location as follows.

GAC Location:\Windows\Microsoft.NET\assembly\GAC_MSIL\Adec.BTSUtility.PurchLineMetaDataQueryService

In the server’s GAC, the dll’s assembly version number will be very handy to validate the latest registration.

7-      Although BizTalk does not use the dll’s configuration file, I recommend copying the Adec.BTSUtility.PurchLineMetaDataQueryService.dll.config next to its assembly in GAC. It is for future traceability.

8-      Modify the BTSNTSvc.exe.config or BTSNTSvc64.exe.config depending if it runs on 32 or 64 bits process to contain the  Adec.BTSUtility.PurchLineMetaDataQueryService.dll.config contents. In the BizTalk server in A-dec, they are located in the following folder.

C:\Program Files (x86)\Microsoft BizTalk Server 2013

Copy the following information in the BTSNTSvc.exe.config or BTSNTSvc64.exe.config.

<!-- app.config for Adec.BTSUtility.PurchLineMetaDataQueryService class -->

<system.serviceModel>

      <bindings>

                  <netTcpBinding>

                              <binding name="QueryServiceEndpoint" transferMode="Streamed" maxReceivedMessageSize="2147483647" />

                  </netTcpBinding>

      </bindings>

      <client>

      <endpoint address="net.tcp:// YourAXAOSServer:port/DynamicsAx/Services/QueryService"

                  binding="netTcpBinding" bindingConfiguration="QueryServiceEndpoint"

                  contract="AxQueryServiceReference.IQueryService" name="QueryServiceEndpoint">

        <identity>

                  <userPrincipalName value="YourAXServiceId" />

        </identity>

      </endpoint>

      </client>

</system.serviceModel>

9-      At this point BizTalk configuration is completed.

10- In the Visual Studio’s map project add the GAC Adec.BTSUtility.PurchLineMetaDataQueryService.dll’s reference.

11- In the MAP add a script Functoid with Select script type, “External Assembly”. Navigate to the Script assembly, class and method. The assembly requires three inputs and has one output. Connect the input and output to desired schema node.

12- Rebuild and deploy. The application is ready for end to end testing.

Hint:

• The Visual Studio Test Map will not work with external assemblies. Except, if the Visual Studio config file, devenv.exe.config, is updated with Query Service configuration information.

Aline your gifts, talents, education, and work, you will feel and know success, happiness and contentment. Happy coding.

Cheers Toraj

Version 1.0.0331.01