Monday, May 14, 2012

Microsoft Dynamics AX 2012 Application Integration Framework – Query Web Service (Loosely typed)

Microsoft Dynamics AX 2012 Application Integration Framework –
Query Web Service (Loosely typed)

Purpose: The purpose of this document is to illustrate how to use Microsoft Dynamics AX 2012 AIF Query AIF Web Service in integration scenarios for different types of custom data.

Challenge: Data model changes in Microsoft Dynamics AX 2012 related to high normalization and introduction of surrogate keys made some integrations more complex. In fact the process of custom AIF Web Service creation remains the same. However in the case when you need to retrieve (read) different (may be even similar) types of custom data with Microsoft Dynamics AX 2012 you may end up creating different dedicated AIF Web Services which increases development effort.

Solution: Microsoft Dynamics AX 2012 out-of-the-box ships with number of AIF Web Services which can be used in integration scenarios. In this simple example I'm going to retrieve (read) Vendor groups information from Microsoft Dynamics AX 2012 by means of external .NET application. Please note that there is no Vendor groups AIF Web Service in Microsoft Dynamics AX 2012 out-of-the-box. In this walkthrough I'll demonstrate how to use Query AIF Web Service in order to retrieve (read) different types of custom data such as Vendor groups from Microsoft Dynamics AX 2012 by means of C#.NET Console application without creating a dedicated AIF Web Service from scratch. Similar logic can be used to retrieve (read) other types of custom data from Microsoft Dynamics AX 2012 using Query AIF Web Service. 

Data Model:
Table Name
Table Description
VendGroup
The VendGroup table contains definitions of vendor groups.

Data Model Diagram:
Please note that I'm going to retrieve (read) Vendor groups information from Microsoft Dynamics AX 2012 by means of external .NET application and I only want to read 2 fields: VendGroup and Name

Walkthrough:
Query AIF Web Service (QueryService) is the system Microsoft Dynamics AX 2012 AIF Web Service which can be used in integration scenarios to retrieve (read) the data. More information about Query AIF Web Service can be found here: http://msdn.microsoft.com/en-us/library/gg847959.aspx

Please note that there is no need to create a Service group and do Inbound port setup for Query AIF Web Service. This is because the system services such as Query AIF Web Service are automatically installed as part of the setup process and are always available.

As a next step you can consume Query AIF Web Service from .NET application by adding Service reference to it.

Service reference

Please note that QueryService AIF Web Service exposes executeQuery operation which will be used to retrieve (read) information about Vendor groups information from Microsoft Dynamics AX 2012.

Source code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

using ConsoleApplication1.ServiceReference1;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            QueryServiceClient client = new QueryServiceClient();          

            QueryMetadata query = new QueryMetadata();
            query.DataSources = new QueryDataSourceMetadata[1];
            query.Name = "VendGroup";

            QueryDataSourceMetadata queryDS = new QueryDataSourceMetadata();
            queryDS.Name = "VendGroup";           
            queryDS.Table = "VendGroup";
            queryDS.Enabled = true;

            query.DataSources[0] = queryDS;

            queryDS.DynamicFieldList = false;

            queryDS.Fields = new QueryDataFieldMetadata[2];

            QueryDataFieldMetadata fieldVendGroup = new QueryDataFieldMetadata();
            fieldVendGroup.FieldName = "VendGroup";
           fieldVendGroup.SelectionField = SelectionField.Database;

            queryDS.Fields[0] = fieldVendGroup;

           QueryDataFieldMetadata fieldName = new QueryDataFieldMetadata();
            fieldName.FieldName = "Name";
            fieldName.SelectionField = SelectionField.Database;

            queryDS.Fields[1] = fieldName;
           
            Paging paging = new ValueBasedPaging() { RecordLimit = 25 };

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

                for (int i = 0; i < dataset.Tables[0].Rows.Count; i++)
                {
                    DataRow datarow = dataset.Tables[0].Rows[i];
                    Console.WriteLine(datarow[0].ToString() + ":" + datarow[1].ToString());
                }
             
            Console.WriteLine("Done!");
            }
            catch (Exception e)
            {
                Console.WriteLine("Error:" + e.Message);      
            }

            Console.ReadLine();
        }
    }
}


