public void GetAll_ReturnsAllUsers()
{
//Arrange
Guid id = Guid.NewGuid();
string username = fixture.Create <string>();
string firstname = fixture.Create <string>();
string lastname = fixture.Create <string>();
string email = fixture.Create <string>();
DateTime createdDate = fixture.Create <DateTime>();
IDataReader reader = Substitute.For <IDataReader>();
reader.Read().Returns(true, true, true, false);
reader.GetOrdinal("id").Returns(0);
reader.GetOrdinal("username").Returns(1);
reader.GetOrdinal("firstname").Returns(2);
reader.GetOrdinal("lastname").Returns(3);
reader.GetOrdinal("email").Returns(4);
reader.GetOrdinal("created_date").Returns(5);
reader.GetGuid(0).Returns(id);
reader.GetString(1).Returns(username);
reader.GetString(2).Returns(firstname);
reader.GetString(3).Returns(lastname);
reader.GetString(4).Returns(email);
reader.GetDateTime(5).Returns(createdDate);
command.ExecuteReaderAsync().Returns(reader);
repository.SetTransaction(transaction);
//Act
var getTask = repository.GetAll();
getTask.Wait();
var users = getTask.Result;
//Assert
command.CommandText.Should().Be("SELECT id, username, firstname, lastname, email, created_date FROM users.users ORDER BY created_date");
command.Transaction.Should().Be(transaction);
command.Received(1).PrepareAsync();
command.Received(1).ExecuteReaderAsync();
users.Should().NotBeNull();
users.Count().Should().Be(3);
foreach (var user in users)
{
user.Id.Should().Be(id);
user.UserName.Should().Be(username);
user.FirstName.Should().Be(firstname);
user.LastName.Should().Be(lastname);
user.Email.Should().Be(email);
}
}
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);
}
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);
}
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);
}
