.net - retrieve - serialize datatable

What's the best way to jSON serialize a.NET DataTable in WCF? (3)

When I configure WCF to use jSON serialization, and include a DataTable in one of my DataContracts, it serializes the DataTable to XML before serializing the entire DataContract to jSON. I want the DataTable to be serialized as jSON, not XML.

My questions are:

  1. Why does it serialize the DataTable to XML first?
  2. How can I get it to serialize to jSON instead?

  1. DataTable is a pure .NET construct which cannot be (easily) represented in a lossless manner by JSON. DataTables contain lots of additional information which JSON cannot store: Primary keys, autoincs, allow nulls, caption, data type, indexes, etc. Serialization to XML/Binary are the only ways a DataTable can be serialized natively by .NET. This XML serialized DataTable is then serialized to JSON.

  2. Use JSON.NET or FastJSON to convert a DataTable to a plain, clean JSON-compatible version of the DataTable, which can be consumed by any JSON client, not just .NET WCF clients. You will lose all DataTable custom properties mentioned in (1) above and only get the field name/value JSON pair. Storage in this fashion is inefficient due to the duplication of field names in every row.

Don't use DataTable in your DataContract. If you want the benefits of a DataTable and your clients are always going to be .NET, serialize the DataTable to a byte array via Binary Serialization and then optionally compress the resultant serialized byte stream. Expose a byte array in your DataContract. This will give you an efficient, fully lossless version of the DataTable on the client-side (after decompression and binary deserialization), not a watered-down JSON version of a DataTable (as offered by (2))...

According to the chart on its homepage, Json.NET is really your only option - you can quickly get it from NuGet. Fortunately it's a great library and very easy to use.

string json = JsonConvert.SerializeObject(myDataSet, new DataSetConverter());

Note that Rich Strahl has a great post with more details, and he also includes some custom work he did to use JavaScriptSerializer with (rather extensive) custom converters for the sake of comparison.

Try this:

public string ConvertDataTabletoString(System.Data.DataTable dt)
    System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
    List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
    Dictionary<string, object> row;
    foreach (System.Data.DataRow dr in dt.Rows)
        row = new Dictionary<string, object>();
        foreach (System.Data.DataColumn col in dt.Columns)
            row.Add(col.ColumnName, dr[col]);
    return serializer.Serialize(rows);