public List <CarDetailed> GetCarsByYear(int startyear, int?finishyear)
{
List <CarDetailed> Cars = new List <CarDetailed>();
using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("GetByYearRange", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Minyear", startyear);
if (finishyear != null)
{
cmd.Parameters.AddWithValue("@Maxyear", finishyear);
}
if (finishyear == null)
{
int currentyear = int.Parse(DateTime.Now.Year.ToString());
cmd.Parameters.AddWithValue("@Maxyear", (currentyear + 5));
}
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
CarDetailed current = new CarDetailed();
current.CarID = (int)dr["CarID"];
current.ModelName = dr["ModelName"].ToString();
current.ModelID = (int)dr["ModelID"];
current.MakeName = dr["MakeName"].ToString();
current.MakeID = (int)dr["MakeID"];
current.IsNew = (bool)dr["Isnew"];
current.ModelYear = (int)dr["ModelYear"];
current.TransmissionID = (int)dr["TransmissionID"];
current.TransmissionName = dr["TransmissionName"].ToString();
current.Color = (int)dr["color"];
current.ColorName = dr["Colorname"].ToString();
current.Interior = (int)dr["Interior"];
current.InteriorDescription = dr["InteriorDescription"].ToString();
current.Mileage = (int)dr["Mileage"];
current.VIN = dr["VIN"].ToString();
current.MSRP = (decimal)dr["MSRP"];
current.Saleprice = (decimal)dr["Saleprice"];
current.CarDescription = dr["CarDescription"].ToString();
current.ImageFileName = dr["ImageFileName"].ToString();
current.AddedBy = dr["Addedby"].ToString();
string cdate = dr["CreatedDate"].ToString();
current.CreatedDate = DateTime.Parse(cdate);
current.IsSold = (bool)dr["IsSold"];
current.IsFeatured = (bool)dr["IsFeatured"];
current.StyleID = (int)dr["StyleID"];
current.BodyStyle = dr["StyleName"].ToString();
Cars.Add(current);
}
}
}
return(Cars);
}
public List <CarDetailed> GetCarsbyPrice(decimal min, decimal?max)
{
CarRepoADO repo = new CarRepoADO();
if (max == null || max == 0)
{
max = repo.GetAllCars().Max(c => c.Saleprice) + 1000;
}
List <CarDetailed> Cars = new List <CarDetailed>();
using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("SearchByPrices", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@min", min);
cmd.Parameters.AddWithValue("@max", max);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
CarDetailed current = new CarDetailed();
current.CarID = (int)dr["CarID"];
current.ModelName = dr["ModelName"].ToString();
current.ModelID = (int)dr["ModelID"];
current.MakeName = dr["MakeName"].ToString();
current.MakeID = (int)dr["MakeID"];
current.IsNew = (bool)dr["Isnew"];
current.ModelYear = (int)dr["ModelYear"];
current.TransmissionID = (int)dr["TransmissionID"];
current.TransmissionName = dr["TransmissionName"].ToString();
current.Color = (int)dr["color"];
current.ColorName = dr["Colorname"].ToString();
current.Interior = (int)dr["Interior"];
current.InteriorDescription = dr["InteriorDescription"].ToString();
current.Mileage = (int)dr["Mileage"];
current.VIN = dr["VIN"].ToString();
current.MSRP = (decimal)dr["MSRP"];
current.Saleprice = (decimal)dr["Saleprice"];
current.CarDescription = dr["CarDescription"].ToString();
current.ImageFileName = dr["ImageFileName"].ToString();
current.AddedBy = dr["Addedby"].ToString();
string cdate = dr["CreatedDate"].ToString();
current.CreatedDate = DateTime.Parse(cdate);
current.IsSold = (bool)dr["IsSold"];
current.IsFeatured = (bool)dr["IsFeatured"];
current.StyleID = (int)dr["StyleID"];
current.BodyStyle = dr["StyleName"].ToString();
Cars.Add(current);
}
}
}
return(Cars);
}
public List <CarDetailed> GetCarsByQuickSearch(string searchterm)//not by year will need split in controller
{
List <CarDetailed> Cars = new List <CarDetailed>();
using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("GetCarsByQuickSearch", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@searchstring", searchterm);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
CarDetailed current = new CarDetailed();
current.CarID = (int)dr["CarID"];
current.ModelName = dr["ModelName"].ToString();
current.ModelID = (int)dr["ModelID"];
current.MakeName = dr["MakeName"].ToString();
current.MakeID = (int)dr["MakeID"];
current.IsNew = (bool)dr["Isnew"];
current.ModelYear = (int)dr["ModelYear"];
current.TransmissionID = (int)dr["TransmissionID"];
current.TransmissionName = dr["TransmissionName"].ToString();
current.Color = (int)dr["color"];
current.ColorName = dr["Colorname"].ToString();
current.Interior = (int)dr["Interior"];
current.InteriorDescription = dr["InteriorDescription"].ToString();
current.Mileage = (int)dr["Mileage"];
current.VIN = dr["VIN"].ToString();
current.MSRP = (decimal)dr["MSRP"];
current.Saleprice = (decimal)dr["Saleprice"];
current.CarDescription = dr["CarDescription"].ToString();
current.ImageFileName = dr["ImageFileName"].ToString();
current.AddedBy = dr["Addedby"].ToString();
string cdate = dr["CreatedDate"].ToString();
current.CreatedDate = DateTime.Parse(cdate);
current.IsSold = (bool)dr["IsSold"];
current.IsFeatured = (bool)dr["IsFeatured"];
current.StyleID = (int)dr["StyleID"];
current.BodyStyle = dr["StyleName"].ToString();
Cars.Add(current);
}
}
}
return(Cars);
}
public CarDetailed GetCarByID(int ID)
{
CarDetailed current = new CarDetailed();
using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("GetCarByID", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@CarID", ID);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr.Read())
{
current.CarID = (int)dr["CarID"];
current.ModelName = dr["ModelName"].ToString();
current.ModelID = (int)dr["ModelID"];
current.MakeName = dr["MakeName"].ToString();
current.MakeID = (int)dr["MakeID"];
current.IsNew = (bool)dr["Isnew"];
current.ModelYear = (int)dr["ModelYear"];
current.TransmissionID = (int)dr["TransmissionID"];
current.TransmissionName = dr["TransmissionName"].ToString();
current.Color = (int)dr["color"];
current.ColorName = dr["Colorname"].ToString();
current.Interior = (int)dr["Interior"];
current.InteriorDescription = dr["InteriorDescription"].ToString();
current.Mileage = (int)dr["Mileage"];
current.VIN = dr["VIN"].ToString();
current.MSRP = (decimal)dr["MSRP"];
current.Saleprice = (decimal)dr["Saleprice"];
current.CarDescription = dr["CarDescription"].ToString();
current.ImageFileName = dr["ImageFileName"].ToString();
current.AddedBy = dr["Addedby"].ToString();
string cdate = dr["CreatedDate"].ToString();
current.CreatedDate = DateTime.Parse(cdate);
current.IsSold = (bool)dr["IsSold"];
current.IsFeatured = (bool)dr["IsFeatured"];
current.StyleID = (int)dr["StyleID"];
current.BodyStyle = dr["StyleName"].ToString();
}
}
}
return(current);
}
public void AddCar(CarDetailed ToAdd)
{
using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("AddCar", cn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter Param = new SqlParameter("@CarID", SqlDbType.Int);
Param.Direction = ParameterDirection.Output;
cmd.Parameters.Add(Param);
cmd.Parameters.AddWithValue("@ModelID", ToAdd.ModelID);
cmd.Parameters.AddWithValue("@StyleID", ToAdd.StyleID);
cmd.Parameters.AddWithValue("@Mileage", ToAdd.Mileage);
cmd.Parameters.AddWithValue("@Salesprice", ToAdd.Saleprice);
cmd.Parameters.AddWithValue("@Isnew", ToAdd.IsNew);
cmd.Parameters.AddWithValue("@IsSold", ToAdd.IsSold);
cmd.Parameters.AddWithValue("@IsFeatured", ToAdd.IsFeatured);
cmd.Parameters.AddWithValue("@ModelYear", ToAdd.ModelYear);
cmd.Parameters.AddWithValue("@ColorID", ToAdd.Color);
cmd.Parameters.AddWithValue("@transmissionID", ToAdd.TransmissionID);
cmd.Parameters.AddWithValue("@interiorID", ToAdd.Interior);
cmd.Parameters.AddWithValue("@VIN", ToAdd.VIN);
cmd.Parameters.AddWithValue("@MSRP", ToAdd.MSRP);
if (string.IsNullOrWhiteSpace(ToAdd.CarDescription))
{
ToAdd.CarDescription = null;
}
cmd.Parameters.AddWithValue("@Description", ToAdd.CarDescription);
if (string.IsNullOrWhiteSpace(ToAdd.ImageFileName))
{
ToAdd.ImageFileName = "Placeholder.jpg";
}
cmd.Parameters.AddWithValue("@ImageFileName", ToAdd.ImageFileName);
cn.Open();
cmd.ExecuteNonQuery();
ToAdd.CarID = (int)Param.Value;
}
}
public void EditCar(CarDetailed ToEdit)
{
using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("EditCar", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@CarID", ToEdit.CarID);
cmd.Parameters.AddWithValue("@ModelID", ToEdit.ModelID);
cmd.Parameters.AddWithValue("@StyleID", ToEdit.StyleID);
cmd.Parameters.AddWithValue("@Mileage", ToEdit.Mileage);
cmd.Parameters.AddWithValue("@Salesprice", ToEdit.Saleprice);
cmd.Parameters.AddWithValue("@Isnew", ToEdit.IsNew);
cmd.Parameters.AddWithValue("@isFeatured", ToEdit.IsFeatured);
cmd.Parameters.AddWithValue("@ModelYear", ToEdit.ModelYear);
cmd.Parameters.AddWithValue("@ColorID", ToEdit.Color);
cmd.Parameters.AddWithValue("@transmissionID", ToEdit.TransmissionID);
cmd.Parameters.AddWithValue("@interiorID", ToEdit.Interior);
cmd.Parameters.AddWithValue("@VIN", ToEdit.VIN);
cmd.Parameters.AddWithValue("@MSRP", ToEdit.MSRP);
if (string.IsNullOrWhiteSpace(ToEdit.CarDescription))
{
ToEdit.CarDescription = " ";
}
cmd.Parameters.AddWithValue("@Description", ToEdit.CarDescription);
if (string.IsNullOrWhiteSpace(ToEdit.ImageFileName))
{
ToEdit.ImageFileName = "Placeholder.JPG";
}
cmd.Parameters.AddWithValue("@ImageFileName", ToEdit.ImageFileName);
cn.Open();
cmd.ExecuteNonQuery();
}
}