Custom JSON string output using Newtonsoft
I am trying to serialize a C # DataTable using Newtonsoft JsonConverter
Code:
JsonConvert.SerializeObject(dt); //dt is DataTable
The result is:
[
{
"Name": "Tiger Nixon",
"Position": "System Architect",
"Address": "Edinburgh",
"No": "5421"
},
{
"Name": "Garrett Winters",
"Position": "Accountant",
"Address": "Tokyo",
"No": "8422"
}
]
The result I want is:
{
"data": [
[
"Tiger Nixon",
"System Architect",
"Edinburgh",
"5421"
],
[
"Garrett Winters",
"Accountant",
"Tokyo",
"8422"
]
]
}
Can I customize the output with Newtonsoft? I tried writing my own code to serialize the DataTable using foreach
on DataTable
, but the performance of the day versus Newtonsoft.
Any help would be appreciated
source to share
You can do this with the following JsonConverter
:
public class DataTableTo2dArrayConverter : JsonConverter
{
public override bool CanConvert(Type objectType)
{
return typeof(DataTable).IsAssignableFrom(objectType);
}
public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
{
throw new NotImplementedException();
}
public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
{
var table = (DataTable)value;
var array2d = table.AsEnumerable().Select(row => table.Columns.Cast<DataColumn>().Select(col => row[col]));
serializer.Serialize(writer, new { data = array2d });
}
}
And then use it like:
var settings = new JsonSerializerSettings();
settings.Converters.Add(new DataTableTo2dArrayConverter());
var json = JsonConvert.SerializeObject(dt, Formatting.Indented, settings);
Please note that System.Data.DataTableExtensions.AsEnumerable()
a link to System.Data.DataSetExtensions.dll
.
source to share
This is how I did it after reading the link provided by JasonWilczak
public class JqueryDatatablesConverter : JsonConverter
{
public override bool CanConvert(Type objectType)
{
return typeof(DataTable).IsAssignableFrom(objectType);
}
public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
{
throw new NotImplementedException();
}
public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
{
dynamic dt = (DataTable)value;
dynamic count = dt.Columns.Count - 1;
writer.WriteStartObject();
writer.WritePropertyName("data");
writer.WriteStartArray();
foreach (DataRow dr in dt.Rows) {
writer.WriteStartArray();
for (int x = 0; x <= count; x++) {
serializer.Serialize(writer, dr[x]);
}
writer.WriteEndArray();
}
writer.WriteEndArray();
writer.WriteEndObject();
}
}
source to share
Here is a working violin demonstrating it . For more information, see the Newtonsoft documentation on custom JsonConverter .
DataTableJsonConverter
Convert DataTable
to custom JSON string.
public class DataTableJsonConverter : JsonConverter
{
public override void WriteJson(JsonWriter w, object v, JsonSerializer s)
{
w.WriteStartObject();
w.WritePropertyName("data");
w.WriteStartArray();
foreach(DataRow r in (v as DataTable).Rows)
{
w.WriteStartArray();
foreach(var c in r.ItemArray)
{
w.WriteValue(c);
}
w.WriteEndArray();
}
w.WriteEndArray();
w.WriteEndObject();
}
public override object ReadJson(JsonReader r, Type t, object v, JsonSerializer s)
{
throw new NotImplementedException("Unnecessary: CanRead is false.");
}
public override bool CanRead { get { return false; } }
public override bool CanConvert(Type objectType)
{
return objectType == typeof(DataTable);
}
}
Here's how to use it
public class Program
{
public static void Main()
{
var dt = SeedData();
var json = JsonConvert.SerializeObject(
dt, Newtonsoft.Json.Formatting.Indented,
new DataTableJsonConverter());
Console.WriteLine(json);
}
public static DataTable SeedData()
{
var dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Position");
for (var i = 0; i < 2; ++i)
{
dt.Rows.Add(new object[] { "Shaun", "Developer" });
}
return dt;
}
}
Here is his exit
{
"data": [
[
"Shaun",
"Developer"
],
[
"Shaun",
"Developer"
]
]
}
Performance
For those interested, here is a fiddle fork that tries to show the performance of three different methods from me, dbc, and warheat1990 over 1500 lines of data and two runs each. They are all very close and for reasons unknown to me, the second launch is always faster.
DataTableJsonConverter:6 ms
DataTableJsonConverter:2 ms
DataTableTo2dArrayConverter:251 ms
DataTableTo2dArrayConverter:11 ms
JqueryDatatablesConverter:1580 ms
JqueryDatatablesConverter:16 ms
source to share