/// <summary> /// Prepare database for operation, ie. create the Backend.db file and Users table within if not already present /// </summary> private void PrepareDb() { try { using var connection = OpenNewConnection(); var command = connection.CreateCommand(); command.CommandText = EmbeddedResourceHelper.GetFileContentsAsString("CreateUsersTable.sql"); command.ExecuteNonQuery(); } catch (Exception ex) { _logger.LogCritical(ex, "Fatal error! Cannot prepare database for operation"); throw; } }
public User InsertNewUser(string email, string passwordHash) { var newUser = new User() { Email = email, PasswordHash = passwordHash }; try { using var connection = OpenNewConnection(); DbCommand command = connection.CreateCommand(); command.CommandText = EmbeddedResourceHelper.GetFileContentsAsString("InsertNewUser.sql"); var param1 = command.CreateParameter(); param1.ParameterName = "Email"; param1.Value = newUser.Email; param1.DbType = System.Data.DbType.String; command.Parameters.Add(param1); var param2 = command.CreateParameter(); param2.ParameterName = "PasswordHash"; param2.Value = newUser.PasswordHash; param2.DbType = System.Data.DbType.String; command.Parameters.Add(param2); using var reader = command.ExecuteReader(); if (reader.Read()) { newUser.Id = reader.GetInt32(0); return(newUser); } else { throw new Exception("Couldn't read primary key."); } } catch (Exception ex) { _logger.LogError(ex, $"Failed to add '{email}' to database."); return(null); } }
public User GetUserWithEmail(string email) { try { using var connection = OpenNewConnection(); DbCommand command = connection.CreateCommand(); command.CommandText = EmbeddedResourceHelper.GetFileContentsAsString("GetUserWithEmail.sql"); var param1 = command.CreateParameter(); param1.ParameterName = "Email"; param1.Value = email; param1.DbType = System.Data.DbType.String; command.Parameters.Add(param1); using var reader = command.ExecuteReader(); if (reader.Read()) { var user = new User { Id = reader.GetInt32(0), Email = reader.GetString(1), PasswordHash = reader.GetString(2) }; return(user); } else { return(null); } } catch (Exception ex) { _logger.LogError(ex, $"Failed to search for '{email}' from database."); return(null); } }