I am trying to deserialize JSON data to a dataset

Question

I thought JSON was supposed to be easy to use.

I have a C# .NET Windows form program in Visual Studio 2015 being used as a client consuming a Web API 2 remote server with RestSharp as the HTTP client.

the data returned from the API call is in Json format. However, when I try to deserialize the returned Json into a dataset using this statement:

DataSet data = (DataSet)JsonConvert.DeserializeObject(response.Content, (typeof(DataSet))); ,

I get this error:

"Unexpected JSON token while reading DataTable: EndArray".

If I use the text from "response.Content" and validate it here: http://www.jsonlint.com/#, the results says "Valid JSON".

When I use the debugger to view the "response.Content" as Json, it shows the correct records that I am looking for.

I have tried different formats of JsonConvert, such as:

DataSet data = JsonConvert.DeserializeObject<DataSet>(response.Content);
DataTable datat = (DataTable)JsonConvert.DeserializeObject(response.Content, (typeof(DataTable)));

I also tried this:

XmlDocument xd1 = new XmlDocument();
xd1 = (XmlDocument)JsonConvert.DeserializeXmlNode(response.Content, "RootObject");
DataSet dataset = new DataSet();
dataset.ReadXml(new XmlNodeReader(xd1));

which I got from here: http://www.c-sharpcorner.com/blogs/how-to-parse-json-string-to-dataset-in-c-sharp1

That seems to work without giving an error, but the dataset contains four tables ( RootObject, data, CustomerRoles and ShoppingCartitems). I tried removing three of the tables, but there are constraints and foreignkeys that I don't know how to remove and it won't allow me to remove the tables until I remove the constraints. If I try to remove the constraints, it gives this error: "Cannot remove unique constraint 'Constraint1'. Remove foreign key constraint 'RootObject_data' first."

Does anybody know how to use the "easy to use", "most popular" JSON format to deserialize my Json data into a dataset and come out with one table of data?

JSON data

