Even Harder Json API call

[robdineen]

Hello there,

I have been given a Json api that is even harder than last time. after entering the Json into a class converter I get the following.

I am using .Net 4.0

class below.

  public class HomeTeam
        {
            public string position { get; set; }
            public string player_name { get; set; }
            public string player_id { get; set; }
            public string captain { get; set; }
            public string wicket_keeper { get; set; }
        }

        public class AwayTeam
        {
            public string position { get; set; }
            public string player_name { get; set; }
            public string player_id { get; set; }
            public string captain { get; set; }
            public string wicket_keeper { get; set; }
        }

        public class Player
        {
            public List<HomeTeam> home_team { get; set; }
            public List<AwayTeam> away_team { get; set; }
        }

        public class Bat
        {
            public string position { get; set; }
            public string batsman_name { get; set; }
            public string batsman_id { get; set; }
            public string how_out { get; set; }
            public string fielder_name { get; set; }
            public string fielder_id { get; set; }
            public string bowler_name { get; set; }
            public string bowler_id { get; set; }
            public string runs { get; set; }
            public string fours { get; set; }
            public string sixes { get; set; }
            public string balls { get; set; }
        }

        public class Fow
        {
            public string runs { get; set; }
            public string wickets { get; set; }
            public string batsman_out_name { get; set; }
            public string batsman_out_id { get; set; }
        }

        public class Bowl
        {
            public string bowler_name { get; set; }
            public string bowler_id { get; set; }
            public string overs { get; set; }
            public string maidens { get; set; }
            public string runs { get; set; }
            public string wides { get; set; }
            public string wickets { get; set; }
            public string no_balls { get; set; }
        }

        public class Innings
        {
            public string team_batting_name { get; set; }
            public string team_batting_id { get; set; }
            public string innings_number { get; set; }
            public string extra_byes { get; set; }
            public string extra_leg_byes { get; set; }
            public string extra_wides { get; set; }
            public string extra_no_balls { get; set; }
            public string extra_penalty_runs { get; set; }
            public string penalties_runs_awarded_in_other_innings { get; set; }
            public string total_extras { get; set; }
            public string runs { get; set; }
            public string wickets { get; set; }
            public string overs { get; set; }
            public string declared { get; set; }
            public List<Bat> bat { get; set; }
            public List<Fow> fow { get; set; }
            public List<Bowl> bowl { get; set; }
        }

        public class MatchDetail
        {
            public string id { get; set; }
            public string status { get; set; }
            public string published { get; set; }
            public string last_updated { get; set; }
            public string competition_name { get; set; }
            public string competition_id { get; set; }
            public string competition_type { get; set; }
            public string match_type { get; set; }
            public string game_type { get; set; }
            public string match_id { get; set; }
            public string match_date { get; set; }
            public string match_time { get; set; }
            public string ground_name { get; set; }
            string ground_id { get; set; }
            public string home_team_name { get; set; }
            public string home_team_id { get; set; }
            public string home_club_name { get; set; }
            public string home_club_id { get; set; }
            public string away_team_name { get; set; }
            public string away_team_id { get; set; }
            public string away_club_name { get; set; }
            public string away_club_id { get; set; }
            public string umpire_1_name { get; set; }
            public string umpire_1_id { get; set; }
            public string umpire_2_name { get; set; }
            public string umpire_2_id { get; set; }
            public string umpire_3_id { get; set; }
            public string referee_id { get; set; }
            public string scorer_1_id { get; set; }
            public string scorer_2_id { get; set; }
            public string toss_won_by_team_id { get; set; }
            public string toss { get; set; }
            public string batted_first { get; set; }
            public string no_of_overs { get; set; }
            public string no_of_innings { get; set; }
            public string no_of_days { get; set; }
            public string no_of_players { get; set; }
            public string no_of_reserves { get; set; }
            public string result { get; set; }
            public string result_type { get; set; }
            public string match_notes { get; set; }
            public List<List<object>> match_result_types { get; set; }
            public List<Player> players { get; set; }
            public List<Innings> innings { get; set; }
        }



        public class RootObject
        {
            public List<MatchDetail> match_details { get; set; }

            
        }

would I be able to insert this into one table or would this be split into several tables.
any help would be great


Regards

Rob

[robdineen]

I have managed to make some sense on how this one works. But have come across a NullReferenceExecption.

see my code below and I have also attached a screenshot of the error. You will see there are two foreach loops based on home and away players.

the first loop runs ok, but the second throws the NullRef error.

