public List <ContactModel> ReadAll() { if (!DbBaseEntity.IsIdValid(Db.ActiveUser.Id)) { return(new List <ContactModel>()); } List <ContactModel> allContacts = new List <ContactModel>(); using (IDbConnection con = SqliteHelper.CreateConnection()) { using (IDbCommand cmd = con.CreateCommand()) { cmd.CommandText = "SELECT * FROM Contact WHERE Owner=@pOwner"; cmd.Parameters.Add(new SqliteParameter("@pOwner", Db.ActiveUser.Id)); con.Open(); using (IDataReader r = cmd.ExecuteReader()) { while (r.Read()) { allContacts.Add(MapContact(r)); } } } con.Close(); } return(allContacts); }
/// <summary> /// Note that this method will also set the correct Id in the contact itself /// </summary> public int Create(ContactModel contact) { CheckOperationAllowed(contact); using (IDbConnection con = SqliteHelper.CreateConnection()) { using (IDbCommand cmd = con.CreateCommand()) { #region Create the command text & parameters dynamically List <FieldInfo> allFields = contact.GetAllFields(); StringBuilder cmdText = new StringBuilder("INSERT INTO Contact VALUES(@pId, "); allFields.ForEach(f => { cmdText.Append(string.Format("@p{0}, ", f.Name)); cmd.Parameters.Add(new SqliteParameter(string.Format("@p{0}", f.Name), f.GetValue(contact))); }); cmdText.Length -= 2; // Remove the last ", " cmdText.Append(")"); cmd.CommandText = cmdText.ToString(); #endregion cmd.Parameters.Add(new SqliteParameter("@pId", null)); SqliteHelper.ExecuteNonQuery(con, cmd); } using (IDbCommand cmd = con.CreateCommand()) { con.Open(); cmd.CommandText = "SELECT Id FROM Contact ORDER BY Id DESC LIMIT 1"; int id = Convert.ToInt32(cmd.ExecuteScalar()); contact.SetId(id); return(id); } } }
/// <summary> /// Note that the user.Password should already have been hashed. /// </summary> public bool IsLoginValid(UserModel user) { if (!EmailExists(user.Email)) { user.SetId(DbBaseEntity.InvalidId); return(false); // Note: This is efficient but hackers could measure the time it takes to return an invalid e-mail address. However, for this project such security measures are rather overkill. } using (IDbConnection con = SqliteHelper.CreateConnection()) using (IDbCommand cmd = con.CreateCommand()) { cmd.CommandText = "SELECT Id FROM User WHERE Email=@pEmail AND Password=@pPassword"; cmd.Parameters.Add(new SqliteParameter("@pEmail", user.Email)); cmd.Parameters.Add(new SqliteParameter("@pPassword", user.Password)); object idFromDb = Convert.ToInt32(SqliteHelper.ExecuteScalar(con, cmd)); if (idFromDb == null) { user.SetId(DbBaseEntity.InvalidId); return(false); } else { user.SetId(Convert.ToInt32(idFromDb)); return(DbBaseEntity.IsIdValid(user.Id)); } } }
/// <param name="user">Note that it assumes that the user.Password has been hashed or it will most likely return false.</param> /// <param name="errorWordId">null if no error occured (or none was caught); otherwise returns the translation key for the error.</param> /// <returns>If the user was created the Id of the user; otherwise returns DbBaseEntity.InvalidId.</returns> public int CreateUser(UserModel user, out string errorWordId) { errorWordId = null; if (!EmailExists(user.Email)) { using (IDbConnection con = SqliteHelper.CreateConnection()) { using (IDbCommand cmd = con.CreateCommand()) { cmd.CommandText = "INSERT INTO User VALUES(@pId, @pEmail, @pPassword)"; cmd.Parameters.Add(new SqliteParameter("@pId", null)); cmd.Parameters.Add(new SqliteParameter("@pEmail", user.Email.ToLower())); cmd.Parameters.Add(new SqliteParameter("@pPassword", user.Password)); SqliteHelper.ExecuteNonQuery(con, cmd); } using (IDbCommand cmd = con.CreateCommand()) { con.Open(); cmd.CommandText = "SELECT Id FROM User ORDER BY Id DESC LIMIT 1"; int id = Convert.ToInt32(cmd.ExecuteScalar()); user.SetId(id); return(id); } } } else { errorWordId = "EmailAlreadyExists"; } return(DbBaseEntity.InvalidId); }
public void DeleteUserByEmail(string email) { using (IDbConnection con = SqliteHelper.CreateConnection()) using (IDbCommand cmd = con.CreateCommand()) { cmd.CommandText = "DELETE FROM User WHERE Email=@pEmail"; cmd.Parameters.Add(new SqliteParameter("@pEmail", email)); SqliteHelper.ExecuteNonQuery(con, cmd); } }
public void Delete(ContactModel contact) { CheckOperationAllowed(contact); if (contact.Id < 0) { throw new Exception(string.Format("Received an invalid Id. Got: {0}.", contact.Id)); } using (IDbConnection con = SqliteHelper.CreateConnection()) using (IDbCommand cmd = con.CreateCommand()) { cmd.CommandText = "DELETE FROM Contact WHERE Id=@pId"; cmd.Parameters.Add(new SqliteParameter("@pId", contact.Id)); } }
public bool EmailExists(string email) { email = email.ToLower(); using (IDbConnection con = SqliteHelper.CreateConnection()) using (IDbCommand cmd = con.CreateCommand()) { cmd.CommandText = "SELECT COUNT(*) FROM User WHERE Email=@pEmail"; cmd.Parameters.Add(new SqliteParameter("@pEmail", email)); con.Open(); int result = Convert.ToInt32(cmd.ExecuteScalar()); con.Close(); return(result == 1); } }
public void Update(ContactModel contact) { CheckOperationAllowed(contact); using (IDbConnection con = SqliteHelper.CreateConnection()) using (IDbCommand cmd = con.CreateCommand()) { List <FieldInfo> allFields = contact.GetAllFields(); #region Create the command text & parameters dynamically StringBuilder cmdText = new StringBuilder("UPDATE Contact SET "); allFields.ForEach(f => { cmdText.Append(string.Format("{0}=@p{0}, ", f.Name)); cmd.Parameters.Add(new SqliteParameter(string.Format("@p{0}", f.Name), f.GetValue(contact))); }); cmdText.Length -= 2; // Remove the last ", " cmdText.Append(" WHERE ID=@pId AND Owner=@pOwner"); cmd.CommandText = cmdText.ToString(); cmd.Parameters.Add(new SqliteParameter("@pId", contact.Id)); #endregion SqliteHelper.ExecuteNonQuery(con, cmd); } }