{
    "success": true,
    "data": [{
        "Id": 6,
        "CustomerGuid": "70b390d8-82d5-4bba-aa68-fc8268a1b1ff",
        "UserName": "victoria_victoria@nopCommerce.com",
        "Email": "victoria_victoria@nopCommerce.com",
        "CustomerRoles": [{
            "Id": 3,
            "Name": "Registered",
            "SystemName": "Registered"
        }],
        "AdminComment": null,
        "IsTaxExempt": false,
        "AffiliateId": 0,
        "VendorId": 0,
        "HasShoppingCartItems": false,
        "Active": false,
        "Deleted": false,
        "IsSystemAccount": false,
        "SystemName": null,
        "LastIpAddress": null,
        "CreatedOnUtc": "\/Date(1472933472393)\/",
        "LastLoginDateUtc": null,
        "LastActivityDateUtc": "\/Date(1472933472393)\/",
        "ExternalAuthenticationRecords": [],
        "ShoppingCartItems": []
    }, {
        "Id": 5,
        "CustomerGuid": "eb9e6f24-f362-4c10-942a-366e2919dc11",
        "UserName": "brenda_lindgren@nopCommerce.com",
        "Email": "brenda_lindgren@nopCommerce.com",
        "CustomerRoles": [{
            "Id": 3,
            "Name": "Registered",
            "SystemName": "Registered"
        }],
        "AdminComment": null,
        "IsTaxExempt": false,
        "AffiliateId": 0,
        "VendorId": 0,
        "HasShoppingCartItems": false,
        "Active": false,
        "Deleted": false,
        "IsSystemAccount": false,
        "SystemName": null,
        "LastIpAddress": null,
        "CreatedOnUtc": "\/Date(1472933472363)\/",
        "LastLoginDateUtc": null,
        "LastActivityDateUtc": "\/Date(1472933472363)\/",
        "ExternalAuthenticationRecords": [],
        "ShoppingCartItems": []
    }, {
        "Id": 4,
        "CustomerGuid": "9f46dbae-6942-410c-90b8-9b38a0890064",
        "UserName": "james_pan@nopCommerce.com",
        "Email": "james_pan@nopCommerce.com",
        "CustomerRoles": [{
            "Id": 3,
            "Name": "Registered",
            "SystemName": "Registered"
        }],
        "AdminComment": null,
        "IsTaxExempt": false,
        "AffiliateId": 0,
        "VendorId": 0,
        "HasShoppingCartItems": false,
        "Active": false,
        "Deleted": false,
        "IsSystemAccount": false,
        "SystemName": null,
        "LastIpAddress": null,
        "CreatedOnUtc": "\/Date(1472933472317)\/",
        "LastLoginDateUtc": null,
        "LastActivityDateUtc": "\/Date(1472933472317)\/",
        "ExternalAuthenticationRecords": [],
        "ShoppingCartItems": []
    }, {
        "Id": 3,
        "CustomerGuid": "6277386b-13ee-427b-9cfe-4ebfa487c340",
        "UserName": "arthur_holmes@nopCommerce.com",
        "Email": "arthur_holmes@nopCommerce.com",
        "CustomerRoles": [{
            "Id": 3,
            "Name": "Registered",
            "SystemName": "Registered"
        }],
        "AdminComment": null,
        "IsTaxExempt": false,
        "AffiliateId": 0,
        "VendorId": 0,
        "HasShoppingCartItems": false,
        "Active": false,
        "Deleted": false,
        "IsSystemAccount": false,
        "SystemName": null,
        "LastIpAddress": null,
        "CreatedOnUtc": "\/Date(1472933472253)\/",
        "LastLoginDateUtc": null,
        "LastActivityDateUtc": "\/Date(1472933472253)\/",
        "ExternalAuthenticationRecords": [],
        "ShoppingCartItems": []
    }, {
        "Id": 2,
        "CustomerGuid": "241f45f1-b38c-4e22-8c5a-743fa3276620",
        "UserName": "steve_gates@nopCommerce.com",
        "Email": "steve_gates@nopCommerce.com",
        "CustomerRoles": [{
            "Id": 3,
            "Name": "Registered",
            "SystemName": "Registered"
        }],
        "AdminComment": null,
        "IsTaxExempt": false,
        "AffiliateId": 0,
        "VendorId": 0,
        "HasShoppingCartItems": false,
        "Active": false,
        "Deleted": false,
        "IsSystemAccount": false,
        "SystemName": null,
        "LastIpAddress": null,
        "CreatedOnUtc": "\/Date(1472933472207)\/",
        "LastLoginDateUtc": null,
        "LastActivityDateUtc": "\/Date(1472933472207)\/",
        "ExternalAuthenticationRecords": [],
        "ShoppingCartItems": []
    }, {
        "Id": 1,
        "CustomerGuid": "a940dc03-5f52-47d2-9391-8597b3b31cf2",
        "UserName": "tony@lakesideos.com",
        "Email": "tony@lakesideos.com",
        "CustomerRoles": [{
            "Id": 1,
            "Name": "Administrators",
            "SystemName": "Administrators"
        }, {
            "Id": 2,
            "Name": "Forum Moderators",
            "SystemName": "ForumModerators"
        }, {
            "Id": 3,
            "Name": "Registered",
            "SystemName": "Registered"
        }],
        "AdminComment": null,
        "IsTaxExempt": false,
        "AffiliateId": 0,
        "VendorId": 0,
        "HasShoppingCartItems": true,
        "Active": true,
        "Deleted": false,
        "IsSystemAccount": false,
        "SystemName": null,
        "LastIpAddress": "71.185.255.7",
        "CreatedOnUtc": "\/Date(1472933470783)\/",
        "LastLoginDateUtc": "\/Date(1477522483903)\/",
        "LastActivityDateUtc": "\/Date(1477523996553)\/",
        "ExternalAuthenticationRecords": [],
        "ShoppingCartItems": [{
            "Id": 1,
            "StoreId": 1,
            "ShoppingCartTypeId": 1,
            "CustomerId": 1,
            "ProductId": 18,
            "AttributesXml": null,
            "CustomerEnteredPrice": 0.0000,
            "Quantity": 1,
            "CreatedOnUtc": "\/Date(1473801903447)\/",
            "UpdatedOnUtc": "\/Date(1473803336207)\/",
            "IsFreeShipping": false,
            "IsShipEnabled": true,
            "AdditionalShippingCharge": 0.0000,
            "IsTaxExempt": false
        }]
    }]
}

