DataTable deserialization - column type mismatch

datatable
serialization

#1

Hi,

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

Issues:
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.

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

Workaround:
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.

Repro:
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.

DataTableDeserialization.zip (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.

Regards.


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

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)