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; }
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; }
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; }
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; }
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()))); } }
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}"); } }
public void Update(int id, PersonEntity person) { using (Connection conn = new Connection()) { Update(id, person, conn); } }
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!"); } }
public int Insert(PersonEntity person) { using (Connection conn = new Connection()) { return Insert(person, conn); } }
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(); }