Esempio n. 1
0
        protected int insert(LeagueKey lKey, LeagueValue lVal)
        {
            string     q         = @"SELECT ID FROM dbo.Leagues WHERE Country=@Country AND League=@League AND Season=@Season";
            SqlCommand selectCmd = new SqlCommand(q, Connection);

            selectCmd.Parameters.AddWithValue("@Country", lKey.Country);
            selectCmd.Parameters.AddWithValue("@League", lKey.League);
            selectCmd.Parameters.AddWithValue("@Season", lKey.Season);
            SqlCommand modifyCmd;

            using (SqlDataReader reader = selectCmd.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    q         = @"UPDATE dbo.Leagues SET Country=@Country, League=@League, Season=@Season, StartDate=@StartDate, EndDate=@EndDate, Teams=@Teams, Matches=@Matches,
                        HomeWinsPercent=@HomeWinsPercent, DrawsPercent=@DrawsPercent, AwayWinsPercent=@AwayWinsPercent, PyramidLevel=@PyramidLevel WHERE ID=@ID";
                    modifyCmd = new SqlCommand(q, Connection);
                    reader.Read();
                    modifyCmd.Parameters.AddWithValue("@ID", reader[0]);
                }
                else
                {
                    q         = @"INSERT INTO dbo.Leagues (
                        Country,League,Season,StartDate,EndDate,Teams,Matches,HomeWinsPercent,DrawsPercent,AwayWinsPercent,PyramidLevel
                    ) VALUES(@Country,@League,@Season,@StartDate,@EndDate,@Teams,@Matches,@HomeWinsPercent,@DrawsPercent,@AwayWinsPercent,@PyramidLevel)";
                    modifyCmd = new SqlCommand(q, Connection);
                }
            }
            modifyCmd.Parameters.AddWithValue("@Country", lKey.Country);
            modifyCmd.Parameters.AddWithValue("@League", lKey.League);
            modifyCmd.Parameters.AddWithValue("@Season", lKey.Season);
            modifyCmd.Parameters.AddWithValue("@StartDate", lVal.endDate);
            modifyCmd.Parameters.AddWithValue("@EndDate", lVal.startDate);
            modifyCmd.Parameters.AddWithValue("@Teams", lVal.Teams.Count);
            modifyCmd.Parameters.AddWithValue("@Matches", lVal.Matches);
            modifyCmd.Parameters.AddWithValue("@HomeWinsPercent", Math.Round((float)lVal.HomeWins / lVal.Matches * 100));
            modifyCmd.Parameters.AddWithValue("@DrawsPercent", Math.Round((float)lVal.Draws / lVal.Matches * 100));
            modifyCmd.Parameters.AddWithValue("@AwayWinsPercent", Math.Round((float)lVal.AwayWins / lVal.Matches * 100));
            modifyCmd.Parameters.AddWithValue("@PyramidLevel", lKey.PyramidLevel);
            return(modifyCmd.ExecuteNonQuery());
        }
Esempio n. 2
0
        public void process()
        {
            int result;
            string hTeam, aTeam;
            string q = @"SELECT Country,League,Season,Round,Date,HomeTeam,AwayTeam,ScoreH,ScoreA FROM dbo.archive ORDER BY Country,League,Season,Round";
            DateTime date;
            Dictionary<LeagueKey, LeagueValue> dict = new Dictionary<LeagueKey, LeagueValue>();
            LeagueKey lKey = new LeagueKey();
            LeagueValue lVal = new LeagueValue();

            using (SqlDataReader reader = new SqlCommand(q, Connection).ExecuteReader())
            {
                while (reader.Read())
                {
                    if (reader["ScoreA"] is DBNull || reader["ScoreH"] is DBNull) continue;
                    lKey.Country = reader["Country"].ToString();
                    lKey.League = reader["League"].ToString();
                    lKey.Season = reader["Season"].ToString();
                    date = (DateTime)reader["Date"];
                    hTeam = reader["HomeTeam"].ToString();
                    aTeam = reader["AwayTeam"].ToString();
                    result = (byte)reader["ScoreH"] - (byte)reader["ScoreA"];
                    if (!dict.ContainsKey(lKey))
                        lVal = new LeagueValue(date, hTeam, aTeam, result);
                    else
                    {
                        lVal = dict[lKey];
                        lVal.Matches++;
                        if (result > 0) lVal.HomeWins++;
                        else if (result < 0) lVal.AwayWins++;
                        else lVal.Draws++;
                        if (lVal.startDate < date) lVal.startDate = date;
                        if (lVal.endDate > date) lVal.endDate = date;
                        lVal.Teams.Add(hTeam);
                        lVal.Teams.Add(aTeam);
                    }
                    dict[lKey] = lVal;
                }
            }
            foreach (KeyValuePair<LeagueKey, LeagueValue> kvp in dict)
            {
                if (insert(kvp.Key, kvp.Value) == 0)
                {
                    Console.WriteLine("something is wrong [Leagues.process]");
                    break;
                }
            }
        }
