JsonSerializer.Parse<DataTable> failed

[xcaptain]

Convert an object into json string and then convert into DataTable is an easy way to generate DataTable, but this approach fails in dotnet core 3 preview5

I have created a working dotnet core 2 code snippet at https://dotnetfiddle.net/cD6NFD

dotnetfiddle doesn't support dotnet core 3 yet, so my dotnet core 3 code example see below:

using System.Data;
using System.Text.Json.Serialization;

public class UserDetails
{
    public string Id { get; set; }
    public string Name { get; set; }
    public string City { get; set; }
    public string Country { get; set; }
}

List<UserDetails> persons = new List<UserDetails>()
{
    new UserDetails() {Id="1001", Name="ABCD", City ="City1", Country="USA"},
    new UserDetails() {Id="1002", Name="PQRS", City ="City2", Country="INDIA"},
    new UserDetails() {Id="1003", Name="XYZZ", City ="City3", Country="CHINA"},
    new UserDetails() {Id="1004", Name="LMNO", City ="City4", Country="UK"},
};
var table = JsonSerializer.Parse<DataTable>(JsonSerializer.ToString(persons));

below are the error log:

CLR/System.Text.Json.JsonReaderException 'The JSON value from [System.Data.DataTable] could not be converted to System.Object.'

[ahsonkhan]

DataTable and the related types are not currently supported by the JsonSerializer (for 3.0).

cc @steveharter

[xcaptain]

Thanks, because NewtonSoft.Json support this feature, would be awesome if System.Text.Json support this.

[AlonCG]

We ran into this issues as well, and being stubborn (& lazy ... since I did not read all the docs first) ... I decided to trudge through and forcefully and painstakingly remove JSON.Net from our 2.2 to 3.1 migration. In the end we created a JsonConverter<DataTable>.

I have not tested it extensively and I am sure there are dozens of other better examples out there, but this was my first attempt and is currently working in our situation. Maybe this can be a stepping stone for others instead of just (1) go back to Json.Net or (2) wait for Net 5!

I'm sure I will refactor this a dozen times before it goes to production but constructive comments are always helpful. If it is not useful (or lacking in any area), then please disregard this post (or admins can delete it!).

public class DataTableJsonConverter : JsonConverter<DataTable>
{
	public override DataTable Read(ref Utf8JsonReader reader, Type typeToConvert, JsonSerializerOptions options)
	{
		...
	}

	public override void Write(Utf8JsonWriter writer, DataTable value, JsonSerializerOptions options)
	{
		CustomDataTableSerializer.WriteDataTable(writer, value, options);
	}
}

Edited as Suggested (below):

public static void WriteDataTable(Utf8JsonWriter jsonWriter, DataTable source, JsonSerializerOptions options)
        {
            jsonWriter.WriteStartArray();
            foreach (DataRow dr in source.Rows) {
                jsonWriter.WriteStartObject();
                foreach (DataColumn col in source.Columns) {
                    var key = col.ColumnName.Trim();
                    var valueString = dr[col].ToString();
                    switch (col.DataType.FullName) {
                        case "System.Guid":
                            jsonWriter.WriteString(key, valueString);
                            break;
                        case "System.Char":
                        case "System.String":
                            jsonWriter.WriteString(key, valueString);
                            break;
                        case "System.Boolean":
                            Boolean.TryParse(valueString, out bool boolValue);
                            jsonWriter.WriteBoolean(key, boolValue);
                            break;
                        case "System.DateTime":
                            var dateValue = DateTime.Parse(valueString);
                            jsonWriter.WriteString(key, dateValue);
                            break;
                        case "System.TimeSpan":
                            var timeSpanValue = TimeSpan.Parse(valueString);
                            jsonWriter.WriteString(key, timeSpanValue.ToString());
                            break;
                        case "System.Byte":
                        case "System.SByte":
                        case "System.Decimal":
                        case "System.Double":
                        case "System.Single":
                        case "System.Int16":
                        case "System.Int32":
                        case "System.Int64":
                        case "System.UInt16":
                        case "System.UInt32":
                        case "System.UInt64":
                            if (long.TryParse(valueString, out long intValue)) {
                                jsonWriter.WriteNumber(key, intValue);
                            } else {
                                double.TryParse(valueString, out double doubleValue);
                                jsonWriter.WriteNumber(key, doubleValue);
                            }
                            break;
                        default:
                            jsonWriter.WriteString(key, valueString);
                            break;
                    }
                }
                jsonWriter.WriteEndObject();
            }
            jsonWriter.WriteEndArray();
        }

