// =====================================================
//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);
}
