using System;

using System.Data.SqlClient;

using Dapper;

​

​

// dbo.ScoreDetail Table

​

// ScoreDetail      | Type              | Nullable

// -----------------------------------------------

// Id               | int (identity)    | not null

// Name             | varchar(100)      | not null

// Score            | smallint          | null

// LastModifiedDate | date              | not null

​

​

public class PlayerScore

{

    public Guid Id { get; set; }    

    public string Name { get; set; }

    public int Score { get; set; }

    public DateTime? LastModifiedDate { get; set; }

}

​

public class ScoreService

{

    //Part 1

    public void Upsert (PlayerScore playerScore)

    {

        /*

        This Api will be available in public. we need to add the following

        1. If the PlayerScore is not in the dbo.ScoreDetail then insert a new record 

        2. If the PlayerScore exist in the dbo.ScoreDetail

            2.1 if Score is different in dbo.ScoreDetail update the record with the current date

            2.2 if Score is same in dbo.ScoreDetail don't update anything

        */

        string query = @"MERGE INTO dbo.ScoreDetail AS [Target]

                        USING (VALUES(@Id, @Name, @Score, @LastModifiedDate)) AS [Source] (Id, Name, Score, LastModifiedDate) 

                        ON Target.Id = Source.ID

                        AND Target.Name = Source.Name

                        WHEN NOT MATCHED BY TARGET THEN

                        INSERT(

                          ID,

                          Name,

                          Score. 

                          LastModifiedDate

                        )

                        VALUES(Source.ID, Source.Name, Source.Score, Source.LastModifiedDate)

                        WHEN MATCHED AND Target.Score <> Source.Score THEN 

                        UPDATE SET

                        Target.Score = Source.Score,

                        Target.LastModifiedDate = Source.LastModifiedDate

                      ";

        using(var connection = new SQLConnection("Connection String")

        {

            connection.Execute(query, playerScore);

        }

    }

    //Part 2

    public void Test()

    {

        // Id   | Type      | Score |

        // --------------------------

        // 1    | Alex      | 80    |

        // 2    | Anna      | 75    |

        // 3    | Ricky     | 89    |

        // 4    | Raymond   | 95    |

​

        // Write the code which will get the high score of the player

       string query = @"SELECT * FROM dbo.ScoreDetail ORDER BY SCORE ASC";

        using(var connection = new SQLConnection("Connection String")

        {

            connection.Query<IList<PlayerScore>>(query, playerScore).FirstOrDefault;

        }

        // Write the code which will return the second highest score of the player

​

        // Insert 10 records with incrementing scores using loop inside a method

    }

}