public async Task<DataPage<UserMessage>> GetUserMessages(string userLogin, int pageNumber, int pageSize) { ValidatePagination(pageNumber, pageSize); using (var conn = new SQLiteConnection(_connectionString)) { await conn.OpenAsync().ConfigureAwait(false); if (await conn.ExecuteScalarAsync<int>("select count(*) from [User] where Login = @userLogin", new { userLogin }).ConfigureAwait(false) == 0) return new DataPage<UserMessage>(pageNumber, pageSize, 0, null); //not found int count = await conn.ExecuteScalarAsync<int>("select count(*) from [Message] m join [User] u on m.UserId=u.UserId where u.Login = @userLogin", new {userLogin}).ConfigureAwait(false); string q = "select u.Login as UserLogin, m.Text, m.Created from [Message] m join [User] u on m.UserId=u.UserId where u.Login = @userLogin order by m.Created"; object prms; if (pageSize == -1) prms = new { userLogin }; else { q += " limit @size offset @skip"; prms = new { userLogin, size = pageSize, skip = (pageNumber - 1) * pageSize }; } return new DataPage<UserMessage>(pageNumber, pageSize, count, (await conn.QueryAsync<UserMessage>(q, prms)).ToList()); } }
public async Task<DataPage<UserInfo>> GetUsers(int pageNumber, int pageSize) { ValidatePagination(pageNumber, pageSize); using (var conn = new SQLiteConnection(_connectionString)) { await conn.OpenAsync().ConfigureAwait(false); int count = await conn.ExecuteScalarAsync<int>("select count(*) from [User]").ConfigureAwait(false); string q = "select [Login] as UserLogin, Display as DisplayName, Created from [User] order by [Login]"; object prms; if (pageSize == -1) prms = null; else { q += " limit @size offset @skip"; prms = new { size = pageSize, skip = (pageNumber - 1) * pageSize }; } return new DataPage<UserInfo>(pageNumber, pageSize, count, (await conn.QueryAsync<UserInfo>(q, prms).ConfigureAwait(false)).ToList()); } }