Convert a data row to a JSON object

Question

I have a DataTable which has only a single row and looks like

   America              |  Africa               |     Japan     |   
   -------------------------------------------------------------
  {"Id":1,"Title":"Ka"} | {"Id":2,"Title":"Sf"} | {"Id":3,"Title":"Ja","Values":{"ValID":4,"Type":"Okinawa"}} 

The DataTable columns are America, Africa, Japan.

Now I want to convert the DataTable to JSON such that the JSON looks like

{
"America": {
    "Id": 1,
    "Title": "Ka"
},
"Africa": {
    "Id": 2,
    "Title": "Sf"
},
"Japan": {
    "Id": 3,
    "Title": "Ja",
    "Values": {
        "ValID": 4,
        "Type": "Okinawa"
    }
  }
}  

My attempt was:

string js = JSonConvverter.Serializeobject(datatable);
var objType =  JObject.Parse(js);

But it didn't work. Any help would be appreciated.

Answer 1

As an alternative to the answer found here, you can use an ExpandoObject to quickly and pretty easily render a single row as JSON, as such:

var expando = new System.Dynamic.ExpandoObject() as IDictionary<string, object>;
foreach (DataColumn col in myRow.Table.Columns)
{
    expando[col.ColumnName] = myRow[col];
}
var json = JsonConvert.SerializeObject(expando);

Answer 2

Assuming you are using json.net, there is a special built-in converter, DataTableConverter, that outputs data tables in an abbreviated format as an array of rows where each row is serialized as column name/value pairs as shown in your question. While there is also a converter for DataSet, there is no specific built-in converter for DataRow. Thus when directly serializing a DataRow Json.NET will serialize all the fields and properties of the DataRow resulting in a more verbose output - which you do not want.

The easiest way to serialize a DataRow in the more compact form used by DataTable is to serialize the entire table to a JArray using JArray.FromObject() and then pick out the array item with the same index as the DataRow you want to serialize:

var rowIndex = 0;

var jArray = JArray.FromObject(datatable, JsonSerializer.CreateDefault(new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore }));

var rowJToken = jArray[rowIndex];
var rowJson = rowJToken.ToString(Formatting.Indented);  // Or Formatting.None if you prefer

Since your table has only one row, rowIndex should be 0. More generally, if you don't know the index of a given DataRow, see How to get the row number from a datatable?.

Demo fiddle #1 here.

Alternatively, if your table is large enough that serializing the entire table has performance implications, you can introduce a custom JsonConverter for DataRow that writes the row to JSON as an object:

public class DataRowConverter : JsonConverter<DataRow>
{
    public override DataRow ReadJson(JsonReader reader, Type objectType, DataRow existingValue, bool hasExistingValue, JsonSerializer serializer)
    {
        throw new NotImplementedException(string.Format("{0} is only implemented for writing.", this));
    }

    public override void WriteJson(JsonWriter writer, DataRow row, JsonSerializer serializer)
    {
        var table = row.Table;
        if (table == null)
            throw new JsonSerializationException("no table");
        var contractResolver = serializer.ContractResolver as DefaultContractResolver;

        writer.WriteStartObject();
        foreach (DataColumn col in row.Table.Columns)
        {
            var value = row[col];

            if (serializer.NullValueHandling == NullValueHandling.Ignore && (value == null || value == DBNull.Value))
                continue;

            writer.WritePropertyName(contractResolver != null ? contractResolver.GetResolvedPropertyName(col.ColumnName) : col.ColumnName);
            serializer.Serialize(writer, value);
        }
        writer.WriteEndObject();
    }
}

And then use it like:

var row = datatable.Rows[rowIndex];

var settings = new JsonSerializerSettings
{
    NullValueHandling = NullValueHandling.Ignore,
    Converters = { new DataRowConverter() },
};
var rowJson = JsonConvert.SerializeObject(row, Formatting.Indented, settings);

Notes:

• While it makes sense to serialize a single DataRow, it doesn't make sense to deserialize one since a DataRow is not a standalone object; it exists only inside some parent DataTable. Thus ReadJson() is not implemented.

• JsonConverter<T> was introduces in Json.NET 11.0.1. In earlier versions inherit from JsonConverter.

Demo fiddle #2 here.