Пример #1
0
        internal static PersonEntity Person(Person ent)
        {
            PersonEntity data = new PersonEntity();

            data.personId = ent.Id;
            data.userName = ent.UserName;
            data.emailAddress = ent.EmailAddress;
            data.firstName = ent.FirstName;
            data.lastName = ent.LastName;
            data.passwordHash = ent.PasswordHash;
            data.updateTimestamp = ent.UpdateTimestamp;

            return data;
        }
Пример #2
0
        internal static Person Person(PersonEntity data)
        {
            Person ent = new Person();

            ent.Id = data.personId;
            ent.UserName = data.userName;
            ent.EmailAddress = data.emailAddress;
            ent.FirstName = data.firstName;
            ent.LastName = data.lastName;
            ent.PasswordHash = data.passwordHash;
            ent.UpdateTimestamp = data.updateTimestamp;

            return ent;
        }
Пример #3
0
        public IList<PersonEntity> GetAllPersons(IConnection conn)
        {
            List<PersonEntity> personList = new List<PersonEntity>();

            string sql = "SELECT personId, userName, emailAddress, firstName, lastName, passwordHash FROM DBO.PERSON;";

            var rdr = conn.ExecuteReader(sql);
            while (rdr.Read())
            {
                var person = new PersonEntity()
                {
                    personId = rdr.IsDBNull(rdr.GetOrdinal("personId")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("personId")),
                    userName = rdr.IsDBNull(rdr.GetOrdinal("userName")) ? null : rdr.GetString(rdr.GetOrdinal("userName")),
                    emailAddress = rdr.IsDBNull(rdr.GetOrdinal("emailAddress")) ? null : rdr.GetString(rdr.GetOrdinal("emailAddress")),
                    firstName = rdr.IsDBNull(rdr.GetOrdinal("firstName")) ? null : rdr.GetString(rdr.GetOrdinal("firstName")),
                    lastName = rdr.IsDBNull(rdr.GetOrdinal("lastName")) ? null : rdr.GetString(rdr.GetOrdinal("lastName")),
                    passwordHash = rdr.IsDBNull(rdr.GetOrdinal("passwordHash")) ? null : rdr.GetString(rdr.GetOrdinal("passwordHash"))
                };
                personList.Add(person);
            }
            return personList;
        }
Пример #4
0
        public IList<PersonEntity> GetAllPersonsLike(string partialUserName, IConnection conn)
        {
            List<PersonEntity> personList = new List<PersonEntity>();

            string sql = "SELECT personId, userName, emailAddress, firstName, lastName, passwordHash FROM DBO.PERSON WHERE userName like @partialUserName;";
            List<SqlParameter> prms = new List<SqlParameter>();
            prms.Add(new SqlParameter { ParameterName = "@partialUserName", Value = "%" + partialUserName + "%" });

            var rdr = conn.ExecuteReader(sql,prms);
            while (rdr.Read())
            {
                var person = new PersonEntity()
                {
                    personId = rdr.IsDBNull(rdr.GetOrdinal("personId")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("personId")),
                    userName = rdr.IsDBNull(rdr.GetOrdinal("userName")) ? null : rdr.GetString(rdr.GetOrdinal("userName")),
                    emailAddress = rdr.IsDBNull(rdr.GetOrdinal("emailAddress")) ? null : rdr.GetString(rdr.GetOrdinal("emailAddress")),
                    firstName = rdr.IsDBNull(rdr.GetOrdinal("firstName")) ? null : rdr.GetString(rdr.GetOrdinal("firstName")),
                    lastName = rdr.IsDBNull(rdr.GetOrdinal("lastName")) ? null : rdr.GetString(rdr.GetOrdinal("lastName")),
                    passwordHash = rdr.IsDBNull(rdr.GetOrdinal("passwordHash")) ? null : rdr.GetString(rdr.GetOrdinal("passwordHash"))
                };
                personList.Add(person);
            }
            return personList;
        }
Пример #5
0
        private void CheckPersonForRequiredValues(PersonEntity p, RepositoryUtils.RepositoryAction action)
        {
            List<string> missingFields = new List<string>();

            if (String.IsNullOrWhiteSpace(p.userName)) missingFields.Add("User Name");
            if (String.IsNullOrWhiteSpace(p.emailAddress)) missingFields.Add("Email Address");
            if (String.IsNullOrWhiteSpace(p.firstName)) missingFields.Add("First Name");
            if (String.IsNullOrWhiteSpace(p.lastName)) missingFields.Add("Last Name");
            if (String.IsNullOrWhiteSpace(p.passwordHash)) missingFields.Add("Password");

            if (missingFields.Count > 0)
            {
                throw new Exception(String.Format("Cannot {0} Person: Missing Fields {1}", action.ToString(), String.Join(", ", missingFields.ToArray())));
            }
        }
