/// <summary>
/// Gets the game configurable boundaries from the database
/// </summary>
/// <returns></returns>
public async Task <GameConfiguration> GetGameConfiguration()
{
Log.Information("GameConfigurationRepository.GetGameConfiguration");
var sql = @"SELECT
MinBoardColumns,
MinBoardRows,
MaxBoardColumns,
MaxBoardRows,
MinNumberOfLightsAtStartUp
FROM GameConfiguration";
using var dbConnection = new SqlConnection(_apiConfiguration.ConnectionString());
return(await dbConnection.QueryFirstAsync <GameConfiguration>(sql));
}
/// <summary>
/// Store the request response model in the database logs schema
/// </summary>
/// <param name="requestResponse"></param>
/// <returns>rows affected</returns>
public async Task <int> LogRequestAsync(RequestResponse requestResponse)
{
string sql = @"
INSERT [logs].[RequestResponse]
([RequestId]
,[DateTime]
,[Flow]
,[Host]
,[Method]
,[QueryString]
,[Body]
,[TimeMs])
VALUES (@RequestSessionId,
@DateTime,
@Flow,
@Host,
@Method,
@QueryString,
@Body,
@TimeMs)";
// note: Dapper automatically manages Opening and Closing of SQL Connections
using var dbConnection = new SqlConnection(_apiConfiguration.ConnectionString());
return(await dbConnection.ExecuteAsync(sql, requestResponse));
}
/// <summary>
/// gets the previous scores
/// </summary>
/// <returns></returns>
public async Task <IEnumerable <GameScore> > GetHighScores(GameRequest gameRequest)
{
Log.Information("GameConfigurationRepository.GetGameConfiguration");
var sql = @"SELECT TOP 20
[Columns]
,[Rows]
,[StartLightCount]
,[PlayerName]
,[NoOfMoves]
FROM [LightsOut].[dbo].[GameScores]
WHERE Columns=@columns
and Rows=@Rows
and StartLightCount=@StartLightCount
ORDER BY NoOfMoves DESC";
using var dbConnection = new SqlConnection(_apiConfiguration.ConnectionString());
return(await dbConnection.QueryAsync <GameScore>(sql, gameRequest));
}
/// <summary>
/// Setup Serilog to log to SQL Server
/// </summary>
public void SetupLogging(IApiConfiguration apiConfiguration)
{
var columnOptions = new ColumnOptions
{
AdditionalColumns = new Collection <SqlColumn>
{
new SqlColumn("RequestId", SqlDbType.UniqueIdentifier)
}
};
Serilog.Log.Logger = new LoggerConfiguration()
.Enrich.FromLogContext()
.Enrich.WithProperty("RequestId", apiConfiguration.RequestPipelineGuid)
.WriteTo.MSSqlServer(
apiConfiguration.ConnectionString(),
sinkOptions: new MSSqlServerSinkOptions {
SchemaName = "logs", TableName = "Log"
},
null, null,
LogEventLevel.Information, null,
columnOptions: columnOptions, null, null)
.CreateLogger();
}
