c# - DataTable反序列化后DateTime列类型变为String类型

Question

我有一个包含两列的数据表。发货日期(日期时间)和计数(整数)。在反序列化字符串后，我注意到如果第一个 itemarray 值为空，则 ShipmentDate 的类型变为字符串。

检查下面的例子。除了第一个数组项外，两个 json 字符串都具有相同的数据。

string jsonTable1 = "[{\"ShipmentDate\":null,\"Count\":3},{\"ShipmentDate\":\"2015-05-13T00:00:00\",\"Count\":13},{\"ShipmentDate\":\"2015-05-19T00:00:00\",\"Count\":1},{\"ShipmentDate\":\"2015-05-26T00:00:00\",\"Count\":1},{\"ShipmentDate\":\"2015-05-28T00:00:00\",\"Count\":2}]";
string jsonTable2 = "[{\"ShipmentDate\":\"2015-05-13T00:00:00\",\"Count\":13},{\"ShipmentDate\":null,\"Count\":3},{\"ShipmentDate\":\"2015-05-19T00:00:00\",\"Count\":1},{\"ShipmentDate\":\"2015-05-26T00:00:00\",\"Count\":1},{\"ShipmentDate\":\"2015-05-28T00:00:00\",\"Count\":2}]";

DataTable tbl1 = Newtonsoft.Json.JsonConvert.DeserializeObject<DataTable>(jsonTable1);
DataTable tbl2 = Newtonsoft.Json.JsonConvert.DeserializeObject<DataTable>(jsonTable2);

Console.WriteLine(tbl1.Columns["ShipmentDate"].DataType);
Console.WriteLine(tbl2.Columns["ShipmentDate"].DataType);

在我的场景中，第一项数组的 ShipmentDate 可以为 null，并且通过将其转换为字符串类型会产生问题。

我有一种情况，其中数据表的模式是动态的。我无法创建强类型类。

Answer 1

这里的基本问题是 Json.NET 的 DataTableConverter推断每个 DataColumn.DataType通过查看出现在第一行 中的标记值。它以这种方式工作，因为它流式传输表的 JSON，而不是将整体加载到中间 JToken 中。等级制度。虽然流式传输可通过减少内存使用提供更好的性能，但这意味着第一行中的 null 值可能会导致列的类型不正确。

这是一个在 stackoverflow 上不时出现的问题，例如在问题 deserialize a datatable with a missing first column 中。 .在那种情况下，提问者事先知道列类型应该是double。在您的情况下，您声明数据表的架构是动态的，因此无法使用该答案。然而，对于那个问题，由于 Json.NET 在 MIT License 下是开源的, 可以创建其 DataTableConverter 的修改版本具有必要的逻辑。

事实证明，通过记住具有不明确数据类型的列，然后在可以确定正确类型时将这些列替换为正确类型的列，可以在保留流行为的同时正确设置列类型:

/// <summary>
/// Converts a <see cref="DataTable"/> to and from JSON.
/// </summary>
public class TypeInferringDataTableConverter : Newtonsoft.Json.Converters.DataTableConverter
{
    // Adapted from https://github.com/JamesNK/Newtonsoft.Json/blob/master/Src/Newtonsoft.Json/Converters/DataTableConverter.cs
    // Original license: https://github.com/JamesNK/Newtonsoft.Json/blob/master/LICENSE.md

    /// <summary>
    /// Reads the JSON representation of the object.
    /// </summary>
    /// <param name="reader">The <see cref="JsonReader"/> to read from.</param>
    /// <param name="objectType">Type of the object.</param>
    /// <param name="existingValue">The existing value of object being read.</param>
    /// <param name="serializer">The calling serializer.</param>
    /// <returns>The object value.</returns>
    public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
    {
        if (reader.TokenType == JsonToken.Null)
        {
            return null;
        }

        DataTable dt = existingValue as DataTable;

        if (dt == null)
        {
            // handle typed datasets
            dt = (objectType == typeof(DataTable))
                ? new DataTable()
                : (DataTable)Activator.CreateInstance(objectType);
        }

        // DataTable is inside a DataSet
        // populate the name from the property name
        if (reader.TokenType == JsonToken.PropertyName)
        {
            dt.TableName = (string)reader.Value;

            reader.ReadAndAssert();

            if (reader.TokenType == JsonToken.Null)
            {
                return dt;
            }
        }

        if (reader.TokenType != JsonToken.StartArray)
        {
            throw JsonSerializationExceptionHelper.Create(reader, "Unexpected JSON token when reading DataTable. Expected StartArray, got {0}.".FormatWith(CultureInfo.InvariantCulture, reader.TokenType));
        }

        reader.ReadAndAssert();

        var ambiguousColumnTypes = new HashSet<string>();

        while (reader.TokenType != JsonToken.EndArray)
        {
            CreateRow(reader, dt, serializer, ambiguousColumnTypes);

            reader.ReadAndAssert();
        }

        return dt;
    }

