Do you have any advice on how we can serialize DataSet,DataTable with System.Text.Json.JsonSerializer ?
Currently it throws this exception :
'A possible object cycle was detected which is not supported. This can either be due to a cycle or if the object depth is larger than the maximum allowed depth of 64.'
Asked
Active
Viewed 7,555 times
2
bahman etghani
- 93
- 2
- 6
3 Answers
9
There is currently no built-in support for types like DataSet and DataTable in System.Text.Json (as of.NET Core 3.1). To be able to serialize such types, you will need to implement your own JsonConverter<T> for the types you need and register it within the JsonSerializerOptions. Writing one for serialization for the particular types you asked for should be fairly easy.
Here's an example that should work for serialization (left out the deserialization component):
public class DataTableConverter : JsonConverter<DataTable>
{
public override DataTable Read(ref Utf8JsonReader reader, Type typeToConvert,
JsonSerializerOptions options)
{
throw new NotImplementedException();
}
public override void Write(Utf8JsonWriter writer, DataTable value,
JsonSerializerOptions options)
{
writer.WriteStartArray();
foreach (DataRow row in value.Rows)
{
writer.WriteStartObject();
foreach (DataColumn column in row.Table.Columns)
{
object columnValue = row[column];
// If necessary:
if (options.IgnoreNullValues)
{
// Do null checks on the values here and skip writing.
}
writer.WritePropertyName(column.ColumnName);
JsonSerializer.Serialize(writer, columnValue, options);
}
writer.WriteEndObject();
}
writer.WriteEndArray();
}
}
public class DataSetConverter : JsonConverter<DataSet>
{
public override DataSet Read(ref Utf8JsonReader reader, Type typeToConvert,
JsonSerializerOptions options)
{
throw new NotImplementedException();
}
public override void Write(Utf8JsonWriter writer, DataSet value,
JsonSerializerOptions options)
{
writer.WriteStartObject();
foreach (DataTable table in value.Tables)
{
writer.WritePropertyName(table.TableName);
JsonSerializer.Serialize(writer, table, options);
}
writer.WriteEndObject();
}
}
private static void DataSet_Serialization_WithSystemTextJson()
{
var options = new JsonSerializerOptions()
{
Converters = { new DataTableConverter(), new DataSetConverter() }
};
(DataTable table, DataSet dataSet) = GetDataSetAndTable();
string jsonDataTable = JsonSerializer.Serialize(table, options);
// [{"id":0,"item":"item 0"},{"id":1,"item":"item 1"}]
Console.WriteLine(jsonDataTable);
string jsonDataSet = JsonSerializer.Serialize(dataSet, options);
// {"Table1":[{"id":0,"item":"item 0"},{"id":1,"item":"item 1"}]}
Console.WriteLine(jsonDataSet);
// Local function to create a sample DataTable and DataSet
(DataTable, DataSet) GetDataSetAndTable()
{
dataSet = new DataSet("dataSet");
table = new DataTable();
DataColumn idColumn = new DataColumn("id", typeof(int))
{
AutoIncrement = true
};
DataColumn itemColumn = new DataColumn("item");
table.Columns.Add(idColumn);
table.Columns.Add(itemColumn);
dataSet.Tables.Add(table);
for (int i = 0; i < 2; i++)
{
DataRow newRow = table.NewRow();
newRow["item"] = "item " + i;
table.Rows.Add(newRow);
}
dataSet.AcceptChanges();
return (table, dataSet);
}
}
This document might provide more guidance: https://learn.microsoft.com/en-us/dotnet/standard/serialization/system-text-json-converters-how-to
ahsonkhan
- 2,285
- 2
- 11
- 15
-
This answer contains good clues but still does not solve the problem. By the way thank you for answering. – bahman etghani Jan 20 '20 at 09:50
-
Can you expand on what you are looking for so I can update the answer? What type of DataSet/DataTables do you need to serialize into JSON and why doesn't the current solution work? – ahsonkhan Jan 20 '20 at 23:43
-
In Writing part of dataset you are calling serializer directly, while you should send datatable to next class which already exists in your code. Besides you do not have anything for deserializing. – bahman etghani Jan 21 '20 at 02:06
-
Great stuff, thanks! @bahmanetghani - this code works well, you could mark it as the Answer? (You didn't ask about deserialization.) – Rory Mar 02 '22 at 23:25
2
Here is a code for DataTable and DataSet custom converters. It supports only common/simple types, what is good way to reduce the risk of being confronted to vulnerabilities of serializers/deserializers.
namespace MyJsonConverters
{
public class DataTableJsonConverter : JsonConverter<DataTable>
{
public override DataTable Read(ref Utf8JsonReader reader, Type typeToConvert, JsonSerializerOptions options)
{
DataTable dt = new DataTable();
while (reader.Read())
{
if (reader.TokenType == JsonTokenType.EndObject)
return dt;
if (reader.TokenType != JsonTokenType.PropertyName)
throw new JsonException();
string propertyName = reader.GetString();
if (propertyName == "TableName")
{
dt.TableName = JsonSerializer.Deserialize<string>(ref reader, options);
}
else if (propertyName == "Columns")
{
JsonDataColumn[] jsonDataColumns = JsonSerializer.Deserialize<JsonDataColumn[]>(ref reader, options);
foreach (JsonDataColumn jsonDataColumn in jsonDataColumns)
{
Type dataType = Type.GetType(jsonDataColumn.DataType);
dt.Columns.Add(jsonDataColumn.ColumnName, dataType);
if (jsonDataColumn.MaxLength > 0)
dt.Columns[dt.Columns.Count].MaxLength = jsonDataColumn.MaxLength;
}
}
else if (propertyName == "Rows")
{
JsonElement[][] jsonRows = JsonSerializer.Deserialize<JsonElement[][]>(ref reader, options);
foreach (JsonElement[] jsonElements in jsonRows)
{
DataRow drNew = dt.NewRow();
int i = -1;
foreach(DataColumn dc in dt.Columns)
{
i++;
if (jsonElements[i].ToString() == "{}") // DBNull
continue;
else if (dc.DataType == typeof(string))
drNew[dc] = jsonElements[i].GetString();
else if (dc.DataType == typeof(bool))
drNew[dc] = jsonElements[i].GetBoolean();
else if (dc.DataType == typeof(DateTime))
drNew[dc] = jsonElements[i].GetDateTime();
else if (dc.DataType == typeof(Int64))
drNew[dc] = jsonElements[i].GetInt64();
else if (dc.DataType == typeof(Int32))
drNew[dc] = jsonElements[i].GetInt32();
else if (dc.DataType == typeof(Int16))
drNew[dc] = jsonElements[i].GetInt16();
else if (dc.DataType == typeof(UInt64))
drNew[dc] = jsonElements[i].GetUInt64();
else if (dc.DataType == typeof(UInt32))
drNew[dc] = jsonElements[i].GetUInt32();
else if (dc.DataType == typeof(UInt16))
drNew[dc] = jsonElements[i].GetUInt16();
else if (dc.DataType == typeof(byte))
drNew[dc] = jsonElements[i].GetByte();
else if (dc.DataType == typeof(sbyte))
drNew[dc] = jsonElements[i].GetSByte();
else if (dc.DataType == typeof(Single))
drNew[dc] = jsonElements[i].GetSingle();
else if (dc.DataType == typeof(Double))
drNew[dc] = jsonElements[i].GetDouble();
else if (dc.DataType == typeof(Decimal))
drNew[dc] = jsonElements[i].GetDecimal();
else if (dc.DataType == typeof(Guid))
drNew[dc] = jsonElements[i].GetGuid();
else if (dc.DataType == typeof(byte[]))
drNew[dc] = jsonElements[i].GetBytesFromBase64();
else
throw new JsonException("Column data type not supported in DataTableJsonConverter: " + dc.DataType);
}
dt.Rows.Add(drNew);
}
}
}
throw new JsonException();
}
public override void Write(Utf8JsonWriter writer, DataTable dt, JsonSerializerOptions options)
{
JsonDataTable j = new JsonDataTable
{
TableName = dt.TableName,
Columns = dt.Columns.Cast<DataColumn>().Select(c => new JsonDataColumn { ColumnName = c.ColumnName, DataType = c.DataType.ToString(), MaxLength = c.MaxLength }).ToArray(),
Rows = dt.Rows.Cast<DataRow>().Select(dr => dr.ItemArray).ToArray()
};
JsonSerializer.Serialize(writer, j, options);
}
public class JsonDataTable
{
public string TableName { get; set; }
public JsonDataColumn[] Columns { get; set; }
public object[][] Rows { get; set; }
}
public class JsonDataColumn
{
public string ColumnName { get; set; }
public string DataType { get; set; }
public int MaxLength { get; set; }
}
}
public class DataSetJsonConverter : JsonConverter<DataSet>
{
public override DataSet Read(ref Utf8JsonReader reader, Type typeToConvert, JsonSerializerOptions options)
{
DataTableJsonConverter dataTableJsonConverter = new DataTableJsonConverter();
DataSet ds = new DataSet();
while (reader.Read())
{
if (reader.TokenType == JsonTokenType.EndObject)
return ds;
if (reader.TokenType != JsonTokenType.PropertyName)
throw new JsonException();
string propertyName = reader.GetString();
if (propertyName == "DataSetName")
{
ds.DataSetName = JsonSerializer.Deserialize<string>(ref reader, options);
}
else if (propertyName == "Tables")
{
reader.Read();
if (reader.TokenType != JsonTokenType.StartArray)
throw new JsonException();
while (reader.Read())
{
if (reader.TokenType == JsonTokenType.EndArray)
break;
DataTable dt = dataTableJsonConverter.Read(ref reader, null, options);
ds.Tables.Add(dt);
}
}
}
throw new JsonException();
}
public override void Write(Utf8JsonWriter writer, DataSet ds, JsonSerializerOptions options)
{
DataTableJsonConverter dataTableJsonConverter = new DataTableJsonConverter();
writer.WriteStartObject();
writer.WritePropertyName("DataSetName");
writer.WriteStringValue(ds.DataSetName);
writer.WritePropertyName("Tables");
writer.WriteStartArray();
foreach(DataTable dt in ds.Tables)
dataTableJsonConverter.Write(writer, dt, options);
writer.WriteEndArray();
writer.WriteEndObject();
}
}
}
Usage:
public class MyClass
{
[System.Text.Json.Serialization.JsonConverter(typeof(MyJsonConverters.DataTableJsonConverter))]
public DataTable DataTable { get; set; }
[System.Text.Json.Serialization.JsonConverter(typeof(MyJsonConverters.DataSetJsonConverter))]
public DataSet DataSet { get; set; }
}
1
Reference loop handling is not currently supported using System.Text.Json. Workarounds:
.Net Core 3.0 possible object cycle was detected which is not supported
https://www.thecodebuzz.com/jsonexception-possible-object-cycle-detected-object-depth/
You could add custom converters on the offending property to deal with an object referring to itself or just add a JsonIgnore attribute to the offending property.
Emmett
- 61
- 1
- 5