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 SellCarNoPromo(SoldCar car, Customer customer, decimal FinalPrice, int PaymentTypeID, string SoldBy) { using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString())) { SqlCommand cmd = new SqlCommand("AddCustomer", cn); cmd.CommandType = CommandType.StoredProcedure; SqlParameter Param = new SqlParameter("@CustomerID", SqlDbType.Int);//c Param.Direction = ParameterDirection.Output; cmd.Parameters.Add(Param); cmd.Parameters.AddWithValue("@CustomerName", customer.CustomerName); cmd.Parameters.AddWithValue("@Phone", customer.Phone); cmd.Parameters.AddWithValue("@Email", customer.Email); cmd.Parameters.AddWithValue("@Streetaddress", customer.StreetAddress); if (string.IsNullOrWhiteSpace(customer.StreetAddress2)) { cmd.Parameters.AddWithValue("@Streetaddress2", DBNull.Value); } else { cmd.Parameters.AddWithValue("@Streetaddress2", customer.StreetAddress2); } cmd.Parameters.AddWithValue("@City", customer.City); cmd.Parameters.AddWithValue("@StateID", customer.StateID); cmd.Parameters.AddWithValue("@Zipcode", customer.Zipcode); cn.Open(); cmd.ExecuteNonQuery(); int x = (int)Param.Value; SqlCommand cmd1 = new SqlCommand("SellaCar", cn); cmd1.CommandType = CommandType.StoredProcedure; cmd1.Parameters.AddWithValue("@CarID", car.Car.CarID); cmd1.Parameters.AddWithValue("@CustomerID", x); cmd1.Parameters.AddWithValue("@SalesPrice", FinalPrice); cmd1.Parameters.AddWithValue("@PurchaseTypeID", PaymentTypeID); cmd1.Parameters.AddWithValue("@PromotonID", DBNull.Value); cmd1.Parameters.AddWithValue("@Soldby", SoldBy); cmd1.ExecuteNonQuery(); } }
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 List <Promotion> GetALLpromotions() { List <Promotion> promos = new List <Promotion>(); using (SqlConnection cn = new SqlConnection(DataSettings.GetConnectionString())) { SqlCommand cmd = new SqlCommand("Select * from promotion", cn); cmd.CommandType = CommandType.Text; cn.Open(); using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { Promotion p = new Promotion(); p.PromotionID = (int)dr["PromotionID"]; p.PromotionName = dr["PromotionName"].ToString(); p.Description = dr["PromotionDescription"].ToString(); string sdate = dr["PromotionStartDate"].ToString(); if (!string.IsNullOrEmpty(sdate)) { p.StartDate = DateTime.Parse(sdate); } string edate = dr["PromotionEndDate"].ToString(); if (!string.IsNullOrEmpty(edate)) { p.EndDate = DateTime.Parse(edate); } p.FlatDiscount = (decimal)dr["FlatDiscount"]; p.PercentDiscount = (int)dr["PercentDiscount"]; p.IsCanceled = (bool)dr["IsCanceled"]; p.IsForNew = (bool)dr["IsForNew"]; p.IsForUsed = (bool)dr["IsForUsed"]; promos.Add(p); } } } return(promos); }
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(); } }