FYI, the attachment is called (Foreach Loop Error)

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Net;
using System.IO;
using System.Runtime.Serialization.Formatters.Binary;
using System.Collections.Generic;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Web.Script.Serialization;
using System.Data.SqlClient;
using System.Configuration;
#endregion

namespace ST_210806adc57542e086c637425fdaf2d3
{

    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        string HT_position;
        string HT_player_name;
        string HT_player_id;
        string HT_captain;
        string HT_wicket_keeper;


        public string AT_position;
        string AT_player_name;
        string AT_player_id;
        string AT_captain;
        string AT_wicket_keeper;

        //string Bat_position;
        //string Bat_batsman_name;
        //string Bat_batsman_id;
        //string Bat_how_out;
        //string Bat_fielder_name;
        //string Bat_fielder_id;
        //string Bat_bowler_name;
        //string Bat_bowler_id;
        //string Bat_runs;
        //string Bat_fours;
        //string Bat_sixes;
        //string Bat_balls;

        //string Fow_runs;
        //string Fow_wickets;
        //string Fow_batsman_out_name;
        //string Fow_batsman_out_id;

        //string Bowl_bowler_name;
        //string Bowl_bowler_id;
        //string Bowl_overs;
        //string Bowl_maidens;
        //string Bowl_runs;
        //string Bowl_wides;
        //string Bowl_wickets;
        //string Bowl_no_balls;

        string Inn_team_batting_name;
        string Inn_team_batting_id;
        string Inn_innings_number;
        string Inn_extra_byes;
        string Inn_extra_leg_byes;
        string Inn_extra_wides;
        string Inn_extra_no_balls;
        string Inn_extra_penalty_runs;
        string Inn_penalties_runs_awarded_in_other_innings;
        string Inn_total_extras;
        string Inn_runs;
        string Inn_wickets;
        string Inn_overs;
        string Inn_declared;


        string MD_id;
        string MD_status;
        string MD_published;
        string MD_last_updated;
        string MD_competition_name;
        string MD_competition_id;
        string MD_competition_type;
        string MD_match_type;
        string MD_game_type;
        string MD_match_id;
        string MD_match_date;
        string MD_match_time;
        string MD_ground_name;
        //string MD_ground_id;
        string MD_home_team_name;
        string MD_home_team_id;
        string MD_home_club_name;
        string MD_home_club_id;
        string MD_away_team_name;
        string MD_away_team_id;
        string MD_away_club_name;
        string MD_away_club_id;
        string MD_umpire_1_name;
        string MD_umpire_1_id;
        string MD_umpire_2_name;
        string MD_umpire_2_id;
        string MD_umpire_3_id;
        string MD_referee_id;
        string MD_scorer_1_id;
        string MD_scorer_2_id;
        string MD_toss_won_by_team_id;
        string MD_toss;
        string MD_batted_first;
        string MD_no_of_overs;
        string MD_no_of_innings;
        string MD_no_of_days;
        string MD_no_of_players;
        string MD_no_of_reserves;
        string MD_result;
        string MD_result_type;
        string MD_match_notes;

        public void Main()
        {
            downloadjson();
        }

