public StandingDTO GetByID(int?id)
{
try
{
StandingDTO standing = new StandingDTO();
using (MySqlConnection conn = _con.GetConnection())
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("SELECT StandingID,DivisionID,TeamID,Score FROM standing WHERE StandingID = ?id", conn);
cmd.Parameters.AddWithValue("id", id);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
standing.StandingID = reader[0] as int? ?? default;
standing.DivisionID = reader[1] as int? ?? default;
standing.TeamID = reader[2] as int? ?? default;
standing.Score = reader[3] as int? ?? default;
}
}
}
return(standing);
}
catch (Exception ex)
{
throw new ContextErrorException(ex);
}
}
//Update
public int?Update(StandingDTO entity)
{
try
{
using (MySqlConnection conn = _con.GetConnection())
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("UPDATE standing SET DivisionID = ?DivisionID, TeamID = ?TeamID, Score = ?Score WHERE StandingID = ?id", conn);
//where id is
cmd.Parameters.AddWithValue("id", entity.StandingID);
//values
cmd.Parameters.AddWithValue("DivisionID", entity.DivisionID ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("TeamID", entity.TeamID ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("Score", entity.Score ?? (object)DBNull.Value);
//execute command
int rowsAffected = cmd.ExecuteNonQuery();
//should return if a row is affected or not
return(rowsAffected);
}
}
catch (Exception ex)
{
throw new ContextErrorException(ex);
}
}
//Delete
public int?Remove(StandingDTO entity)
{
try
{
using (MySqlConnection conn = _con.GetConnection())
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("DELETE FROM standing WHERE StandingID = ?id", conn);
//where id =
cmd.Parameters.AddWithValue("id", entity.StandingID);
//execute command
int rowsAffected = cmd.ExecuteNonQuery();
//should return if a row is affected or not
return(rowsAffected);
}
}
catch (Exception ex)
{
throw new ContextErrorException(ex);
}
}
//Create
public int?Add(StandingDTO entity)
{
try
{
using (MySqlConnection conn = _con.GetConnection())
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("INSERT INTO standing (DivisionID,TeamID,Score) VALUES(?DivisionID,?TeamID,?Score)", conn);
//values
cmd.Parameters.AddWithValue("DivisionID", entity.DivisionID ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("TeamID", entity.TeamID ?? (object)DBNull.Value);
cmd.Parameters.AddWithValue("Score", entity.Score ?? (object)DBNull.Value);
//execute command
int rowsAffected = cmd.ExecuteNonQuery();
//should return if a row is affected or not
return(rowsAffected);
}
}
catch (Exception ex)
{
throw new ContextErrorException(ex);
}
}