Result:


.NET Console application – Vendor groups



Summary: For the purposes of relatively simple integration scenarios Query AIF Web Service in Microsoft Dynamics AX 2012 can be used to retrieve (read) the information for different types of custom data without creating dedicated AIF Web Services. For example, in order to retrieve (read) the information about Customer groups and Vendor groups information with Microsoft Dynamics AX 2012 from external .NET application Query AIF Web Service may be used which allows for dynamic, loosely typed integration scenarios. As the result at run time external .NET application may just supply different query corresponding to Customer group or Vendor group metadata while calling the same executeQuery operation on Query AIF Web Service. Please note that for complex integration scenarios when it is required to retrieve (read) the information based on complex query with a lot of filtering and sorting it's recommended to use dedicated AIF Web Services which increases code readability and facilitates maintenance effort.


Author: Alex Anikiev, PhD, MCP


Tags: Microsoft Dynamics ERP, Microsoft Dynamics AX 2012, Integration, Application Integration Framework, Custom Data, Query AIF Web Service.


Note: This document is intended for information purposes only, presented as it is with no warranties from the author. This document may be updated with more content to better outline the concepts and describe the examples.

Sunday, May 6, 2012

Microsoft Dynamics AX 2012 Application Integration Framework – Custom Web Service (Loosely typed)

Microsoft Dynamics AX 2012 Application Integration Framework – Custom Web Service (Loosely typed)
Purpose: The purpose of this document is to illustrate how to use Microsoft Dynamics AX 2012 AIF Generic Document AIF Web Service in integration scenarios for different types of custom data.

Challenge: Data model changes in Microsoft Dynamics AX 2012 related to high normalization and introduction of surrogate keys made some integrations more complex. In fact the process of custom AIF Web Service creation remains the same. However in the case when you need to synchronize different (may be even similar) types of custom data with Microsoft Dynamics AX 2012 you may end up creating different dedicated AIF Web Services which increases development effort.    

Solution: Microsoft Dynamics AX 2012 out-of-the-box ships with number of AIF Web Services which can be used in integration scenarios. In this simple example I'm going to synchronize Customer groups information with Microsoft Dynamics AX 2012 from external .NET application. Please note that there is no Customer groups AIF Web Service in Microsoft Dynamics AX 2012 out-of-the-box. In this walkthrough I'll demonstrate how to use Generic Document AIF Web Service in order to synchronize different types of custom data such as Customer groups with Microsoft Dynamics AX 2012 from C#.NET Console application without creating a dedicated AIF Web Service from scratch. Similar logic can be used to synchronize other types of custom data with Microsoft Dynamics AX 2012 using Generic Document AIF Web Service.

Data Model:

Table Name
Table Description
CustGroup
The CustGroup table contains a list of groups into which customers can be added. Every customer must specify a customer group with which it is associated. The group contains information such as default payment terms and settlement periods, and is also used for reporting.

Data Model Diagram:
Please note that I'm going to synchronize Customer groups information with Microsoft Dynamics AX 2012 from external .NET application and I only want to expose 2 fields: CustGroup and Name

Walkthrough:

Generic Document (AifGenericDocumentService) AIF Web Service is the standard Microsoft Dynamics AX 2012 AIF Web Service which can be used in integration scenarios.  Generic Document AIF Web Service is also used by Microsoft Dynamics AX 2012 Excel Add-in for publishing data using Tables.


Service group
Generic Document AIF Web Service is included into AifGDS Service group.

Inbound port

After you deploy AifGDS Service group corresponding AifGDS port will be activated

Service Reference:

