public async Task <PaginatedResponse <OrderModel> > Query(GetOrdersRequest request)
{
var parameters = new Dictionary <string, object>();
var where = new SqlWhereBuilder(request, _columns).Build();
var orderBy = new SqlOrderByBuilder($@"{nameof(OrderModel.Id)} ASC", _columns, request).Build();
var pagination = new SqlPaginationBuilder(request).Build();
var select = $@"
SELECT DISTINCT
{nameof(OrderModel.Id)},
{nameof(OrderModel.CarFrame)},
{nameof(OrderModel.Model)},
{nameof(OrderModel.LicensePlate)},
{nameof(OrderModel.DeliveryDate)}
FROM {nameof(CarsContext.Orders)} {where.Sql} {orderBy} {pagination.Sql}";
var count = $@"SELECT COUNT(*) FROM {nameof(CarsContext.Orders)} O {where.Sql}";
parameters.AddRange(where.Parameters);
parameters.AddRange(pagination.Parameters);
var result = await _db.QueryMultipleAsync($"{select};{count}", parameters);
return(new PaginatedResponse <OrderModel>(result.Read <OrderModel>(), result.ReadFirst <long>()));
}
public void SqlOrderByBuilder_Single_Field_Ascending_Tests()
{
var sorters = new Queue <SorterNode>();
sorters.Enqueue(new SorterNode
{
FieldName = "Field1",
SortingOrder = SorterNode.SortingOrders.Ascending
});
var builder = new SqlOrderByBuilder(new SqlServerDatabaseDriver());
Assert.AreEqual("Field1 ASC", builder.Build(sorters));
}
public async Task <PaginatedResponse <UserListItemResponseModel> > Query(GetUsersRequest request, CancellationToken cancellationToken)
{
var parameters = new Dictionary <string, object>();
var orderBy = new SqlOrderByBuilder($"U.[{nameof(User.Name)}] ASC", _columns, request).Build();
var pagination = new SqlPaginationBuilder(request).Build();
var gendersLeftJoin =
$"LEFT JOIN [{nameof(DataBaseContext.Genders)}] G ON G.[{nameof(Gender.Id)}] = U.[{nameof(User.GenderId)}]";
var select = $@"
SELECT
U.[{nameof(User.Id)}] AS [{nameof(UserListItemResponseModel.Id)}],
U.[{nameof(User.Email)}] AS [{nameof(UserListItemResponseModel.Email)}],
U.[{nameof(User.Name)}] AS [{nameof(UserListItemResponseModel.Name)}],
U.[{nameof(User.Birthdate)}] AS [{nameof(UserListItemResponseModel.Birthdate)}],
U.[{nameof(User.GenderId)}] AS [{nameof(UserListItemResponseModel.GenderId)}],
G.[{nameof(Gender.Name)}] AS [{nameof(UserListItemResponseModel.GenderName)}]
FROM [{nameof(DataBaseContext.Users)}] U
{gendersLeftJoin}
{orderBy} {pagination.Sql}";
var count = $@"
SELECT COUNT(U.[{nameof(User.Id)}])
FROM [{nameof(DataBaseContext.Users)}] U
{gendersLeftJoin}";
parameters.AddRange(pagination.Parameters);
var result = await WithConnection(async connection =>
{
var queryResult = await connection.QueryMultipleAsync($"{select};{count}", parameters);
return(new PaginatedResponse <UserListItemResponseModel>(queryResult.Read <UserListItemResponseModel>(),
queryResult.ReadFirst <long>()));
}, cancellationToken);
return(result);
}
