private string SetCategoryRange(ref NpgsqlCommand cmd, CarSearchCriteria carSearchCriteria) { string whereClause = ""; if (carSearchCriteria.CategoryFrom != null || carSearchCriteria.CategoryTo != null) { whereClause = " carcategory BETWEEN @mincategory AND @maxcategory "; string minCategory = CarCategory.Empty.DBValue; string maxCategory = CarCategory.Max.DBValue; if (carSearchCriteria.CategoryFrom != null) { minCategory = carSearchCriteria.CategoryFrom.DBValue; } if (carSearchCriteria.CategoryTo != null) { maxCategory = carSearchCriteria.CategoryTo.DBValue; } cmd.Parameters.AddWithValue("mincategory", minCategory); cmd.Parameters.AddWithValue("maxcategory", maxCategory); } return(whereClause); }
private string SetYearRange(ref NpgsqlCommand cmd, CarSearchCriteria carSearchCriteria) { string whereClause = ""; if (carSearchCriteria.YearFrom >= 0 || carSearchCriteria.YearTo >= 0) { whereClause = " caryear BETWEEN @minyear AND @maxyear "; int minYear = 0; int maxYear = int.MaxValue; if (carSearchCriteria.YearFrom >= 0) { minYear = carSearchCriteria.YearFrom; } if (carSearchCriteria.YearTo >= 0) { maxYear = carSearchCriteria.YearTo; } cmd.Parameters.AddWithValue("minyear", minYear); cmd.Parameters.AddWithValue("maxyear", maxYear); } return(whereClause); }
private string SetMaxPowerRange(ref NpgsqlCommand cmd, CarSearchCriteria carSearchCriteria) { string whereClause = ""; if (carSearchCriteria.MaxPowerFrom >= 0 || carSearchCriteria.MaxPowerTo >= 0) { whereClause = " carmaxpower BETWEEN @minmaxpower AND @maxmaxpower "; int minMaxPower = 0; int maxMaxPower = int.MaxValue; if (carSearchCriteria.MaxPowerFrom >= 0) { minMaxPower = carSearchCriteria.MaxPowerFrom; } if (carSearchCriteria.MaxPowerTo >= 0) { maxMaxPower = carSearchCriteria.MaxPowerTo; } cmd.Parameters.AddWithValue("minmaxpower", minMaxPower); cmd.Parameters.AddWithValue("maxmaxpower", maxMaxPower); } return(whereClause); }
/// <summary>Gets the list of cars for search criteria.</summary> /// <param name="carSearchCriteria">The car search criteria.</param> /// <param name="orderedList">If set to <c>true</c> the list will be ordered.</param> /// <returns>The list of cars found.</returns> public List <Car> GetListForSearchCriteria(CarSearchCriteria carSearchCriteria, Boolean orderedList = false) { validation.ValidateSearchCriteria(carSearchCriteria); List <Car> cars = repository.GetListByCriteria(carSearchCriteria, orderedList); return(cars); }
private string SetRegionCondition(ref NpgsqlCommand cmd, CarSearchCriteria carSearchCriteria) { string whereClause = ""; if (carSearchCriteria.RegionDescription != null) { whereClause = " regdescription = @regiondescription "; cmd.Parameters.AddWithValue("regiondescription", carSearchCriteria.RegionDescription); } return(whereClause); }
private string SetManufacturerCondition(ref NpgsqlCommand cmd, CarSearchCriteria carSearchCriteria) { string whereClause = ""; if (carSearchCriteria.ManufacturerName != null) { whereClause = " manname = @manufacturername "; cmd.Parameters.AddWithValue("manufacturername", carSearchCriteria.ManufacturerName); } return(whereClause); }
private string SetDriveTrainCondition(ref NpgsqlCommand cmd, CarSearchCriteria carSearchCriteria) { string whereClause = ""; if (carSearchCriteria.DriveTrain != null) { whereClause = " cardrivetrain = @drivetrain "; cmd.Parameters.AddWithValue("drivetrain", carSearchCriteria.DriveTrain); } return(whereClause); }
private string SetCountryCondition(ref NpgsqlCommand cmd, CarSearchCriteria carSearchCriteria) { string whereClause = ""; if (carSearchCriteria.CountryDescription != null) { whereClause = " coudescription = @countrydescription "; cmd.Parameters.AddWithValue("countrydescription", carSearchCriteria.CountryDescription); } return(whereClause); }
/// <summary>Validates the search criteria.</summary> /// <param name="carSearchCriteria">The car search criteria.</param> public void ValidateSearchCriteria(CarSearchCriteria carSearchCriteria) { if (carSearchCriteria.CategoryFrom != null) { ValidateCategory(carSearchCriteria.CategoryFrom); } if (carSearchCriteria.CategoryTo != null) { ValidateCategory(carSearchCriteria.CategoryTo); } if (carSearchCriteria.DriveTrain != null) { ValidateDriveTrain(carSearchCriteria.DriveTrain); } }
private string AddJoins(CarSearchCriteria carSearchCriteria) { string joinClause = ""; if (carSearchCriteria.ManufacturerName != null || carSearchCriteria.CountryDescription != null || carSearchCriteria.RegionDescription != null) { joinClause += " INNER JOIN manufacturers on cars.carmankey = manufacturers.mankey "; } if (carSearchCriteria.CountryDescription != null || carSearchCriteria.RegionDescription != null) { joinClause += " INNER JOIN countries on manufacturers.mancoukey = countries.coukey "; } if (carSearchCriteria.RegionDescription != null) { joinClause += " INNER JOIN regions on countries.couregkey = regions.regkey "; } return(joinClause); }
/// <summary>Gets the list of cars limited by criteria.</summary> /// <param name="carSearchCriteria">The car search criteria.</param> /// <param name="orderedList">If set to <c>true</c> the list will be ordered.</param> /// <returns>The list of cars found.</returns> public List <Car> GetListByCriteria(CarSearchCriteria carSearchCriteria, Boolean orderedList = false) { List <Car> cars = new List <Car>(); string whereClause = ""; var cmd = new NpgsqlCommand(); cmd.Connection = npgsqlConnection; cmd.CommandText = "SELECT * FROM cars "; cmd.CommandText += AddJoins(carSearchCriteria); whereClause += SetCategoryRange(ref cmd, carSearchCriteria); whereClause += AddWhereAnd(whereClause.Length, SetYearRange(ref cmd, carSearchCriteria)); whereClause += AddWhereAnd(whereClause.Length, SetMaxPowerRange(ref cmd, carSearchCriteria)); whereClause += AddWhereAnd(whereClause.Length, SetDriveTrainCondition(ref cmd, carSearchCriteria)); whereClause += AddWhereAnd(whereClause.Length, SetManufacturerCondition(ref cmd, carSearchCriteria)); whereClause += AddWhereAnd(whereClause.Length, SetCountryCondition(ref cmd, carSearchCriteria)); whereClause += AddWhereAnd(whereClause.Length, SetRegionCondition(ref cmd, carSearchCriteria)); if (!String.IsNullOrWhiteSpace(whereClause)) { cmd.CommandText += " WHERE " + whereClause; } if (orderedList) { if (String.IsNullOrWhiteSpace(getListOrderByField)) { getListOrderByField = idField; } cmd.CommandText += " ORDER BY " + getListOrderByField; } else { cmd.CommandText += " ORDER BY " + idField; } cmd.Prepare(); NpgsqlDataReader dataReader = cmd.ExecuteReader(); while (dataReader.Read()) { Car car = RecordToEntity(dataReader); cars.Add(car); } dataReader.Close(); cmd.Dispose(); return(cars); }