Bind API Json response to gridview

Question

User1291657044 posted

I have the below JSON coming from an API and would like to bind it to gridview on a webform. I have found examples that help me bind the "status" and "source" portion of it but none of them show me how I could get the "data" part of JSON appropriately. Any help would be appreciated. 

[
{
"status":"OK",
"data":[
{
"Class":"class1",
"segments":[
{
"seg1":"2020-06-20T08:00:00Z",
"seg2":"3.1"
}
],
"price":"10"
},
{
"Class":"class2",
"segments":[
{
"seg1":"2020-06-20T08:00:00Z",
"seg2":"2.2"
}
],
"price":"12"
},
{
"Class":"class3",
"segments":[
{
"seg1":"2020-06-20T08:00:00Z",
"seg2":"2.1"
}
],
"price":"20"
}
],
"source":"API"
}
]

Answer 1

User1535942433 posted

Hi eyobzeleke,

Accroding to your description,as far as I think,you could create class and stored in datatable.

More details,you could refer to below codes:

protected void Page_Load(object sender, EventArgs e)
        {
            String json = "{" +
        "\"status\": \"OK\"," +
        "\"data\": [" +
           " {" +
                "\"Class\": \"class1\"," +
                "\"segments\": [" +
                "{"+
                "\"seg1\":\"2020-06-20T08:00:00Z\","+
                "\"seg2\":\"3.1\","+
                "}"+
                "],"+
                "\"price\":\"10\","+
            "}," +
            "{" +
               "\"Class\": \"class2\"," +
                "\"segments\": [" +
                "{" +
                "\"seg1\":\"2020-06-20T08:00:00Z\"," +
                "\"seg2\":\"2.2\"," +
                "}" +
                "]," +
                "\"price\":\"12\"," +
             "}," +
            "{" +
               "\"Class\": \"class3\"," +
                "\"segments\": [" +
                "{" +
                "\"seg1\":\"2020-06-20T08:00:00Z\"," +
                "\"seg2\":\"2.1\"," +
                "}" +
                "]," +
                "\"price\":\"20\"," +
            "}" +
        "]," +
        "\"source\": \"API\"," +
            "}";
            RootObect data = (RootObect)JsonConvert.DeserializeObject(json, (typeof(RootObect)));
            DataTable dtdata = new DataTable();
            dtdata.Columns.Add("status");
            dtdata.Columns.Add("Class");
            dtdata.Columns.Add("seg1");
            dtdata.Columns.Add("seg2");
            dtdata.Columns.Add("price");
            dtdata.Columns.Add("source");

            foreach (var item in data.data)
            {
                DataRow row = dtdata.NewRow();
                row[0] = data.status;
                row[5] = data.source;
                string Class = item.Class;
                foreach (var items in item.segments)
                {
                    string seg1 = items.seg1;
                    string seg2 = items.seg2;
                    row[2] = seg1;
                    row[3] = seg2;
                };
                string price = item.price;
                
                row[1] = Class;
                row[4] = price;
                dtdata.Rows.Add(row);

            }

            GridView1.DataSource = dtdata;
            GridView1.DataBind();

        }

        public class RootObect
        {
            public string status { get; set; }
            public List<Data> data { get; set; }
            public string source { get; set; }
        }
        public class Data
        {
            public string Class{ get;set; }
            public List<Segments> segments { get; set; }
            public string price { get; set; }
        }
        public class Segments
        {
            public string seg1 { get; set; }
            public string seg2 { get; set; }
        }

Best regards,

Yijing Sun

Answer 2

User1291657044 posted

Thank you Yij! Exactly what i was looking for!

Answer 3

User1291657044 posted

na

Answer 4

User1291657044 posted

Hi, how would you do this if the json string looks like the following? I have added a second element to the segments section and the foreach loop only give me the last occurrence? I was expecting 6 rows and not just 3. Any help would be appreciated.