There is no "most popular" format for your data. "Unexpected JSON token" means the parser you are using didn't expect "EndArray" or the `]` character — OneCricketeer, Oct 27 '16 at 21:10
@cricket_007 I'm sorry, but when I go here: http://www.newtonsoft.com/json, they claim that they are the most popular .NET Library. — Tony, Oct 27 '16 at 21:17
@cricket_007 >> "Unexpected JSON token" means the parser you are using didn't expect "EndArray" or the ] character<< Why can't I just remove that "]" character into a substring and use that substring? Thanks. — Tony, Oct 28 '16 at 12:41
The Dataset class that you are trying to deserialize your JSON to does not match the format of JSON you have. Like I said below, you don't have a Dataset, you have a custom object. — OneCricketeer, Oct 28 '16 at 13:19
A different option that you have is to not deserialize your JSON into an object at all and instead parse it yourself and build table objects and Dataset objects — OneCricketeer, Oct 28 '16 at 13:22
And related to my last point: you haven't even told us what the table should look like. You have doubly nested data - this array of Customers has an array of Shopping Cart items, for example. How are you expecting to represent that in a single table? — OneCricketeer, Oct 28 '16 at 13:27
@cricket_007 - What you are saying makes sense now. I apologize for not understanding this sooner. I never thought about how the deserializer would handle the three arrays nested inside the customer records. I will begin to create the model classes that are needed and follow the outline that you have created. Thanks for your help. — Tony, Oct 29 '16 at 12:44

OneCricketeer · Answer 1 · 2016-10-28T13:46:05.323

1

Here's a brief overview of what you need to do.

Taking from NewtonSoft - Deserialize Object example

Snippet of your data

