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

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 most straightforward 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 { /* Add settings as required e.g. */ 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?.

This should work well for tables with just one row, but will not be performant in general.

Demo fiddle #1 here.

Alternatively, if the table has many rows but few columns, you could transform the row into a Dictionary<string, object> using LINQ, and serialize that:

var rowJson = JsonConvert.SerializeObject(
    row.Table.Columns.Cast<DataColumn>()
        .ToDictionary(c => c.ColumnName, c => row.IsNull(c) ? null : row[c]), 
    Formatting.Indented);

As long as the number of columns is small, the performance should not be too bad.

Fiddle #2 here.

The most performant way to serialize a row will be to 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 settings = new JsonSerializerSettings
{
    Converters = { new DataRowConverter() },
    // Add other settings as required.
};
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 #3 here.

Answer 2

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 3

Spelling? Shouldn't your call to the "SerializeObject" method be:

string js = JsonConvert.SerializeObject(datatable);

Also see a similar question on converting a datatable to JSON string.

Answer 4

Like Gazzi said you should include the NewtonSoft.Json library it is included in Visual Studio.

To get an object similar to what you have described you should create a data model like Country.cs which would look like this.

[Serializable]
public class CountryModel {
   public int ID { get; set; }
   public string Title { get; set; }
   public List<ValueModel> Values { get; set; }
}

[Serializable]
public class ValueModel {
   public int ValueID { get; set; }
   public string Type { get; set; }
}

Create a helper function to convert your table data to these models. You can then serialize/deserialize data as need using the Newtonsoft Library.