public IEnumerable <VehicleShortItem> GetFeatured() { List <VehicleShortItem> vehicles = new List <VehicleShortItem>(); using (var cn = new SqlConnection(Settings.GetConnectionString())) { SqlCommand cmd = new SqlCommand("FeaturedVehicles", cn); cmd.CommandType = CommandType.StoredProcedure; cn.Open(); using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { VehicleShortItem row = new VehicleShortItem(); row.VehicleID = (int)dr["VehicleID"]; row.ModelID = (int)dr["ModelID"]; row.MakeID = (int)dr["MakeID"]; row.MakeName = dr["MaKeName"].ToString(); row.ModelName = dr["ModelName"].ToString(); row.Year = dr["Year"].ToString(); row.SalesPrice = (decimal)dr["SalesPrice"]; row.Picture = dr["Picture"].ToString(); row.Featured = (bool)dr["Featured"]; vehicles.Add(row); } } } return(vehicles); }
public IEnumerable <VehicleShortItem> AdminSearch(AdminVehicleSearchParameters 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.AdminMinPrice.HasValue) { query += "AND Price >= @AdminMinPrice "; cmd.Parameters.AddWithValue("@AdminMinPrice", parameters.AdminMinPrice.Value); } if (parameters.AdminMaxPrice.HasValue) { query += "AND Price <= @AdminMaxPrice "; cmd.Parameters.AddWithValue("@AdminMaxPrice", parameters.AdminMaxPrice.Value); } if (!string.IsNullOrEmpty(parameters.AdminMinYear)) { query += "AND Year >= @AdminMinYear "; cmd.Parameters.AddWithValue("@AdminMinYear", parameters.AdminMinYear); } if (!string.IsNullOrEmpty(parameters.AdminMaxYear)) { query += "AND Year <= @AdminMaxYear "; cmd.Parameters.AddWithValue("@AdminMaxYear", parameters.AdminMaxYear); } if (!string.IsNullOrEmpty(parameters.AdminMakeName)) { query += "AND MakeName = @AdminMakeName "; cmd.Parameters.AddWithValue("@AdminMakeName", parameters.AdminMakeName); } if (!string.IsNullOrEmpty(parameters.AdminModelName)) { query += "AND ModelName = @AdminModelName "; cmd.Parameters.AddWithValue("@AdminModelName", parameters.AdminModelName); } if (!string.IsNullOrEmpty(parameters.AdminYear)) { query += "AND Year = @AdminYear "; cmd.Parameters.AddWithValue("@AdminYear", parameters.AdminYear); } 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); }