        public void downloadjson()
        {

            string Conn = @"sql server connection";


            string url = @"json api URL";






            using (SqlConnection conn = new SqlConnection(Conn))
            {

                conn.Open();

                RootObject outPutMetrics = GetWebServiceResult(url);




                foreach (var comp in outPutMetrics.match_details)
                {
                    MD_id = comp.id;
                    MD_status = comp.status;
                    MD_published = comp.published;
                    MD_last_updated = comp.last_updated;
                    MD_competition_name = comp.competition_name;
                    MD_competition_id = comp.competition_id;
                    MD_competition_type = comp.competition_type;
                    MD_match_type = comp.match_type;
                    MD_game_type = comp.game_type;
                    MD_match_id = comp.match_id;
                    MD_match_date = comp.match_date;
                    MD_match_time = comp.match_time;
                    MD_ground_name = comp.ground_name;
                    //   MD_ground_id = comp.ground_id;
                    MD_home_team_name = comp.home_team_name;
                    MD_home_team_id = comp.home_team_id;
                    MD_home_club_name = comp.home_club_name;
                    MD_home_club_id = comp.home_club_id;
                    MD_away_team_name = comp.home_team_name;
                    MD_away_team_id = comp.away_team_id;
                    MD_away_club_name = comp.away_club_name;
                    MD_away_club_id = comp.away_club_id;
                    MD_umpire_1_name = comp.umpire_1_name;
                    MD_umpire_1_id = comp.umpire_1_id;
                    MD_umpire_2_name = comp.umpire_2_name;
                    MD_umpire_2_id = comp.umpire_2_id;
                    MD_umpire_3_id = comp.umpire_3_id;
                    MD_referee_id = comp.referee_id;
                    MD_scorer_1_id = comp.scorer_1_id;
                    MD_scorer_2_id = comp.scorer_2_id;
                    MD_toss_won_by_team_id = comp.toss_won_by_team_id;
                    MD_toss = comp.toss;
                    MD_batted_first = comp.batted_first;
                    MD_no_of_overs = comp.no_of_overs;
                    MD_no_of_innings = comp.no_of_innings;
                    MD_no_of_days = comp.no_of_days;
                    MD_no_of_players = comp.no_of_players;
                    MD_no_of_reserves = comp.no_of_reserves;
                    MD_result = comp.result;
                    MD_result_type = comp.result_type;
                    MD_match_notes = comp.match_notes;

                    foreach (var inn in comp.innings)
                    {
                        Inn_team_batting_name = inn.team_batting_name;
                        Inn_team_batting_id = inn.team_batting_id;
                        Inn_innings_number = inn.innings_number;
                        Inn_extra_byes = inn.extra_byes;
                        Inn_extra_leg_byes = inn.extra_leg_byes;
                        Inn_extra_wides = inn.extra_wides;
                        Inn_extra_no_balls = inn.extra_no_balls;
                        Inn_extra_penalty_runs = inn.extra_penalty_runs;
                        Inn_penalties_runs_awarded_in_other_innings = inn.penalties_runs_awarded_in_other_innings;
                        Inn_total_extras = inn.total_extras;
                        Inn_runs = inn.runs;
                        Inn_wickets = inn.wickets;
                        Inn_overs = inn.overs;
                        Inn_declared = inn.declared;


                        foreach (var play in comp.players)
                        {


                            foreach (var home in play.home_team)
                            {
                                HT_position = home.position;
                                HT_player_name = home.player_name;
                                HT_player_id = home.player_id;
                                HT_captain = home.captain;
                                HT_wicket_keeper = home.wicket_keeper;


                            }

                            foreach (var away in play.away_team)
                            {

                                AT_position = away.position;
                                AT_player_name = away.player_name;
                                AT_player_id = away.player_id;
                                AT_captain = away.captain;
                                AT_wicket_keeper = away.wicket_keeper;

                            }


                        }





                        //foreach (var bowl in inn.bowl)
                        //{

                        //    Bowl_bowler_name = bowl.bowler_name;
                        //    Bowl_bowler_id = bowl.bowler_id;
                        //    Bowl_overs = bowl.overs;
                        //    Bowl_maidens = bowl.maidens;
                        //    Bowl_runs = bowl.runs;
                        //    Bowl_wides = bowl.wides;
                        //    Bowl_wickets = bowl.wickets;
                        //    Bowl_no_balls = bowl.no_balls;

                        //}


                        //foreach (var fow in inn.fow)
                        //{
                        //    Fow_runs = fow.runs;
                        //    Fow_wickets = fow.wickets;
                        //    Fow_batsman_out_name = fow.batsman_out_name;
                        //    Fow_batsman_out_id = fow.batsman_out_id;

                        //}

                        //foreach (var bat in inn.bat)
                        //{
                        //    Bat_position = bat.position;
                        //    Bat_batsman_name = bat.batsman_name;
                        //    Bat_batsman_id = bat.batsman_id;
                        //    Bat_how_out = bat.how_out;
                        //    Bat_fielder_name = bat.fielder_name;
                        //    Bat_fielder_id = bat.fielder_id;
                        //    Bat_bowler_name = bat.bowler_name;
                        //    Bat_bowler_id = bat.bowler_id;
                        //    Bat_runs = bat.runs;
                        //    Bat_fours = bat.fours;
                        //    Bat_sixes = bat.sixes;
                        //    Bat_balls = bat.balls;


                        //}

                        SqlCommand sql = new SqlCommand("INSERT INTO [Staging].[Innings_Scorecards]([Match_ID],[team_batting_name],[team_batting_id],[innings_number],[extra_byes],[extra_leg_byes],[extra_wides],[extra_no_balls],[extra_penalty_runs],[penalties_runs_awarded_in_other_innings],[total_extras],[runs],[wickets],[overs],[declared])  values ( @MD_match_id, @Inn_team_batting_name, @Inn_team_batting_id, @Inn_innings_number,@Inn_extra_byes,@Inn_extra_wides,@Inn_extra_no_balls,@Inn_extra_leg_byes,@Inn_extra_penalty_runs,@Inn_penalties_runs_awarded_in_other_innings,@Inn_total_extras,@Inn_runs,@Inn_wickets,@Inn_overs,@Inn_declared)", conn);

                        sql.Parameters.AddWithValue("@MD_match_id", MD_match_id);
                        sql.Parameters.AddWithValue("@Inn_team_batting_name", Inn_team_batting_name);
                        sql.Parameters.AddWithValue("@Inn_team_batting_id", Inn_team_batting_id);
                        sql.Parameters.AddWithValue("@Inn_innings_number", Inn_innings_number);
                        sql.Parameters.AddWithValue("@Inn_extra_byes", Inn_extra_byes);
                        sql.Parameters.AddWithValue("@Inn_extra_wides", Inn_extra_wides);
                        sql.Parameters.AddWithValue("@Inn_extra_no_balls", Inn_extra_no_balls);
                        sql.Parameters.AddWithValue("@Inn_extra_leg_byes", Inn_extra_leg_byes);
                        sql.Parameters.AddWithValue("@Inn_extra_penalty_runs", Inn_extra_penalty_runs);
                        sql.Parameters.AddWithValue("@Inn_penalties_runs_awarded_in_other_innings",
                            Inn_penalties_runs_awarded_in_other_innings);
                        sql.Parameters.AddWithValue("@Inn_total_extras", Inn_total_extras);
                        sql.Parameters.AddWithValue("@Inn_runs", Inn_runs);
                        sql.Parameters.AddWithValue("@Inn_wickets", Inn_wickets);
                        sql.Parameters.AddWithValue("@Inn_overs", Inn_overs);
                        sql.Parameters.AddWithValue("@Inn_declared", Inn_declared);







                        sql.ExecuteNonQuery();

                        sql.Parameters.Clear();

                    }






                }

            }

        }




