public IHttpActionResult Search(decimal?minRate, decimal?maxRate, string make, string model, int typeid)
{
var repo = new MobileSuitRepositoryADO();
try
{
var parameters = new MobileSuitSearchParameters()
{
MinRate = minRate,
MaxRate = maxRate,
Make = make,
Model = model,
TypeId = typeid
};
var result = repo.Search(parameters);
return(Ok(result));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public IEnumerable <Featured_Item> Search(MobileSuitSearchParameters parameters)
{
List <Featured_Item> listings = new List <Featured_Item>();
using (var cn = new SqlConnection(Settings.GetConnectionString()))
{
string query = "SELECT TOP 12 InventoryNumber,[Name],MA.MakeName,MO.ModelName,SerialNumber,UserID,MS.MakeModelID,TypeID,[BodyStyleID],[Year],[CenturyID],WeaponID,ColorID,Interior,MSRP,SalePrice, [Description], [Image], [Creation Date], Featured FROM [Mobile Suit] MS Inner Join MakeModel MM on MS.MakeModelID = MM.MakeModelID INNER JOIN Make MA on MM.MakeID = MA.MakeID INNER JOIN Model MO on MM.ModelID = MO.ModelID Where 1=1";
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
if (parameters.MinRate.HasValue)
{
query += "AND SalePrice >= @MinRate ";
cmd.Parameters.AddWithValue("@MinRate", parameters.MinRate.Value);
}
if (parameters.MaxRate.HasValue)
{
query += "AND SalePrice <= @MaxRate ";
cmd.Parameters.AddWithValue("@MaxRate", parameters.MaxRate.Value);
}
if (!string.IsNullOrEmpty(parameters.Make))
{
query += "AND MA.MakeId= @Make ";
cmd.Parameters.AddWithValue("@Make", parameters.Make);
}
if (!string.IsNullOrEmpty(parameters.Model))
{
query += "AND MO.ModelId = @Model ";
cmd.Parameters.AddWithValue("@Model", parameters.Model);
}
query += "AND TypeID = @TypeID ";
cmd.Parameters.AddWithValue("@TypeID", parameters.TypeId);
query += "ORDER BY [Creation Date] DESC";
cmd.CommandText = query;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Featured_Item row = new Featured_Item();
row.InventoryNumber = (int)dr["InventoryNumber"];
row.Make = dr["MakeName"].ToString();
row.Model = dr["ModelName"].ToString();
row.SalePrice = (int)dr["SalePrice"];
row.Name = dr["Name"].ToString();
if (dr["Image"] != DBNull.Value)
{
row.Image = dr["Image"].ToString();
}
listings.Add(row);
}
}
}
return(listings);
}
