DataTable deserialization - column type mismatch




Although this is not a bug per se, it’s something we’ve encountered and haven’t found a “good” workaround for.

When passing a DataTable as an argument to a different process (either through LaunchWorkflowInteractive or InvokeWorkflow with Isolated=True), deserialized DataTable object has different Column.DataType values.

Issue 1:
This may cause a deserialization error if first row type is inferred as too specific for all values.
Error will be similar to:

Source: Launch Workflow Interactive: Newtonsoft.Json

Exception Type: JsonSerializationException

Newtonsoft.Json.JsonSerializationException: Error converting value “aaa” to type ‘System.Double’. Path ‘[1].Second’. —> System.FormatException: Input string was not in a correct format.[…]

Issue 2:
If first row value is missing, column type will be System.String.
This may cause errors when trying to use the values.

When DataTable is serialized, only row data is put into Json.
On deserialization column types for the DataTable are inferred from first row values.

Before passing as argument, copy DT schema, add a dummy row with only string values to make sure everything is treated as a string on deserialization. Add rest of the rows after. Pass the new DT as argument, discard the old one.
Requires parsing of values on reading from the DT.

Files attached below.

Example log output (without an error):

Notice that after deserialization:
2016.02.01 has been parsed as DateTime (was a String since it’s not recognized by Excel as valid DateTime)
All numeric values from column Second are now System.String, because first didn’t have a value.

To cause an error put a valid number in B2 and change any other row B value to text, or change any C value (but not first row) to something that can’t be parsed as valid DateTime. (10.4 KB)
(can’t attach excels, so zipping all 3 files - Run from InvokeExcelBug)

Any ideas how to make a better workaround and/or solve the issue would be welcome.
Only thing that comes to mind is to use a customized serialization and only passing already serialized objects, but that’s a very heavy handed approach that I’d rather avoid.


StoreProcedure with Datatable as a arugument
Passing Datatable using Launch Interactive workflow

Found an ok’ish workaround using binary formatter. Definitely not ideal, but at least pretty easy to add where needed.

Still a more robust solution would be preferred.

Attached examples (use any excel, shouldn’t matter what’s there).
DTBugWorkaround.xaml (13.4 KB)
InvokeDTBugWorkaround.xaml (12.8 KB)