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);
}
