//This method asks the database for the journey that a certain user reserved. public static List <Journey> GetReservedJourneyList(string userEmail) { CheckDataBaseConnection(); connection.Open(); NpgsqlCommand comm = new NpgsqlCommand("SELECT * FROM reserve as re, utilisateur as ut, trajet as tr WHERE ut.email = (@email) AND ut.email = re.email AND re.eid = tr.eid;", connection); comm.Parameters.AddWithValue("email", userEmail); NpgsqlDataReader result = comm.ExecuteReader(); List <Journey> output = new List <Journey>(); while (result.Read()) { Journey j = new Journey((int)result["eid"], (string)result["adressedep"], (string)result["villedep"], (string)result["adressearr"], (string)result["villearr"], (string)result["hdep"], (string)result["harr"], (int)result["km"], (int)result["nbplaces"], (string)result["comment"], (bool)result["pets"], (bool)result["smoke"], (bool)result["music"], (bool)result["speak"]); output.Add(j); } connection.Close(); return(output); }
//This method put in the database a new journey, and link it in the table propose with the user proposing it. public static void ProposeNewJourney(string userEmail, Journey j) { CheckDataBaseConnection(); connection.Open(); NpgsqlCommand pullData = new NpgsqlCommand("SELECT MAX(eid) FROM trajet;", connection); NpgsqlDataReader result = pullData.ExecuteReader(); if (result.HasRows) { result.Read(); j.Eid = result.GetInt32(0) + 1; } else { j.Eid = 1; } connection.Close(); //Here we can see that the connection is checked and opened multiple times. It is done so to avoid two request being sent at once CheckDataBaseConnection(); connection.Open(); NpgsqlCommand comm = new NpgsqlCommand("INSERT INTO trajet VALUES ((@eid), (@adDep), (@vilDep), (@adArr), (@vilArr), (@hdep), (@harr), (@km), (@pets), (@smoke), (@music), (@talk), (@comm), (@nbPl));", connection); comm.Parameters.AddWithValue("eid", j.Eid); comm.Parameters.AddWithValue("km", j.Km); comm.Parameters.AddWithValue("pets", j.Pets); comm.Parameters.AddWithValue("smoke", j.Smoke); comm.Parameters.AddWithValue("comm", j.Comm); comm.Parameters.AddWithValue("adDep", j.AdressDep); comm.Parameters.AddWithValue("vilDep", j.VilleDep); comm.Parameters.AddWithValue("harr", j.Harr); comm.Parameters.AddWithValue("music", j.Music); comm.Parameters.AddWithValue("nbPl", j.NbPlaces); comm.Parameters.AddWithValue("adArr", j.AdresseArr); comm.Parameters.AddWithValue("vilArr", j.VilleArr); comm.Parameters.AddWithValue("hdep", j.Hdep); comm.Parameters.AddWithValue("talk", j.Talk); comm.ExecuteReader(); connection.Close(); CheckDataBaseConnection(); connection.Open(); NpgsqlCommand comm2 = new NpgsqlCommand("INSERT INTO propose VALUES ((@email), (@eid));", connection); comm2.Parameters.AddWithValue("email", userEmail); comm2.Parameters.AddWithValue("eid", j.Eid); comm2.ExecuteReader(); connection.Close(); }