private void UpdateScore(int place, int points, ScoreSystemInfo scoreSystem)
{
try
{
using (SqlConnection connection = Database.Connection)
{
string query = "UPDATE Score SET Score.Points = @Points " +
"WHERE Score.Place = @Place " +
"AND Score.ScoreSystemId = (SELECT scoreSystem.Id " +
" FROM ScoreSystem AS scoreSystem " +
" WHERE scoreSystem.Name = @ScoreSystemName)";
using (SqlCommand command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@Place", place);
command.Parameters.AddWithValue("@Points", points);
command.Parameters.AddWithValue("@ScoreSystemName", scoreSystem.Name);
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw new DatabaseException(ex.Message);
}
}
public void RemoveScore(int place, ScoreSystemInfo scoreSystem)
{
try
{
using (SqlConnection connection = Database.Connection)
{
string query = "DELETE FROM Score " +
"WHERE Place = @Place " +
"AND ScoreSystemId = (SELECT scoreSystem.Id " +
" FROM ScoreSystem as scoreSystem " +
" INNER JOIN Score as score " +
" ON scoreSystem.Id = score.ScoreSystemId " +
" WHERE scoreSystem.Id = score.ScoreSystemId " +
" AND scoreSystem.Name = @ScoreSystemName)";
using (SqlCommand command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@Place", place);
command.Parameters.AddWithValue("@ScoreSystemName", scoreSystem.Name);
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw new DatabaseException(ex.Message);
}
}
private void AddScore(int place, int points, ScoreSystemInfo scoreSystem)
{
try
{
using (SqlConnection connection = Database.Connection)
{
string query = "INSERT INTO Score (Place, Points, ScoreSystemId) " +
"SELECT @Place, @Points, scoreSystem.Id " +
"FROM ScoreSystem as scoreSystem " +
"WHERE scoreSystem.Name = @ScoreSystemName";
using (SqlCommand command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@Place", place);
command.Parameters.AddWithValue("@Points", points);
command.Parameters.AddWithValue("@ScoreSystemName", scoreSystem.Name);
command.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw new DatabaseException(ex.Message);
}
}
public List <ScoreInfo> GetScoresForScoreSystem(ScoreSystemInfo scoreSystem)
{
List <ScoreInfo> scores = new List <ScoreInfo>();
try
{
using (SqlConnection connection = Database.Connection)
{
string query = "SELECT score.Place as Place, score.Points as Points " +
"FROM Score as score " +
"INNER JOIN ScoreSystem as scoreSystem " +
"ON score.ScoreSystemId = scoreSystem.Id " +
"WHERE scoreSystem.Name = @ScoreSystemName";
using (SqlCommand command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@ScoreSystemName", scoreSystem.Name);
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
scores.Add(new ScoreInfo(Convert.ToInt32(reader["Place"]), Convert.ToInt32(reader["Points"])));
}
}
}
}
}
catch (Exception ex)
{
throw new DatabaseException(ex.Message);
}
return(scores);
}
public void AddOrUpdateScore(int place, int points, ScoreSystemInfo scoreSystem)
{
List <string> errorFields = new List <string>();
if (place < 0)
{
errorFields.Add("Place");
}
if (points < 0)
{
errorFields.Add("Points");
}
if (errorFields.Count > 0)
{
throw new InvalidDataFormatException("The input is incorrect", String.Join(",", errorFields));
}
if (ScoreExists(place, scoreSystem.Name))
{
UpdateScore(place, points, scoreSystem);
}
else
{
AddScore(place, points, scoreSystem);
}
}
public void AddInvalidPlaceTest()
{
ScoreSystemInfo scoreSystem = new ScoreSystemInfo("System 1", 0);
ScoreMssqlContext sqlContext = new ScoreMssqlContext();
sqlContext.AddOrUpdateScore(-1, 1, scoreSystem);
}
public void AddInvalidNumberOfPontsTest()
{
ScoreSystemInfo scoreSystem = new ScoreSystemInfo("System 1", 0);
ScoreMssqlContext sqlContext = new ScoreMssqlContext();
sqlContext.AddOrUpdateScore(1, -1, scoreSystem);
}
public void UpdateScoreTest()
{
ScoreSystemInfo scoreSystem = new ScoreSystemInfo("System 1", 0);
ScoreSystemMssqlContext scoreSystemContext = new ScoreSystemMssqlContext();
ScoreMssqlContext scoreContext = new ScoreMssqlContext();
try
{
Assert.AreEqual(0, scoreSystemContext.GetScoreSystems().Count, "Cleanup of database failed");
scoreSystemContext.AddScoreSystem(scoreSystem.Name, scoreSystem.PointsForFastestLap);
scoreContext.AddOrUpdateScore(1, 10, scoreSystem);
Assert.AreEqual(1, scoreContext.GetScoresForScoreSystem(scoreSystem).Count);
Assert.AreEqual(10, scoreContext.GetScoresForScoreSystem(scoreSystem)[0].Points);
scoreContext.AddOrUpdateScore(1, 5, scoreSystem);
Assert.AreEqual(1, scoreContext.GetScoresForScoreSystem(scoreSystem).Count);
Assert.AreEqual(5, scoreContext.GetScoresForScoreSystem(scoreSystem)[0].Points);
}
finally
{
scoreContext.RemoveScore(1, scoreSystem);
Assert.AreEqual(0, scoreContext.GetScoresForScoreSystem(scoreSystem).Count);
scoreSystemContext.RemoveScoreSystem("System 1");
Assert.AreEqual(0, scoreSystemContext.GetScoreSystems().Count, "Cleanup of database failed");
}
}
public ScoreSystem(ScoreSystemInfo scoreSystem) : this(scoreSystem.Name, scoreSystem.PointsForFastestLap)
{
}