String json = "{" +
"\"status\": \"OK\"," +
"\"data\": [" +
" {" +
"\"Class\": \"class1\"," +
"\"segments\": [" +
"{" +
"\"seg1\":\"2020-06-20T08:00:00Z\"," +
"\"seg2\":\"3.1\"," +
"}," +
"{" +
"\"seg1\":\"2050-06-20T08:00:00Z\"," +
"\"seg2\":\"9.1\"," +
"}" +
"]," +
"\"price\":\"10\"," +
"}," +
"{" +
"\"Class\": \"class2\"," +
"\"segments\": [" +
"{" +
"\"seg1\":\"2020-06-20T08:00:00Z\"," +
"\"seg2\":\"2.2\"," +
"}," +
"{" +
"\"seg1\":\"2050-06-20T08:00:00Z\"," +
"\"seg2\":\"5.8\"," +
"}" +
"]," +
"\"price\":\"12\"," +
"}," +
"{" +
"\"Class\": \"class3\"," +
"\"segments\": [" +
"{" +
"\"seg1\":\"2020-06-20T08:00:00Z\"," +
"\"seg2\":\"2.1\"," +
"}," +
"{" +
"\"seg1\":\"2020-09-20T08:00:00Z\"," +
"\"seg2\":\"2.1\"," +
"}" +
"]," +
"\"price\":\"20\"," +
"}" +
"]," +
"\"source\": \"API\"," +
"}";

This is what I was expecting to see in the gridview:

status	Class	seg1	seg2	price	source
OK	class1	2020-06-20T08:00:00Z	3.1	10	API
OK	class2	2020-06-20T08:00:00Z	2.2	12	API
OK	class3	2020-06-20T08:00:00Z	2.1	20	API
OK	class1	2050-06-20T08:00:00Z	9.1	10	API
OK	class2	2050-06-20T08:00:00Z	2.2	12	API
OK	class3	2020-09-20T08:00:00Z	5.8	20	API

Answer 5

User1738843376 posted

Hi,

Apparently you know the data structure of the JSON you are receiving, so i would deserialize the JSON into objects (you can refer to this post https://stackoverflow.com/questions/3142495/deserialize-json-into-c-sharp-dynamic-object/3806407#3806407 and see how to do it), then use the resulting object and bind the Data portion (that should at that point be a nested object inside the main one) to the DataGrid.

Answer 6

User1291657044 posted

Hi Obelix. so the code piece above works and gives me results in gridview but for some reason the foreach loop is adding values (price) instead of individually sorting them piece by piece. I want no values to be added but displayed as they are. Feel like the foreach loop need to be fixed to handle it? Appreciate all the help.

RootObect data = (RootObect)JsonConvert.DeserializeObject(json, (typeof(RootObect)));
            DataTable dtdata = new DataTable();
            dtdata.Columns.Add("status");
            dtdata.Columns.Add("Class");
            dtdata.Columns.Add("seg1");
            dtdata.Columns.Add("seg2");
            dtdata.Columns.Add("price");
            dtdata.Columns.Add("source");

            foreach (var item in data.data)
            {
                DataRow row = dtdata.NewRow();
                row[0] = data.status;
                row[5] = data.source;
                string Class = item.Class;
                foreach (var items in item.segments)
                {
                    string seg1 = items.seg1;
                    string seg2 = items.seg2;
                    row[2] = seg1;
                    row[3] = seg2;
                };
                string price = item.price;
                
                row[1] = Class;
                row[4] = price;
                dtdata.Rows.Add(row);

            }

            GridView1.DataSource = dtdata;
            GridView1.DataBind();

        }

        public class RootObect
        {
            public string status { get; set; }
            public List<Data> data { get; set; }
            public string source { get; set; }
        }
        public class Data
        {
            public string Class{ get;set; }
            public List<Segments> segments { get; set; }
            public string price { get; set; }
        }
        public class Segments
        {
            public string seg1 { get; set; }
            public string seg2 { get; set; }
        }

Answer 7

User1738843376 posted

Hey eyobzeleke, 

I don't see how the code you posted can be adding (summing) the prices, as there is no place it it where it could apparently happen. Have you checked if you have no logic in place on the ASPx file,  inside the GridView that somehow sums the values? Can you post the data and the resulting GridView, so maybe we could detect a pattern that might help identify why this is occurring?

Also, might be worth trying, change the variable named "Class" to something else that does not correspond to a coding language keyword... might be generating some sort of mess...

Answer 8

User1291657044 posted

Sure, Obelix, so I looked at this again and you're right this doesn't do the summing... another similar one that I use is doing it. However, the foreach loop on this still doesn't give me all the values I needed. Here is the resultset I am getting in the gridview and the example code I am using:

