public static List <Vehicle> GetAvailableVehicles() { List <Vehicle> allVehicles = new List <Vehicle>(); NpgsqlCommand command = new NpgsqlCommand(); NpgsqlConnection connection = new NpgsqlConnection(ConfigurationManager.AppSettings["Connection"]); connection.Open(); command.Connection = connection; command.CommandText = $"Select car_id, location_id, modell, brand, hp, price, feature1, feature2, feature3, feature4, notavailable, reserved, in_use, locked from instacar.car where deleted = false and notavailable = false " + $"except " + $"Select c.car_id, c.location_id, c.modell, c.brand, c.hp, c.price, c.feature1, c.feature2, c.feature3, c.feature4, " + $"c.notavailable, c.reserved, c.in_use, c.locked from {TABLE} as c inner join {TABLERENT} as r on r.car_id = c.car_id " + $"where (dateend > current_Timestamp or dateend is null) and c.notavailable = false and c.deleted = false;"; command.Parameters.AddWithValue("t", DateTime.Now); NpgsqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { allVehicles.Add(new Vehicle(connection) { CarId = reader.GetInt64(0), LocationId = reader.GetInt64(1), Modell = reader.IsDBNull(2) ? null : reader.GetString(2), Brand = reader.IsDBNull(3) ? null : reader.GetString(3), HP = reader.IsDBNull(4) ? 0 : reader.GetInt64(4), Price = reader.IsDBNull(5) ? 0 : reader.GetDouble(5), Feature1 = reader.IsDBNull(6) ? 0 : reader.GetInt64(6), Feature2 = reader.IsDBNull(7) ? 0 : reader.GetInt64(7), Feature3 = reader.IsDBNull(8) ? 0 : reader.GetInt64(8), Feature4 = reader.IsDBNull(9) ? 0 : reader.GetInt64(9), NotAvailable = reader.IsDBNull(10) ? true : reader.GetBoolean(10), Reserved = reader.IsDBNull(11) ? null : (DateTime?)reader.GetDateTime(11), InUse = reader.IsDBNull(12) ? true : reader.GetBoolean(12), Locked = reader.IsDBNull(13) ? true : reader.GetBoolean(13) }); } reader.Close(); connection.Close(); foreach (Vehicle v in allVehicles) { v.MainImage = ImageCar.GetMainImageByte(v.CarId); } return(allVehicles); }
//---------------------------------------------------------------------------------------------- //Static //---------------------------------------------------------------------------------------------- #region static public static List <Vehicle> GetAllVehicles() { NpgsqlConnection connection = new NpgsqlConnection(ConfigurationManager.AppSettings["Connection"]); connection.Open(); List <Vehicle> allVehicles = new List <Vehicle>(); Vehicle vehicle = null; NpgsqlCommand command = new NpgsqlCommand(); command.Connection = connection; command.CommandText = $"Select * from {TABLE} where deleted = false;"; NpgsqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { allVehicles.Add( vehicle = new Vehicle(connection) { CarId = reader.GetInt64(0), LocationId = reader.GetInt64(1), Modell = reader.IsDBNull(2) ? null : reader.GetString(2), Brand = reader.IsDBNull(3) ? null : reader.GetString(3), HP = reader.IsDBNull(4) ? 0 : reader.GetInt64(4), Price = reader.IsDBNull(5) ? 0 : reader.GetDouble(5), Feature1 = reader.IsDBNull(6) ? 0 : reader.GetInt64(6), Feature2 = reader.IsDBNull(7) ? 0 : reader.GetInt64(7), Feature3 = reader.IsDBNull(8) ? 0 : reader.GetInt64(8), Feature4 = reader.IsDBNull(9) ? 0 : reader.GetInt64(9), NotAvailable = reader.IsDBNull(10) ? true : reader.GetBoolean(10), Reserved = reader.IsDBNull(11) ? null : (DateTime?)reader.GetDateTime(11), InUse = reader.IsDBNull(12) ? true : reader.GetBoolean(12), Locked = reader.IsDBNull(13) ? true : reader.GetBoolean(13) } ); } reader.Close(); connection.Close(); foreach (Vehicle v in allVehicles) { v.MainImage = ImageCar.GetMainImageByte(v.CarId); } return(allVehicles); }