    private static void CreateRow(JsonReader reader, DataTable dt, JsonSerializer serializer, HashSet<string> ambiguousColumnTypes)
    {
        DataRow dr = dt.NewRow();
        reader.ReadAndAssert();

        while (reader.TokenType == JsonToken.PropertyName)
        {
            string columnName = (string)reader.Value;

            reader.ReadAndAssert();

            DataColumn column = dt.Columns[columnName];
            if (column == null)
            {
                bool isAmbiguousType;

                Type columnType = GetColumnDataType(reader, out isAmbiguousType);
                column = new DataColumn(columnName, columnType);
                dt.Columns.Add(column);

                if (isAmbiguousType)
                    ambiguousColumnTypes.Add(columnName);
            }
            else if (ambiguousColumnTypes.Contains(columnName))
            {
                bool isAmbiguousType;
                Type newColumnType = GetColumnDataType(reader, out isAmbiguousType);
                if (!isAmbiguousType)
                    ambiguousColumnTypes.Remove(columnName);
                if (newColumnType != column.DataType)
                {
                    column = ReplaceColumn(dt, column, newColumnType, serializer);
                }
            }

            if (column.DataType == typeof(DataTable))
            {
                if (reader.TokenType == JsonToken.StartArray)
                {
                    reader.ReadAndAssert();
                }

                DataTable nestedDt = new DataTable();

                var nestedUnknownColumnTypes = new HashSet<string>();

                while (reader.TokenType != JsonToken.EndArray)
                {
                    CreateRow(reader, nestedDt, serializer, nestedUnknownColumnTypes);

                    reader.ReadAndAssert();
                }

                dr[columnName] = nestedDt;
            }
            else if (column.DataType.IsArray && column.DataType != typeof(byte[]))
            {
                if (reader.TokenType == JsonToken.StartArray)
                {
                    reader.ReadAndAssert();
                }

                List<object> o = new List<object>();

                while (reader.TokenType != JsonToken.EndArray)
                {
                    o.Add(reader.Value);
                    reader.ReadAndAssert();
                }

                Array destinationArray = Array.CreateInstance(column.DataType.GetElementType(), o.Count);
                Array.Copy(o.ToArray(), destinationArray, o.Count);

                dr[columnName] = destinationArray;
            }
            else
            {
                object columnValue = (reader.Value != null)
                    ? serializer.Deserialize(reader, column.DataType) ?? DBNull.Value
                    : DBNull.Value;

                dr[columnName] = columnValue;
            }

            reader.ReadAndAssert();
        }

        dr.EndEdit();
        dt.Rows.Add(dr);
    }

    static object RemapValue(object oldValue, Type newType, JsonSerializer serializer)
    {
        if (oldValue == null)
            return null;
        if (oldValue == DBNull.Value)
            return oldValue;
        return JToken.FromObject(oldValue, serializer).ToObject(newType, serializer);
    }

    private static DataColumn ReplaceColumn(DataTable dt, DataColumn column, Type newColumnType, JsonSerializer serializer)
    {
        var newValues = Enumerable.Range(0, dt.Rows.Count).Select(i => dt.Rows[i]).Select(r => RemapValue(r[column], newColumnType, serializer)).ToList();

        var ordinal = column.Ordinal;
        var name = column.ColumnName;
        var @namespace = column.Namespace;

        var newColumn = new DataColumn(name, newColumnType);
        newColumn.Namespace = @namespace;
        dt.Columns.Remove(column);
        dt.Columns.Add(newColumn);
        newColumn.SetOrdinal(ordinal);

        for (int i = 0; i < dt.Rows.Count; i++)
            dt.Rows[i][newColumn] = newValues[i];

        return newColumn;
    }