Esempio n. 3
0
 protected int insert(LeagueKey lKey, LeagueValue lVal)
 {
     string q = @"SELECT ID FROM dbo.Leagues WHERE Country=@Country AND League=@League AND Season=@Season";
     SqlCommand selectCmd = new SqlCommand(q, Connection);
     selectCmd.Parameters.AddWithValue("@Country", lKey.Country);
     selectCmd.Parameters.AddWithValue("@League", lKey.League);
     selectCmd.Parameters.AddWithValue("@Season", lKey.Season);
     SqlCommand modifyCmd;
     using (SqlDataReader reader = selectCmd.ExecuteReader())
     {
         if (reader.HasRows)
         {
             q = @"UPDATE dbo.Leagues SET Country=@Country, League=@League, Season=@Season, StartDate=@StartDate, EndDate=@EndDate, Teams=@Teams, Matches=@Matches,
                 HomeWinsPercent=@HomeWinsPercent, DrawsPercent=@DrawsPercent, AwayWinsPercent=@AwayWinsPercent, PyramidLevel=@PyramidLevel WHERE ID=@ID";
             modifyCmd = new SqlCommand(q, Connection);
             reader.Read();
             modifyCmd.Parameters.AddWithValue("@ID", reader[0]);
         }
         else
         {
             q = @"INSERT INTO dbo.Leagues (
                 Country,League,Season,StartDate,EndDate,Teams,Matches,HomeWinsPercent,DrawsPercent,AwayWinsPercent,PyramidLevel
             ) VALUES(@Country,@League,@Season,@StartDate,@EndDate,@Teams,@Matches,@HomeWinsPercent,@DrawsPercent,@AwayWinsPercent,@PyramidLevel)";
             modifyCmd = new SqlCommand(q, Connection);
         }
     }
     modifyCmd.Parameters.AddWithValue("@Country", lKey.Country);
     modifyCmd.Parameters.AddWithValue("@League", lKey.League);
     modifyCmd.Parameters.AddWithValue("@Season", lKey.Season);
     modifyCmd.Parameters.AddWithValue("@StartDate", lVal.endDate);
     modifyCmd.Parameters.AddWithValue("@EndDate", lVal.startDate);
     modifyCmd.Parameters.AddWithValue("@Teams", lVal.Teams.Count);
     modifyCmd.Parameters.AddWithValue("@Matches", lVal.Matches);
     modifyCmd.Parameters.AddWithValue("@HomeWinsPercent", Math.Round((float)lVal.HomeWins / lVal.Matches * 100));
     modifyCmd.Parameters.AddWithValue("@DrawsPercent", Math.Round((float)lVal.Draws / lVal.Matches * 100));
     modifyCmd.Parameters.AddWithValue("@AwayWinsPercent", Math.Round((float)lVal.AwayWins / lVal.Matches * 100));
     modifyCmd.Parameters.AddWithValue("@PyramidLevel", lKey.PyramidLevel);
     return modifyCmd.ExecuteNonQuery();
 }
Esempio n. 4
0
        public void process()
        {
            int      result;
            string   hTeam, aTeam;
            string   q = @"SELECT Country,League,Season,Round,Date,HomeTeam,AwayTeam,ScoreH,ScoreA FROM dbo.archive ORDER BY Country,League,Season,Round";
            DateTime date;
            Dictionary <LeagueKey, LeagueValue> dict = new Dictionary <LeagueKey, LeagueValue>();
            LeagueKey   lKey = new LeagueKey();
            LeagueValue lVal = new LeagueValue();

            using (SqlDataReader reader = new SqlCommand(q, Connection).ExecuteReader())
            {
                while (reader.Read())
                {
                    if (reader["ScoreA"] is DBNull || reader["ScoreH"] is DBNull)
                    {
                        continue;
                    }
                    lKey.Country = reader["Country"].ToString();
                    lKey.League  = reader["League"].ToString();
                    lKey.Season  = reader["Season"].ToString();
                    date         = (DateTime)reader["Date"];
                    hTeam        = reader["HomeTeam"].ToString();
                    aTeam        = reader["AwayTeam"].ToString();
                    result       = (byte)reader["ScoreH"] - (byte)reader["ScoreA"];
                    if (!dict.ContainsKey(lKey))
                    {
                        lVal = new LeagueValue(date, hTeam, aTeam, result);
                    }
                    else
                    {
                        lVal = dict[lKey];
                        lVal.Matches++;
                        if (result > 0)
                        {
                            lVal.HomeWins++;
                        }
                        else if (result < 0)
                        {
                            lVal.AwayWins++;
                        }
                        else
                        {
                            lVal.Draws++;
                        }
                        if (lVal.startDate < date)
                        {
                            lVal.startDate = date;
                        }
                        if (lVal.endDate > date)
                        {
                            lVal.endDate = date;
                        }
                        lVal.Teams.Add(hTeam);
                        lVal.Teams.Add(aTeam);
                    }
                    dict[lKey] = lVal;
                }
            }
            foreach (KeyValuePair <LeagueKey, LeagueValue> kvp in dict)
            {
                if (insert(kvp.Key, kvp.Value) == 0)
                {
                    Console.WriteLine("something is wrong [Leagues.process]");
                    break;
                }
            }
        }