예제 #1
0
        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);
        }