Пример #6
0
        public void Update(int id, PersonEntity person, IConnection conn)
        {
            CheckPersonForRequiredValues(person, RepositoryUtils.RepositoryAction.Update);

            var contactToUpdate = GetPersonById(id);
            if (contactToUpdate == null)
            {
                throw new Exception("Contact does not exist in database");
            }
            string sql =        @"UPDATE person SET [userName]=@userName,
                                                    [emailAddress]=@emailAddress,
                                                    [firstName]=@firstName,
                                                    [lastName]=@lastName,
                                                    [passwordHash]=@passwordHash,
                                                    [updateTimestamp]=getdate()
                                                    WHERE personId=@Id";
            List<SqlParameter> prms = new List<SqlParameter>();
            prms.Add(new SqlParameter { ParameterName = "@Id", Value = id });
            prms.Add(new SqlParameter { ParameterName = "@userName", Value = person.userName });
            prms.Add(new SqlParameter { ParameterName = "@emailAddress", Value = person.emailAddress });
            prms.Add(new SqlParameter { ParameterName = "@firstName", Value = person.firstName });
            prms.Add(new SqlParameter { ParameterName = "@lastName", Value = person.lastName });
            prms.Add(new SqlParameter { ParameterName = "@passwordHash", Value = person.passwordHash });

            var number = conn.ExecuteNonQuery(sql,prms);

            if (number != 1)
            {
                throw new Exception($"No Contacts were updated with Id: {id}");
            }
        }
Пример #7
0
 public void Update(int id, PersonEntity person)
 {
     using (Connection conn = new Connection())
     {
         Update(id, person, conn);
     }
 }
Пример #8
0
        public int Insert(PersonEntity person, IConnection conn)
        {
            CheckPersonForRequiredValues(person, RepositoryUtils.RepositoryAction.Insert);

            var contactExists = GetPersonByEmail(person.emailAddress);
            if (contactExists != null)
            {
                throw new Exception($"Entity {person.emailAddress} already exists in database!");
            }

            string sql =
                @"INSERT INTO[dbo].[person] (userName, emailAddress, firstName, lastName, passwordHash, updateTimestamp)
                VALUES(@userName, @emailAddress, @firstName, @lastName, @passwordHash, getdate());SELECT CAST(scope_identity() AS int)";
            List<SqlParameter> prms = new List<SqlParameter>();
            prms.Add(new SqlParameter { ParameterName = "@userName", Value = person.userName });
            prms.Add(new SqlParameter { ParameterName = "@emailAddress", Value = person.emailAddress });
            prms.Add(new SqlParameter { ParameterName = "@firstName", Value = person.firstName });
            prms.Add(new SqlParameter { ParameterName = "@lastName", Value = person.lastName });
            prms.Add(new SqlParameter { ParameterName = "@passwordHash", Value = person.passwordHash });

            try
            {
                return int.Parse(conn.ExecuteScalar(sql,prms).ToString());
            }
            catch (Exception)
            {
                throw new Exception($"Entity {person.firstName} {person.lastName} not inserted in database!");
            }
        }
Пример #9
0
 public int Insert(PersonEntity person)
 {
     using (Connection conn = new Connection())
     {
         return Insert(person, conn);
     }
 }
Пример #10
0
        public PersonEntity GetPersonByUserName(string userName, IConnection conn)
        {
            List<PersonEntity> personList = new List<PersonEntity>();

            string sql = "SELECT personId, userName, emailAddress, firstName, lastName, passwordHash, updateTimestamp FROM DBO.PERSON WHERE userName = @userName;";
            List<SqlParameter> prms = new List<SqlParameter>();
            prms.Add(new SqlParameter { ParameterName = "@userName", Value = userName });

            var rdr = conn.ExecuteReader(sql,prms);
            while (rdr.Read())
            {
                var person = new PersonEntity()
                {
                    personId = rdr.IsDBNull(rdr.GetOrdinal("personId")) ? -1 : rdr.GetInt32(rdr.GetOrdinal("personId")),
                    userName = rdr.IsDBNull(rdr.GetOrdinal("userName")) ? null : rdr.GetString(rdr.GetOrdinal("userName")),
                    emailAddress = rdr.IsDBNull(rdr.GetOrdinal("emailAddress")) ? null : rdr.GetString(rdr.GetOrdinal("emailAddress")),
                    firstName = rdr.IsDBNull(rdr.GetOrdinal("firstName")) ? null : rdr.GetString(rdr.GetOrdinal("firstName")),
                    lastName = rdr.IsDBNull(rdr.GetOrdinal("lastName")) ? null : rdr.GetString(rdr.GetOrdinal("lastName")),
                    passwordHash = rdr.IsDBNull(rdr.GetOrdinal("passwordHash")) ? null : rdr.GetString(rdr.GetOrdinal("passwordHash")),
                    updateTimestamp = rdr.IsDBNull(rdr.GetOrdinal("updateTimestamp")) ? new DateTime() : rdr.GetDateTime(rdr.GetOrdinal("updateTimestamp"))
                };
                personList.Add(person);
            }
            return personList.FirstOrDefault();
        }