public async Task <ITEM> LoadItemAsync(int?ID_ITEM)
{
ITEM temp = new ITEM();
var con = _db.Database.GetDbConnection();
con.Open();
using (var command = con.CreateCommand())
{
// this does a few things. First it returns the given item id, the item name, and the current rating aggregate across all ratings for it.
// additionally it finds the next seqential ID, and the previous seqential ID. If none found then 0.
// this is important for the 'next/previous' buttons.
string q = @" SELECT top(1) b.ID
,b.TITLE
,isnull(C.RATING, 0) as RATING
,( SELECT isnull(min(a.id), 0)
FROM [test].[dbo].[ITEM] a
where a.id > b.ID) as [NEXT]
,( SELECT isnull(max(a.id), 0)
FROM [test].[dbo].[ITEM] a
where a.id < b.ID) as [PREVIOUS]
FROM [test].[dbo].[ITEM] b
left JOIN (
SELECT y.[ID]
,AVG(CAST(z.RATING AS FLOAT)) as RATING
FROM [dbo].[ITEM] y
JOIN [dbo].[ITEM_RATING] z
ON y.ID = z.ID_ITEM
GROUP BY y.ID
) c ON B.ID = c.ID ";
if (ID_ITEM != null)
{
// IF there is a specific ID (say from someone clicking 'next' then load that specific item's info, next, previous, name, aggregate rating, etc.
q += " WHERE b.ID = @ID_ITEM ";
DbParameter tempParameter = command.CreateParameter();
tempParameter.ParameterName = "@ID_ITEM";
tempParameter.Value = ID_ITEM;
command.Parameters.Add(tempParameter);
}
command.CommandText = q;
System.Data.Common.DbDataReader reader = await command.ExecuteReaderAsync();
if (reader.HasRows)
{
reader.Read();
temp = new ITEM();
temp.ID = (int)reader["ID"];
temp.TITLE = (string)reader["TITLE"];
temp.RATING = Convert.ToDecimal(reader["RATING"]);
temp.NEXT = (int)reader["NEXT"];
temp.PREVIOUS = (int)reader["PREVIOUS"];
}
reader.Dispose();
}
return(temp);
}
//Testing Helper - adding items to the table.
public async Task <int> AddItemAsync(string ItemName)
{
ITEM temp = new ITEM()
{
TITLE = ItemName
};
await _db.ITEM_DBSet.AddAsync(temp);
await _db.SaveChangesAsync();
return(temp.ID);
}
