samedi 7 mai 2016

What is the best way of "converting" object to SQLite record?

I am using Livescore API, that returns List of objects.

Each object is one match with about 30 fields (e.g.: AwayGoalDetails, HomeTeamRedCardDetails, HomeGoals, etc.), fields are either string, string[] or int type.

Right now I am doing something that feels really dumb. My table-creating query looks like this:

string sql = "create table BundesligaFixtures (AwayGoalDetails text, AwayGoals int, AwayLineupDefense text, AwayLineupForward text, AwayLineupGoalkeeper text, AwayLineupMidfield text, AwayLineupSubstitutes text, AwaySubDetails text, AwayTeam text, AwayTeamFormation text, AwayTeamRedCardDetails text, AwayTeamYellowCardDetails text, AwayTeam_Id int, Date numeric, FixtureMatch_Id int, HomeGoalDetails text, HomeGoals int, HomeLineupDefense text, HomeLineupForward text, HomeLineupGoalkeeper text, HomeLineupMidfield text, HomeLineupSubstitutes text, HomeSubDetails text, HomeTeam text, HomeTeamFormation text, HomeTeamRedCardDetails text, HomeTeamYellowCardDetails text, HomeTeam_Id int, Location text, Round int, Spectators int, Time text)";

Then I iterate over the List with foreach loop (I convert string[] to csv so I can store them as TEXT) with this monstrosity:

foreach (var match in matches)
        {
            sql = String.Format("insert into BundesligaFixtures values ('{0}', {1}, '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', {12}, {13}, {14}, '{15}', {16}, '{17}', '{18}', '{19}', '{20}', '{21}', '{22}', '{23}', '{24}', '{25}', '{26}', {27}, '{28}', {29}, {30}, '{31}')", StringModifier.ToString(match.AwayGoalDetails), match.AwayGoals, StringModifier.ToString(match.AwayLineupDefense), StringModifier.ToString(match.AwayLineupForward), match.AwayLineupGoalkeeper, StringModifier.ToString(match.AwayLineupMidfield), StringModifier.ToString(match.AwayLineupSubstitutes), StringModifier.ToString(match.AwaySubDetails), match.AwayTeam, match.AwayTeamFormation, StringModifier.ToString(match.AwayTeamRedCardDetails), StringModifier.ToString(match.AwayTeamYellowCardDetails), match.AwayTeam_Id, match.Date, match.FixtureMatch_Id, StringModifier.ToString(match.HomeGoalDetails), match.HomeGoals, StringModifier.ToString(match.HomeLineupDefense), StringModifier.ToString(match.HomeLineupForward), match.HomeLineupGoalkeeper, StringModifier.ToString(match.HomeLineupMidfield), StringModifier.ToString(match.HomeLineupSubstitutes), StringModifier.ToString(match.HomeSubDetails), match.HomeTeam, match.HomeTeamFormation, StringModifier.ToString(match.HomeTeamRedCardDetails), StringModifier.ToString(match.HomeTeamYellowCardDetails), match.HomeTeam_Id, match.Location, match.Round, match.Spectators, match.Time);
            command = new SQLiteCommand(sql, m_dbConnection);
            command.ExecuteNonQuery();
        }

Is there any better way to do this?

Aucun commentaire:

Enregistrer un commentaire