public void WideReceiversList(string file) { using (StreamReader sr = new StreamReader(file)) { sr.ReadLine(); for (int i = 0; i < 16; i++) { foreach (Player.PlayerInfo Player in WideReceivers) { Player.QBRWTE p = new Player.QBRWTE(); string line = sr.ReadLine(); string[] pLine = line.Split('|'); p.PassYard = pLine[0]; p.RushYard = pLine[1]; p.ReceivingYards = pLine[2]; p.Receptions = pLine[3]; p.Touchdowns = pLine[4]; p.Interceptions = pLine[5]; p.Fumbles = pLine[6]; Player.Stats.Add(p); } } Console.WriteLine("The Number of WR is " + WideReceivers.Count); Console.WriteLine("The Number of WR Stats is " + WideReceivers[0].Stats.Count); } }
public void insertPlayerData() { foreach (Player.PlayerInfo QB in 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, ByeWeek)\n" + "VALUES (SELECT PI.PlayerID FROM Players.PlayerInfo PI WHERE PI.[NAME] = " + QB.Name + ", " + p.PassYard + ", " + p.RushYard + ", " + p.ReceivingYards + ", " + p.Receptions + ", " + p.Touchdowns + ", " + p.Interceptions + ", " + p.Fumbles + ", " + p.GameID + ", " + p.ByeWeek + ") "; } } foreach (Player.PlayerInfo RB in 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, ByeWeek)\n" + "VALUES (SELECT PI.PlayerID FROM Players.PlayerInfo PI WHERE PI.[NAME] = " + RB.Name + ", " + p.PassYard + ", " + p.RushYard + ", " + p.ReceivingYards + ", " + p.Receptions + ", " + p.Touchdowns + ", " + p.Interceptions + ", " + p.Fumbles + ", " + p.GameID + ", " + p.ByeWeek + ") "; } } foreach (Player.PlayerInfo WR in 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, ByeWeek)\n" + "VALUES (SELECT PI.PlayerID FROM Players.PlayerInfo PI WHERE PI.[NAME] = " + WR.Name + ", " + p.PassYard + ", " + p.RushYard + ", " + p.ReceivingYards + ", " + p.Receptions + ", " + p.Touchdowns + ", " + p.Interceptions + ", " + p.Fumbles + ", " + p.GameID + ", " + p.ByeWeek + ") "; } } foreach (Player.PlayerInfo TE in 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, ByeWeek)\n" + "VALUES (SELECT PI.PlayerID FROM Players.PlayerInfo PI WHERE PI.[NAME] = " + TE.Name + ", " + p.PassYard + ", " + p.RushYard + ", " + p.ReceivingYards + ", " + p.Receptions + ", " + p.Touchdowns + ", " + p.Interceptions + ", " + p.Fumbles + ", " + p.GameID + ", " + p.ByeWeek + ") "; } } foreach (Player.PlayerInfo DEF in 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, Safeties, Interceptions, Fumbles, TeamGameID, ByeWeek)\n" + "VALUES (SELECT PI.PlayerID FROM Players.PlayerInfo PI WHERE PI.[NAME] = " + DEF.Name + ", " + p.PassYardsAllowed + ", " + p.RushYardsAllowed + ", " + p.Touchdowns + ", " + p.Safeties + ", " + p.Interceptions + ", " + p.Fumbles + ", " + p.GameID + ", " + p.ByeWeek + ") "; } } }
/// <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++; } }