In this post I'd like to dig a bit deeper. I've received some emails and messages asking for examples on how to use the dynamic queries. Well, I will do you one better and show you how to use both interchangeably. We will get a static query from the AOT, change a few things on it, and then execute it. All with the standard system services!
So, we will first use the metadata service to retrieve the AOT query from AX. In this example, I will be using Visual Studio 2012, but you should be able to run through this using Visual Studio 2010 just fine. We start by creating a new Console Application project. Again, we'll be focused on using the system services here, but feel free to use a WPF app instead of a console app (you can merge with my previous article for example). I'm in Visual Studio 2012 so I'm using .NET 4.5, but you can use .NET 4.0 or 3.5 as well.
Next, right-click on the References node in your solution explorer and select Add Service Reference.
This will bring up a dialog where you can enter the URL of your Meta Data Service. Enter the URL and press the GO button. This will connect and grab the WSDL for the metadata service. Enter a namespace for the service reference proxies (I named it 'AX' - don't add "MetaData" in the name, you'll soon find out why). I also like to go into advanced and change the Collection Type to List. I love Linq (which also works on Arrays though) and lists are just nicer to work with I find. Click OK on the advanced dialog and OK on the service reference dialog to create the reference and proxies.
Ok, now we are ready to code! We'll get metadata for a query (we'll use the AOT query "CustTransOpen" as an example) and print the list of datasources in this query, and print how many field ranges each datasource has. This is just to make sure our code is working.
static AX.QueryMetadata GetQuery(string name)
{
AX.AxMetadataServiceClient metaDataClient = new AX.AxMetadataServiceClient();
List queryNames = new List();
queryNames.Add(name);
var queryMetaData = metaDataClient.GetQueryMetadataByName(queryNames);
if (queryMetaData != null && queryMetaData.Count() > 0)
return queryMetaData[0];
return null;
} Very simple code, we create an instance of the AxMetadataServiceClient and call the GetQueryMetadataByName operation on it. Note that we have to convert our query's name string into a list of strings because we can fetch metadata for multiple queries at once. Similarly, we have to convert the results returned from a list back into 1 query metadata object (assuming we got one). We'll return null if we didn't get anything back. If you left the service reference Collection Type to Array, either change this code to create an array of strings for the query names instead of a List, or you can actually right-click the service reference, select "Configure Service Reference" and change the Collection Type to List at this point.
We'll make a recursive method to traverse the datasources and their children, and print out the ranges each datasource has, like so:
static void PrintDatasourceRanges(AX.QueryDataSourceMetadata datasource)
{
Console.WriteLine(string.Format("{0} has {1} ranges", datasource.Name, datasource.Ranges.Count()));
foreach (var childDatasource in datasource.DataSources)
{
PrintDatasourceRanges(childDatasource);
}
}I'm using a console application so I'm using Console.WriteLine, and I have a Main method for the rest of my code. If you're doing a WPF app, you may want to consider outputting to a textbox, and adding the following code somewhere it's relevant to you, for example under the clicked event of a button. Here we call our GetQuery method, and then call the PrintDatasourceRanges for each datasource.
static void Main(string[] args)
{
AX.QueryMetadata query = GetQuery("CustTransOpen");
if (query != null)
{
foreach (var datasource in query.DataSources)
{
PrintDatasourceRanges(datasource);
}
}
Console.ReadLine();
}Note that we have a Console.ReadLine at the end, which will prevent the Console app to close until I press the ENTER key. When we run this project, here's the output:
Ok, so we're getting the query's metadata. Note that the classes used here (QueryMetadata, QueryMetadataRange etc) are the exact same classes the query service accepts. However, if we add a new service reference for the query service, AX will ask for a new namespace and not re-use the objects already created for the metadata service. If we give it a new namespace we can't pass the query object received from the metadata back into the query service. Of course I wouldn't bring this up if there wasn't a solution!
In your solution explorer, right-click on your project and select "Open Folder in File Explorer".
In the explorer window, there will be a folder called "Service References". Inside you'll find a sub-folder that has the name of the namespace you gave your service reference. In my case "AX". The folder contains XML schemas (xsd), datasource files, the C# files with the proxy code, etc. One particular file is of interest to us: Reference.svcmap. This file contains the URL for the service, the advanced settings for the proxy generation, etc (you can open with notepad, it's an XML file). But the node called MetadataSources contains only one subnode, with the service URL. If we add a second node with a reference to our second URL, we can regenerate the proxies for both URLs within the same service reference, effectively forcing Visual Studio to reuse the proxies across the two URLs. So, let's change the XML file as follows. Note that XML is case sensitive, and obviously the tags must match so make sure you have no typos. Also make sure to increment the SourceId attribute.
Original:
New:
Again, I can't stress enough, don't make typos, and make sure you use upper and lower case correctly as shown. Now, save the Reference.svcmap file and close it. Back in Visual Studio, right-click your original service reference, and click "Update Service Reference".
FYI, if you select "Configure Service Reference" you'll notice that compared to when we opened this from the Advanced button upon adding the reference, there is now a new field at the top that says "Address: Multiple addresses (editable in .svcmap file)").
If you made no typos, your proxies will be updated and you are now the proud owner of a service reference for metadata service and query service, sharing the same proxies (basically, one service reference with two URLs). First, let's create a method to execute a query.
static System.Data.DataSet ExecuteQuery(AX.QueryMetadata query)
{
AX.QueryServiceClient queryClient = new AX.QueryServiceClient();
AX.Paging paging = new AX.PositionBasedPaging() { StartingPosition = 1, NumberOfRecordsToFetch = 5 };
return queryClient.ExecuteQuery(query, ref paging);
}
Note that I use PositionBasedPaging to only fetch the first 5 records. You can play around with the paging, there are different types of paging you can apply. So now for the point of this whole article. We will change our Main method to fetch the query from the AOT, then execute it. For good measure, we'll check if there is already a range on the AccountNum field on CustTable, and if so set it. Here I'm doing a little Linq trickery: I select the first (or default, meaning it returns null if it can't find it) range with name "AccountNum". If a range is found, I set its value to "2014" (a customer ID in my demo data set). Finally I execute the query and output the returned dataset's XML to the console.
static void Main(string[] args)
{
AX.QueryMetadata query = GetQuery("CustTransOpen");
if (query != null)
{
var range = (from r in query.DataSources[0].Ranges where r.Name == "AccountNum" select r).FirstOrDefault();
if (range != null)
{
range.Value = "2014";
}
System.Data.DataSet dataSet = ExecuteQuery(query);
Console.WriteLine(dataSet.GetXml());
}
Console.ReadLine();
}And there you have it. We retrieved a query from the AOT, modified the query by setting one of its range values, then executed that query. Anything goes here, the metadata you retrieve can be manipulated like you would a Query object in X++. You can add more datasources, remove datasources, etc.
For example, before executing the query, we can remove all datasource except "CustTable". Also make sure to clear order by fields since they may be referencing the other datasources. Again using some Linq trickery to achieve that goal.
// Delete child datasource of our first datasource (custtable)
query.DataSources[0].DataSources.Clear();
// Remove all order by fields that are not for the CustTable datasource
query.OrderByFields.RemoveAll(f => f.DataSource != "CustTable");