status	Class	seg1	seg2	price	source
OK	class1	2050-06-20T08:00:00Z	9.1	10	API
OK	class2	2050-06-20T08:00:00Z	5.8	12	API
OK	class3	2020-09-20T08:00:00Z	2.1	20	API

protected void Page_Load(object sender, EventArgs e)
{
String json = "{" +
"\"status\": \"OK\"," +
"\"data\": [" +
" {" +
"\"Class\": \"class1\"," +
"\"segments\": [" +
"{" +
"\"seg1\":\"2020-06-20T08:00:00Z\"," +
"\"seg2\":\"3.1\"," +
"}," +
"{" +
"\"seg1\":\"2050-06-20T08:00:00Z\"," +
"\"seg2\":\"9.1\"," +
"}" +
"]," +
"\"price\":\"10\"," +
"}," +
"{" +
"\"Class\": \"class2\"," +
"\"segments\": [" +
"{" +
"\"seg1\":\"2020-06-20T08:00:00Z\"," +
"\"seg2\":\"2.2\"," +
"}," +
"{" +
"\"seg1\":\"2050-06-20T08:00:00Z\"," +
"\"seg2\":\"5.8\"," +
"}" +
"]," +
"\"price\":\"12\"," +
"}," +
"{" +
"\"Class\": \"class3\"," +
"\"segments\": [" +
"{" +
"\"seg1\":\"2020-06-20T08:00:00Z\"," +
"\"seg2\":\"2.1\"," +
"}," +
"{" +
"\"seg1\":\"2020-09-20T08:00:00Z\"," +
"\"seg2\":\"2.1\"," +
"}" +
"]," +
"\"price\":\"20\"," +
"}" +
"]," +
"\"source\": \"API\"," +
"}";
RootObect data = (RootObect)JsonConvert.DeserializeObject(json, (typeof(RootObect)));
DataTable dtdata = new DataTable();
dtdata.Columns.Add("status");
dtdata.Columns.Add("Class");
dtdata.Columns.Add("seg1");
dtdata.Columns.Add("seg2");
dtdata.Columns.Add("price");
dtdata.Columns.Add("source");

foreach (var item in data.data)
{
DataRow row = dtdata.NewRow();
row[0] = data.status;
row[5] = data.source;
string Class = item.Class;
foreach (var items in item.segments)
{
string seg1 = items.seg1;
string seg2 = items.seg2;
row[2] = seg1;
row[3] = seg2;
};
string price = item.price;

row[1] = Class;
row[4] = price;
dtdata.Rows.Add(row);

}

GridView1.DataSource = dtdata;
GridView1.DataBind();

}

public class RootObect
{
public string status { get; set; }
public List<Data> data { get; set; }
public string source { get; set; }
}
public class Data
{
public string Class { get; set; }
public List<Segments> segments { get; set; }
public string price { get; set; }
}
public class Segments
{
public string seg1 { get; set; }
public string seg2 { get; set; }
}

I am looking to see these values included in the resultset as well. They are the 1st occurrents in the list of segments (bolded in the code). How do i get all the values? It will be a total of 6 rows in this specific example. Thanks for the help.

OK	class1	2020-06-20T08:00:00Z	3.1	10	API
OK	class2	2020-06-20T08:00:00Z	2.2	12	API
OK	class3	2020-06-20T08:00:00Z	2.1	20	API

Answer 9

User1738843376 posted

Ok, i can see the issue now.

Each class has multiple segments, and since you're iterating the classes, you must iterate per segment before building each row:

foreach (var item in data.data)
{
	
	foreach (var items in item.segments)
	{
	
		DataRow row = dtdata.NewRow();
		
		string classItem = item.Class;
		string price = item.price;
		string seg1 = items.seg1;
		string seg2 = items.seg2;
		
		row[0] = data.status;
		row[1] = classItem;
		row[2] = seg1;
		row[3] = seg2;			
		row[4] = price;
		row[5] = data.source;
		
		dtdata.Rows.Add(row);
		
	};	

}

The way you were doing it, you would only get the last segment to be iterated inside each class

Answer 10

User1291657044 posted

Thanks Obelix! That works perfectly!