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);
}