haxor haxor - 2 months ago 25
JSON Question

Convert DataTable to JSON

I've reviewed the answers here:



But they don't help for my particular use case. I am retrieving a DataTable from a SQL data adapter and want to convert the DataTable into a List (that's easy enough) and then serialize the List into JSON (using JSON.net, that's easy enough).

The problem is that I seem to have to use List. Ok, fine - so I have this code:

DataTable result = GoMagicallyGatherSomeData();
List<DataRow> ret = new List<DataRow>();
if (result != null && result.Rows.Count > 0)
{
foreach (DataRow curr in result.Rows)
{
ret.Add(curr);
}
}


or

DataTable result = GoMagicallyGatherSomeData();
List<DataRow> ret = result.AsEnumerable().ToList();


When I go to serialize the List, it... isn't what I expect.

What I would like to get back is:

[
{
"TestId":1,
"AccountId":1,
"SomeString":"This is an updated test",
"SomeTimestamp":"2016-01-01T00:00:00Z",
"SomeDecimal":5.55
},
{
"TestId":3,
"AccountId":1,
"SomeString":"This is a third test",
"SomeTimestamp":"2016-01-01T00:00:00Z",
"SomeDecimal":5.55
},
{ ... removed for brevity ... }
]


And what I actually get back is:

[
{
"RowError":"",
"RowState":2,
"Table":[
{
"TestId":1,
"AccountId":1,
"SomeString":"This is an updated test",
"SomeTimestamp":"2016-01-01T00:00:00Z",
"SomeDecimal":5.55
},
{
"TestId":3,
"AccountId":1,
"SomeString":"This is a second test",
"SomeTimestamp":"2016-01-01T00:00:00Z",
"SomeDecimal":5.55
},
{ ... removed for brevity ... }
],
"ItemArray":[
1,
1,
"This is an updated test",
"2016-01-01T00:00:00Z",
5.55
],
"HasErrors":false
},
{
"RowError":"",
"RowState":2,
"Table":[

... there seems to be an instance of this for every row in the result ...

],
"ItemArray":[
1,
1,
"This is an updated test",
"2016-01-01T00:00:00Z",
5.55
],
"HasErrors":false
}
]


The other challenge is that I need to do this without awareness of the actual type of the data.

Any insight? Anyone have a suggestion on the best way to do this? Could I get away with copying out the 'table' array from the first serialized DataRow, or, could subsequent serialized DataRows actually contain different data than the first?

Answer

You could convert the DataTable into a List<dynamic> and then convert it to json. For sample, to convert to a dynamic list:

public static List<dynamic> ConvertToDataTable(DataTable dataTable)
{
    var result = new List<dynamic>();
    foreach (DataRow row in dataTable.Rows)
    {
        dynamic dyn = new ExpandoObject();      
        foreach (DataColumn column in dataTable.Columns)
        {
            var dic = (IDictionary<string, object>)dyn;
            dic[column.ColumnName] = row[column];
        }
        result.Add(dyn);
    }
    return result;
}

You also could make an extension method like this post. Then use it and to convert to List<dynamic> and finally convert the list to json using the JsonConvert from Newtonsoft.Json (a.k.a. Json.Net). For sample:

var list = ConvertToDataTable(dataTable);

var json = JsonConvert.SerializeObject(list);