public override async Task <int> GetCount(UsersQueryParam param) { string sql = @"select count(u.Id) Count from Users u {0}"; var queryWhere = GetQueryWhere(param); return(await _connection.QueryFirstAsync <int>(String.Format(sql, queryWhere.Clause), queryWhere.Params)); }
public override async Task <IEnumerable <UserDTO> > Get(UsersQueryParam param) { string sql = @"select u.Id, u.FirstName, u.SecondName, COALESCE(Sum(p.TotalAmount), CAST(0 as money)) TotalAmount from Users u left join Purchases p on p.UserId = u.Id {0} group by u.Id, u.FirstName, u.SecondName order by TotalAmount desc" ; var queryWhere = GetQueryWhere(param); return(await _connection.QueryAsync <UserDTO>(String.Format(sql, queryWhere.Clause), queryWhere.Params)); }
private QueryWhere GetQueryWhere(UsersQueryParam param) { var nativeParams = new Dictionary <string, object>(); string where = null; if (!String.IsNullOrWhiteSpace(param.Part1) && !String.IsNullOrWhiteSpace(param.Part2)) { where = "where upper(u.FirstName) like CONCAT(@part1,'%') and upper(u.SecondName) like CONCAT(@part2,'%')"; nativeParams["part1"] = param.Part1.ToUpperInvariant(); nativeParams["part2"] = param.Part2.ToUpperInvariant(); } else if (!String.IsNullOrWhiteSpace(param.Part1)) { where = "where upper(u.FirstName) like CONCAT(@part1,'%') or upper(u.SecondName) like CONCAT(@part1,'%')"; nativeParams["part1"] = param.Part1.ToUpperInvariant(); } return(new QueryWhere { Clause = where, Params = nativeParams }); }