/// <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(); }