Getting record from QueryRun in C#

This week I wrote some C# code using .NET Interop to X++ and I needed to get records from a QueryRun object. You surely know how to do it in AX (by calling QueryRun.get()), nevertheless I wanted to do it in a slightly better way. My method is rather short, but it may be difficult to grasp, if you’re not too familiar with C#. It utilizes several great features that don’t exist in X++.

I had several requirements.

I wanted to call the method in a natural way, such as queryRun.GetTable(…), therefore I built it as an extension method.

I wanted a descriptive exception if the table doesn’t exist in the query.

I wanted to get the right type, such as LogisticsPostalAddress instead of Common (all these are .NET proxy classes for X++ objects). It means that the method must be generic and returning the generic type. I also constrained the generic type only to classes extending Common (T GetTable() where T: Common).

Unfortunately queryRun.get() returns an instance of the Common class, not instances of particular tables. Fortunately it’s possible to create the right table proxy by passing the Common object to the constructor (e.g. LogisticsPostalAddress(common)). But we can’t use parametrized constructors of generic types, therefore I ask the caller to provide a method that constructs the table proxy from Common.

I also wanted to avoid hard-coded table names and I decided to simply take the name of the generic type.

This is my implementation:

public static T GetTable(this QueryRun queryRun, Func<Common, T> creator, int occurence = 1)
    where T : Common
{
    string tableName = typeof(T).Name;
    int tableId = Global.tableName2Id(tableName);
    Common common;
 
    try
    {
        common = queryRun.get(tableId, occurence);
    }
    catch (XppException)
    {
        throw new InvalidOperationException(String.Format("The query does not contain {0}", tableName));
    }
 
    return creator(common);
}

And this is how I use it, passing a lambda function as the argument:

var address = queryRun.GetTable(c => new LogisticsPostalAddress(c));

The C# compiler is smart enough to infer the return type and the type of the address variable just from the type returned by my lambda function.

Writing code like this is real fun!

The value 1 is not found in the map

We were getting the error, “The value 1 is not found in the map”, in one of our rather complex processes in AX 2012. It occurred only sometimes, which made debugging difficult, but finally I managed to track it down. The cause lies in something that many people use, therefore it’s possible that you will, or already did, run into the same problem.

I found the issue in AX 2012 R2 CU7 and reproduce it also in AX 2012 R3.

The problem can be demonstrated by running the following piece of code. It creates a progress bar, waits for some time and calls incCount() to update the progress. The last call to incCount() throws the error.

SysOperationProgress progress;
#AviFiles
 
