public VehicleDetailsItem GetDetails(int vehicleID)
{
VehicleDetailsItem vehicle = null;
using (var cn = new SqlConnection(Settings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("VehicleDetails", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@VehicleID", vehicleID);
cn.Open();
//SELECT VehicleID, [Year], Mileage, MSRP, SalesPrice, [Description], Picture, Featured, VIN, UserID, ModelID, ColorID, TypeID, TransmissionID, InteriorID
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr.Read())
{
vehicle = new VehicleDetailsItem();
vehicle.VehicleID = (int)dr["VehicleID"];
vehicle.MakeID = (int)dr["MakeID"];
vehicle.ModelID = (int)dr["ModelID"];
vehicle.Mileage = (int)dr["Mileage"];
vehicle.MSRP = (int)dr["MSRP"];
vehicle.SalesPrice = (int)dr["SalesPrice"];
vehicle.Description = dr["Description"].ToString();
if (dr["Picture"] != DBNull.Value)
{
vehicle.Picture = dr["Picture"].ToString();
}
vehicle.VIN = dr["VIN"].ToString();
vehicle.ColorID = (int)dr["ColorID"];
vehicle.TransmissionID = (int)dr["TransmissionID"];
vehicle.InteriorID = (int)dr["InteriorID"];
vehicle.BodyID = (int)dr["BodyID"];
vehicle.MakeName = dr["MakeName"].ToString();
vehicle.ModelName = dr["ModelName"].ToString();
vehicle.Year = (int)dr["Year"];
vehicle.BodyStyleName = dr["BodyStyleName"].ToString();
vehicle.InteriorColor = dr["InteriorColor"].ToString();
vehicle.TransmissionType = dr["TransmissionType"].ToString();
vehicle.ColorName = dr["ColorName"].ToString();
}
}
}
return(vehicle);
}
public VehicleDetailsItem GetDetails(int id)
{
VehicleDetailsItem vehicle = new VehicleDetailsItem();
using (var cn = new SqlConnection(Settings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("GetDetails", cn);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ID", id);
cmd.Connection = cn;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr.Read())
{
vehicle.ID = (int)dr["ID"];
vehicle.Make = dr["Make"].ToString();
vehicle.Model = dr["Model"].ToString();
vehicle.Year = (int)dr["Year"];
vehicle.BodyStyle = dr["BodyStyle"].ToString();
vehicle.Transmission = dr["Transmission"].ToString();
vehicle.Color = dr["Color"].ToString();
vehicle.Interior = dr["Interior"].ToString();
vehicle.Mileage = (int)dr["Mileage"];
vehicle.VIN = dr["VIN#"].ToString();
vehicle.SalePrice = (decimal)dr["SalePrice"];
vehicle.MSRP = (decimal)dr["MSRP"];
vehicle.Description = dr["Description"].ToString();
if (dr["ImageFileName"] != DBNull.Value)
{
vehicle.ImageFileName = dr["ImageFileName"].ToString();
}
if (dr["PurchaseID"] != DBNull.Value)
{
vehicle.PurchaseID = (int)dr["PurchaseID"];
}
}
}
}
return(vehicle);
}
public VehicleDetailsItem GetDetails(int vehicleID)
{
VehicleDetailsItem vehicle = null;
using (var cn = new SqlConnection(Settings.GetConnectionString()))
{
SqlCommand cmd = new SqlCommand("VehicleSelectDetails", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@vehicleID", vehicleID);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr.Read())
{
vehicle = new VehicleDetailsItem();
vehicle.VehicleID = (int)dr["VehicleID"];
vehicle.VehicleYear = (int)dr["VehicleYear"];
vehicle.MakeName = dr["MakeName"].ToString();
vehicle.ModelName = dr["ModelName"].ToString();
vehicle.Price = (decimal)dr["Price"];
vehicle.Mileage = (int)dr["Mileage"];
vehicle.Vin = dr["Vin"].ToString();
vehicle.MSRP = (decimal)dr["MSRP"];
vehicle.VehicleDescription = dr["VehicleDescription"].ToString();
vehicle.ImageFileName = dr["ImageFileName"].ToString();
vehicle.BodyDescription = dr["BodyDescription"].ToString();
vehicle.Gears = dr["Gears"].ToString();
vehicle.BodyColorName = dr["BodyColorName"].ToString();
vehicle.InteriorColorName = dr["InteriorColorName"].ToString();
vehicle.SaleStatus = (bool)dr["SaleStatus"];
}
}
}
return(vehicle);
}
public IEnumerable <VehicleDetailsItem> Search(InventorySearchParameters parameters)
{
List <VehicleDetailsItem> results = new List <VehicleDetailsItem>();
using (var cn = new SqlConnection(Settings.GetConnectionString()))
{
string query = "SELECT TOP 20 ID,Make,Model,[Year],BodyStyle,Transmission,Color,Interior,Mileage,VIN#,SalePrice,MSRP,ImageFileName,[Description]" +
" FROM Inventory i INNER JOIN Makes ma ON i.MakeID = ma.MakeID" +
" INNER JOIN Models mo ON i.ModelID = mo.ModelID" +
" INNER JOIN BodyStyles b ON i.BodyStyleID = b.BodyStyleID" +
" INNER JOIN Transmissions t ON i.TransmissionID = t.TransmissionID" +
" INNER JOIN Colors c ON i.ColorID = c.ColorID" +
" INNER JOIN Interiors ir ON i.InteriorID = ir.InteriorID" +
" WHERE i.PurchaseID IS NULL ";
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
if (!string.IsNullOrEmpty(parameters.QuickSearch))
{
query += $"AND (Make LIKE '%' + @QuickSearch + '%' OR Model LIKE '%' + @QuickSearch + '%' OR [Year] LIKE @QuickSearch + '%') ";
cmd.Parameters.AddWithValue("@QuickSearch", parameters.QuickSearch);
}
if (parameters.MinPrice.HasValue)
{
query += $"AND SalePrice >= @MinPrice ";
cmd.Parameters.AddWithValue("@MinPrice", parameters.MinPrice.Value);
}
if (parameters.MaxPrice.HasValue)
{
query += $"AND SalePrice <= @MaxPrice ";
cmd.Parameters.AddWithValue("@MaxPrice", parameters.MaxPrice.Value);
}
if (parameters.MinYear.HasValue)
{
query += $"AND [YEAR] >= @MinYear ";
cmd.Parameters.AddWithValue("@MinYear", parameters.MinYear.Value);
}
if (parameters.MaxYear.HasValue)
{
query += $"AND [Year] <= @MaxYear ";
cmd.Parameters.AddWithValue("@MaxYear", parameters.MaxYear.Value);
}
if (parameters.NewOrUsed.HasValue)
{
query += $"AND TypeID = @NewOrUsed ";
cmd.Parameters.AddWithValue("@NewOrUsed", parameters.NewOrUsed.Value);
}
query += "ORDER BY MSRP DESC";
cmd.CommandText = query;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
VehicleDetailsItem vehicle = new VehicleDetailsItem();
vehicle.ID = (int)dr["ID"];
vehicle.Make = dr["Make"].ToString();
vehicle.Model = dr["Model"].ToString();
vehicle.Year = (int)dr["Year"];
vehicle.BodyStyle = dr["BodyStyle"].ToString();
vehicle.Transmission = dr["Transmission"].ToString();
vehicle.Color = dr["Color"].ToString();
vehicle.Interior = dr["Interior"].ToString();
vehicle.Mileage = (int)dr["Mileage"];
vehicle.VIN = dr["VIN#"].ToString();
vehicle.SalePrice = (decimal)dr["SalePrice"];
vehicle.MSRP = (decimal)dr["MSRP"];
vehicle.Description = dr["Description"].ToString();
if (dr["ImageFileName"] != DBNull.Value)
{
vehicle.ImageFileName = dr["ImageFileName"].ToString();
}
results.Add(vehicle);
}
}
}
return(results);
}