        private RootObject GetWebServiceResult(string url)
        {

            //' var w = new WebClient();

            HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(url);

            HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();

            RootObject jsonResponse = null;


            Stream responseStream = httpWResp.GetResponseStream();

            string jsondata = null;

            //jsondata = w.DownloadString(url);

            using (StreamReader reader = new StreamReader(responseStream))
            {

                jsondata = reader.ReadToEnd().Replace("\\", "");

                reader.Close();

            }


            var jss = new JavaScriptSerializer();
            jsonResponse = jss.Deserialize<RootObject>(jsondata.Trim('"'));





            var dict = jss.Deserialize<RootObject>(jsondata);


            return jsonResponse;

        }
    }
}

        public class HomeTeam
        {
            public string position { get; set; }
            public string player_name { get; set; }
            public string player_id { get; set; }
            public string captain { get; set; }
            public string wicket_keeper { get; set; }
        }

        public class AwayTeam
        {
            public string position { get; set; }
            public string player_name { get; set; }
            public string player_id { get; set; }
            public string captain { get; set; }
            public string wicket_keeper { get; set; }
        }

        public class Player
        {
            public List<HomeTeam> home_team { get; set; }
            public List<AwayTeam> away_team { get; set; }
        }

        public class Bat
        {
            public string position { get; set; }
            public string batsman_name { get; set; }
            public string batsman_id { get; set; }
            public string how_out { get; set; }
            public string fielder_name { get; set; }
            public string fielder_id { get; set; }
            public string bowler_name { get; set; }
            public string bowler_id { get; set; }
            public string runs { get; set; }
            public string fours { get; set; }
            public string sixes { get; set; }
            public string balls { get; set; }
        }

        public class Fow
        {
            public string runs { get; set; }
            public string wickets { get; set; }
            public string batsman_out_name { get; set; }
            public string batsman_out_id { get; set; }
        }

        public class Bowl
        {
            public string bowler_name { get; set; }
            public string bowler_id { get; set; }
            public string overs { get; set; }
            public string maidens { get; set; }
            public string runs { get; set; }
            public string wides { get; set; }
            public string wickets { get; set; }
            public string no_balls { get; set; }
        }

        public class Innings
        {
            public string team_batting_name { get; set; }
            public string team_batting_id { get; set; }
            public string innings_number { get; set; }
            public string extra_byes { get; set; }
            public string extra_leg_byes { get; set; }
            public string extra_wides { get; set; }
            public string extra_no_balls { get; set; }
            public string extra_penalty_runs { get; set; }
            public string penalties_runs_awarded_in_other_innings { get; set; }
            public string total_extras { get; set; }
            public string runs { get; set; }
            public string wickets { get; set; }
            public string overs { get; set; }
            public string declared { get; set; }
            public List<Bat> bat { get; set; }
            public List<Fow> fow { get; set; }
            public List<Bowl> bowl { get; set; }
        }

        public class MatchDetail
        {
            public string id { get; set; }
            public string status { get; set; }
            public string published { get; set; }
            public string last_updated { get; set; }
            public string competition_name { get; set; }
            public string competition_id { get; set; }
            public string competition_type { get; set; }
            public string match_type { get; set; }
            public string game_type { get; set; }
            public string match_id { get; set; }
            public string match_date { get; set; }
            public string match_time { get; set; }
            public string ground_name { get; set; }
            string ground_id { get; set; }
            public string home_team_name { get; set; }
            public string home_team_id { get; set; }
            public string home_club_name { get; set; }
            public string home_club_id { get; set; }
            public string away_team_name { get; set; }
            public string away_team_id { get; set; }
            public string away_club_name { get; set; }
            public string away_club_id { get; set; }
            public string umpire_1_name { get; set; }
            public string umpire_1_id { get; set; }
            public string umpire_2_name { get; set; }
            public string umpire_2_id { get; set; }
            public string umpire_3_id { get; set; }
            public string referee_id { get; set; }
            public string scorer_1_id { get; set; }
            public string scorer_2_id { get; set; }
            public string toss_won_by_team_id { get; set; }
            public string toss { get; set; }
            public string batted_first { get; set; }
            public string no_of_overs { get; set; }
            public string no_of_innings { get; set; }
            public string no_of_days { get; set; }
            public string no_of_players { get; set; }
            public string no_of_reserves { get; set; }
            public string result { get; set; }
            public string result_type { get; set; }
            public string match_notes { get; set; }
            public List<List<object>> match_result_types { get; set; }
            public List<Player> players { get; set; }
            public List<Innings> innings { get; set; }
        }



        public class RootObject
        {
            public List<MatchDetail> match_details { get; set; }

            
        }

