示例#1
0
        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);
        }
示例#2
0
        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();
            }
        }
示例#3
0
        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);
        }
示例#4
0
        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);
        }
示例#5
0
        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);
        }
示例#6
0
        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);
        }
示例#7
0
        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);
        }
示例#8
0
        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();
            }
        }
示例#9
0
        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);
        }
示例#10
0
        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);
        }