{
    "Id": 1,
    "CustomerGuid": "a940dc03-5f52-47d2-9391-8597b3b31cf2",
    "UserName": "tony@lakesideos.com",
    "Email": "tony@lakesideos.com"

This is a Customer object. It needs it's own C# model class.

public class Customer {

    int id;
    string guid;
    string username;
    string email;
    // etc...

    // getters and setters...
}

This is stored in a list of data

"data": [ { ... }, { ... } ]

You need another Object to hold a list of those objects plus a boolean success value. Let's call this one Response.

{
    "success": true,
    "data": [{

That class starts with

public class Response {

    bool success;
    IList<Customer> data;

Then, you need other lists and object classes for something like this

"CustomerRoles": [{
        "Id": 3,
        "Name": "Registered",
        "SystemName": "Registered"
    }],

Once you've modeled the entire domain of classes, you should be able to have

Response res = JsonConvert.DeserializeObject<Response>(json);

Since you seem to have already tried that, and are getting an error about the END_ARRAY character, something within your DataSet class is not correctly mapped to a List

And then get res.data to access the list of data, then you can loop over those Customer objects to build your Dataset / table objects, I assume to display your data in some list of the GUI

edited Oct 28 '16 at 13:46

answered Oct 27 '16 at 21:27

OneCricketeer

179,855
19
132
245

2

You can easily model your clases in: http://json2csharp.com/ , copy and paste your json file content and you'll get the c# clases – McNets Oct 27 '16 at 21:31
Thanks @mcNets I knew there was a site like that for Java – OneCricketeer Oct 27 '16 at 21:32
I use xml files in my web services, and then convert this files to dataset, xml to dataset add foreigen keys to the dataset.tables, but I'm not sure what will happen if you define your own dataset (using dataset editor) and then ReadXml using this dataset. – McNets Oct 27 '16 at 21:50
I appreciate everyone's help, but it seems much more complicated and much more work to do this than what I see here: [link](http://www.newtonsoft.com/json/help/html/DeserializeDataSet.htm). Has anyone done what is shown in that link? Thanks, Tony – Tony Oct 27 '16 at 22:20
@Tony Your JSON is not a `DataSet`, it is some [custom Object](http://www.newtonsoft.com/json/help/html/DeserializeObject.htm) – OneCricketeer Oct 27 '16 at 22:21
@AnthonyRussell yes, I use web services to download well konw xml files. – McNets Oct 27 '16 at 22:33
@mcNets XML is not part of the question. I do not know what you are referring to. – OneCricketeer Oct 27 '16 at 22:33
I'm confused about all this. >>Your JSON is not a DataSet<<. I thought that was what that article was telling. How to make a dataset out of the JSON data. Thanks. – Tony Oct 27 '16 at 22:33
1

@mcNets :-| I meant that you made a MVVM pun... When you convert JSON into business objects they're called models – DotNetRussell Oct 27 '16 at 22:33
@Tony Your "API call" is returning JSON. You need to "deserialize" this JSON into a C# object. The **next** step is loading these objects into a `Dataset`. Take it one step at a time, don't jump through more hoops than necessary. – OneCricketeer Oct 27 '16 at 22:35
@cricket_007 >>Your JSON is not a DataSet, it is some custom Object<< I thought it is just a text string formatted a certain way. Does the JsonConvert.DeserializeObject method know that it is not a Dataset? I am not understanding this. Thanks. – Tony Oct 28 '16 at 12:52
Exactly... It is text formatted a certain way. That certain way **is not a Dataset**, so you get an error. Newtonsoft can't build a Dataset object that has tables and rows from something with an email, guid, username, etc. Get it? It is trying to literally convert the JSON data into a C# class verbatim based on the names of keys in the JSON compared to the attributes of the C# class. It does no conversion to help you build a table – OneCricketeer Oct 28 '16 at 13:33
If you still don't understand what I'm trying to say, then your same question seems to have been asked before with all different JSON formats, which is probably what is confusing you. You could try something like this, maybe http://stackoverflow.com/a/34922372/2308683 – OneCricketeer Oct 28 '16 at 13:44
I created all of the classes (Response, Customer, ShoppingCartItem and ExternalAuthenticationRecord). When I execute this statement: "Response res = JsonConvert.DeserializeObject(response.Content)";, if I look at "res" in the debugger, it shows success:false and data:null. It does not catch any error. Response is coded as you have indicated. I am not sure of why I am not getting any of the data from the response.Content(json) data. – Tony Oct 31 '16 at 14:26
I'd start by extracting pieces of the JSON and making sure you can build one `Customer`, for example. Though, seems like an opportunity for a [new post](http://stackoverflow.com/questions/ask). Since you seem to understand what you need to do though, you may accept this answer as well. – OneCricketeer Oct 31 '16 at 14:32

Brinky · Answer 2 · 2016-10-28T00:30:57.883

0

Try starting from Response.Data instead of Response.Content, that will get you one layer in to expose the actual data to deserialize.
For example:

var response = _requestProcessor.Process<Request, Response>(
                new Request(id)).dataList;

return new JsonNetResult() { Data = response, JsonRequestBehavior= JsonRequestBehavior.AllowGet };

Whatever the Request object is, it has an object that holds the responses' data in it; that is the actual item to deserialize.

Perhaps what you need to do is what Cricket_007 suggests, and make a class to encapsulate the Success object returned in the JSON, then deserialize to one Success object and a Data object, which is a list of the objects you're trying to get to. The steps you need to follow are:

Convert the whole JSON string to an object that consists of the two objects within, Success and Data. JSONObject content = new JSONObject(Response.Content); perhaps, this is untested.
From that object, deserialize just the Data object within it.

content.data -or- content["data"]

or some such should get you to the parts you want to work with.

edited Oct 28 '16 at 00:30

answered Oct 27 '16 at 21:55

Brinky

418
3
9

Brinky, there is no Response.Data. Maybe i'm not understanding you, but I don't see where in the request object there is responses' data. Thanks. – Tony Oct 27 '16 at 22:28
Your Response.Content object is encapsulating the entire response from the service you are calling. What you want to do is further refine the part of Response,Content down to the data: object within, then deserialize that portion. That is the relevant part for your uses. Perhaps convert the entire JSON string returned to a JSONObject: – Brinky Oct 27 '16 at 23:43
Brinky, If I understand you correctly, it sounds like I just need to make a substring from the response.Content string and that substring should contain the data I want to deserialize into a Dataset. I would essentially remove the part of the Json data that looks like this: ""success": true,". – Tony Oct 28 '16 at 12:13

I am trying to deserialize JSON data to a dataset

2 Answers2

Linked