I reuse WriteDataTable in multiple spots, hence the reason it is separate. Once we get this into our staging environment, I'll most probably find missing edge-cases and possibly more efficient ways to write this out.

Happy coding!

[ahsonkhan]

but constructive comments are always helpful

From a quick glance, I would recommend one adjustment that caught my eye. Remove all the calls to JsonEncodedText.Encode and pass the strings directly to the writer. Unless you are doing custom encoding specific to a particular key/value pair, it is just extra overhead and making things slower (+ you are writing more code). JsonEncodedText is mainly useful for known/constant string literals you want to encode up front and store in statics to re-use. And if you want to globally change the encoding behavior, change the Encoder setting on JsonWriterOptions instead.

For example:

- jsonWriter.WriteString(JsonEncodedText.Encode(key), JsonEncodedText.Encode(valueString));
+ jsonWriter.WriteString(key, valueString);

[AlonCG]

@ahsonkhan Yeah, that is simple. For some reason early on I thought it was required, so just copied and pasted it there! Thanks!

[etghanibahman]

@AlonCG The main problem is when you are going to read serialized data and turn it to dataset.
In this case you do not know what is the type of column, so you do not know you can use which function to retrieve data.

[AlonCG]

@etghanibahman, yeah ... I basically just do the reverse, hence the "..." in the Read function.

This needs some work, since I don't like the double enumeration and could use some optimization (and I am not sure it is even a good idea, but I needed reusable bits)... but again, this could just be used as a stepping stone for others that are stuck and need a bit of a boost?

public class DataTableJsonConverter : JsonConverter<DataTable>
{
    public override DataTable Read(ref Utf8JsonReader reader, Type typeToConvert, JsonSerializerOptions options)
    {
        using var jsonDoc = JsonDocument.ParseValue(ref reader);
        var rootElement = jsonDoc.RootElement;
        var dataTable = rootElement.JsonElementToDataTable();
        return dataTable;
    }

    public override void Write(Utf8JsonWriter writer, DataTable value, JsonSerializerOptions options)
    {
        ... (see above)
    }
}

JsonElementToDataTable Extension and support functions (could use some work for sure!)

public static DataTable JsonElementToDataTable(this JsonElement dataRoot)
{
    var dataTable = new DataTable();
    var firstPass = true;
    foreach (var element in dataRoot.EnumerateArray()) {
        if (firstPass) {
            foreach (var col in element.EnumerateObject()) {
                var colValue = col.Value;
                dataTable.Columns.Add(new DataColumn(col.Name, colValue.ValueKind.ValueKindToType(colValue.ToString())));
            }
            firstPass = false;
        }
        var row = dataTable.NewRow();
        foreach (var col in element.EnumerateObject()) {
            row[col.Name] = col.Value.JsonElementToTypedValue();
        }
        dataTable.Rows.Add(row);
    }
    return dataTable;
}

private static Type ValueKindToType(this JsonValueKind valueKind, string value)
{
    switch (valueKind) {
        case JsonValueKind.String:      // 3
            return typeof(System.String);
        case JsonValueKind.Number:      // 4    
            if (Int64.TryParse(value, out var intValue)) {
                return typeof(System.Int64);
            } else {
                return typeof(System.Double);
            }
        case JsonValueKind.True:        // 5
        case JsonValueKind.False:       // 6
            return typeof(System.Boolean);
        case JsonValueKind.Undefined:   // 0
            return null;
        case JsonValueKind.Object:      // 1 
            return typeof(System.Object);
        case JsonValueKind.Array:       // 2
            return typeof(System.Array);
        case JsonValueKind.Null:        // 7
            return null;
        default:
            return typeof(System.Object);
    }
}

private static object JsonElementToTypedValue(this JsonElement jsonElement)
{
    switch (jsonElement.ValueKind) {
        case JsonValueKind.Object:      // 1  (these need special handling)?
        case JsonValueKind.Array:       // 2
        case JsonValueKind.String:      // 3
            if (jsonElement.TryGetGuid(out Guid guidValue)) {
                return guidValue;
            } else {
                if (jsonElement.TryGetDateTime(out DateTime datetime)) {
                    // If an offset was provided, use DateTimeOffset.
                    if (datetime.Kind == DateTimeKind.Local) {
                        if (jsonElement.TryGetDateTimeOffset(out DateTimeOffset datetimeOffset)) {
                            return datetimeOffset;
                        }
                    }
                    return datetime;
                }
                return jsonElement.ToString();
            }
        case JsonValueKind.Number:      // 4    
            if (jsonElement.TryGetInt64(out long longValue)) {
                return longValue;
            } else {
                return jsonElement.GetDouble();
            }
        case JsonValueKind.True:        // 5
        case JsonValueKind.False:       // 6
            return jsonElement.GetBoolean();
        case JsonValueKind.Undefined:   // 0
        case JsonValueKind.Null:        // 7
            return null;
        default:
            return jsonElement.ToString();
    }
}

