public async Task <int> GetCountAsync(MessageQueryModel query, CancellationToken cancellationToken)
{
if (cancellationToken.IsCancellationRequested)
{
return(0);
}
var sqlParams = new List <object>
{
new NpgsqlParameter("@IsValid", true)
};
var sqlWhere = new StringBuilder("WHERE IsValid=@IsValid ");
if (!string.IsNullOrEmpty(query.ApiRoute))
{
sqlWhere = sqlWhere.Append("AND ApiRoute LIKE CONCAT('%',@ApiRoute,'%')");
sqlParams.Add(new NpgsqlParameter("@ApiRoute", query.ApiRoute));
}
var sql = $"SELECT COUNT(1) AS count FROM {GetTableName()} {sqlWhere}";
await using var connection = new NpgsqlConnection(_options.Value.DbConnection);
var reader = connection.ExecuteQuery(sql, sqlParams.ToArray());
var count = 0;
while (reader.Read())
{
count += Convert.ToInt32(Convert.ToInt32(reader["count"]));
}
return(await Task.FromResult(count));
}
public override async Task <PageResultDto <ConfigurationMessage> > GetAsync(MessageQueryModel query, CancellationToken cancellationToken = default)
{
var result = new PageResultDto <ConfigurationMessage>();
if (cancellationToken.IsCancellationRequested)
{
return(result);
}
var sqlParams = new List <object>
{
new SqlParameter("@Environment", _source.Environment),
new SqlParameter("@NameSpace", _source.NameSpace)
};
var sqlWhere = new StringBuilder("WHERE [Environment]=@Environment AND [NameSpace]=@NameSpace ");
if (query.Id.HasValue)
{
sqlWhere = sqlWhere.Append("AND [Id]=@Id ");
sqlParams.Add(new SqlParameter("@Id", query.Id));
}
if (!string.IsNullOrEmpty(query.Key))
{
sqlWhere = sqlWhere.Append("AND [Key] LIKE CONCAT('%',@Key,'%') ");
sqlParams.Add(new SqlParameter("@Key", query.Key));
}
var sql = $@"SELECT * FROM {GetTableName()} {sqlWhere} ORDER BY [UpdateTime] DESC offset {query.PageIndex * query.PageSize} rows fetch next {query.PageSize} rows only";
using var connection = new SqlConnection(_source.DbConnection);
var reader = connection.ExecuteQuery(sql, sqlParams.ToArray());
var list = new List <ConfigurationMessage>();
while (reader.Read())
{
list.Add(new ConfigurationMessage
{
Id = Convert.ToInt32(reader["Id"]),
Key = reader["Key"].ToString(),
Value = reader["Value"].ToString(),
Description = reader["Description"].ToString(),
Environment = reader["Environment"].ToString(),
NameSpace = reader["NameSpace"].ToString(),
CreateTime = Convert.ToDateTime(reader["CreateTime"].ToString()),
UpdateTime = Convert.ToDateTime(reader["UpdateTime"].ToString()),
UtcTime = Convert.ToDateTime(reader["UtcTime"].ToString())
});
}
result.Count = await GetCountAsync(cancellationToken);
result.Items = list;
return(await Task.FromResult(result));
}
public async Task GetAllAsync(MessageQueryModel query, HttpContext context, CancellationToken cancellationToken)
{
try
{
query ??= new MessageQueryModel();
var messages = await _configurationStorage.GetAsync(query, cancellationToken);
var apiResult = new ApiResult <PageResultDto <ConfigurationMessage> >(messages);
await context.Response.WriteAsync(apiResult.ToJson(), Encoding.UTF8, cancellationToken);
}
catch (Exception e)
{
_logger.LogError(e, e.Message);
var apiResult = new ApiResult <List <ConfigurationMessage> >(500, e.Message);
await context.Response.WriteAsync(apiResult.ToJson(), Encoding.UTF8, cancellationToken);
}
}
public async Task <PageResultDto <RouteRoleEntity> > GetAsync(MessageQueryModel query, CancellationToken cancellationToken = default)
{
var result = new PageResultDto <RouteRoleEntity>();
if (cancellationToken.IsCancellationRequested)
{
return(result);
}
var sqlParams = new List <object>
{
new NpgsqlParameter("@IsValid", true)
};
var sqlWhere = new StringBuilder("WHERE IsValid=@IsValid ");
if (!string.IsNullOrEmpty(query.ApiRoute))
{
sqlWhere = sqlWhere.Append("AND ApiRoute LIKE CONCAT('%',@ApiRoute,'%')");
sqlParams.Add(new NpgsqlParameter("@ApiRoute", query.ApiRoute));
}
var sql = $@"SELECT * FROM {GetTableName()} {sqlWhere} ORDER BY UpdateTime DESC LIMIT {query.PageSize} OFFSET {query.PageIndex * query.PageSize}";
await using var connection = new NpgsqlConnection(_options.Value.DbConnection);
var reader = connection.ExecuteQuery(sql, sqlParams.ToArray());
var list = new List <RouteRoleEntity>();
while (reader.Read())
{
list.Add(new RouteRoleEntity
{
Id = Convert.ToInt32(reader["Id"]),
Roles = reader["Roles"].ToString().ToObject <List <string> >(),
ApiName = reader["ApiName"].ToString(),
ApiRoute = reader["ApiRoute"].ToString(),
IsValid = Convert.ToBoolean(reader["IsValid"].ToString()),
CreateTime = Convert.ToDateTime(reader["CreateTime"].ToString()),
UpdateTime = Convert.ToDateTime(reader["UpdateTime"].ToString()),
});
}
result.Count = await GetCountAsync(query, cancellationToken);
result.Items = list;
return(await Task.FromResult(result));
}