0

I am trying to convert json that I deserialized to a class to a datatable by using the code below ,however the code below fails at the last line.

using (var webClient = new System.Net.WebClient())
{
    var downloadTable = webClient.DownloadString(url);
    var myTable = JsonConvert.DeserializeObject<leagueTable>(downloadTable);
    DataTable dt = myTable;
}

I know that I could deserialized directly to a datatable but I want to deserialized it to a class first so that I can manipulate the data and let the structure of the data be known to others that use the code.

The JSON is nested and the classes made for it is below

public class leagueTable
{
    public string leaguename { get; set; }
    public int gameday { get; set; }
    public System.Collections.ObjectModel.Collection<Ranking> ranking { get; set; }
}

public class Ranking
{
    public int rank { get; set; }
    public string club { get; set; }
    public int games { get; set; }
    public int points { get; set; }
}
Nate
  • 93
  • 1
  • 3
  • 11

3 Answers3

0

Have you tried having your class inherit from the DataTable class? Ideally you would be able to manipulate the data and have the end result still be a DataTable.

Matt Harper
  • 122
  • 8
  • I just tried that and I no longer get the error I used too have but now the code gets an error at runtime. On the deserealization line I get the error 'Unexpected JSON token when reading DataTable. Expected StartArray, got StartObject.' – Nate Jan 07 '15 at 16:58
0

For your json string to get deserialized into a DataTable, it needs to be an array of json objects, where each first-level object in the array corresponds to a row. For instance, this would work fine:

string data = "[{\"FirstName\": \"John\", \"LastName\": \"Smith\"}, {\"FirstName\": \"Mohammed\", \"LastName\": \"Lee\"}]"; 
var dt = JsonConvert.DeserializeObject<DataTable>(data);

Notice that that whole json string is within a []. It wont work even if it is a json object containing only an array.

If you want to deserialize into a custom type first, then I would suggest that you deserialize the exact same json into a List<T> instead of a DataTable.

class Name 
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

...

var names = JsonConvert.DeserializeObject<List<Name>>(data);

And then there are ways to convert the list to a DataTable. Having said that, once you have deserialized into a custom list type, would you still want to convert that into a DataTable? If the DataTable is to be used for data binding on controls, there are other options for binding sources.

Community
  • 1
  • 1
sudheeshix
  • 1,541
  • 2
  • 17
  • 28
  • I wanted to put it into a datatable so that i can insert it into a sql server table. is there a better way to do that? if so would you be able to edit your answer please? – Nate Jan 08 '15 at 16:51
  • In that scenario, it would be better to deserialize into a list of your custom type, and then use a [table-type input parameter](http://msdn.microsoft.com/en-us/library/bb510489.aspx) to send all the data to the sproc that inserts this data into the SQL Server table. – sudheeshix Jan 08 '15 at 18:19
  • On the deserealize line of your code I get the error 'Cannot deserialize the current JSON object into type List because the type requires a JSON array to deserialize correctly.' Also the json is nested and I have edited the question to show the classes made for the json – Nate Jan 08 '15 at 22:43
  • Wow! If you have nested types, I dont think you can use a DataTable there. With the classes you mention, your json string should be in this format for it to get deserialized into a List: [{"leaguename":"foo","gameday":1,"ranking":[{"rank":1,"club":"club1","games":1,"points":1},{"rank":2,"club":"club2","games":2,"points":2}]},{"leaguename":"bar","gameday":1,"ranking":[{"rank":3,"club":"club3","games":3,"points":3},{"rank":4,"club":"club4","games":4,"points":4}]}] – sudheeshix Jan 08 '15 at 23:18
  • I assumed it could be put into a datatable because I could assign a datasource to the desearlized json by doing var myTable = JsonConvert.DeserializeObject(downloadTable); and then GridView1.DataSource = myTable.ranking;. In the Json only the ranking part is an array. It looks like this: {"league":"topLeague","matchday":5,"ranking":[{"rank":1,"team":"Furies","playedG‌​ames":5,"points":20},{"rank":1,"team":"Tornado","playedGames":5,"points":15}]}. Do you have an idea of why it wont deserealize into a list? – Nate Jan 09 '15 at 00:06
  • Oh, i see what you are doing now. You want to deserialize your entire json into a custom class, but the rankings array inside it is the data source for a UI control, and so, you want that as a DataTable. If so, you should modify the ranking property in the leagueTable class to use DataTable instead of Collection `public DataTable ranking { get; set; }` You can then call `JsonConvert.DeserializeObject(data);` and the ranking would still be a DataTable. – sudheeshix Jan 09 '15 at 00:33
  • Nearly there now, only problem is that `public DataTable ranking {get;set;}` doesnt link with the Ranking class – Nate Jan 09 '15 at 02:44
  • It wont. You can either use a List or a DataTable. – sudheeshix Jan 09 '15 at 03:12
0

If you have only one object serialized then add the square brackets to be parsed to data table correctly.

var json = JsonConvert.SerializeObject(zone, Formatting.None,
                        new JsonSerializerSettings()
                        {
                            ReferenceLoopHandling = ReferenceLoopHandling.Ignore
                        });

json = "[" + json + "]";

DataTable table = JsonConvert.DeserializeObject<DataTable>(json);
Edi
  • 1,900
  • 18
  • 27