public IEnumerable <CarDetailsItem> GetDetails()
{
List <CarDetailsItem> cars = new List <CarDetailsItem>();
using (var cn = new SqlConnection(Settings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("CarSelectDetails", cn);
cmd.CommandType = CommandType.StoredProcedure;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
CarDetailsItem row = new CarDetailsItem();
row.CarId = (int)dr["CarId"];
row.CarMakeId = (int)dr["CarMakeId"];
row.CarModelId = (int)dr["CarModelId"];
row.CarMakeName = dr["CarMakeName"].ToString();
row.CarModelName = dr["CarModelName"].ToString();
row.CarYear = (int)dr["CarYear"];
row.BodyStyle = dr["BodyStyle"].ToString();
row.InteriorColor = dr["InteriorColor"].ToString();
row.VinNumber = dr["VinNumber"].ToString();
row.Transmission = dr["Transmission"].ToString();
row.CarColor = dr["CarColor"].ToString();
row.Mileage = (int)dr["Mileage"];
row.CarPrice = (decimal)dr["CarPrice"];
row.IsSold = (bool)dr["IsSold"];
if (dr["ImageFileName"] != DBNull.Value)
{
row.ImageFileName = dr["ImageFileName"].ToString();
}
if (dr["CarDescription"] != DBNull.Value)
{
row.CarDescription = dr["CarDescription"].ToString();
}
if (dr["CarSalePrice"] != DBNull.Value)
{
row.CarSalePrice = (decimal)dr["CarSalePrice"];
}
cars.Add(row);
}
}
}
return(cars);
}
public IEnumerable <CarDetailsItem> SearchUsedInventory(CarSearchParameters parameters)
{
List <CarDetailsItem> cars = new List <CarDetailsItem>();
using (var cn = new SqlConnection(Settings.GetConnectionString()))
{
string query = "SELECT TOP 20 CarId, CarMakeName, CarModelName, BodyStyle, VinNumber, CarYear, Transmission, " +
"CarColor, InteriorColor, Mileage, CarPrice, CarSalePrice, ImageFileName " +
"FROM Car c INNER JOIN CarModel cd ON c.CarModelId = cd.CarModelId INNER JOIN CarMake cm ON cd.CarMakeId = cm.CarMakeId " +
"WHERE 1 = 1 AND CategoryId = 1 ";
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
if (parameters.MinPrice.HasValue)
{
query += "AND ((CarSalePrice >= @MinPrice) OR (CarPrice >= @MinPrice)) ";
cmd.Parameters.AddWithValue("@MinPrice", parameters.MinPrice.Value);
}
if (parameters.MaxPrice.HasValue)
{
query += "AND ((CarPrice <= @MaxPrice) OR (CarSalePrice <= @MaxPrice)) ";
cmd.Parameters.AddWithValue("@MaxPrice", parameters.MaxPrice.Value);
}
if (parameters.MinYear.HasValue)
{
query += "AND CarYear >= @MinYear ";
cmd.Parameters.AddWithValue("@MinYear", parameters.MinYear.Value);
}
if (parameters.MaxYear.HasValue)
{
query += "AND CarYear <= @MaxYear ";
cmd.Parameters.AddWithValue("@MaxYear", parameters.MaxYear.Value);
}
if (!string.IsNullOrEmpty(parameters.CarMakeName))
{
query += "AND CarMakeName LIKE @CarMakeName ";
cmd.Parameters.AddWithValue("@CarMakeName", parameters.CarMakeName + '%');
}
if (!string.IsNullOrEmpty(parameters.CarModelName))
{
query += "OR CarModelName LIKE @CarModelName ";
cmd.Parameters.AddWithValue("@CarModelName", parameters.CarModelName + '%');
}
if (!string.IsNullOrEmpty(parameters.CarYear.ToString()))
{
query += "OR CarYear LIKE @CarYear ";
cmd.Parameters.AddWithValue("@CarYear", parameters.CarYear + '%');
}
query += "ORDER BY CarPrice DESC";
cmd.CommandText = query;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
CarDetailsItem row = new CarDetailsItem();
row.CarId = (int)dr["CarId"];
row.CarYear = (int)dr["CarYear"];
row.CarMakeName = dr["CarMakeName"].ToString();
row.CarModelName = dr["CarModelName"].ToString();
row.BodyStyle = dr["BodyStyle"].ToString();
row.InteriorColor = dr["InteriorColor"].ToString();
row.VinNumber = dr["VinNumber"].ToString();
row.Transmission = dr["Transmission"].ToString();
row.CarColor = dr["CarColor"].ToString();
row.Mileage = (int)dr["Mileage"];
row.CarPrice = (decimal)dr["CarPrice"];
if (dr["CarSalePrice"] != DBNull.Value)
{
row.CarSalePrice = (decimal)dr["CarSalePrice"];
}
if (dr["ImageFileName"] != DBNull.Value)
{
row.ImageFileName = dr["ImageFileName"].ToString();
}
cars.Add(row);
}
}
}
return(cars);
}
