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

Question

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:

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

Welcome to S.O. What have you tried in order to serialize to json? — Daniele Armanasco, Jul 29 '13 at 16:10
Consider this and convert your datatable to JSON http://stackoverflow.com/questions/17398019/how-to-convert-datatable-to-json-in-c-sharp — Ali Umair, Jun 03 '15 at 08:21

score 14 · Accepted Answer · answered Jul 29 '13 at 18:06

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.
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))...

Great answers. Thank you. My clients are not all .NET, and I quickly discovered the inefficiencies you mentioned. I am going to try modifying this approach so that I don't have to include the column/field names with every row. — rhyno, Jul 30 '13 at 19:53
i know this is an old thread, but fastjson supported datatable serialization/deserialization from version 1.7.7 which was released before june 2011. and the xml format is inefficent, it include cumbersome tag for each field AND row. — , Nov 06 '15 at 14:52
@Keith Blows, how about serializing a List of DataTables? How do you suggest to do that? — Xegara, Nov 10 '16 at 09:30
I created [this gist](https://gist.github.com/jmbeach/ec94e1a6048bd1bb4a2c737ad2d5f539) to test Newtonsoft.Json (meant to be ran in linqpad). The serialization works the way you'd hope. — Jared Beach, Jan 24 '19 at 20:42

score 6 · Answer 2 · edited Mar 07 '15 at 11:34

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]);
        }
        rows.Add(row);
    }
    return serializer.Serialize(rows);
}

score 4 · Answer 3 · edited Mar 10 '17 at 02:40

4

I had same issue, my wcf service was not formatting the json properly while converting it from Dataset to Json. I got it working by using the following solution:

using System.ServiceModel.Channels;
using System.ServiceModel.Web;

dsData is my Dataset

string json = Newtonsoft.Json.JsonConvert.SerializeObject(dsData);
return WebOperationContext.Current.CreateTextResponse(json, "application/json;charset=utf-8", System.Text.Encoding.UTF8);

and "Message" will be the return type.

edited Mar 10 '17 at 02:40

abatishchev

98,240
88
296
433

answered Dec 24 '15 at 04:09

yoshiiiiiiii

953
8
20

This answer really saved my life today :) I gave up on looking for simple method to return DataTable from db to JSON **without** declaring objects explicitly for every table/procedure. My mistake was that my `OperationContract` was returning serialized json simply as `string`. That way, it required, for example, using `JSON.parse` client-side. Changing return type to `System.ServiceModel.Channels.Message` and using `WebOperationContext.Current.CreateTextResponse(string)` as shown above **works perfectly**! :) – Skipper Apr 04 '18 at 12:21
I've never actually thought that You can do stuff like this with WCF WS - setting response instead of just returning `int/string/class/List etc` – Skipper Apr 04 '18 at 12:22

score 2 · Answer 4 · answered Jul 29 '13 at 16:12

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.

answered Jul 29 '13 at 16:12

ladenedge

13,197
11
60
117

1

This worked, but then I quickly realized that the data column/field names are being included with EVERY row, making it very inefficient, as mentioned by Keith. I am going to try modifying the DataTableConverter and DataRowConverter so that I don't repeat all of these column/field names. – rhyno Jul 30 '13 at 19:51
@rhyno what was your end solution for not repeating all of the column/field names? I have an idea of what I was going to do, but if you have already accomplished that it would save me some time. – DeadlyChambers Oct 14 '14 at 18:08

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

4 Answers4

Linked

Related