public void Field_DoesNotExist_ThrowsException()
{
var sut = new DbFilter <Restaurant>()
{
Field = "testit", Operator = OperatorEnum.Equal, Value = "123"
};
Assert.ThrowsException <Exceptions.InvalidFilterFieldException>(() =>
sut.GetFilterSql("filterval"));
}
public void IntField_Equal_ShouldHaveCorrectSql()
{
var sut = new DbFilter <Restaurant>()
{
Field = "Id", Operator = OperatorEnum.Equal, Value = "123"
};
var filterString = sut.GetFilterSql("filterval");
Assert.AreEqual(" Id = @filterval", filterString);
}
public void StringField_Like_ShouldHaveCorrectSql()
{
var sut = new DbFilter <Restaurant>()
{
Field = "City", Operator = OperatorEnum.Like, Value = "Pitts%"
};
var filterString = sut.GetFilterSql("filterval");
Assert.AreEqual(" City like @filterval", filterString);
}
public async Task <IEnumerable <Review> > GetReviewsAsync(int page, int pagesize, DbFilter <Review> filter)
{
using (var db = GetConnection())
{
var query = @"SELECT id, user_id as UserId, restaurant_id as RestaurantId, heading, content, rating
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY heading ) AS RowNum, id, user_id, restaurant_id, heading, content, rating
FROM Review
<<whereclause>>
) AS RowConstrainedResult
WHERE RowNum >= @start
AND RowNum < @end
ORDER BY RowNum";
IEnumerable <Review> result;
var start = ((page - 1) * pagesize) + 1;
var end = start + pagesize;
object param;
if (filter != null)
{
query = query.Replace("<<whereclause>>", string.Format("where {0}", filter.GetFilterSql("filterparam")));
param = new { start, end, filterparam = filter.Value };
}
else
{
query = query.Replace("<<whereclause>>", "");
param = new { start, end };
}
result = await db.QueryAsync <Review>(query, param);
return(result);
}
}
public async Task <IEnumerable <Restaurant> > GetRestaurantsAsync(int page, int pagesize, DbFilter <Restaurant> filter)
{
using (var db = GetConnection())
{
var query = @"SELECT id, name, address, city
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY Name ) AS RowNum, id, name, address, city
FROM Restaurant
<<whereclause>>
) AS RowConstrainedResult
WHERE RowNum >= @start
AND RowNum < @end
ORDER BY RowNum";
Task <IEnumerable <Restaurant> > result;
var start = ((page - 1) * pagesize) + 1;
var end = start + pagesize;
if (filter != null)
{
query = query.Replace("<<whereclause>>", string.Format("where {0}", filter.GetFilterSql("filterparam")));
result = db.QueryAsync <Restaurant>(query, new { start, end, filterparam = filter.Value });
}
else
{
query = query.Replace("<<whereclause>>", "");
result = db.QueryAsync <Restaurant>(query, new { start, end });
}
return(await result);
}
}