Many Thanks

Rob

[Arjay]

Most likely the away_team list hasn't been initialized in the play object.

There are a couple of ways to deal with this:

The first way is to guard the code where you use the list:

if(play.away_team != null)
{
  foreach(var away in play.away_team)
  {

  }
}

While this approach works, I find the code to be messy (as you force the users of the list to always check that the list isn't null before using the list - yuk).

Another approach is to initialize the list in the class constructor.

public class Player
{
    public List<HomeTeam> home_team { get; set; }
    public List<AwayTeam> away_team { get; set; }

    public Player()
    {
        // Initialize lists
        home_team = new List<HomeTeam>();
        away_team = new List<AwayTeam>();
    }
}

The second approach allows you to write code like the following without having to worry about if the list has been initialized*

// No worries - the list is always initialized in the constructor   
foreach(var away in play.away_team)
{

}

* I wrote the asterisk because you still may have a null list if you assign the list to null (from another null list). I generally code things up to always initialize lists in the constructors, so I never have this problem.

However, if you were concerned about the null case (or needed to retrofit legacy code), you could write a 'SafeList' helper class with Get and Set methods that check for null and initialized the list when necessary.

You would use it like:

public class Player
{
    public List<AwayTeam> AwayTeams
    {
        get { return SafeList.Get(ref _awayTeams); }
        set { SafeList.Set(ref _awayTeams, value ); }
    }

    private List<AwayTeam> _awayTeams = new List<AwayTeam>();
}

[Arjay]

Here's one SafeList implementation:

public static class SafeList
{
    public static List<T> Get<T>(ref List<T> list)
    {
        return list ?? new List<T>();
    }

    public static void Set<T>(ref List<T> list, List<T> value)
    {
        // if list is null or the value is null, initialize list to empty
        if (list == null || value == null)
        {
            list = new List<T>();
        }

        // if the value isn't null, set the list
        if (value != null)
        {
            list = value;
        }
    }
}

[robdineen]

WOW

thank you very much, I shall get into that and have a go.

I shall come back and let you know and how I got it working..

Thank you again

Regards

Rob

[robdineen]

Thank you very much for you help on this.

The Json is now working lovely now, so thank you for that,.

the method I choose was to separate the lists in a player class as below.



public class Player
{
public List<HomeTeam> home_team { get; set; }
public List<AwayTeam> away_team { get; set; }

public Player()
{
// Initialize lists
home_team = new List<HomeTeam>();
away_team = new List<AwayTeam>();
}
}


Regards

Rob