Creating a Leaderboard with Stored Procedures in an API Application

Question

How can a leaderboard be implemented for an application that uses an API, including creating the necessary stored procedures?

I am new to this technology but have some understanding of data access using ADO.NET. What steps can be taken to achieve this?

Answer 1

Implementing a leaderboard for an application that interacts with an API involves several steps, including database design, stored procedures for data retrieval, and API integration. Since you're familiar with ADO.NET, I'll outline a structured approach:

---

1. Database Design

You'll need a table to store leaderboard data. A basic structure could be:

CREATE TABLE Leaderboard (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Username NVARCHAR(100) NOT NULL,
    Score INT NOT NULL,
    CreatedAt DATETIME DEFAULT GETDATE()
);

---

2. Creating Stored Procedures

Insert or Update a Score

If a user submits a new score, update their existing score if it's higher, otherwise ignore it.

CREATE PROCEDURE UpdateLeaderboard
    @Username NVARCHAR(100),
    @Score INT
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM Leaderboard WHERE Username = @Username)
    BEGIN
        UPDATE Leaderboard
        SET Score = CASE WHEN Score < @Score THEN @Score ELSE Score END
        WHERE Username = @Username;
    END
    ELSE
    BEGIN
        INSERT INTO Leaderboard (Username, Score)
        VALUES (@Username, @Score);
    END
END;

Retrieve the Top Scores

To display the leaderboard:

CREATE PROCEDURE GetLeaderboard
    @TopN INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT TOP (@TopN) Username, Score
    FROM Leaderboard
    ORDER BY Score DESC, CreatedAt ASC;
END;

---

3. API Integration (Using ADO.NET)

In your C# application, you can call the stored procedures using ADO.NET.

Updating Scores

using (SqlConnection conn = new SqlConnection("your_connection_string"))
{
    using (SqlCommand cmd = new SqlCommand("UpdateLeaderboard", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@Username", "Player1");
        cmd.Parameters.AddWithValue("@Score", 2000);

        conn.Open();
        cmd.ExecuteNonQuery();
    }
}

Fetching Leaderboard

using (SqlConnection conn = new SqlConnection("your_connection_string"))
{
    using (SqlCommand cmd = new SqlCommand("GetLeaderboard", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TopN", 10);

        conn.Open();
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine($"Username: {reader["Username"]}, Score: {reader["Score"]}");
            }
        }
    }
}

---

4. Exposing API for Frontend

If you're exposing the leaderboard through an API (ASP.NET Web API):

Leaderboard Controller

[HttpGet("leaderboard/{topN}")]
public async Task<IActionResult> GetLeaderboard(int topN)
{
    List<LeaderboardEntry> leaderboard = new List<LeaderboardEntry>();

    using (SqlConnection conn = new SqlConnection("your_connection_string"))
    {
        using (SqlCommand cmd = new SqlCommand("GetLeaderboard", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@TopN", topN);
            conn.Open();
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    leaderboard.Add(new LeaderboardEntry
                    {
                        Username = reader["Username"].ToString(),
                        Score = Convert.ToInt32(reader["Score"])
                    });
                }
            }
        }
    }

    return Ok(leaderboard);
}

Leaderboard Model

public class LeaderboardEntry
{
    public string Username { get; set; }
    public int Score { get; set; }
}

---

5. Frontend Consumption

If your application has a frontend, it can make an API request like:

fetch("https://yourapi.com/leaderboard/10")
    .then(response => response.json())
    .then(data => console.log(data));

---

Conclusion

1. Database: Store leaderboard data.
2. Stored Procedures: Handle inserting/updating scores and retrieving rankings.
3. ADO.NET: Call stored procedures from your application.
4. API: Expose leaderboard data.
5. Frontend: Fetch and display data.

Answer 2

CREATE TABLE Users (

UserId SERIAL PRIMARY KEY,

Username VARCHAR(50) UNIQUE,

Email VARCHAR(100)

);

CREATE TABLE Scores (

ScoreId SERIAL PRIMARY KEY,

UserId INT REFERENCES Users(UserId),

Score INT,

Timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);