public async Task <IEnumerable <User> > GetAll(int skip, int limit)
{
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 LIMIT :p_limit OFFSET :p_offset";
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);
}
private async Task Insert(User user)
{
using (IPostgresCommand command = Connection.CreateCommand())
{
command.CommandText = @"
INSERT INTO users.users (
id,
username,
firstname,
lastname,
email,
created_date
) values (
:p_id,
:p_username,
:p_firstname,
:p_lastname,
:p_email,
:p_created_date
)
";
AddParameter(command, DbType.Guid, ParameterDirection.Input, "p_id", user.Id);
AddParameter(command, DbType.String, ParameterDirection.Input, "p_username", user.UserName);
AddParameter(command, DbType.String, ParameterDirection.Input, "p_firstname", user.FirstName);
AddParameter(command, DbType.String, ParameterDirection.Input, "p_lastname", user.LastName);
AddParameter(command, DbType.String, ParameterDirection.Input, "p_email", user.Email);
AddParameter(command, DbType.DateTime, ParameterDirection.Input, "p_created_date", user.CreatedDate);
await command.PrepareAsync();
await command.ExecuteNonQueryAsync();
}
}
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 void AddParameter(IPostgresCommand command, DbType dbType, ParameterDirection direction, string name, object value)
{
IPostgresParameter param = command.CreateParameter();
param.DbType = dbType;
param.Direction = direction;
param.ParameterName = name;
param.Value = value;
command.Parameters.Add(param);
}
public PostgresReadModelUserRepositoryTests()
{
fixture = new Fixture();
parameterCollection = Substitute.For <IPostgresParameterCollection>();
command = Substitute.For <IPostgresCommand>();
transaction = Substitute.For <IPostgresTransaction>();
connection = Substitute.For <IPostgresConnection>();
command.CreateParameter().Returns(args => Substitute.For <IPostgresParameter>());
command.Parameters.Returns(parameterCollection);
connection.CreateCommand().Returns(command);
connection.BeginTransaction().Returns(transaction);
repository = new PostgresReadModelUserRepository(connection);
}
private void AddSetClause(StringBuilder builder, IPostgresCommand command, DbType dbType, string columnName, object value)
{
var parameterName = $"p_{columnName}";
if (!builder.ToString().Contains("SET"))
{
builder.Append("SET ");
}
if (command.Parameters.Any())
{
builder.Append(", ");
}
builder.Append($"{columnName} = :{parameterName}");
AddParameter(command, dbType, ParameterDirection.Input, parameterName, value);
}
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);
}