//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(); } }