// ===================================================== //Delete statement public override bool Delete(HockeyPlayer player) // ===================================================== { bool updateStatus = false; string playerName = player.FirstName + " " + player.LastName; MySqlDbHockeyPlayer hockeyPlayer = (MySqlDbHockeyPlayer)Read(playerName); if (OpenConnection() == true) { if (hockeyPlayer != null) { //Create Command string mySqlQuery = $"delete from mondaynighthockey.players where player_id={hockeyPlayer.PlayerId}"; MySqlCommand cmd = new MySqlCommand(mySqlQuery, _connection); // Execute the DELETE int affectedRows = cmd.ExecuteNonQuery(); if (affectedRows == 1) { updateStatus = true; } } // Finished with the command CloseConnection(); } return(updateStatus); }
// ===================================================== //Insert statement public override bool Insert(HockeyPlayer player) // ===================================================== { bool insertStatus = false; MySqlDbHockeyPlayer hockeyPlayer = new MySqlDbHockeyPlayer(player); if (OpenConnection() == true) { string firstName = hockeyPlayer.FirstName; string lastName = hockeyPlayer.LastName; string position = hockeyPlayer.PlayerPos; string playerType = hockeyPlayer.PlayerType.ToString(); string skillLevel; if (hockeyPlayer.Level == HockeyPlayer.PlayerSkill.Level_A) { skillLevel = "A"; } else if (hockeyPlayer.Level == HockeyPlayer.PlayerSkill.Level_B) { skillLevel = "B"; } else if (hockeyPlayer.Level == HockeyPlayer.PlayerSkill.Level_C) { skillLevel = "C"; } else //(hockeyPlayer.Level == HockeyPlayer.PlayerSkill.Level_D) { skillLevel = "D"; } string goalie; if (hockeyPlayer.Goalie == true) { goalie = "Y"; } else { goalie = "N"; } string mySqlQuery = $"insert into mondaynighthockey.players (player_last_name, player_first_name, player_level, player_position, player_goalie, player_type, player_team, player_last_wk) values (\"{lastName}\", \"{firstName}\", '{skillLevel[0]}', \"{position}\", '{goalie[0]}', \"{playerType}\", \"Unaffiliated\", \"Zed\")"; Console.WriteLine(mySqlQuery); //Create Command MySqlCommand cmd = new MySqlCommand(mySqlQuery, _connection); // Execute the INSERT int affectedRows = cmd.ExecuteNonQuery(); // Finished with the command CloseConnection(); if (affectedRows == 1) { insertStatus = true; } } return(insertStatus); }
// ============================================================== public MySqlDbHockeyPlayer(MySqlDbHockeyPlayer player) : base(player) // ============================================================== { PlayerId = player.PlayerId; }
// ===================================================== // ===================================================== // Select Statement public HockeyPlayer SelectPlayer(string playerName) // Expect "first last" // ===================================================== { MySqlDbHockeyPlayer player = null; string query = null; if (OpenConnection() == true) { // Parse the string into last_name/first_name string[] name = playerName.Split(' '); if (name.Length == 3 && name[1] == "St.") { query = $"SELECT * FROM players WHERE player_last_name = \"" + name[1] + " " + name[2] + "\" AND player_first_name = \"" + name[0] + "\""; } else { query = $"SELECT * FROM players WHERE player_last_name = \"" + name[1] + "\" AND player_first_name = \"" + name[0] + "\""; } //Create Command MySqlCommand cmd = new MySqlCommand(query, _connection); //Create a data reader and Execute the command MySqlDataReader dataReader = cmd.ExecuteReader(); while (dataReader.Read()) { var index = dataReader["player_id"] + ""; var lastName = dataReader["player_last_name"] + ""; var firstName = dataReader["player_first_name"] + ""; var level = dataReader["player_level"] + ""; var position = dataReader["player_position"] + ""; var goalie = dataReader["player_goalie"] + ""; var type = dataReader["player_type"] + ""; var team = dataReader["player_team"] + ""; var lastWeek = dataReader["player_last_wk"] + ""; var captain = dataReader["captain"] + ""; var altCaptain = dataReader["alt_captain"] + ""; int playerId; int.TryParse(index, out playerId); HockeyPlayer.PlayerSkill skillLevel; if (level == "D") { skillLevel = HockeyPlayer.PlayerSkill.Level_D; } else if (level == "C") { skillLevel = HockeyPlayer.PlayerSkill.Level_C; } else if (level == "B") { skillLevel = HockeyPlayer.PlayerSkill.Level_B; } else // (level == "A") { skillLevel = HockeyPlayer.PlayerSkill.Level_A; } bool canPlayGoalie = false; if (goalie == "Y") { canPlayGoalie = true; } bool isCaptain = false, isAltCaptain = false; if (captain == "Y") { isCaptain = true; } if (altCaptain == "Y") { isAltCaptain = true; } player = new MySqlDbHockeyPlayer(playerId, lastName, firstName, skillLevel, position, canPlayGoalie, type[0], team, lastWeek, isCaptain, isAltCaptain); } CloseConnection(); } return(player); }