Retrieving date/datetime from ADO.NET into a record field

In Dynamics AX, you can use ADO.NET to retrieve data from external data sources.  This is quite easy to do for text and numeric data types, but not for Date or DateTime data types.

If you try a simple assign, you’ll end up throwing an exception:

blocks.Field1 = reader.get_Item( "REGISTRATIONDATE" );

Error executing code: Wrong argument types in variable assignment.

Using SqlDataReader.GetDataTypeName() to check the data type, it shows DateTime2.  The destination field is a datetime field, but this problem as well as the solution happens even for plain date fields.

So, we might try to use SqlDataReader.GetDateTime() instead, but that results in exactly the same error.

The problem is happening because what is returned by the get_Item() and GetDateTime() is actually a CLR object, not a native X++ object.  As is documented for the CLR Interop layer in AX, AX can’t chain operations on CLR objects, and what is happening above is actually an implicit conversion feeding into an assignment operation.

To work around this, we need to stuff the return value from get_Item() into a local variable, and then assign that variable into the record field instead.  This is called a bounce variable.

utcDateTime dateTimeTmp;

...

dateTimeTmp = reader.get_Item( "REGISTRATIONDATE" );
blocks.Field1 = dateTimeTmp;

A bit non-intuitive, because we’re still dealing with an implicit conversion feeding into an assignment operation, it just happens to work with a local variable.

This same problem is also happening for decimal/numeric fields and int-values to be loaded into an enum field.  Again, the solution is the same: Create a local bounce for the value.