Answered by:
iteration over a json string

Question
-
User-1950594959 posted
I am working on deserialize json string using JavaScriptSerializer in C#, and trying to parse the json string and map it to the appropriate columns in the sql server table for inserting data. I have the sample json string as below.
JSON
{ "event": [ { "Id": 456895, "Name": "Chelsea - Arsenal", "BetOffers": [ { "BetType": "Game", "Picks": [ { "Pick": "1", "Odds": 1.15 }, { "Pick": "x", "Odds": 1.46 }, { "Pick": "2", "Odds": 1.15 } ] } ] } , { "Id": 456879, "Name": "Liverpool - Manchester United", "BetOffers": [ { "BetType": "Game", "Picks": [ { "Pick": "1", "Odds": 1.20 }, { "Pick": "x", "Odds": 1.42 }, { "Pick": "2", "Odds": 1.85 } ] } ] } ] }
Based on the json output string I am writing my class in C# as below.
Class
public class Event { public int Id { get; set; } public string Name { get; set; } public List<BetOffer> BetOffers { get; set; } } public class BetOffer { public string BetType { get; set; } public List<BetPick> Picks { get; set; } } public class BetPick { public string Pick { get; set; } public double Odds { get; set; } } public class MyRootObject { public List<List<BetPick>> @event { get; set; } } var root = new JavaScriptSerializer().Deserialize<MyRootObject>(jsonString);
Also, unable to read deserialized json string into the variable root.
Insert data into the table as following.
string connectionString = "Database ConnectionString"; DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("ID", typeof(string))); dt.Columns.Add(new DataColumn("Name", typeof(string))); dt.Columns.Add(new DataColumn("BetType", typeof(string))); dt.Columns.Add(new DataColumn("Pick", typeof(string))); dt.Columns.Add(new DataColumn("Odds", typeof(string))); DataRow dr = dt.NewRow(); for (var i = 0; i < root.event.Count; i++) { dr = dt.NewRow(); dr["ID"] = root.event[i].Id;//stuck at table to json string parse and map. List does not contain definition for Id dt.Rows.Add(dr); }
Question
I'm stuck with how to parse json string and map data to appropriate columns in the table for inserting data into a sql table. Unable to find root.event[0].Id from the json string?
Error
List does not contain definition for Id
Friday, November 20, 2020 2:59 PM
Answers
-
User1686398519 posted
Hi dotnetenthusiast,
- Your code needs to be modified.
-
dr["BetType"] = root.events[i].BetOffers.BetType; dr["Pick"] = root.events[i].BetOffers.Pick; dr["Odds"] = root.events[i].BetOffers.Odds;
-
- You can improve your code like this:
- You can use reflection to convert "root" into a DataTable.
-
string jsonstring= "{\"events\":[{\"Id\":456895,\"Name\":\"Chelsea - Arsenal\",\"BetOffers\":{\"BetType\":\"Game\",\"Pick\":\"1\",\"Odds\":1.15}},{\"Id\":456879,\"Name\":\"Liverpool - Manchester United\",\"BetOffers\":{\"BetType\":\"Game\",\"Pick\":\"1\",\"Odds\":1.2}}]}"; var root = JsonSerializer.Deserialize<MyRootObject>(jsonstring); DataTable dt2 = ToDataTable<Event>(root.events);
-
public DataTable ToDataTable<T>(List<T> items) { DataTable table = new DataTable(typeof(T).Name); PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T)); for (int i = 0; i < props.Count; i++) { PropertyDescriptor prop = props[i]; table.Columns.Add(prop.Name, prop.PropertyType); } object[] values = new object[props.Count]; foreach (T item in items) { for (int i = 0; i < values.Length; i++) { values[i] = props[i].GetValue(item); } table.Rows.Add(values); } return table; }
Best Regards,
YihuiSun
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, November 25, 2020 8:55 AM - Your code needs to be modified.
All replies
-
User475983607 posted
List does not contain definition for IdCorrect. You have a List of a List.
root.event[0][i]
Friday, November 20, 2020 3:40 PM -
User-1950594959 posted
Hi @mgebhard, Thanks for your response. Yes, fixed the iteration as below but, for some reason variable j is not getting incremented.
for (int i = 0; i < root.@event.Count; i++) { for (int j = 0; j < root.@event[i].Count; j++) { Console.WriteLine("Id: " + root.@event[i][j].Id); Console.WriteLine("Name: " + root.@event[i][j].Name); for (int k = 0; k < root.@event[i][j].BetOffers.Count; k++) { Console.WriteLine("BetType: " + root.@event[i][j].BetOffers[k].BetType); } Console.WriteLine(); } }
Regards
Friday, November 20, 2020 6:05 PM -
User475983607 posted
dotnetenthusiast
Hi @mgebhard, Thanks for your response. Yes, fixed the iteration as below but, for some reason variable j is not getting incremented.Correct, and the expected behavior since there is 1 item in the outer array.
for (int i = 0; i < root.@event[0].Count; i++) {
Maybe the code that generates the JSON needs to be fixed? Your class structure does not match the JSON.
Friday, November 20, 2020 6:21 PM -
User-1950594959 posted
Hi @mgebhard, Fixed my json output in the original post.
Friday, November 20, 2020 7:24 PM -
User475983607 posted
Example
{ "events": [ { "Id": 456895, "Name": "Chelsea - Arsenal", "BetOffers": [ { "BetType": "Game", "Picks": [ { "Pick": "1", "Odds": 1.15 }, { "Pick": "x", "Odds": 1.46 }, { "Pick": "2", "Odds": 1.15 } ] } ] }, { "Id": 456879, "Name": "Liverpool - Manchester United", "BetOffers": [ { "BetType": "Game", "Picks": [ { "Pick": "1", "Odds": 1.20 }, { "Pick": "x", "Odds": 1.42 }, { "Pick": "2", "Odds": 1.85 } ] } ] } ] }
Data Model
public class Rootobject { public Event[] events { get; set; } } public class Event { public int Id { get; set; } public string Name { get; set; } public Betoffer[] BetOffers { get; set; } } public class Betoffer { public string BetType { get; set; } public BetPick[] Picks { get; set; } } public class BetPick { public string Pick { get; set; } public float Odds { get; set; } }
.NET Core Console
static async Task Main(string[] args) { Rootobject obj; //fileName is a static string path to the JSON file using (FileStream fs = File.OpenRead(fileName)) { obj = await JsonSerializer.DeserializeAsync<Rootobject>(fs); } foreach (Event evnt in obj.events) { Console.WriteLine(evnt.Id); Console.WriteLine(evnt.Name); foreach(Betoffer item in evnt.BetOffers) { Console.WriteLine($"\t{item.BetType}"); foreach(BetPick pick in item.Picks) { Console.WriteLine($"\t\t{pick.Pick}"); Console.WriteLine($"\t\t{pick.Odds}"); } } } }
Results
456895 Chelsea - Arsenal Game 1 1.15 x 1.46 2 1.15 456879 Liverpool - Manchester United Game 1 1.2 x 1.42 2 1.85
Friday, November 20, 2020 7:37 PM -
User-1950594959 posted
Hi @mgedhard, I have constraints using new frameworks and newtonsoft library. Could you please guide me using the javascriptserializer.
Thank you
Friday, November 20, 2020 11:52 PM -
User475983607 posted
I have constraints using new frameworks and newtonsoft library. Could you please guide me using the javascriptserializer.The only change is highlighted below.
static void Main(string[] args) { //fileName is a static string path to the JSON file string jsonString = File.ReadAllText(fileName); Rootobject obj = new JavaScriptSerializer().Deserialize<Rootobject>(jsonString); foreach (Event evnt in obj.events) { Console.WriteLine(evnt.Id); Console.WriteLine(evnt.Name); foreach (Betoffer item in evnt.BetOffers) { Console.WriteLine($"\t{item.BetType}"); foreach (BetPick pick in item.Picks) { Console.WriteLine($"\t\t{pick.Pick}"); Console.WriteLine($"\t\t{pick.Odds}"); } } } }
Saturday, November 21, 2020 12:09 AM -
User-1950594959 posted
Hi mgebhard, I am running into error "[Task Name] Error: No parameterless constructor defined for type of BetOffer[]'.
Monday, November 23, 2020 5:53 AM -
User1686398519 posted
Hi dotnetenthusiast,
You can also use Newtonsoft.Json or System.Text.Json for serialization and deserialization.
You can refer to the following solutions.
- Newtonsoft.Json
- Code
-
var jsonstring = "{\"event\":[{\"Id\":456895,\"Name\":\"Chelsea - Arsenal\",\"BetOffers\":[{\"BetType\":\"Game\",\"Picks\":[{\"Pick\":\"1\",\"Odds\":1.15},{\"Pick\":\"x\",\"Odds\":1.46},{\"Pick\":\"2\",\"Odds\":1.15}]}]},{\"Id\":456879,\"Name\":\"Liverpool - Manchester United\",\"BetOffers\":[{\"BetType\":\"Game\",\"Picks\":[{\"Pick\":\"1\",\"Odds\":1.2},{\"Pick\":\"x\",\"Odds\":1.42},{\"Pick\":\"2\",\"Odds\":1.85}]}]}]}"; var root = JsonConvert.DeserializeObject(jsonstring); List<dynamic> list = new List<dynamic>(); list.Add(root); var teststring = JsonConvert.SerializeObject(list.ToArray()); DataTable table = JsonConvert.DeserializeObject<DataTable>(teststring);Here is the result
-
- Code
- System.Text.Json
- Code
-
var jsonstring = "{\"event\":[{\"Id\":456895,\"Name\":\"Chelsea - Arsenal\",\"BetOffers\":[{\"BetType\":\"Game\",\"Picks\":[{\"Pick\":\"1\",\"Odds\":1.15},{\"Pick\":\"x\",\"Odds\":1.46},{\"Pick\":\"2\",\"Odds\":1.15}]}]},{\"Id\":456879,\"Name\":\"Liverpool - Manchester United\",\"BetOffers\":[{\"BetType\":\"Game\",\"Picks\":[{\"Pick\":\"1\",\"Odds\":1.2},{\"Pick\":\"x\",\"Odds\":1.42},{\"Pick\":\"2\",\"Odds\":1.85}]}]}]}"; var root = JsonSerializer.Deserialize<MyRootObject>(jsonstring); List<MyRootObject> list = new List<MyRootObject>(); list.Add(root); PropertyDescriptorCollection props =TypeDescriptor.GetProperties(typeof(MyRootObject)); DataTable table = new DataTable(); for (int i = 0; i < props.Count; i++) { PropertyDescriptor prop = props[i]; table.Columns.Add(prop.Name, prop.PropertyType); } object[] values = new object[props.Count]; foreach (MyRootObject item in list) { for (int i = 0; i < values.Length; i++) { values[i] = props[i].GetValue(item); } table.Rows.Add(values); }
-
public class Event { public int Id { get; set; } public string Name { get; set; } public List<BetOffer> BetOffers { get; set; } } public class BetOffer { public string BetType { get; set; } public List<BetPick> Picks { get; set; } } public class BetPick { public string Pick { get; set; } public double Odds { get; set; } } public class MyRootObject { public List<Event> @event { get; set; } }
-
- Code
Here is the result.
Best Regards,
YihuiSun
Monday, November 23, 2020 6:44 AM - Newtonsoft.Json
-
User-1950594959 posted
Hi YihuiSun, Thanks very much for your response. I modified my json to below with the models and run into error with the data table.
Error: Unexpected JSON token when reading DataTable: StartObject. Path '[0].events[0].BetOffers'
{ "events": [ { "Id": 456895, "Name": "Chelsea - Arsenal", "BetOffers": { "BetType": "Game", "Pick": "1", "Odds": 1.15 } }, { "Id": 456879, "Name": "Liverpool - Manchester United", "BetOffers": { "BetType": "Game", "Pick": "1", "Odds": 1.2 } } ] } public class Event { public int Id { get; set; } public string Name { get; set; } public BetOffer BetOffers { get; set; } } public class BetOffer { public string BetType { get; set; } public string Pick { get; set; } public double Odds { get; set; } } public class MyRootObject { public List<Event> event { get; set; } }
Regards
Monday, November 23, 2020 4:18 PM -
User475983607 posted
Hi mgebhard, I am running into error "[Task Name] Error: No parameterless constructor defined for type of BetOffer[]'.
The code I provided above was tested and verified before I shared the code. Share all your relevant code if you need a community code review.
Monday, November 23, 2020 9:16 PM -
User1686398519 posted
Hi dotnetenthusiast,
"BetOffers": {
- The json data you provide for the second time cannot be directly deserialized into Datable type.
- You can see that the BetOffers in the first json data is an array, which means that only if the json data conforms to this format, you can directly deserialize it to Datable.
- I suggest you use the second method, it is more general.JsonSerializer can better control the serialization of objects.
Best Regards,
YihuiSun
Tuesday, November 24, 2020 7:17 AM -
User-1950594959 posted
Hi @mgebhard and @YihuiSun, Thanks very much for your response. I am using the below code. Please let me know if this could be improved.
{ "events": [ { "Id": 456895, "Name": "Chelsea - Arsenal", "BetOffers": { "BetType": "Game", "Pick": "1", "Odds": 1.15 } }, { "Id": 456879, "Name": "Liverpool - Manchester United", "BetOffers": { "BetType": "Game", "Pick": "1", "Odds": 1.2 } } ] } public class Event { public int Id { get; set; } public string Name { get; set; } public BetOffer BetOffers { get; set; } } public class BetOffer { public string BetType { get; set; } public string Pick { get; set; } public double Odds { get; set; } } public class MyRootObject { public List<Event> events { get; set; } } try { string jsonstring; var root = JsonSerializer.Deserialize<MyRootObject>(jsonstring); string connectionString = ""; DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("ID", typeof(string))); dt.Columns.Add(new DataColumn("Name", typeof(string))); dt.Columns.Add(new DataColumn("BetType", typeof(string))); dt.Columns.Add(new DataColumn("Pick", typeof(string))); dt.Columns.Add(new DataColumn("Odds", typeof(string))); DataRow dr = dt.NewRow(); for (int i = 0; i < root.events.Count; i++) { dr = dt.NewRow(); dr["ID"] = root.events[i].ID; dr["Name"] = root.events[i].Name; dr["BetType"] = root.events[i].BetType; dr["Pick"] = root.events[i].Pick; dr["Odds"] = root.events[i].Odds; dt.Rows.Add(dr); } }
Thank you
Wednesday, November 25, 2020 12:20 AM -
User1686398519 posted
Hi dotnetenthusiast,
- Your code needs to be modified.
-
dr["BetType"] = root.events[i].BetOffers.BetType; dr["Pick"] = root.events[i].BetOffers.Pick; dr["Odds"] = root.events[i].BetOffers.Odds;
-
- You can improve your code like this:
- You can use reflection to convert "root" into a DataTable.
-
string jsonstring= "{\"events\":[{\"Id\":456895,\"Name\":\"Chelsea - Arsenal\",\"BetOffers\":{\"BetType\":\"Game\",\"Pick\":\"1\",\"Odds\":1.15}},{\"Id\":456879,\"Name\":\"Liverpool - Manchester United\",\"BetOffers\":{\"BetType\":\"Game\",\"Pick\":\"1\",\"Odds\":1.2}}]}"; var root = JsonSerializer.Deserialize<MyRootObject>(jsonstring); DataTable dt2 = ToDataTable<Event>(root.events);
-
public DataTable ToDataTable<T>(List<T> items) { DataTable table = new DataTable(typeof(T).Name); PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T)); for (int i = 0; i < props.Count; i++) { PropertyDescriptor prop = props[i]; table.Columns.Add(prop.Name, prop.PropertyType); } object[] values = new object[props.Count]; foreach (T item in items) { for (int i = 0; i < values.Length; i++) { values[i] = props[i].GetValue(item); } table.Rows.Add(values); } return table; }
Best Regards,
YihuiSun
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, November 25, 2020 8:55 AM - Your code needs to be modified.