public IHttpActionResult Search(decimal?minPrice, decimal?maxPrice, string cardName, string setName, string colorName)
{
var repo = CardInfoRepositoryFactory.GetRepository();
try
{
var parameters = new CardInfoSearchParameters()
{
MinPrice = minPrice,
MaxPrice = maxPrice,
CardName = cardName,
SetName = setName,
ColorName = colorName
};
var result = repo.Search(parameters);
return(Ok(result));
}
catch (Exception ex)
{
return(BadRequest(ex.Message));
}
}
public IEnumerable <CardLongItem> Search(CardInfoSearchParameters parameters)
{
List <CardLongItem> cardInfo = new List <CardLongItem>();
using (var cn = new SqlConnection(Settings.GetConnectionString()))
{
string query = "CREATE TABLE #Temp (CardID int, ColorName varchar(50)) INSERT INTO #Temp SELECT i.CardId, c.ColorName FROM CardInfo i " +
"JOIN CardColors cc ON cc.CardId = i.CardId JOIN Colors c ON c.ColorId = cc.ColorId CREATE TABLE #ColorString(CardID int, ColorName varchar(50)) " +
"INSERT INTO #ColorString Select Main.CardID, Left(Main.Colors, Len(Main.Colors) - 1) As 'Students' From(Select distinct T2.CardId," +
"(Select T1.ColorName + ', ' AS[text()] From #Temp T1 Where T1.CardId = T2.CardID ORDER BY T1.CardID For XML PATH(''))[Colors] From #Temp T2)" +
" [Main] SELECT DISTINCT CardInfo.CardId, CardName, CmcAmount, CardArtURL, ArtistName, CardNumber, MSRP, SalePrice, SetName, TypeName, #ColorString.ColorName, CardText," +
" [Power], Toughness FROM CardInfo INNER JOIN CardSet ON CardInfo.CardId = CardSet.CardId INNER JOIN[Set] ON CardSet.SetId = [Set].SetId " +
"INNER JOIN CMC ON CardInfo.CmcId = CMC.CmcId INNER JOIN Artist ON CardInfo.ArtistId = Artist.ArtistId INNER JOIN CardType ON " +
"CardInfo.CardId = CardType.CardId INNER JOIN[Type] ON CardType.TypeId = [Type].TypeId INNER JOIN CardColors ON CardInfo.CardId = " +
"CardColors.CardId INNER JOIN Colors ON CardColors.ColorId = Colors.ColorId INNER JOIN #ColorString ON CardInfo.CardId = #ColorString.CardID WHERE 1 = 1 ";
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
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 (!string.IsNullOrEmpty(parameters.CardName))
{
query += "AND CardName LIKE @CardName ";
cmd.Parameters.AddWithValue("@CardName", '%' + parameters.CardName + '%');
}
if (!string.IsNullOrEmpty(parameters.SetName))
{
query += "AND SetName LIKE @SetName ";
cmd.Parameters.AddWithValue("@SetName", parameters.SetName + '%');
}
if (!string.IsNullOrEmpty(parameters.ColorName))
{
query += "AND #ColorString.ColorName LIKE @ColorName ";
cmd.Parameters.AddWithValue("@ColorName", '%' + parameters.ColorName + '%');
}
query += "ORDER BY MSRP DESC";
cmd.CommandText = query;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
CardLongItem row = new CardLongItem();
row.CardId = (int)dr["CardId"];
row.CardName = dr["CardName"].ToString();
row.CmcAmount = dr["CmcAmount"].ToString();
row.CardArtURL = dr["CardArtURL"].ToString();
row.ArtistName = dr["ArtistName"].ToString();
row.CardNumber = dr["CardNumber"].ToString();
row.MSRP = (int)dr["MSRP"];
row.SalePrice = (int)dr["SalePrice"];
row.SetName = dr["SetName"].ToString();
row.TypeName = dr["TypeName"].ToString();
row.ColorName = dr["ColorName"].ToString();
if (dr["CardText"] != DBNull.Value)
{
row.CardText = dr["CardText"].ToString();
}
if (dr["Power"] != DBNull.Value)
{
row.Power = (int)dr["Power"];
}
if (dr["Toughness"] != DBNull.Value)
{
row.Toughness = (int)dr["Toughness"];
}
cardInfo.Add(row);
}
}
}
return(cardInfo);
}