progress = SysOperationProgress::construct();
progress.setAnimation(#AviUpdate);
progress.setCaption("Some caption");
 
sleep((progress.updateInterval() + 1) * 1000);
 
// The progress form gets initialized here.
progress.incCount();
 
// Wait for more than 10 seconds
sleep(11000);
 
progress.incCount();

You see that there is nothing suspicious going on – I just simulate a process that runs for some time and occasionally updates the progress. I’ll explain the important of waiting a little bit later.

If you debug the code, you’ll find that the error is thrown at line 33 of SysOperationProgressBase.updateTime(). It’s this line:

r3 = totalValue.lookup(1)-progress;

It should be clear what happens in this code – it tries to get the value for the key 1 from totalValue map but it doesn’t exist (→ The value 1 is not found in the map).

Why the map doesn’t contain the key? It occurs that the initialization is done in setTotal(), which isn’t called in the code above. Therefore if you always call setTotal() when creating SysOperationProgress, you won’t ever get into this problem. Nevertheless you have to do it even if you actually don’t know the total – then you have to set it to zero:

progress.setTotal(0);

It’s also worth noting that setTotal() gets called if you create the instance by SysOperationProgress::newGeneral(), which developers often do anyway.

Nevertheless why the error occurs only sometimes? The SysOperationProgressBase class usually checks whether the key exists in the map before trying to use it. The exception is – obviously – in updateTime() and several conditions must be met before this piece of code gets called. The most tricky one is this:

if (time-laptime > #tenSeconds)

It must be ten seconds since the last update and if it took less, this code doesn’t get executed and no error is thrown. Therefore we got this error as a side effect of something slowing the system down. Now it’s also clear why my script waits for more than ten seconds before calling incCount() again.

Always calling setTotal() is a relatively easy workaround, but it would be better if SysOperationProgress handled the problem in a better way. It should either accept that the value 1 doesn’t have to exist in totalValue (and check if it’s there before using it) or it should throw an error to inform the developer that he’s trying to use an object that hasn’t been properly initialized.

This blog post doesn’t suggest that The value 1 is not found in the map is always caused by a progress bar – it may refer to any other map. Nevertheless it’s one of things that you may want to check.

Connection string for LINQ to SQL

In my article about connecting to external databases with the help of LINQ to SQL, I already mentioned how to parametrize which database to use, because you’ll likely use different databases for development, test and live environments.

I wrote: In real implementations, I usually store DB server and DB name in AX database, create a connection string from them (with the help of a connection string builder) and pass the connection string to data context’s constructor.

Let’s look at what it means in practice.

First of all, let me remind you the .NET class:

public class Data
{
    public void Import()
    {
        TestDBDataContext db = new TestDBDataContext("); 
        …
    }
}

Here I create the data context without any parameters, therefore it connects to the database that I used when designing the library in Visual Studio. It’s also possible to change it in the designer.

A common way for specifying connection details is using connection strings like this:

Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=True

This approach is supported even by LINQ to SQL and you could simply pass a connection string to the constructor of a data context class:

new TestDBDataContext("Data Source=.;Initial Catalog=TestDB;Integrated Security=True");

I could construct such a string by myself, but I’m not interested in details of the syntax, in concatenating strings and so on. A connection builder will deal with it for me. Therefore I’ll add a field to the Data class holding the connection builder instance:

SqlConnectionStringBuilder connStringBuilder;

I also have to add

using System.Data.SqlClient;

on the top of the file, because that’s the namespace containing SqlConnectionStringBuilder class.

I have to decide what I want to include in my connection string and usually a database server name and a database name are enough, therefore these two values will be my parameters. I add a constructor to the Data class that accepts these parameters, creates a connection string builder and set its properties:

public Data(string dbServer, string dbName)
{
    //TODO: parameter validation
 
    connStringBuilder = new SqlConnectionStringBuilder();
    connStringBuilder.DataSource = dbServer;
    connStringBuilder.InitialCatalog = dbName;
    connStringBuilder.IntegratedSecurity = true;
}

Now we can get the connection string at any time from ConnectionString property of the builder, therefore let’s pass it to the data context:

new TestDBDataContext(connStringBuilder.ConnectionString);

Put all together, the class now looks like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
 
namespace ExtSystem
{
    public class Data
    {
        SqlConnectionStringBuilder connStringBuilder;
 
        public Data(string dbServer, string dbName)
        {
            //TODO: parameter validation
 
            connStringBuilder = new SqlConnectionStringBuilder();
            connStringBuilder.DataSource = dbServer;
            connStringBuilder.InitialCatalog = dbName;
            connStringBuilder.IntegratedSecurity = true;
        }
 
        public void Import()
        {
            TestDBDataContext db = new TestDBDataContext(connStringBuilder.ConnectionString);}
    }
}

Rebuild the solution and open Dynamics AX (restart the client if it was running).

Our test job in AX contained the following code:

new ExtSystem.Data().Import();

It doesn’t compile anymore, because we have to specify the arguments (database server + database name). The following implementation connects to exactly the same database as before.

new ExtSystem.Data('.', 'TestDB').Import();

I can easily prove that the parameters are utilized by specifying an invalid database name such as WrongDB. I’ll get an error saying “Cannot open database “WrongDB” requested by the login”.

Now the only remaining thing is to create fields in AX database for the parameters and use them when creating the Data class.

Also note that if I wanted, I could select the data from AX database directly in the .NET class (by creating a proxy to the table etc.). But I think that it would be an unnecessary coupling between the library and the table. Using parameters is more flexible and it also make testing easier.

Connection to external database

You sometimes need to connect from Dynamics AX to another database and read or write data there. The usual approach uses OdbcConnection class, as described on MSDN in How to: Connect to an External Database from X++ Code [AX 2012]. Although it definitely is a possible solution (I used it already in AX 3.0), it has so many problems that I almost never use it these days.

My intention is to show one of possible alternatives that solves many of these issues. Although the core idea can be used in AX from version 4.0, this blog post uses AX 2012 and depends on several features that don’t exist in older version.

First of all, look at how you’re supposed to construct the query for OdbcConnection:

sql = "SELECT * FROM MYTABLE WHERE FIELD = "
            + criteria
            + " ORDER BY FIELD1, FIELD2 ASC ;";

and how to get results:

print resultSet.getString(1);
print resultSet.getString(3);

(This is taken from the sample code on MSDN.)

You have to define the query as a simple string, therefore you have to write SQL code by yourself, you won’t get any IntelliSense, compile-type control or anything like that. You have to execute your SQL against the target database to find errors such as misspelled field names.

You also depend on column indexes when accessing data, which becomes cumbersome very quickly and it’s easy to break. For example, notice that the query above selects all fields. What will happen if I add another field, e.g. the query starts returning ANewField, Field1 and Field2 instead of Field1 and Field2? Column indexes now refers to different fields and it again won’t be found until actually running the code.

It’s simply unpleasant for development and horrible for maintenance.

Wouldn’t it be better if we could simply refer to field as if we do in normal X++ development, get compile-type control and so on? Good news – we can!

Ssms_Table

Before we actually start, let me show you the table that will simulate my external data. It has four fields: ID and Name that I’m going to import to AX, Status used for tracking what needs to be processed and UpdateTime storing the data and time of the update.

It can be created by the following script:

CREATE TABLE [DataForAX](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](30) NOT NULL,
    [Status] [tinyint] NOT NULL,
    [UpdateTime] [datetime] NULL,
    CONSTRAINT [PK_DataForAX] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )
)

You can also put some test data there:

INSERT INTO DataForAX
VALUES 	(N'Croup', 0, null),
	(N'Vandemar', 0, null)

Note that it’s important for the table to have a primary key, otherwise we wouldn’t be able to update the data.

AxObjects

Let’s also prepare some objects in AX. We’ll need a table storing the data and because the Status field is an integer representing various states, we can very naturally model it as an X++ enum.

Status field values:

  • 0 = Ready
  • 1 = Imported
  • 2 = Failed

Now start Visual Studio, because most of our work will happen there. Create a new C# class library, add it to AOT and drag the table and the enum from Application Explorer to your project. We’ll need them later.

Open Server Explorer (View > Server Explorer) and create a connection to your database. I’m using a SQL Server database on the current machine, but you can use ODBC, connect to remote servers or anything you need.

ServerExplorerAddConnection

ChooseDataSource

AddConnection

Now you should see your tables directly in Server Explorer in Visual Studio:

ServerExplorerTable

Right-click your project in Solution Explorer, choose Add > New Item… and find LINQ to SQL Classes. Set the name (TestDB) and click Add.

AddLinqClass

A database diagram will be added to your project and its design surface will open. Drag the table from Server Explorer to the design surface.

DesignSurface

It will not only render the table in the designer, it will also generate a class representing the table (and a few other things needed for LINQ to SQL). If you want, you can open TestDB.designer.cs and verify that there is a class for DataForAX table:

public partial class DataForAX : INotifyPropertyChanging, INotifyPropertyChanged
{
    private int _ID;
    private string _Name;
    private byte _Status;
    private System.Nullable _UpdateTime;
 
    …
}

It’s a more advanced topic, but notice that it’s a partial class. It allows you to extend the class without meddling with code generated by the designer. Extending the class is useful in many scenarios, but we don’t need it today.

There is one thing we should change, though – the type of the Status field is byte while would like to use our enum from AX. Go to the designer, open properties of the Status field and type the name of the enum (ExtRecordStatus) into the Type field.

StatusFieldProperties

MapToEnum

It might look as nothing special, but I think it’s pretty cool. We’re mapping a field from an external database to an X++ enum (albeit through a proxy) and we’ll be able to use in LINQ queries, when assigning values to the field and so on.

Now add a new class to your project, call it Data, for example, make it public and add the Import() method as shown below:

public class Data
{
    public void Import()
    {
        TestDBDataContext db = new TestDBDataContext();
 
        var query = from d in db.DataForAXes
                    where d.Status == ExtRecordStatus.Ready
                    select d;
 
        foreach (DataForAX record in query)
        {
            ExtTable axTable = new ExtTable()
            {
                ID = record.ID,
                Name = record.Name
            };
 
            if (axTable.ValidateWrite())
            {
                axTable.Insert();
                record.Status = ExtRecordStatus.Imported;
            }
            else
            {
                record.Status = ExtRecordStatus.Failed;
            }
 
            record.UpdateTime = DateTime.UtcNow;
        }
 
        db.SubmitChanges();
    }
}

Let me explain it a little bit. Firstly we create a data context:

TestDBDataContext db = new TestDBDataContext();

It’s a class that defines where to connect, track changes and so on. In real implementations, I usually store DB server and DB name in AX database, create a connection string from them (with the help of a connection string builder) and pass the connection string to data context’s constructor.

Then we create the LINQ query.

var query = from d in db.DataForAXes
            where d.Status == ExtRecordStatus.Ready
            select d;

This simple example can’t show the full power of LINQ, but it’s obvious that we’re not building SQL by ourselves; we use strongly-typed objects instead. Notice also how we filter the data by the X++ enum.

The foreach loop fetches data from database and fill it to an instance of DataForAX class. Later we simply access fields by name – nothing like resultSet.getString(3) anymore.

This code:

ExtTable axTable = new ExtTable()
{
    ID = record.ID,
    Name = record.Name
};
 
if (axTable.ValidateWrite())
{
    axTable.Insert();
}

sets data to an AX table buffer (ExtTable is the table we created in AX at the very beginning) and calls its validateWrite() and insert() methods as usual. I typically pass records back into AX and process them there, but inserting them to AX database from here works equally well.

Then the code changes values of Status and UpdateTime fields. Finally db.SubmitChanges() writes changed data to the external database. We could also insert or delete records in the external database if needed.

That completes our library; the remaining step is calling it from AX. Open project properties and choose where to deploy the library. We’ll run it from a job for demonstration, therefore we have to deploy it to the client.

DeployToClient

Rebuild the project and restart your AX client, if it was running.

Create a job in AX and call the Import() method from there:

try
{
    new ExtSystem.Data().Import();
}
catch (Exception::CLRError)
{
    throw error(AifUtil::getClrErrorMessage());
}

That’s all what we need to do here, because reading the data, inserting them to AX database and updating the external database is already handled by the .NET library.

The whole application has just a few lines of code, doesn’t contain any strings with SQL commands, it doesn’t depend on the order of columns, the compiler checks whether we use data types correctly and so on.

It may require to learn a few new things, but I strongly believe that AX developers should look more at how things are done these days instead of sticking to old ways and low-level APIs such as the OdbcConnection class. Many great frameworks and language features waits for you to use them to increase your productivity and develop things hardly achievable in X++ – and AX 2012 made it easier than ever. It’s would be a pity not to benefit from all the progress done in recent years.

I couldn’t really discuss LINQ or the database designer while keeping a sensible length of this post. Fortunately you’ll find a plenty of resources about these things. LINQ to SQL also isn’t the only way – maybe ADO.NET would be more suitable for your particular project. Nevertheless I hope I managed to show you how to use these frameworks from AX – and that it’s not difficult at all.

AX management shell – multiple models

Even if you’re not familiar with PowerShell, you probably don’t have much trouble with using AX management cmdlets. You just call the right command, set some parameters and it’s done.

# Just for example
Export-AXModel -Model "MyModel" -File "c:\my.axmodel"

If you don’t know the parameters, you’ll simply use Get-Help or some of its aliases, such as man:

man Export-AXModel -Detailed

But if you don’t know PowerShell well, you may not know how, say, to work with multiple models at once. This is often useful, therefore it’s worth showing how to do it.

Before we look at any code, ensure yourself that you have a development environment such as PowerShell ISE or PowerGUI. These applications will help you saving your scripts, discovering commands and parameters, setting breakpoints, reviewing variables and many other things. No need to do everything directly in console!

But there is a problem – if you try to use AX cmdlets outside AX management shell, they won’t be recognized. Fortunately the fix is easy – add the following line to your script (including the dot at the beginning):

. 'C:\Program Files\Microsoft Dynamics AX\60\ManagementUtilities\Microsoft.Dynamics.ManagementUtilities.ps1'

You can also run it directly in console or even to add it to your profile, so it’s loaded automatically every time you run PowerShell.

Let’s warm up with a few simple commands (using PowerShell 3.0 syntax):

# Lists all AX modules - nothing new here
Get-AXModel
 
# Lists just model names and layers, to make things more readable
Get-AXModel | select Name, Layer
 
# Lists models in a specific layer
Get-AXModel | ? Layer -eq USR

The “| ” symbol is a pipe and it passes the output of one command to the input of another command. It would be great if could use it with AX cmdlets. e.g. for passing a list of models to Export-AXModel. Unfortunately it’s not supported and AX cmdlets also don’t accept arrays as arguments, therefore we have to call each command for a single model at a time. It’s not that bad as it might sound, because we can use a loop to sequentially execute a command for every element in an array.

# Let's set some parameters first 
$toDir = 'C:\Models'
$layer = 'USR'
 
# Export all models in a given layer to file
Get-AXModel | ? Layer -eq $layer | % { Export-AXModel -Model $_.Name -File "$toDir\$($_.Name).axmodel" }
 
# Uninstall all modules from a given layer
Get-AXModel | ? Layer -eq $layer | % { Uninstall-AXModel -Model $_.Name -NoPrompt }

It might look a little bit cryptic at first, but you see it’s rather simple and you shouldn’t have problems to apply the same approach in your own scripts. It’s much faster to write (or copy) something like this than typing, running and waiting for several individual commands.