After successful deployment you can consume Generic Document AIF Web Service from .NET application by adding Service reference to it. Please note that GenericDocumentService AIF Web Service exposes create operation which will be used to create Customer groups information in Microsoft Dynamics AX 2012.

Source code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using ConsoleApplication1.ServiceReference1;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            GenericDocumentServiceClient client = new GenericDocumentServiceClient();

            CallContext context = new CallContext();
            context.Company = "ceu";

            QueryMetadata query = new QueryMetadata();           
            query.DataSources = new QueryDataSourceMetadata[1];
            query.Name = "CustGroup";

            QueryDataSourceMetadata queryDS = new QueryDataSourceMetadata();
            queryDS.Table = "CustGroup";
            queryDS.Name = "CustGroup";
            queryDS.Enabled = true;           
            queryDS.EnabledSpecified = true;          

            query.DataSources[0] = queryDS;
           
            queryDS.DynamicFieldList = false;
            queryDS.DynamicFieldListSpecified = true;
           
            queryDS.Fields = new QueryDataFieldMetadata[2];

            QueryDataFieldMetadata fieldCustGroup = new QueryDataFieldMetadata();
            fieldCustGroup.FieldName = "CustGroup";
            fieldCustGroup.SelectionField = SelectionField.Database;
            fieldCustGroup.SelectionFieldSpecified = true;

            queryDS.Fields[0] = fieldCustGroup;

            QueryDataFieldMetadata fieldName = new QueryDataFieldMetadata();
            fieldName.FieldName = "Name";
            fieldName.SelectionField = SelectionField.Database;
            fieldName.SelectionFieldSpecified = true;

            queryDS.Fields[1] = fieldName;                      
            
            string xmlDocument = "<?xml version=\"1.0\" encoding=\"utf-16\"?> <CustGroup xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns=\"http://schemas.microsoft.com/dynamics/2008/01/documents/CustGroup\"><SenderId xsi:nil=\"true\" /><CustGroup class=\"entity\"><_DocumentHash xsi:nil=\"true\" /><CustGroup>Alex</CustGroup><Name>Alex</Name></CustGroup></CustGroup>";

            try
            {
                //string[] schema = client.getSchemas(context, query);
                EntityKey[] entityKey = client.create(context, query, xmlDocument);

                Console.WriteLine("Done!");               
            }
            catch (Exception e)
            {
                Console.WriteLine("Error:" + e.Message);      
            }

            Console.ReadLine();
        }
    }
}

Schemas:

Document Schema

<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns:tns="http://schemas.microsoft.com/dynamics/2008/01/documents/CustGroup" xmlns:st="http://schemas.microsoft.com/dynamics/2008/01/sharedtypes" elementFormDefault="qualified" targetNamespace="http://schemas.microsoft.com/dynamics/2008/01/documents/CustGroup" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:import schemaLocation="SharedTypes.xsd" namespace="http://schemas.microsoft.com/dynamics/2008/01/sharedtypes" />
  <xs:element name="CustGroup" type="tns:AxdCustGroup" />
  <xs:complexType name="AxdCustGroup">
    <xs:sequence>
      <xs:element minOccurs="0" name="DocPurpose" nillable="true" type="st:AxdEnum_XMLDocPurpose" />
      <xs:element minOccurs="0" name="SenderId" nillable="true" type="st:AxdExtType_DataAreaId" />
      <xs:element minOccurs="0" maxOccurs="unbounded" name="CustGroup" type="tns:AxdEntity_CustGroup" />
    </xs:sequence>
  </xs:complexType>
  <xs:complexType name="AxdEntity_CustGroup">
    <xs:sequence>
      <xs:element minOccurs="0" name="_DocumentHash" nillable="true" type="xs:string" />
      <xs:element minOccurs="1" name="CustGroup" type="st:AxdExtType_CustGroupId" />
      <xs:element minOccurs="0" name="Name" nillable="true" type="st:AxdExtType_Description" />
    </xs:sequence>
    <xs:attribute fixed="entity" name="class" use="required" />
    <xs:attribute name="action" type="st:AxdEnum_AxdEntityAction" />
  </xs:complexType>