While this works for us, we have not done extensive testing on it, at all. It was just implemented to get around the issue of not having serialization for DataTables. So instead of waiting for Core 5 or going back to Json.Net (hindsight being 20/20), this is what we got.

[etghanibahman]

Thank you, it works really well in my case.

[etghanibahman]

@AlonCG Basically there is a severe need to have a serializer for dataset, I have made some amendments in your solution to have it. Fro example I have made some changes for column of type datetime or timespans, because based on my experience in our current project, in case we have no data for mentioned types, the code that you have written will cease to work.

        public override void Write(Utf8JsonWriter jsonWriter, DataSet value, JsonSerializerOptions options)
        {
            jsonWriter.WriteStartArray();
            foreach (DataTable source in value.Tables)
            {
                foreach (DataRow dr in source.Rows)
                {
                    jsonWriter.WriteStartObject();
                    foreach (DataColumn col in source.Columns)
                    {
                        var key = col.ColumnName.Trim();
                        var valueString = dr[col].ToString();
                        switch (col.DataType.FullName)
                        {
                            case "System.Guid":
                                jsonWriter.WriteString(key, valueString);
                                break;
                            case "System.Char":
                            case "System.String":
                                jsonWriter.WriteString(key, valueString);
                                break;
                            case "System.Boolean":
                                Boolean.TryParse(valueString, out bool boolValue);
                                jsonWriter.WriteBoolean(key, boolValue);
                                break;
                            case "System.DateTime":
                                if (DateTime.TryParse(valueString, out DateTime dateValue))
                                {
                                    jsonWriter.WriteString(key, dateValue);
                                }
                                else
                                {
                                    jsonWriter.WriteString(key, "");
                                }
                                break;
                            case "System.TimeSpan":
                                if (DateTime.TryParse(valueString, out DateTime timeSpanValue))
                                {
                                    jsonWriter.WriteString(key, timeSpanValue.ToString());
                                }
                                else
                                {
                                    jsonWriter.WriteString(key, "");
                                }
                                break;
                            case "System.Byte":
                            case "System.SByte":
                            case "System.Decimal":
                            case "System.Double":
                            case "System.Single":
                            case "System.Int16":
                            case "System.Int32":
                            case "System.Int64":
                            case "System.UInt16":
                            case "System.UInt32":
                            case "System.UInt64":
                                if (long.TryParse(valueString, out long intValue))
                                {
                                    jsonWriter.WriteNumber(key, intValue);
                                }
                                else
                                {
                                    double.TryParse(valueString, out double doubleValue);
                                    jsonWriter.WriteNumber(key, doubleValue);
                                }
                                break;
                            default:
                                jsonWriter.WriteString(key, valueString);
                                break;
                        }
                    }
                    jsonWriter.WriteEndObject();
                }

            }
            jsonWriter.WriteEndArray();
        }

[ahsonkhan]

From @bkbartje in dotnet/runtime#31795

when I use this method
var ds = System.Text.Json.JsonSerializer.Deserialize(json);
the dataset has gets no tables and stays empty.

[layomia]

From @chenyj796 in dotnet/runtime#41920:

Description

I want to serialize a DataTable use JsonSerializer:

class Program
{
    class User
    {
        public string Name { get; set; }
    }

    static void Main(string[] args)
    {
        var user = new User { Name = "abc" };

        var table = new System.Data.DataTable();
        table.Columns.Add(new System.Data.DataColumn(nameof(User.Name)));

        var row = table.NewRow();
        row[nameof(User.Name)] = user.Name;
        table.Rows.Add(row);

        var json = System.Text.Json.JsonSerializer.Serialize(table);
    }
}

and it throws an exception:

