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.

12 comments:

  1. Hi, excellent article.

    Is it possible to add filters to the query?

    I've noticed there is a query.Filter property but haven't figured out how to use it.

    Thanks,

    ReplyDelete
  2. When i m trying like this, I got this error

    "The socket connection was aborted. This could be caused by an error processing your message or a receive timeout being exceeded by the remote host, or an underlying network resource issue. Local socket timeout was '00:00:59.6709812'."
    can u help me

    Thanks & Regards,
    P.maruthi

    ReplyDelete
    Replies
    1. Hi P.maruthi!

      You can define 2 Bindings: Client Binding and Server Binding. Each Binding has Time-out settings. I suppose that you didn't change (or specify) Client Binding, and your Server Binding Time-out limit is 1 minute. In order to make it bigger please change Server Binding on Inbound port in AX. Please find detailed guidance on my blog here: http://ax2012exceldataimport.blogspot.com/2012/08/dynamics-ax-2012-excel-add-in-issues.html

      Best Regards,
      /Alex

      Delete
  3. Hi, good article,

    do you if it is posible to get data from display methods with this tecnique?

    ReplyDelete
  4. I get an error at the end when it tries to read the rows:

    Cannot find table 0.

    Any ideas?

    ReplyDelete
  5. Does some one know how to pass login information, company info and partition key to the query service call ?
    I am trying to use below code but it is not working for me.
    https://code.msdn.microsoft.com/windowsdesktop/Walkthrough-of-using-73c07391

    ReplyDelete
  6. Mohammad, try calling the helpdesk, I'm sure they will do something for you.
    Guys, do you know where can I get microsoft erp software for a fair price and with some guidance, as I'm new to this subject? I'm thinking about changing the software at my company but I definitely need advice.

    ReplyDelete
  7. I don't think that the Microsoft Dynamic Ax is any better.. My personal favorite is Microsoft Dynamic 365.. it has some of the great features mate.. try it

    ReplyDelete
  8. Microsoft dynamics is better. everyone knows that but i like MicrosoftDynamic 365 more.

    ReplyDelete
  9. Microsoft dynamic is most software all around the world because of its excellent features.

    ReplyDelete