</xs:schema>

Please note that Document Schema generated contains definitions of 2 elements (fields) such as "CustGroup" and "Name" which were added to the data set programmatically.

SharedTypes Schema

<?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns:tns="http://schemas.microsoft.com/dynamics/2008/01/sharedtypes" elementFormDefault="qualified" targetNamespace="http://schemas.microsoft.com/dynamics/2008/01/sharedtypes" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:simpleType name="AxdEnum_XMLDocPurpose">
    <xs:annotation>
      <xs:documentation xml:lang="EN-US">Purpose:Purpose</xs:documentation>
    </xs:annotation>
    <xs:restriction base="xs:string">
      <xs:enumeration value="Original" />
      <xs:enumeration value="Duplicate" />
      <xs:enumeration value="Proforma" />
    </xs:restriction>
  </xs:simpleType>
  <xs:simpleType name="AxdExtType_DataAreaId">
    <xs:annotation>
      <xs:documentation xml:lang="EN-US">Company:ID for an area of data</xs:documentation>
   </xs:annotation>
    <xs:restriction base="xs:string">
      <xs:minLength value="0" />
      <xs:maxLength value="4" />
    </xs:restriction>
  </xs:simpleType>
  <xs:simpleType name="AxdEnum_AxdEntityAction">
    <xs:annotation>
      <xs:documentation xml:lang="EN-US">AxdEntityAction:AxdEntityAction</xs:documentation>
    </xs:annotation>
    <xs:restriction base="xs:string">
      <xs:enumeration value="create" />
      <xs:enumeration value="update" />
      <xs:enumeration value="replace" />
      <xs:enumeration value="delete" />
    </xs:restriction>
  </xs:simpleType>
  <xs:simpleType name="AxdExtType_CustGroupId">
    <xs:annotation>
      <xs:documentation xml:lang="EN-US">Customer group:Group of customers.</xs:documentation>
    </xs:annotation>
    <xs:restriction base="xs:string">
      <xs:minLength value="0" />
      <xs:maxLength value="10" />
    </xs:restriction>
  </xs:simpleType>
  <xs:simpleType name="AxdExtType_Description">
    <xs:annotation>
      <xs:documentation xml:lang="EN-US">Description:Brief description of transaction</xs:documentation>
    </xs:annotation>
    <xs:restriction base="xs:string">
      <xs:minLength value="0" />
      <xs:maxLength value="60" />
    </xs:restriction>
  </xs:simpleType>
</xs:schema>

Please note that you can call getSchemas method on GenericDocumentServiceClient class in C#.NET Console application to get the list of schemas generated in form of array.

XML:
If you turn on Logging for Inbound port you will be able to review XML documents generated in process of integration. Please see below how request XML document looks like

<?xml version="1.0" encoding="UTF-8" ?>
   - <GenericDocumentServiceCreateRequest xmlns="http://schemas.microsoft.com/dynamics/2011/01/services">