System.NotSupportedException
  HResult=0x80131515
  Message=Serialization and deserialization of 'System.Type' instances are not supported and should be avoided since they can lead to security issues. Path: $.Columns.DataType.
  Source=System.Text.Json
  StackTrace:
   at System.Text.Json.ThrowHelper.ThrowNotSupportedException(WriteStack& state, NotSupportedException ex)
   at System.Text.Json.Serialization.JsonConverter`1.WriteCore(Utf8JsonWriter writer, T& value, JsonSerializerOptions options, WriteStack& state)
   at System.Text.Json.JsonSerializer.WriteCore[TValue](JsonConverter jsonConverter, Utf8JsonWriter writer, TValue& value, JsonSerializerOptions options, WriteStack& state)
   at System.Text.Json.JsonSerializer.WriteCore[TValue](Utf8JsonWriter writer, TValue& value, Type inputType, JsonSerializerOptions options)
   at System.Text.Json.JsonSerializer.Serialize[TValue](TValue& value, Type inputType, JsonSerializerOptions options)
   at System.Text.Json.JsonSerializer.Serialize[TValue](TValue value, JsonSerializerOptions options)
   at ConsoleApp3.Program.Main(String[] args) in C:\Users\Administrator\source\repos\ConsoleApp3\ConsoleApp3\Program.cs:line 24

  此异常最初是在此调用堆栈中引发的: 
    [外部代码]

内部异常 1:
NotSupportedException: Serialization and deserialization of 'System.Type' instances are not supported and should be avoided since they can lead to security issues.

When I serialize the DataTable use JsonConvert, it works fine.

var json = Newtonsoft.Json.JsonConvert.SerializeObject(table);

I will get the json as below:

[{"Name":"abc"}]

Other information

PS> dotnet --version
5.0.100-preview.8.20417.9

[layomia]

We recently published guidance against (de)serializing the DataSet and DataTable types from untrusted input. In keeping with JsonSerializer's emphasis on security, we'll throw a clear NotSupportedException for these types, similar to the behavior for System.Type.

Those who need this functionality can write custom converters as shown in examples provided above. We'll work on providing guidance on this in the JSON documentation, including an example of how to implement the functionality safely, if possible.

[layomia]

Moving this to the dotnet/docs repo as this is now considered an issue we should provide security guidance, documentation, and possibly a workaround for. cc @tdykstra

[dotnet-bot]

This issue has been closed as part of the issue backlog grooming process outlined in #22351.

That automated process may have closed some issues that should be addressed. If you think this is one of them, reopen it with a comment explaining why. Tag the @dotnet/docs team for visibility.

[MelbourneDeveloper]

We recently published guidance against (de)serializing the DataSet and DataTable types from untrusted input. In keeping with JsonSerializer's emphasis on security, we'll throw a clear NotSupportedException for these types, similar to the behavior for System.Type.

@layomia is there no option to simply ignore unsafe types? If not, isn't that a bug?

[MelbourneDeveloper]

How do we hook in to the processor so that we can just ignore these types?

[XtremeOwnageDotCom]

To confirm, this is still indeed an issue.... .net 5's system.text.json still cannot handle a datatable.

[steveharter]

STJ will treat any unknown type that does not implement IEnumerable as a POCO, meaning all public properties will be (de)serialized and the default constructor called. For types where that is not compatible, you can configure a custom converter to throw.

Here's an example:

  class Program
  {
      static void Main(string[] args)
      {
          JsonSerializerOptions options = new();
          options.Converters.Add(new UnsupportedType<DataTable>());

          DataTable dt = JsonSerializer.Deserialize<DataTable>("{}", options); // throws NotSupportedException
      }
  }

  internal class UnsupportedType<T> : JsonConverter<T>
  {
      public override T? Read(ref Utf8JsonReader reader, Type typeToConvert, JsonSerializerOptions options)
      {
          throw new NotSupportedException($"{typeof(T)} does not support deserialization.");
      }

      public override void Write(Utf8JsonWriter writer, T value, JsonSerializerOptions options)
      {
          throw new NotSupportedException($"{typeof(T)} does not support serialization.");
      }
  }

[Seabizkit]

I quite disappointed... could someone give me the implementation for doing this.
like this is closed... but no work around nothing? .net8 has been release and we STILL do not have Table sterilize and desterilize?

its 2023, newtonsoft has this for like how long?

cant even find a DataTableJsonConverter : JsonConverter

which works.. quite sad

[Transis-Felipe]

Same problem here.
It will not be included in .net9 ?
Any plans to include this in future versions of .net ?

[XtremeOwnageDotCom]

Whew, this issue started in NET CORE 3.0, and has now reached .NET 9 !

[Jimex]

Whew, this issue started in NET CORE 3.0, and has now reached .NET 9 !

Typical Microsoft style; if you look at the WinUI3 UI designer posts, you will see the worse.