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);
}
}