public IHttpActionResult UsedSearch(decimal?usedMinPrice, decimal?usedMaxPrice, string usedMinYear, string usedMaxYear, string usedMakeName, string usedModelName, string usedYear) { var repo = VehicleRepositoryFactory.GetRepository(); try { var parameters = new UsedVehicleSearchParameters() { UsedMinPrice = usedMinPrice, UsedMaxPrice = usedMaxPrice, UsedMinYear = usedMinYear, UsedMaxYear = usedMaxYear, UsedMakeName = usedMakeName, UsedModelName = usedModelName, UsedYear = usedYear }; var result = repo.UsedSearch(parameters); return(Ok(result)); } catch (Exception ex) { return(BadRequest(ex.Message)); } }
public IEnumerable <VehicleShortItem> UsedSearch(UsedVehicleSearchParameters parameters) { List <VehicleShortItem> vehicles = new List <VehicleShortItem>(); using (var cn = new SqlConnection(Settings.GetConnectionString())) { string query = "SELECT TOP 20 VehicleId, BodyStyle, Year, Transmission, Color, Interior, Miles, Type, Vin, Price, Msrp, " + "ImageFileName, MakeName, ModelName FROM Vehicle INNER JOIN Make ON Vehicle.MakeId = Make.MakeId INNER JOIN Model ON " + "Vehicle.ModelId = Model.ModelId WHERE Type='USED' AND 1 = 1 "; SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; if (parameters.UsedMinPrice.HasValue) { query += "AND Price >= @UsedMinPrice "; cmd.Parameters.AddWithValue("@UsedMinPrice", parameters.UsedMinPrice.Value); } if (parameters.UsedMaxPrice.HasValue) { query += "AND Price <= @UsedMaxPrice "; cmd.Parameters.AddWithValue("@UsedMaxPrice", parameters.UsedMaxPrice.Value); } if (!string.IsNullOrEmpty(parameters.UsedMinYear)) { query += "AND Year >= @UsedMinYear "; cmd.Parameters.AddWithValue("@UsedMinYear", parameters.UsedMinYear); } if (!string.IsNullOrEmpty(parameters.UsedMaxYear)) { query += "AND Year <= @UsedMaxYear "; cmd.Parameters.AddWithValue("@UsedMaxYear", parameters.UsedMaxYear); } if (!string.IsNullOrEmpty(parameters.UsedMakeName)) { query += "AND MakeName = @UsedMakeName "; cmd.Parameters.AddWithValue("@UsedMakeName", parameters.UsedMakeName); } if (!string.IsNullOrEmpty(parameters.UsedModelName)) { query += "AND ModelName = @UsedModelName "; cmd.Parameters.AddWithValue("@UsedModelName", parameters.UsedModelName); } if (!string.IsNullOrEmpty(parameters.UsedYear)) { query += "AND Year = @UsedYear "; cmd.Parameters.AddWithValue("@UsedYear", parameters.UsedYear); } cmd.CommandText = query; cn.Open(); using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { VehicleShortItem row = new VehicleShortItem(); row.VehicleId = (int)dr["VehicleId"]; row.BodyStyle = dr["BodyStyle"].ToString(); row.Year = dr["Year"].ToString(); row.Transmission = dr["Transmission"].ToString(); row.Color = dr["Color"].ToString(); row.Interior = dr["Interior"].ToString(); row.Type = dr["Type"].ToString(); row.Vin = dr["Vin"].ToString(); row.Price = (decimal)dr["Price"]; row.Msrp = (decimal)dr["Msrp"]; row.MakeName = dr["MakeName"].ToString(); row.ModelName = dr["ModelName"].ToString(); if (dr["ImageFileName"] != DBNull.Value) { row.ImageFileName = dr["ImageFileName"].ToString(); } vehicles.Add(row); } } } return(vehicles); }