- <_axdQuery>
<CompanyRanges xsi:nil="true" xmlns="http://schemas.datacontract.org/2004/07/Microsoft.Dynamics.AX.Framework.Services.Metadata.Contracts" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />
- <DataSources xmlns="http://schemas.datacontract.org/2004/07/Microsoft.Dynamics.AX.Framework.Services.Metadata.Contracts">
- <QueryDataSourceMetadata>
<Company xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />
<DataSources xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />
<DynamicFieldList>false</DynamicFieldList>
<Enabled>true</Enabled>
- <Fields>
- <QueryFieldMetadata xsi:type="QueryDataFieldMetadata" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<FieldName>CustGroup</FieldName>
<SelectionField>Database</SelectionField>
<TableName xsi:nil="true" />
</QueryFieldMetadata>
- <QueryFieldMetadata xsi:type="QueryDataFieldMetadata" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<FieldName>Name</FieldName>
<SelectionField>Database</SelectionField>
<TableName xsi:nil="true" />
</QueryFieldMetadata>
</Fields>
<LabelId xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />
<Name>CustGroup</Name>
<Ranges xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />
<Relations xsi:nil="true" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />
<Table>CustGroup</Table>
</QueryDataSourceMetadata>
</DataSources>
<Description xsi:nil="true" xmlns="http://schemas.datacontract.org/2004/07/Microsoft.Dynamics.AX.Framework.Services.Metadata.Contracts" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />
<Filters xsi:nil="true" xmlns="http://schemas.datacontract.org/2004/07/Microsoft.Dynamics.AX.Framework.Services.Metadata.Contracts" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />
<GroupByFields xsi:nil="true" xmlns="http://schemas.datacontract.org/2004/07/Microsoft.Dynamics.AX.Framework.Services.Metadata.Contracts" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />
<HavingFilters xsi:nil="true" xmlns="http://schemas.datacontract.org/2004/07/Microsoft.Dynamics.AX.Framework.Services.Metadata.Contracts" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />
<Name xmlns="http://schemas.datacontract.org/2004/07/Microsoft.Dynamics.AX.Framework.Services.Metadata.Contracts">CustGroup</Name>
<OrderByFields xsi:nil="true" xmlns="http://schemas.datacontract.org/2004/07/Microsoft.Dynamics.AX.Framework.Services.Metadata.Contracts" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />
<OriginalAotQueryName xsi:nil="true" xmlns="http://schemas.datacontract.org/2004/07/Microsoft.Dynamics.AX.Framework.Services.Metadata.Contracts" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />
<TitleLabelId xsi:nil="true" xmlns="http://schemas.datacontract.org/2004/07/Microsoft.Dynamics.AX.Framework.Services.Metadata.Contracts" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />
</_axdQuery>
<_documentXml><?xml version="1.0" encoding="utf-16"?> <CustGroup xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/dynamics/2008/01/documents/CustGroup"><SenderId xsi:nil="true" /><CustGroup class="entity"><_DocumentHash xsi:nil="true" /><CustGroup>Alex</CustGroup> <Name>Alex</Name></CustGroup></CustGroup></_documentXml>
</GenericDocumentServiceCreateRequest>

Please note that _xmlDocument element in request XML document is exactly what has been specified as _xmlDocument parameter when create method was called on GenericDocumentServiceClient class in C#.NET Console application

Result:

Dynamics AX – Customer group


Summary: For the purposes of relatively simple integration scenarios Generic Document AIF Web Service in Microsoft Dynamics AX 2012 can be used for different types of custom data without creating dedicated AIF Web Services. For example, in order to synchronize Customer groups and Vendor groups information with Microsoft Dynamics AX 2012 from external .NET application Generic Document AIF Web Service may be used which allows for dynamic, loosely typed integration scenarios. As the result at run time external .NET application may just supply different value of _xmlDocument parameter corresponding to Customer group or Vendor group information while calling the same create operation on Generic Document AIF Web Service. Please note that required XML document schemas for different types of custom data will have to be defined upfront. This approach may also be used for populating different staging tables in Microsoft Dynamics AX 2012 from external .NET application by using the same means, or when the integration itself works based on dynamic maps, which reduces development effort. Please note that for complex integration scenarios with a lot of custom business logic and validations it's recommended to use dedicated AIF Web Services which increases code readability and facilitates maintenance effort.

Author: Alex Anikiev, PhD, MCP

Tags: Microsoft Dynamics ERP, Microsoft Dynamics AX 2012, Integration, Application Integration Framework, Custom Data, Generic Document AIF Web Service.

Note: This document is intended for information purposes only, presented as it is with no warranties from the author. This document may be updated with more content to better outline the concepts and describe the examples.