示例#1
0
 public void KickerList(string file)
 {
     using (StreamReader sr = new StreamReader(file))
     {
         sr.ReadLine();
         for (int i = 0; i < 16; i++)
         {
             foreach (Player.PlayerInfo Player in Kickers)
             {
                 Player.Kicker p = new Player.Kicker();
                 if (sr.EndOfStream)
                 {
                     break;
                 }
                 string   line  = sr.ReadLine();
                 string[] pLine = line.Split('|');
                 p.XPMade   = pLine[0];
                 p.XPMissed = pLine[1];
                 p.FGGD     = pLine[2];
                 p.FGNG     = pLine[3];
                 Player.KickerStats.Add(p);
             }
         }
         Console.WriteLine("The Number of K is " + Kickers.Count);
         Console.WriteLine("The Number of K Stats is " + Kickers[0].KickerStats.Count);
     }
 }
        /// <summary>
        /// Inserts the player stats.
        /// </summary>
        public void InsertPlayerData()
        {
            Controller controller = new Controller();

            controller.FillPlayerInfoArray(@"..\\..\\..\\..\\GeneratedDataPlayerInfo.txt");
            controller.SortPlayerInfo();
            controller.QuarterBackList(@"..\\..\\..\\..\\GeneratedDataQBStats.txt");
            controller.RunningBackList(@"..\\..\\..\\..\\GeneratedDataRBStats.txt");
            controller.WideReceiversList(@"..\\..\\..\\..\\GeneratedDataWRStats.txt");
            controller.TightEndsList(@"..\\..\\..\\..\\GeneratedDataTEStats.txt");
            controller.KickerList(@"..\\..\\..\\..\\GeneratedDataKickerStats.txt");
            controller.DefenseList(@"..\\..\\..\\..\\DefensiveStats.txt");

            foreach (Player.PlayerInfo QB in controller.QuarterBacks)
            {
                for (int i = 0; i < QB.Stats.Count; i++)
                {
                    Player.QBRWTE p   = QB.Stats[i];
                    string        sql = $"INSERT Players.PlayerStats(PlayerID, PassYards, RushYards, ReceivingYards, Receptions, Touchdowns, Interceptions, Fumbles, TeamGameID) " +
                                        $"VALUES((SELECT PI.PlayerID FROM Players.PlayerInfo PI WHERE PI.[NAME] = N'{QB.Name}'), {p.PassYard}, {p.RushYard}, {p.ReceivingYards}, {p.Receptions}, {p.Touchdowns}, {p.Interceptions}, {p.Fumbles}, " +
                                        $"(SELECT DISTINCT(G.GameID) FROM Games.Game G RIGHT JOIN NFL.Teams T ON T.TeamID = G.HomeTeamID OR T.TeamID = G.VisitorTeamID " +
                                        $"RIGHT JOIN Players.TeamPlayer TP ON TP.TeamID = T.TeamID RIGHT JOIN Players.PlayerInfo Player ON Player.Name = TP.Name WHERE T.TeamID = " +
                                        $"(SELECT TPInfo.TeamID FROM Players.PlayerInfo Info INNER JOIN Players.TeamPlayer TPInfo ON TPInfo.Name = Info.Name WHERE Info.Name = '{QB.Name}') " +
                                        $"ORDER BY G.GameID ASC OFFSET {i} ROWS FETCH NEXT 1 ROWS ONLY" +
                                        $"))";
                    SqlCommand     command;
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    command = new SqlCommand(sql, connection);
                    adapter.InsertCommand = new SqlCommand(sql, connection);
                    adapter.InsertCommand.ExecuteNonQuery();
                    command.Dispose();
                }
            }
            foreach (Player.PlayerInfo RB in controller.RunningBacks)
            {
                for (int i = 0; i < RB.Stats.Count; i++)
                {
                    Player.QBRWTE p   = RB.Stats[i];
                    string        sql = $"INSERT Players.PlayerStats(PlayerID, PassYards, RushYards, ReceivingYards, Receptions, Touchdowns, Interceptions, Fumbles, TeamGameID) " +
                                        $"VALUES((SELECT PI.PlayerID FROM Players.PlayerInfo PI WHERE PI.[NAME] = N'{RB.Name}'), {p.PassYard}, {p.RushYard}, {p.ReceivingYards}, {p.Receptions}, {p.Touchdowns}, {p.Interceptions}, {p.Fumbles}, " +
                                        $"(SELECT DISTINCT(G.GameID) FROM Games.Game G RIGHT JOIN NFL.Teams T ON T.TeamID = G.HomeTeamID OR T.TeamID = G.VisitorTeamID " +
                                        $"RIGHT JOIN Players.TeamPlayer TP ON TP.TeamID = T.TeamID RIGHT JOIN Players.PlayerInfo Player ON Player.Name = TP.Name WHERE T.TeamID = " +
                                        $"(SELECT TPInfo.TeamID FROM Players.PlayerInfo Info INNER JOIN Players.TeamPlayer TPInfo ON TPInfo.Name = Info.Name WHERE Info.Name = '{RB.Name}') " +
                                        $"ORDER BY G.GameID ASC OFFSET {i} ROWS FETCH NEXT 1 ROWS ONLY" +
                                        $"))";
                    SqlCommand     command;
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    command = new SqlCommand(sql, connection);
                    adapter.InsertCommand = new SqlCommand(sql, connection);
                    adapter.InsertCommand.ExecuteNonQuery();
                    command.Dispose();
                }
            }
            foreach (Player.PlayerInfo WR in controller.WideReceivers)
            {
                for (int i = 0; i < WR.Stats.Count; i++)
                {
                    Player.QBRWTE p   = WR.Stats[i];
                    string        sql = $"INSERT Players.PlayerStats(PlayerID, PassYards, RushYards, ReceivingYards, Receptions, Touchdowns, Interceptions, Fumbles, TeamGameID) " +
                                        $"VALUES((SELECT PI.PlayerID FROM Players.PlayerInfo PI WHERE PI.[NAME] = N'{WR.Name}'), {p.PassYard}, {p.RushYard}, {p.ReceivingYards}, {p.Receptions}, {p.Touchdowns}, {p.Interceptions}, {p.Fumbles}, " +
                                        $"(SELECT DISTINCT(G.GameID) FROM Games.Game G RIGHT JOIN NFL.Teams T ON T.TeamID = G.HomeTeamID OR T.TeamID = G.VisitorTeamID " +
                                        $"RIGHT JOIN Players.TeamPlayer TP ON TP.TeamID = T.TeamID RIGHT JOIN Players.PlayerInfo Player ON Player.Name = TP.Name WHERE T.TeamID = " +
                                        $"(SELECT TPInfo.TeamID FROM Players.PlayerInfo Info INNER JOIN Players.TeamPlayer TPInfo ON TPInfo.Name = Info.Name WHERE Info.Name = '{WR.Name}') " +
                                        $"ORDER BY G.GameID ASC OFFSET {i} ROWS FETCH NEXT 1 ROWS ONLY" +
                                        $"))";
                    SqlCommand     command;
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    command = new SqlCommand(sql, connection);
                    adapter.InsertCommand = new SqlCommand(sql, connection);
                    adapter.InsertCommand.ExecuteNonQuery();
                    command.Dispose();
                }
            }
            foreach (Player.PlayerInfo TE in controller.TightEnds)
            {
                for (int i = 0; i < TE.Stats.Count; i++)
                {
                    Player.QBRWTE p   = TE.Stats[i];
                    string        sql = $"INSERT Players.PlayerStats(PlayerID, PassYards, RushYards, ReceivingYards, Receptions, Touchdowns, Interceptions, Fumbles, TeamGameID) " +
                                        $"VALUES((SELECT PI.PlayerID FROM Players.PlayerInfo PI WHERE PI.[NAME] = N'{TE.Name}'), {p.PassYard}, {p.RushYard}, {p.ReceivingYards}, {p.Receptions}, {p.Touchdowns}, {p.Interceptions}, {p.Fumbles}, " +
                                        $"(SELECT DISTINCT(G.GameID) FROM Games.Game G RIGHT JOIN NFL.Teams T ON T.TeamID = G.HomeTeamID OR T.TeamID = G.VisitorTeamID " +
                                        $"RIGHT JOIN Players.TeamPlayer TP ON TP.TeamID = T.TeamID RIGHT JOIN Players.PlayerInfo Player ON Player.Name = TP.Name WHERE T.TeamID = " +
                                        $"(SELECT TPInfo.TeamID FROM Players.PlayerInfo Info INNER JOIN Players.TeamPlayer TPInfo ON TPInfo.Name = Info.Name WHERE Info.Name = '{TE.Name}') " +
                                        $"ORDER BY G.GameID ASC OFFSET {i} ROWS FETCH NEXT 1 ROWS ONLY" +
                                        $"))";
                    SqlCommand     command;
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    command = new SqlCommand(sql, connection);
                    adapter.InsertCommand = new SqlCommand(sql, connection);
                    adapter.InsertCommand.ExecuteNonQuery();
                    command.Dispose();
                }
            }
            foreach (Player.PlayerInfo K in controller.Kickers)
            {
                for (int i = 0; i < K.KickerStats.Count; i++)
                {
                    Player.Kicker p   = K.KickerStats[i];
                    string        sql = $"INSERT Players.KickerStats(PlayerID, XPMade, XPMissed, FGGD, FGNG, TeamGameID) " +
                                        $"VALUES((SELECT PI.PlayerID FROM Players.PlayerInfo PI WHERE PI.[NAME] = N'{K.Name}'), {p.XPMade}, {p.XPMissed}, {p.FGGD}, {p.FGNG}, " +
                                        $"(SELECT DISTINCT(G.GameID) FROM Games.Game G RIGHT JOIN NFL.Teams T ON T.TeamID = G.HomeTeamID OR T.TeamID = G.VisitorTeamID " +
                                        $"RIGHT JOIN Players.TeamPlayer TP ON TP.TeamID = T.TeamID RIGHT JOIN Players.PlayerInfo Player ON Player.Name = TP.Name WHERE T.TeamID = " +
                                        $"(SELECT TPInfo.TeamID FROM Players.PlayerInfo Info INNER JOIN Players.TeamPlayer TPInfo ON TPInfo.Name = Info.Name WHERE Info.Name = '{K.Name}') " +
                                        $"ORDER BY G.GameID ASC OFFSET {i} ROWS FETCH NEXT 1 ROWS ONLY" +
                                        $"))";
                    SqlCommand     command;
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    command = new SqlCommand(sql, connection);
                    adapter.InsertCommand = new SqlCommand(sql, connection);
                    adapter.InsertCommand.ExecuteNonQuery();
                    command.Dispose();
                }
            }
            int    k      = 1;
            Random random = new Random();

            foreach (Player.PlayerInfo DEF in controller.Defense)
            {
                for (int i = 0; i < DEF.DefStats.Count; i++)
                {
                    Player.Defense p   = DEF.DefStats[i];
                    string         sql = $"INSERT Players.DefenseStats(PlayerID, PassYardsAllowed, RushYardsAllowed, TouchdownsAllowed, Touchdowns, Safeties, Interceptions, Fumbles, TeamGameID, ByeWeek) " +
                                         $"VALUES({k}, {p.PassYardsAllowed}, {p.RushYardsAllowed}, {p.Touchdowns}, {random.Next(10)}, {p.Safeties}, {p.Interceptions}, {p.Fumbles}, " +
                                         $"(SELECT DISTINCT(G.GameID) FROM Games.Game G RIGHT JOIN NFL.Teams T ON T.TeamID = G.HomeTeamID OR T.TeamID = G.VisitorTeamID " +
                                         $"RIGHT JOIN Players.TeamPlayer TP ON TP.TeamID = T.TeamID RIGHT JOIN Players.PlayerInfo Player ON Player.Name = TP.Name WHERE T.TeamID = {k} " +
                                         $"ORDER BY G.GameID ASC OFFSET {i} ROWS FETCH NEXT 1 ROWS ONLY), (SELECT T.ByeWeek FROM NFL.Teams T WHERE T.TeamID = {k}))";
                    SqlCommand     command;
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    command = new SqlCommand(sql, connection);
                    adapter.InsertCommand = new SqlCommand(sql, connection);
                    adapter.InsertCommand.ExecuteNonQuery();
                    command.Dispose();
                }
                k++;
            }
        }