-1

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.

Classes

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);

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
    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.Id from the json string?

Error

List does not contain definition for Id

paone
  • 828
  • 8
  • 18
  • 6
    Why are we using `JavaScriptSerializer` again? Although likely not the problem here. I would suggest using Json.Net or Text.Json – TheGeneral Nov 20 '20 at 06:13
  • @TheGeneral, I have constraints using Newtonsoft Json. – paone Nov 20 '20 at 06:18
  • 1
    I recommend clarifying the question. The title specifically states deserialization, but you have database stuff in the question. So what's *not* working, needs to be the question. – Erik Philips Nov 20 '20 at 06:23
  • Even reading it with JavaScript and saving it back in format you like would likely be easier... Getting free advice on such an old piece of code could be hard... – Alexei Levenkov Nov 20 '20 at 06:23
  • @ErikPhilips, when I try to parse Id (root.event.Id) from the json string I get the error List does not contain definition for Id. – paone Nov 20 '20 at 06:37
  • Your root object is 'List>' which ultimately is a list which does not contain a definition for Id. Try making your root object something that has an Id per row. Have a look at https://stackoverflow.com/questions/51140749/cant-list-table-from-sql-to-mvc-view as well. It might help. – openshac Nov 20 '20 at 07:26
  • `root.event` **cannot** have an `Id` property because it is a list. Did you mean `root.event[0].Id` ? – Lasse V. Karlsen Nov 20 '20 at 08:34
  • The data structure represented by your json is not flat. Therefore, it cannot be represented as a single DataTable. – Alexander Petrov Nov 20 '20 at 15:31

1 Answers1

2

@event property must be of type List<List<Event>>.

public class MyRootObject
{
    public List<List<Event>> @event { get; set; }
}
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; }
}

Let's iterate through this structure. Since you used a for loop, I also made code on for loops (of course, this code can be greatly simplified).

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);

            for (int l = 0; l < root.@event[i][j].BetOffers[k].Picks.Count; l++)
            {
                Console.WriteLine(root.@event[i][j].BetOffers[k].Picks[l].Pick +
                    "  " + root.@event[i][j].BetOffers[k].Picks[l].Odds);
            }
        }
        Console.WriteLine();
    }
}

As you can see, there are four nested loops. A DataTable is a square matrix that can be traversed by two loops.

The data structure represented by your json is not flat. Therefore, it cannot be represented as a single DataTable.

Alexander Petrov
  • 13,457
  • 2
  • 20
  • 49