Exemplo n.º 1
0
        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);
        }