//Get a list of all the ratings for a given ID.
public async Task <List <ITEM_RATING> > LoadItemsRatingsAsync(int ID_ITEM)
{
List <ITEM_RATING> temp = new List <ITEM_RATING>();
var con = _db.Database.GetDbConnection();
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
using (var command = con.CreateCommand())
{
string q = @"SELECT [ID_ITEM]
,[ID_USER]
,b.NAME
,[RATING]
,[DATE_ENTERED]
FROM dbo.ITEM_RATING a WITH(NOLOCK)
join [USER] b WITH(NOLOCK) ON a.ID_USER = b.ID
WHERE [ID_ITEM] = @ID_ITEM
ORDER BY DATE_ENTERED DESC";
command.CommandText = q;
DbParameter tempParameter = command.CreateParameter();
tempParameter.ParameterName = "@ID_ITEM";
tempParameter.Value = ID_ITEM;
command.Parameters.Add(tempParameter);
System.Data.Common.DbDataReader reader = await command.ExecuteReaderAsync();
temp = new List <ITEM_RATING>();
if (reader.HasRows)
{
while (reader.Read())
{
var row = new ITEM_RATING
{
ID_ITEM = (int)reader["ID_ITEM"],
ID_USER = (int)reader["ID_USER"],
RATING = (int)(reader["RATING"]),
DATE_ENTERED = Convert.ToDateTime(reader["DATE_ENTERED"]),
USER_NAME = (string)(reader["NAME"]),
};
temp.Add(row);
}
}
reader.Dispose();
}
return(temp);
}
//INSERT or UPATE a given item's rating for a user.
public async Task UpdateRatingAsync(int ID_ITEM, int ID_USER, int RATING)
{
ITEM_RATING temp = await _db.ITEM_RATING_DBSet.FindAsync(ID_ITEM, ID_USER);
if (temp == null)
{
// do insert
temp = new ITEM_RATING();
temp.ID_ITEM = ID_ITEM;
temp.ID_USER = ID_USER;
temp.RATING = RATING;
temp.DATE_ENTERED = DateTime.Now;
_db.ITEM_RATING_DBSet.Add(temp);
_db.SaveChanges();
}
else
{
temp.RATING = RATING;
temp.DATE_ENTERED = DateTime.Now;
_db.ITEM_RATING_DBSet.Update(temp);
_db.SaveChanges();
}
}