Dynamics 365 Business Central 2022 Wave 2: introducing the new DataTransfer data type

I’ve talked some days ago about some UI improvements in the Dynamics 365 Business Central 2022 Wave 2 release (version 21). You can read the details here. There’s another quite hidden feature that I love a lot in this new release (not yet documented) and it’s related to the availability of a new construct for writing faster upgrade code.

When refactoring an app, it’s common to move a field from one table to another or to copy all data from one table to another table. With this new feature available on version 21, the developer can copy the data from the old field to the new field, or copy the data from the old table to the new table, in a set-based manner without the need to write a loop.

What does that mean in detail?

Starting from Dynamics 365 Business Central 2022 Wave 2 release (version 21), a new data type called DataTransfer is available. This new data type is available only on upgrade codeunits at the monent (codeunits with SubType = Upgrade) and it permits to move data quickly.

How this new data type works?

To show the new data type usage, let’s consider the following scenario. I have a V1 extension with a table like the following:

This table contains data from purchase and sales documents and here the Type field (enum with values like Sale and Purchase) is part of a primary key that we want to change.

In a new extension version, we want to obsolete this old table and replace it with a new table that contains only the sales records from the old table. The new table is defined as follows:

Here the primary key is changed and we have also a new field called Start Date that during the upgrdae we want to initialize with the current date.

To perform the data upgrade, we define an Upgrade codeunit and on this codeunit we can write something like the following:

This code uses the new DataTransfer data type to perform the data transfer upgrade process.

With the SetTables(SourceTable: Integer, DestinationTable: Integer) procedure we define the source and destination tables for the data transfer.

Then we use the AddSourceFilter(SourceField: Integer, String: Text, [Value: Joker, …]) method to add the filter we want to apply on the source table for the data transfer.

The DataTransfer construct has also a method called AddJoin(SourceField: Integer, DestinationField: Integer) that permits you to adds a field pair to be used to create a join condition which determines which rows to transfer, optional for same table transfers.

As said before, in the data upgrade process we want to initialize the Start Date field on the destination table with the curent date. To do that, we can use the AddConstantValue(Value: Joker, DestinationField: Integer) method that permits to specify the value to set in the given field in the destination table.

When the rules are set, we can start the data transfer by calling the CopyFields method. This method copies the fields specified in AddFields with filters from AddSourceFilter, and the join conditions from AddJoins in one bulk operation in SQL.

For the data transfer, you can also use the CopyRows() method, that copies the rows from the source table to the destination table with the fields selected with AddFields and the filters applied with AddSourceFilter, in one bulk operation in SQL.

Why is this new data type so cool?

Because it generates and runs one single set-based SQL query instead of performing a loop + insert or update operation, and essentially this means one word: performance! With this new construct, data upgrades are from 50x to 90x faster now (not 50%, but 50x… yes!).

The new Dynamics 365 Business Central 2022 Wave 2 release (version 21) base application uses this new construct a lot on internal upgrade procedures:

Here an example of usage in the standard base application:

and here an example with joins:

When writing upgrade code in your AL extensions, I absolutely suggest to use this new data type starting from now. Your upgrade processes will be faster.

The common question that I think could pop out now is: can I use the new DataTransfer data type also in my standard codeunits for performing faster data transfer? At the moment the answer is no, it’s executed only in upgrade codeunits. I think that is absolutely interesting to have the possibility to have it also on standard processes for performing a fast data movement between tables and I’m quite sure that the team under this feature will start thinking on this for next releases…