/// <summary> /// Возврат списка пользователей с пагинацией, фильтром или сортировкой /// </summary> /// <param name="skip"></param> /// <param name="limit"></param> /// <returns></returns> public async Task <UserPaging> GetAll(int skip, int limit, string filter) { using (var conn = new SqlConnection(AppSettings.ConnectionString)) { string sqlfilter, sql, sqlc; CreateFilter(filter, out sqlfilter, out sql, out sqlc); var result = (await conn.QueryAsync <User, Brigade, User>(sql, (user, brigade) => { user.Brigade = brigade; return(user); }, new { skip = skip, limit = limit }) ).ToArray(); foreach (var item in result) { item.PasswordHash = null; } var count = conn.ExecuteScalar <int>(sqlc); var output = new UserPaging() { SortOptions = new SortOptions(), Data = result, Total = count }; return(output); } }
/// <summary> /// Возврат списка пользователей с пагинацией /// </summary> /// <param name="skip"></param> /// <param name="limit"></param> /// <returns></returns> public async Task <UserPaging> GetAll(int skip, int limit) { using (var conn = new SqlConnection(AppSettings.ConnectionString)) { var sql = Sql.SqlQueryCach["Users.AllPaging"]; var result = (await conn.QueryAsync <User, Brigade, User>(sql, (user, brigade) => { user.Brigade = brigade; return(user); }, new { skip = skip, limit = limit }) ).ToArray(); foreach (var item in result) { item.PasswordHash = null; } var sqlc = Sql.SqlQueryCach["Users.CountAll"]; var count = conn.ExecuteScalar <int>(sqlc); var output = new UserPaging() { Data = result, Total = count }; return(output); } }
public async Task <UserPaging> GetAllWithLogin(int skip, int limit, string search = null, string sort = null) { using (var conn = new SqlConnection(AppSettings.ConnectionString)) { SortOptions sortOptions = new SortOptions(); string orderBy = "ORDER BY id"; if (sort != null) { sortOptions = JsonConvert.DeserializeObject <SortOptions>(sort); if (sortOptions.Column != null) { orderBy = $"ORDER BY {sortOptions.Column} {(sortOptions.Direction == 1 ? "DESC" : "ASC")}"; } } var sql = Sql.SqlQueryCach["Users.AllWithLoginPaging"]; if (search != null) { sql = sql.Replace("--and", $"AND (Login LIKE '%' + @search + '%' OR Name LIKE '%' + @search + '%') "); } sql += $"{Environment.NewLine} {orderBy} OFFSET @skip ROWS FETCH NEXT @limit ROWS ONLY;"; var result = (await conn.QueryAsync <User>(sql, new { skip = skip, limit = limit, search })).ToArray(); var sqlRoleR = new UserRoleRepository(); foreach (var item in result) { item.PasswordHash = null; item.Role = sqlRoleR.GetById(item.RoleId); } sqlRoleR.Dispose(); var sqlc = Sql.SqlQueryCach["Users.CountAllWithLogin"]; if (search != null) { sqlc = sqlc.Replace("--and", $"AND (Login LIKE '%' + @search + '%' OR Name LIKE '%' + @search + '%') "); } var count = conn.ExecuteScalar <int>(sqlc, new { search }); var output = new UserPaging() { SortOptions = sortOptions, Data = result, Total = count }; return(output); } }