public async Task <int> CountLogsAsync(string level, string searchCriteria) { var queryBuilder = new StringBuilder(); queryBuilder.Append("SELECT COUNT(message) FROM "); queryBuilder.Append(_options.Schema); queryBuilder.Append("."); queryBuilder.Append(_options.TableName); var whereIncluded = false; if (!string.IsNullOrEmpty(level)) { queryBuilder.Append(" WHERE level = @Level "); whereIncluded = true; } if (!string.IsNullOrEmpty(searchCriteria)) { queryBuilder.Append(whereIncluded ? " AND message LIKE @Search OR exception LIKE @Search " : " WHERE message LIKE @Search OR exception LIKE @Search "); } using IDbConnection connection = new NpgsqlConnection(_options.ConnectionString); return(await connection.ExecuteScalarAsync <int>(queryBuilder.ToString(), new { Level = LogLevelConverter.GetLevelValue(level), Search = searchCriteria != null ? "%" + searchCriteria + "%" : null })); }
private async Task <IEnumerable <LogModel> > GetLogsAsync(int page, int count, string level, string searchCriteria) { var queryBuilder = new StringBuilder(); queryBuilder.Append("SELECT message, message_template, level, timestamp, exception, log_event AS \"Properties\" FROM "); queryBuilder.Append(_options.Schema); queryBuilder.Append("."); queryBuilder.Append(_options.TableName); var whereIncluded = false; if (!string.IsNullOrEmpty(level)) { queryBuilder.Append(" WHERE level = @Level "); whereIncluded = true; } if (!string.IsNullOrEmpty(searchCriteria)) { queryBuilder.Append(whereIncluded ? " AND message LIKE @Search OR exception LIKE @Search " : " WHERE message LIKE @Search OR exception LIKE @Search "); } queryBuilder.Append(" ORDER BY timestamp DESC LIMIT @Count OFFSET @Offset "); using IDbConnection connection = new NpgsqlConnection(_options.ConnectionString); var logs = await connection.QueryAsync <PostgresLogModel>(queryBuilder.ToString(), new { Offset = page *count, Count = count, Level = LogLevelConverter.GetLevelValue(level), Search = searchCriteria != null ? "%" + searchCriteria + "%" : null }); var index = 1; foreach (var log in logs) { log.RowNo = (page * count) + index++; } return(logs); }