    private static Type GetColumnDataType(JsonReader reader, out bool isAmbiguous)
    {
        JsonToken tokenType = reader.TokenType;

        switch (tokenType)
        {
            case JsonToken.Integer:
            case JsonToken.Boolean:
            case JsonToken.Float:
            case JsonToken.String:
            case JsonToken.Date:
            case JsonToken.Bytes:
                isAmbiguous = false;
                return reader.ValueType;
            case JsonToken.Null:
            case JsonToken.Undefined:
                isAmbiguous = true;
                return typeof(string);
            case JsonToken.StartArray:
                reader.ReadAndAssert();
                if (reader.TokenType == JsonToken.StartObject)
                {
                    isAmbiguous = false;
                    return typeof(DataTable); // nested datatable
                }
                else
                {
                    isAmbiguous = false;
                    bool innerAmbiguous;
                    // Handling ambiguity in array entries is not yet implemented because the first non-ambiguous entry in the array
                    // might occur anywhere in the sequence, requiring us to scan the entire array to determine the type, 
                    // e.g., given: [null, null, null, 314, null]
                    // we would need to scan until the 314 value, and do:
                    // return typeof(Nullable<>).MakeGenericType(new[] { reader.ValueType }).MakeArrayType();
                    Type arrayType = GetColumnDataType(reader, out innerAmbiguous);
                    return arrayType.MakeArrayType();
                }
            default:
                throw JsonSerializationExceptionHelper.Create(reader, "Unexpected JSON token when reading DataTable: {0}".FormatWith(CultureInfo.InvariantCulture, tokenType));
        }
    }
}

internal static class JsonSerializationExceptionHelper
{
    public static JsonSerializationException Create(this JsonReader reader, string format, params object[] args)
    {
        // Adapted from https://github.com/JamesNK/Newtonsoft.Json/blob/master/Src/Newtonsoft.Json/JsonPosition.cs

        var lineInfo = reader as IJsonLineInfo;
        var path = (reader == null ? null : reader.Path);
        var message = string.Format(CultureInfo.InvariantCulture, format, args);
        if (!message.EndsWith(Environment.NewLine, StringComparison.Ordinal))
        {
            message = message.Trim();
            if (!message.EndsWith(".", StringComparison.Ordinal))
                message += ".";
            message += " ";
        }
        message += string.Format(CultureInfo.InvariantCulture, "Path '{0}'", path);
        if (lineInfo != null && lineInfo.HasLineInfo())
            message += string.Format(CultureInfo.InvariantCulture, ", line {0}, position {1}", lineInfo.LineNumber, lineInfo.LinePosition);
        message += ".";

        return new JsonSerializationException(message);
    }
}

internal static class StringUtils
{
    // Adapted from https://github.com/JamesNK/Newtonsoft.Json/blob/master/Src/Newtonsoft.Json/Utilities/StringUtils.cs
    public static string FormatWith(this string format, IFormatProvider provider, object arg0)
    {
        return format.FormatWith(provider, new[] { arg0 });
    }

    private static string FormatWith(this string format, IFormatProvider provider, params object[] args)
    {
        return string.Format(provider, format, args);
    }
}

internal static class JsonReaderExtensions
{
    public static void ReadAndAssert(this JsonReader reader)
    {
        if (reader == null)
            throw new ArgumentNullException("reader");
        if (!reader.Read())
        {
            throw JsonSerializationExceptionHelper.Create(reader, "Unexpected end when reading JSON.");
        }
    }
}

然后像这样使用它:

var settings = new JsonSerializerSettings { Converters = new[] { new TypeInferringDataTableConverter() } };

DataTable tbl1 = Newtonsoft.Json.JsonConvert.DeserializeObject<DataTable>(jsonTable1, settings);
DataTable tbl2 = Newtonsoft.Json.JsonConvert.DeserializeObject<DataTable>(jsonTable2, settings);

不要设置NullValueHandling = NullValueHandling.Ignore 因为现在可以正确处理空值。

原型(prototype) fiddle

请注意，虽然此类处理具有 null 值的列的重新键入，但它不处理包含第一个数组项为 null 的数组值的列的重新键入。例如，如果某列的第一行具有值

[null, null, null, 314, null]

然后推断的列类型理想情况下是 typeof( long? [] )，但是这里没有实现。可能需要将 JSON 完全加载到 JToken 层次结构中才能做出该决定。