public async Task <User> GetByEmail(string email) { User user = null; using (IPostgresCommand query = Connection.CreateCommand()) { query.CommandText = "SELECT id, username, firstname, lastname, email, created_date FROM users.users WHERE lower(email) = :p_email"; AddParameter(query, DbType.String, ParameterDirection.Input, "p_email", email.ToLower()); if (Transaction != null) { query.Transaction = Transaction; } await query.PrepareAsync(); using (var reader = await query.ExecuteReaderAsync()) { user = ReadUser(reader); } } return(user); }
public async Task <User> GetById(System.Guid id) { User user = null; using (IPostgresCommand query = Connection.CreateCommand()) { query.CommandText = "SELECT id, username, firstname, lastname, email, created_date FROM users.users WHERE id = :p_id"; AddParameter(query, DbType.Guid, ParameterDirection.Input, "p_id", id); if (Transaction != null) { query.Transaction = Transaction; } await query.PrepareAsync(); using (var reader = await query.ExecuteReaderAsync()) { user = ReadUser(reader); } } return(user); }
private async Task Update(User modifiedUser, User existingUser) { using (IPostgresCommand command = Connection.CreateCommand()) { StringBuilder builder = new StringBuilder(); builder.Append("UPDATE users.users "); if (modifiedUser.UserName != existingUser.UserName) { AddSetClause(builder, command, DbType.String, "username", modifiedUser.UserName); } if (modifiedUser.FirstName != existingUser.FirstName) { AddSetClause(builder, command, DbType.String, "firstname", modifiedUser.FirstName); } if (modifiedUser.LastName != existingUser.LastName) { AddSetClause(builder, command, DbType.String, "lastname", modifiedUser.LastName); } if (modifiedUser.Email != existingUser.Email) { AddSetClause(builder, command, DbType.String, "email", modifiedUser.Email); } if (modifiedUser.CreatedDate != existingUser.CreatedDate) { AddSetClause(builder, command, DbType.DateTime, "created_date", modifiedUser.CreatedDate); } builder.Append(" WHERE id = :p_id"); AddParameter(command, DbType.Guid, ParameterDirection.Input, "p_id", modifiedUser.Id); command.CommandText = builder.ToString(); await command.PrepareAsync(); await command.ExecuteNonQueryAsync(); } }
public async Task <IEnumerable <User> > GetAll() { IEnumerable <User> users = null; using (IPostgresCommand query = Connection.CreateCommand()) { query.CommandText = "SELECT id, username, firstname, lastname, email, created_date FROM users.users ORDER BY created_date"; if (Transaction != null) { query.Transaction = Transaction; } await query.PrepareAsync(); using (var reader = await query.ExecuteReaderAsync()) { users = ReadUsers(reader); } } return(users); }
public async Task <IEnumerable <User> > Search(string pattern, int skip, int limit) { IEnumerable <User> users = null; using (IPostgresCommand query = Connection.CreateCommand()) { Guid guidPattern = Guid.Empty; if (Guid.TryParse(pattern, out guidPattern)) { query.CommandText = @" SELECT id, username, firstname, lastname, email, created_date FROM users.users WHERE id = :p_id "; AddParameter(query, DbType.Guid, ParameterDirection.Input, "p_id", guidPattern); } else { query.CommandText = @" SELECT id, username, firstname, lastname, email, created_date FROM users.users WHERE username ILIKE :p_pattern OR firstname ILIKE :p_pattern OR lastname ILIKE :p_pattern OR email ILIKE :p_pattern ORDER BY created_date LIMIT :p_limit OFFSET :p_offset "; AddParameter(query, DbType.String, ParameterDirection.Input, "p_pattern", $"%{pattern.Replace(' ', '%')}%"); AddParameter(query, DbType.Int32, ParameterDirection.Input, "p_limit", limit); AddParameter(query, DbType.Int32, ParameterDirection.Input, "p_offset", skip); } if (Transaction != null) { query.Transaction = Transaction; } await query.PrepareAsync(); using (var reader = await query.ExecuteReaderAsync()) { users = ReadUsers(reader); } } return(users); }