public IHttpActionResult SalesSearch(decimal?salesMinPrice, decimal?salesMaxPrice, string salesMinYear, string salesMaxYear, string salesMakeName, string salesModelName, string salesYear) { var repo = VehicleRepositoryFactory.GetRepository(); try { var parameters = new SalesVehicleSearchParameters() { SalesMinPrice = salesMinPrice, SalesMaxPrice = salesMaxPrice, SalesMinYear = salesMinYear, SalesMaxYear = salesMaxYear, SalesMakeName = salesMakeName, SalesModelName = salesModelName, SalesYear = salesYear }; var result = repo.SalesSearch(parameters); return(Ok(result)); } catch (Exception ex) { return(BadRequest(ex.Message)); } }
public IEnumerable <VehicleShortItem> SalesSearch(SalesVehicleSearchParameters 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, " + "IsSold, ImageFileName, MakeName, ModelName FROM Vehicle INNER JOIN Make ON Vehicle.MakeId = Make.MakeId INNER JOIN Model ON " + "Vehicle.ModelId = Model.ModelId WHERE IsSold=0 AND 1 = 1 "; SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; if (parameters.SalesMinPrice.HasValue) { query += "AND Price >= @SalesMinPrice "; cmd.Parameters.AddWithValue("@SalesMinPrice", parameters.SalesMinPrice.Value); } if (parameters.SalesMaxPrice.HasValue) { query += "AND Price <= @SalesMaxPrice "; cmd.Parameters.AddWithValue("@SalesMaxPrice", parameters.SalesMaxPrice.Value); } if (!string.IsNullOrEmpty(parameters.SalesMinYear)) { query += "AND Year >= @SalesMinYear "; cmd.Parameters.AddWithValue("@SalesMinYear", parameters.SalesMinYear); } if (!string.IsNullOrEmpty(parameters.SalesMaxYear)) { query += "AND Year <= @SalesMaxYear "; cmd.Parameters.AddWithValue("@SalesMaxYear", parameters.SalesMaxYear); } if (!string.IsNullOrEmpty(parameters.SalesMakeName)) { query += "AND MakeName = @SalesMakeName "; cmd.Parameters.AddWithValue("@SalesMakeName", parameters.SalesMakeName); } if (!string.IsNullOrEmpty(parameters.SalesModelName)) { query += "AND ModelName = @SalesModelName "; cmd.Parameters.AddWithValue("@SalesModelName", parameters.SalesModelName); } if (!string.IsNullOrEmpty(parameters.SalesYear)) { query += "AND Year = @SalesYear "; cmd.Parameters.AddWithValue("@SalesYear", parameters.SalesYear); } 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.IsSold = (bool)dr["IsSold"]; 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); }