How to Convert Json String into DataTable in ASP.Net with C#

Question

I am using this API. It gives json output according to user input. For Example:

{
"apple.com":{"status":"regthroughothers","classkey":"domcno"},
"asdfgqwx.com":{"status":"available","classkey":"domcno"},
"microsoft.org":{"status":"unknown"},
"apple.org":{"status":"unknown"},
"microsoft.com":{"status":"regthroughothers","classkey":"domcno"},
"asdfgqwx.org":{"status":"unknown"}
}

Now I want to change this json output in dataTable and then Bind in Data Control like Gridview and repeater.

I am using this method to do this but when I passed above json output in this method , It throws an Error

Unexpected JSON token when reading DataTable. Expected StartArray, got StartObject. Path '', line 1, position 1.

Deserialize your jsonstring to some class List<myCLass> myList = JsonConvert.DeserializeObject<List<myCLas>>(jsonString); And then create new datatable using that list — Mennan
– Mennan, Commented Feb 18, 2016 at 13:32
Also this is faster : DataTable dt = (DataTable)JsonConvert.DeserializeObject(json, (typeof(DataTable))); — Mennan
– Mennan, Commented Feb 18, 2016 at 13:35
Sorry!! I am unable to understand . Can you write some code here in detail. bcoz I have already used this code " DataTable dt = (DataTable)JsonConvert.DeserializeObject(json, (typeof(DataTable))); " — user3786581
– user3786581, Commented Feb 18, 2016 at 13:36
A DataTable is a bad choice to convert it to. You're much better off creating a model that is a .NET object representing the data from your JSON. GridView and Repeater both have support for binding to anything that implements IEnumerable. This makes it much easier to work with. However, your JSON is a strange format, and the correct C# class to represent it escapes me at the moment. — mason
– mason, Commented Feb 18, 2016 at 14:06

Community · Accepted Answer · 2017-05-23 12:07:02Z

3

To convert to DataTable You must understood what You are doing:

DataTable represents a collection of rows and columns your JSON string must represent a collection which can by convert to collection of rows and columns.

This is example of correct JSON file which can by change to DataTable:

[{
    "column1": "1788",
    "column2": "19"
},
{
    "column1": "1789",
    "column2": "24"
},
{
    "column1": "1790",
    "column2": "24"
},
{
    "column1": "1790",
    "column2": "23"
},
{
    "column1": "1790",
    "column2": "21"
}]

Each pair column1 and column2 is the row

Now Your JSON is not good. I change it to this: (correct DataTable schema)

  [{
        "name": "apple.com",
        "status": "regthroughothers",
        "classkey": "domcno"
    },
    {
        "name": "asdfgqwx.com",
        "status": "available",
        "classkey": "domcno"
    },
    {
        "name": "microsoft.org",
        "status": "unknown",
        "classkey": ""
    },
    {
        "name": "apple.org",
        "status": "unknown",
        "classkey": ""
    },
    {
        "name": "microsoft.com",
        "status": "regthroughothers",
        "classkey": "domcno"
    },
    {
        "name": "asdfgqwx.org",
        "status": "unknown",
        "classkey": "domcno"
    }]

And I add [ sign and ] sign at the beginning and the end of array

Next You can do this. It is working example for deserializing above JSON string to DataTable

using Newtonsoft.Json;

public class JsonExample
    {

        private string jsonObject = "[{ \"name\": \"apple.com\",    \"status\": \"regthroughothers\",   \"classkey\": \"domcno\"},{ \"name\": \"asdfgqwx.com\", \"status\": \"available\",  \"classkey\": \"domcno\"},{ \"name\": \"microsoft.org\",    \"status\": \"unknown\",    \"classkey\": \"\"},{   \"name\": \"apple.org\",    \"status\": \"unknown\",    \"classkey\": \"\"},{   \"name\": \"microsoft.com\",    \"status\": \"regthroughothers\",   \"classkey\": \"domcno\"},{ \"name\": \"asdfgqwx.org\", \"status\": \"unknown\",    \"classkey\": \"domcno\"}]".Trim();

        public JsonExample()
        { 
            DataTable items = JsonConvert.DeserializeObject<DataTable>(jsonObject);

            foreach (DataRow item in items.Rows)
            {
                Console.WriteLine($"Name: {item[0]} Status: {item[1]}  classkey {item[2]} " );
            }

        }
    }

BUT if You sill don;t want to change JSON file

private string jsonObject = JSON_String.Replace("{", "[{").Replace("}", "}]");

public JsonExample()
        {
            JArray jArray = JArray.Parse(jsonObject);

            DataTable dt = new DataTable();

            dt.Columns.Add("Name");
            dt.Columns.Add("status");
            dt.Columns.Add("classkey");

            foreach (JProperty item in jArray[0])
            {
                var jArray2 = JArray.Parse(item.Value.ToString());

                foreach (var item2 in jArray2)
                {
                    dt.Rows.Add(item.Name, item2["status"], item2["classkey"]);
                    Console.WriteLine($"Name: {item.Name} Status: {item2["status"]}  classkey {item2["classkey"]} ");
                }
            } 
        }

Effect is the same as but You don't need do change the JSON String exept 2 replace.

Effect when You parse first string with method I write:

Additional information:

Convert JSON response to DataTable

edited May 23, 2017 at 12:07

CommunityBot

11 silver badge

answered Feb 18, 2016 at 14:27

blogprogramisty.net

1,7921 gold badge20 silver badges23 bronze badges

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

5 Comments

Gabe Over a year ago

Change how it's generated, goldeinsten is correct. The error you're getting means the json cannot parse, or even begin to start parsing because it's at line 1, pos 1. I'd say your json generation is erroneous over the actual datatable implementation.

blogprogramisty.net Over a year ago

@user3786581 ok, i see. I update my answer to do this

Panagiotis Kanavos Over a year ago

@goodeinstein note that this has been asked before and the OP is actually asking how to display the data in ASP.NET. There's no need to convert the data to a DataTable at all. While BoundFields can only bind to immediate properties, a Template field can bind to nested properties, allowing you to bind to the nested status, classkey properties directly

blogprogramisty.net Over a year ago

Thanks, I see, but since yesterday I want to answer to this question the correct way and I learn much about JSON.NET and today I discover how to use JArray object : )

user3786581 Over a year ago

Thanks to All of you ...Specially @goodeinstein.Finally I got it.

Collectives™ on Stack Overflow

How to Convert Json String into DataTable in ASP.Net with C#

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related