Deserialize JSON String into DataTable / DataSet using c#

Question

I have following JSON String

{
"1":[
        {"cityId":93,"cityName":"Tapah","cityCode":"TAP"},
        {"cityId":3,"cityName":"Melaka","cityCode":"MLK"},
        {"cityId":6,"cityName":"Kota Bharu","cityCode":"KB"},
        {"cityId":7,"cityName":"Dungun","cityCode":"DG"}
    ],
"2":[
        {"cityId":77,"cityName":"Grik","cityCode":"GRIK"},
        {"cityId":6,"cityName":"Kota Bharu","cityCode":"KB"},
        {"cityId":7,"cityName":"Dungun","cityCode":"DG"},
        {"cityId":98,"cityName":"Bangan Serai","cityCode":"BS"}
    ],
"6":[
        {"cityId":3,"cityName":"Melaka","cityCode":"MLK"},
        {"cityId":82,"cityName":"Teluk Intan","cityCode":"TI"},
        {"cityId":7,"cityName":"Dungun","cityCode":"DG"}
    ]
}

When I using JSON.NET

DataSet obj = JsonConvert.DeserializeObject(responseBody);

I am getting following Table only

cityId  |  cityName   | cityCode
-----------------------------------
93           Tapah          TAP
3            Melaka         MLK
6            Kota Bharu     KB
7            Dungun         DG

I need to like this

from  |    cityId  |  cityName   | cityCode
-----------------------------------------------
 1            93           Tapah          TAP
 1            3            Melaka         MLK
 1            6            Kota Bharu     KB
 1            7            Dungun         DG
 2            77           Grik           GRIK
 2            6            Kota Bharu     KB
 2            7            Dungun         DG
 2            98           Bangan Serai   BS
 6            3            Melaka         MLK
 6            82           Teluk Intan    TI
 6            7            Dungun         DG

Mrinal Kamboj · Accepted Answer · 2014-11-18 07:43:46Z

1

The reason for the issue is the incorrect usage of the JsonConvert.DeserializeObject following should work in this case:

Define a type / schema with the columns details (as shown in above response)

public class City
{
    public int cityId;
    public string cityName;
    public string cityCode;
}

Now what you are getting in response body is the type, Json is by default key value pair

Dictionary<object, List<City>>

Now your deserialization code should be:

var obj = JsonConvert.DeserializeObject<Dictionary<object, List<City>>>(responseBody);

obj will be of the type Dictionary<object, List<City>>

Now create the Schema of the Custom Dataset object as follows:

public class CustomDataset
    {
        public object from;
        public int cityId;
        public string cityName;
        public string cityCode;
    }

Use simple foreach to create the custom dataset from obj type as follows:

List<CustomDataset> cdList = new List<CustomDataset> ();

foreach(object key in obj.Keys)
{
foreach(City c in obj[key])
{
  cdList.Add(key, c.cityId, c.cityName, cityCode);
}
}

cdList of type List<CustomDataset> wil be your return containing solution you need. Final part using Foreach can be skipped if you use Linq, which would be simple as follows:

List<CustomDataset> cdList = 
                              obj.SelectMany(x => x.Value.Select(y =>
                                               new CustomDataset
                                               {
                                                from = x.Key,
                                                cityId = y.cityId,
                                                cityName = y.cityName,
                                                cityCode =  y.cityCode
                                               }
                                               )).ToList();

edited Nov 18, 2014 at 7:43

answered Nov 17, 2014 at 12:18

Mrinal Kamboj

11.6k5 gold badges45 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Steven Liekens Over a year ago

Why use type object for TKey? JSON.NET is smart enough to call Convert.ToInt32() if you use type int instead.

Mrinal Kamboj Over a year ago

@Steven Liekens You are right, I just made the implementation generic

Mrinal Kamboj Over a year ago

@CnuVas welcome, please refer to the recent edit, I have corrected the Linq query, now this will fetch the result that you expect. Linq provides an elegant solution vis a vis foreach loop

Steven Liekens · Accepted Answer · 2014-11-17 15:58:22Z

0

I suggest de-serializing JSON objects to framework types that are semantically equivalent. In your case, the JSON is semantically equivalent to IDictionary<TKey, ICollection<TValue>>>

IDictionary<int, ICollection<City>> cities;
cities = JsonConvert.DeserializeObject<IDictionary<int, ICollection<City>>>(responseBody);

Then, do the DataSet conversion yourself instead of relying on reflection.

var dataSet = new DataSet();
var dataTable = dataSet.Tables.Add();
dataTable.Columns.Add("from");
dataTable.Columns.Add("cityId");
dataTable.Columns.Add("cityName");
dataTable.Columns.Add("cityCode");

// foreach KeyValuePair in IDictionary<TKey, ICollection<TValue>>
foreach (var cityGroup in cities)
{
    // foreach TValue in ICollection<TValue>
    foreach (var city in cityGroup.Value)
    {
        dataTable.LoadDataRow(
            new object[]
            {
                cityGroup.Key,
                city.cityId,
                city.cityName,
                city.cityCode
            },
            LoadOption.PreserveChanges);
    }
}

For TValue, I used a simple data contract class.

public class City
{
    public int cityId;
    public string cityName;
    public string cityCode;
}

edited Nov 17, 2014 at 15:58

answered Nov 17, 2014 at 11:11

Steven Liekens

14.2k9 gold badges69 silver badges96 bronze badges

1 Comment

CnuVas Over a year ago

Thank you so much Steven Liekens

Collectives™ on Stack Overflow

Deserialize JSON String into DataTable / DataSet using c#

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related