Walkthrough: Using the Query Service to Return a Flattened Dataset

Article
11/29/2021

Important

This content is archived and is not being updated. For the latest documentation, see Microsoft Dynamics 365 product documentation. For the latest release plans, see Dynamics 365 and Microsoft Power Platform release plans.

Applies To: Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

This walkthrough shows you how to call the query service and retrieve data from tables that implement table inheritance in Microsoft Dynamics AX in a flattened data table. This is accomplished by setting the ReturnFlatDataSet property on the query object. When this property is set to true, data from the base table and any derived tables that make up the query data sources is returned in a single DataTable in the DataSet. For more information about how to use the query service to return data from hierarchical tables, see Walkthrough: Using the Query Service to Query Tables in a Hierarchy.

This walkthrough illustrates the following tasks:

Add a reference to the query service.
Call the query service and return data from hierarchical tables.

Prerequisites

To complete this walkthrough you will need:

Microsoft Dynamics AX 2012
Visual Studio 2008
To have created the tables and query in the Hierarchical Tables, Data and Query section of the topic Creating Query Service Test Data.

Add Service References

In order to call the system services, you must first create a project in Visual Studio and add references to the services.

To add a reference to the query service and the metadata service

Open Visual Studio and create a new project by selecting File > New Project.
In the Project types tree, select Visual C# > Windows and then click the Windows Forms Application template.
Type a project name such as QueryServiceFlatDatasetTest and then click OK.
In Solution Explorer, right-click References and select Add Service Reference.
In the Address Field, enter the URL of the query service. For example, http://localhost:8101/DynamicsAx/Services/QueryService.
Click Go. In the Services tree, you should see the QueryService.
Type QueryServiceReference in the Namespace field and then click OK.
In Solution Explorer, right-click References and select Add Service Reference.
In the Address Field, enter the URL of the metadata service. For example, http://localhost:8101/DynamicsAx/Services/MetadataService.
Click Go. In the Services tree, you should see the AxMetadataService.
Type QueryServiceReference in the Namespace field and then click OK.

Call the Services and Retrieve the Data

Now we will write the code that calls the query service and returns the data specified by the TestPartiesInWA query. The code first calls the metadata service to return the TestPartiesInWA query object. Then the code specifies that a flattened data set should be returned by setting the ReturnFlatDataSet property on the query.

To call the services

In Solution Explorer, double-click Form1.cs to display the form in design mode.
Open the Toolbox and add a DataGridView control to Form1. By default, it is named DataGridView1.

In the Form1.cs file, add the following using statements.

using System;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Data;
using QueryServiceFlatDatasetTest.QueryServiceReference;
using QueryServiceFlatDatasetTest.MetadataServiceReference;
// Remove these usings after M3, only here for conversion code
using System.Runtime.Serialization;
using System.IO;

In the Form1_Load event, add the code to call the query service. The Form1.cs file should contain the following code.

using System;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Data;
using QueryServiceFlatDatasetTest.QueryServiceReference;
using QueryServiceFlatDatasetTest.MetadataServiceReference;
// Remove these usings after M3, only here for conversion code
using System.Runtime.Serialization;
using System.IO;

namespace QueryServiceFlatDatasetTest
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            DataSet dataSet;
            Paging paging = null;
            QueryServiceClient client = new QueryServiceClient();
            AxMetadataServiceClient metadataClient = new AxMetadataServiceClient();

            // Create the query object.
            MetadataServiceReference.QueryMetadata queryMetadata = 
                new MetadataServiceReference.QueryMetadata();

            // Get the query from the AOT using the metadata service.
            queryMetadata = metadataClient.GetQueryMetadataByName(new[] { "TestPartiesInWA" })[0];

            // Set the query property.
            queryMetadata.ReturnFlatDataSet = true;

            // This code is here to convert the queryMetadata object due to namespace conflicts w/mdsr and qsr - remove after M3
            using (MemoryStream memoryStream = new MemoryStream())
            {
                QueryServiceReference.QueryMetadata queryMetadataQS = new QueryServiceReference.QueryMetadata();

                DataContractSerializer mdsSerializer = new DataContractSerializer(typeof(MetadataServiceReference.QueryMetadata));
                DataContractSerializer qsSerializer = new DataContractSerializer(typeof(QueryServiceReference.QueryMetadata));
                mdsSerializer.WriteObject(memoryStream, queryMetadata);
                memoryStream.Seek(0, SeekOrigin.Begin);
                queryMetadataQS = (QueryServiceReference.QueryMetadata)qsSerializer.ReadObject(memoryStream);

                dataSet = client.ExecuteQuery(queryMetadataQS, ref paging);
            }

            // Call the query service with the query retrieved from the metadata service.
            //dataSet = client.ExecuteQuery(queryMetadata, ref paging);
            // Display the flattened dataset in the grid.
            dataGridView1.DataSource = dataSet.Tables["TestPartiesInWA"];
        }
    }
}

This code sets the ReturnFlatDataSet property and calls the query service. The data that is returned is contained in a single DataTable in the DataSet. The data is displayed in the grid by setting the grid data source to the DataTable object.