public static void SearchByBrandAndModel() { //Ask the brand Console.WriteLine("What brand you want to search?"); string brandToSearch = Console.ReadLine(); brandToSearch = CarAdder.FirstLetterToUpperCase(brandToSearch); //Ask the model Console.WriteLine("What model you want to search? "); string modelToSearch = Console.ReadLine(); modelToSearch = modelToSearch.ToUpper(); using (searchCarBrandAndModel = new NpgsqlCommand($"SELECT cars.platenumber, cars.brand, cars.model, cars.year, cars.price, fuel.carpower, colour.colour FROM cars LEFT JOIN colour on colour.colour_id = cars.colorid LEFT JOIN fuel on fuel.fuelid = cars.fuelid WHERE cars.brand = '{brandToSearch}' AND cars.model = '{modelToSearch}'", connection)) { searchCarBrandAndModel.Prepare(); try { using (NpgsqlDataReader dataReader = searchCarBrandAndModel.ExecuteReader()) while (dataReader.Read()) { Console.WriteLine($" {dataReader.GetString(0)}, {dataReader.GetString(1)}, {dataReader.GetString(2)}, {dataReader.GetInt16(3)}, {dataReader.GetInt16(4)}, {dataReader.GetString(5)}, { dataReader.GetString(6)}"); } } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { connection.Close(); connection.Dispose(); } } }
public static void DeleteCar() { //Ask platenumber of the car which user wants to delete string carToDelete = CarAdder.CarPlateNumber(); //Execute the query with the user input using (deleteCar = new NpgsqlCommand($"DELETE FROM cars WHERE platenumber = '{carToDelete}';", connection)) { deleteCar.Prepare(); try { using (NpgsqlDataReader dataReader = deleteCar.ExecuteReader()); Console.WriteLine("Car deleted from the database"); } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { connection.Close(); connection.Dispose(); } } }
static void Main() { //Dictionary for cars, use plate number as key //Values are the other parameters of the car Dictionary <string, string> carsDictionary = new Dictionary <string, string>(); //Main menu Console.WriteLine("What do you want to do?"); Console.WriteLine("1 - Add car"); Console.WriteLine("2 - Search car"); Console.WriteLine("3 - Delete car"); Console.WriteLine("4 - Quit"); int input = int.Parse(Console.ReadLine()); while (true) { switch (input) { //Add Car case 1: string platenumber = CarAdder.CarPlateNumber(); string carbrand = CarAdder.CarBrand(); string carmodel = CarAdder.CarModel(); int caryear = CarAdder.CarYear(); int fueltype = CarAdder.FuelType(); int price = CarAdder.CarPrice(); int color = CarAdder.CarColor(); //If specific car exists in the database with same platenumber, we print specific error message on screen //Else we add car to the Dictionary and to the database //This is just an example which only works on runtime if (carsDictionary.ContainsKey(platenumber)) { Console.WriteLine("Car with specific plate number already exists"); } else { //Add to dictionary carsDictionary.Add(platenumber, carbrand + " " + carmodel + " " + caryear + " " + fueltype + " " + price + " " + color); //Create new instance Car car = new Car(platenumber, carbrand, carmodel, caryear, fueltype, price, color); //Add to the database SqlQuery.Connection(); SqlQuery.AddCarToTheDb(car); } Main(); break; //Search Car case 2: CarSearcher.CarSearchMenu(); break; //Delete Car case 3: SqlQuery.Connection(); SqlQuery.DeleteCar(); Main(); break; case 4: Environment.Exit(1); break; } } }