json - deserialize a datatable with a missing first column

Question

I'm attempting to serialize a .NET DataTable to a JSON file and then deserialize the JSON file back into a DataTable. Fairly straightforward I thought.

However, I have a table, 3 rows by 3 columns, each element is of type double. If any value in the first row is null, when JSON.Net deserializes the json file to a DataTable object, all values of the column that was null in the first row become strings.

To be clear, it is only if a value in the first row is null that this happens. If any values are null in any other row than the first, the remaining values in that column remain doubles.

1. If I replace the null with a double, everything works as expected (I can't do this in my case, however).

2. If I set NullValueHandling = NullValueHandling.Ignore, all values stay as doubles, except the first row now gets listed as the last row:

Example:

"Column2": 1.0,
"Column3": 1.1
},
{
   "Column1": 0.0,
   "Column2": 0.5,
   "Column3": 2.0
},

Becomes:

  "Column2": 1.0,
  "Column3": 1.1
},
{
  "Column2": 0.5,
  "Column3": 2.0,
  "Column1": 0.0
},

I need to be able to deserialize the JSON, keep the Columns in order, and not have null values in the first row cause all values in that row to become strings. I also need to keep the Column1 of the first row (in the case above) null - don't care if it is a null string or a DBNull.

Any thoughts? (My test code below..comment/uncomment NullValueHandling to see the problem)

        DataTable table = new DataTable("MyTable");
        table.Columns.Add("Column1", typeof(double));
        table.Columns.Add("Column2", typeof(double));
        table.Columns.Add("Column3", typeof(double));

        for (int i = 0; i < 10; i++) {
            if (i == 0)
                table.Rows.Add(null, 1.0, 1.1);
            else
               table.Rows.Add(0.0, 0.5, 2.0);
        }

        JsonSerializer serializer = new JsonSerializer();
        //serializer.TypeNameHandling = TypeNameHandling.All;
        serializer.NullValueHandling = NullValueHandling.Ignore;
        using (StreamWriter sw1 = new StreamWriter("1st.json"))
        using (JsonWriter writer1 = new JsonTextWriter(sw1))
        {
            writer1.Formatting = Formatting.Indented;
            serializer.Serialize(writer1, table);
        }

        DataTable newtable;
        using (StreamReader sr = new StreamReader("1st.json"))
        using (JsonReader reader = new JsonTextReader(sr))
        {
            newtable = (DataTable)serializer.Deserialize(reader, typeof(DataTable));
        }

        using (StreamWriter sw = new StreamWriter("3rd.json"))
        using (JsonWriter writer = new JsonTextWriter(sw))
        {
            writer.Formatting = Formatting.Indented;
            serializer.Serialize(writer, newtable);
        }

Answer 1

Json.NET is open source under the MIT License, so one possibility would be to create a modified version of its DataTableConverter to suit your needs. Its source code is available here.

First, create your own forked version of this class, called, say, JsonDefaultTypeDataTableConverter<T>. Modify the method GetColumnDataType to return typeof(T) for a null column value:

/// <summary>
/// Converts a <see cref="DataTable"/> to and from JSON.
/// Adapted from https://github.com/JamesNK/Newtonsoft.Json/blob/master/Src/Newtonsoft.Json/Converters/DataTableConverter.cs
/// </summary>
public class JsonDefaultTypeDataTableConverter<T>  : JsonConverter
{
    private static Type GetColumnDataType(JsonReader reader)
    {
        JsonToken tokenType = reader.TokenType;

        switch (tokenType)
        {
            case JsonToken.Integer:
            case JsonToken.Boolean:
            case JsonToken.Float:
            case JsonToken.String:
            case JsonToken.Date:
            case JsonToken.Bytes:
                return reader.ValueType;
            case JsonToken.Null:
            case JsonToken.Undefined:
                return typeof(T); // WAS typeof(string).
            case JsonToken.StartArray:
                CheckedRead(reader);
                if (reader.TokenType == JsonToken.StartObject)
                {
                    return typeof (DataTable); // nested datatable
                }

                Type arrayType = GetColumnDataType(reader);
                return arrayType.MakeArrayType();
            default:
                throw new JsonSerializationException(string.Format("Unexpected JSON token when reading DataTable: {0}", tokenType));
        }
    }
}

You'll also need to fix the calls to throw a JsonSerializationException at around line 232, for instance as follows:

    private static void CheckedRead(JsonReader reader)
    {
        if (!reader.Read())
        {
            throw new JsonSerializationException(string.Format("Unexpected end when reading DataTable."));
        }
    }

And, around line 114:

        if (reader.TokenType != JsonToken.StartArray)
        {
            throw new JsonSerializationException(string.Format("Unexpected JSON token when reading DataTable. Expected StartArray, got {0}.", reader.TokenType));
        }

Now, given that you know your table contains columns of double values, you can use it like this:

        JsonSerializer serializer = new JsonSerializer();
        //serializer.TypeNameHandling = TypeNameHandling.All;
        //serializer.NullValueHandling = NullValueHandling.Ignore; -- DO NOT USE THIS OPTION.
        serializer.Converters.Add(new JsonDefaultTypeDataTableConverter<double>());

Note that, in doing this, you're not modifying the internals of Json.NET itself, you're copying and modifying one of its set of standard converters for commonly used .Net types.

Update: full version here.