Memory/Memory.Data/ScoreHandler.cs

using Memory.Logic;
using System.Data.SQLite;

namespace Memory.Data
{
    public class ScoreHandler : IScoreHandler
    {
        public const string URI = "Data Source=Scores.db;Version=3;";

        // Ensure the database and table exist upon ScoreHandler creation
        public ScoreHandler()
        {
            using SQLiteConnection connection = new(URI);
            connection.Open();
            using SQLiteCommand command = new("CREATE TABLE IF NOT EXISTS Scores(ID INTEGER PRIMARY KEY, Name TEXT, Points INTEGER)", connection);
            command.ExecuteNonQuery();
            connection.Close();
        }

        // Get the position of a certain score
        public int GetScorePosition(int score)
        {
            using SQLiteConnection connection = new(URI);
            connection.Open();
            using SQLiteCommand command = new("SELECT COUNT(*) + 1 FROM Scores WHERE Points > @Score", connection);
            command.Parameters.AddWithValue("@Score", score);
            using SQLiteDataReader reader = command.ExecuteReader();
            if (!reader.Read())
            {
                return 0;
            }
            return reader.GetInt32(0);
        }

        // Get the top 10 highscores
        public List<Score> GetTopScores()
        {
            List<Score> scores = [];
            using SQLiteConnection connection = new(URI);
            connection.Open();
            using SQLiteCommand command = new("SELECT Name, Points FROM Scores ORDER BY Points DESC", connection);
            using SQLiteDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                scores.Add(new(reader.GetString(0), reader.GetInt32(1)));
            }
            connection.Close();
            return scores;
        }

        // Write the new score to the database
        public void WriteScore(Score score)
        {
            using SQLiteConnection connection = new(URI);
            connection.Open();
            using SQLiteCommand command = new("INSERT INTO Scores(Name, Points) VALUES(@Name, @Points)", connection);
            command.Parameters.AddWithValue("@Name", score.Name);
            command.Parameters.AddWithValue("@Points", score.Points);
            command.ExecuteNonQuery();
            connection.Close();
        }
    }
}