locked
iteration over a json string RRS feed

  • 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, 

    1. 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;
    2. 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

All replies

  • User475983607 posted

    List does not contain definition for Id

    Correct.  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.

    1. 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
    2. 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; }
              }

    Here is the result. 

    Best Regards,

    YihuiSun

    Monday, November 23, 2020 6:44 AM
  • 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": {

    1. The json data you provide for the second time cannot be directly deserialized into Datable type.
    2. 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.
    3. 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, 

    1. 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;
    2. 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