public IActionResult UpdateGame(int gameId, GameWithMetadata updatedGame)
{
var currentUserId = _userRepo.GetUserIdByUid(UserId);
if (_gameRepo.GetGameById(gameId) == null)
{
return(NotFound());
}
_gameRepo.UpdateGame(gameId, updatedGame);
return(Ok());
}
public void UpdateGame(int gameId, GameWithMetadata updatedGame)
{
using var db = new SqlConnection(_connectionString);
var parameterToUpdateGame = new { gameId };
var selectedGameToUpdate = GetGameById(gameId);
if (selectedGameToUpdate != null)
{
// first, update the data for the game:
var sqlToUpdateGame = @"UPDATE [dbo].[Games]
SET [Name] = @name
,[Songs] = @songs
,[Description] = @description
,[PreworkLevelId] = @preworkLevelId
,[Prework] = @prework
,[Instructions] = @instructions
,[Credit] = @credit
,[WebsiteUrl] = @websiteUrl
,[GameIconId] = @gameIconId
,[PhotoUrl] = @photoUrl
,[GamePhotoId] = @gamePhotoId
,[Keywords] = @keywords
WHERE Id = @gameId";
var parametersToUpdateGame = new
{
gameId,
name = updatedGame.Name,
songs = updatedGame.Songs,
description = updatedGame.Description,
preworkLevelId = updatedGame.PreworkLevelId,
prework = updatedGame.Prework,
instructions = updatedGame.Instructions,
credit = updatedGame.Credit,
websiteUrl = updatedGame.WebsiteUrl,
gameIconId = updatedGame.GameIconId,
photoUrl = updatedGame.PhotoUrl,
gamePhotoId = updatedGame.GamePhotoId,
keywords = updatedGame.Keywords,
};
db.Execute(sqlToUpdateGame, parametersToUpdateGame);
// next update related gameinstrument records and gameage records if changed:
//UPDATE GAMEINSTRUMENT and GAMEAGE RECORDS!!!
// first, check if the instrument IDs selected in the frontend for this game exist for any of the records in the gameInstruments list for this game - if they do, ignore that instrument id:
var updatedGameInstrumentIds = updatedGame.InstrumentIdsForGame;
var sqlForInstrumentsForSelectedGame = @"select *
from GameInstruments gi
where gi.GameId = @gameId";
var currentGameInstruments = db.Query <GameInstrument>(sqlForInstrumentsForSelectedGame, parameterToUpdateGame);
var currentInstrumentIds = new List <int>();
foreach (var instrument in currentGameInstruments)
{
var id = instrument.InstrumentId;
currentInstrumentIds.Add(id);
}
if (updatedGameInstrumentIds != null)
{
foreach (var instId in updatedGameInstrumentIds)
{
if (currentInstrumentIds.Contains(instId))
{
break;
}
// if the instrument ID coming in from frontend does not exist in a gameInstrument record for this game, then create a new GI record:
if (currentInstrumentIds.IndexOf(instId) == -1)
{
var sqlToAddNewGameInstrumentRecord = @"INSERT INTO [dbo].[GameInstruments]
([GameId]
,[InstrumentId])
OUTPUT INSERTED.Id
VALUES
(@gameId
,@instrumentId)";
var parametersToAddNewGameInstrument = new { gameId, instrumentId = instId };
var newGiRecord = db.ExecuteScalar <int>(sqlToAddNewGameInstrumentRecord, parametersToAddNewGameInstrument);
}
}
}
;
// last, if an instrument ID that already appears in a GI record in the DB for this game but DOES NOT appear in the list of instrument IDs coming in from frontend for this updated game, then delete that GI record:
foreach (var instId in currentInstrumentIds)
{
if (updatedGameInstrumentIds.IndexOf(instId) == -1)
{
var sqlToDeleteGameInstrumentRecord = @"DELETE FROM [dbo].[GameInstruments]
WHERE GameId = @gameId
AND InstrumentId = @instrumentId";
var parametersToDeleteGameInstrument = new { gameId, instrumentId = instId };
db.Execute(sqlToDeleteGameInstrumentRecord, parametersToDeleteGameInstrument);
}
}
// next, do the same thing for gameAge records:
// first, check if the age IDs selected in the frontend for this game exist for any of the records in the gameAges list for this game - if they do, ignore that age id:
var updatedGameAgeIds = updatedGame.AgeIdsForGame;
var sqlForAgesForSelectedGame = @"select *
from GameAges ga
where ga.GameId = @gameId";
var currentGameAges = db.Query <GameAge>(sqlForAgesForSelectedGame, parameterToUpdateGame);
var currentAgeIds = new List <int>();
foreach (var age in currentGameAges)
{
var id = age.AgeId;
currentAgeIds.Add(id);
}
if (updatedGameAgeIds != null)
{
foreach (var ageId in updatedGameAgeIds)
{
if (currentAgeIds.Contains(ageId))
{
break;
}
// if the age ID coming in from frontend does not exist in a gameAge record for this game, then create a new GA record:
if (currentAgeIds.IndexOf(ageId) == -1)
{
var sqlToAddNewGameAgeRecord = @"INSERT INTO [dbo].[GameAges]
([GameId]
,[AgeId])
OUTPUT INSERTED.Id
VALUES
(@gameId
,@ageId)";
var parametersToAddNewGameAge = new { gameId, ageId };
var newGaRecord = db.ExecuteScalar <int>(sqlToAddNewGameAgeRecord, parametersToAddNewGameAge);
}
}
}
;
// last, if an age ID that already appears in a GA record in the DB for this game but DOES NOT appear in the list of age IDs coming in from frontend for this updated game, then delete that GA record:
foreach (var ageId in currentAgeIds)
{
if (updatedGameAgeIds.IndexOf(ageId) == -1)
{
var sqlToDeleteGameAgeRecord = @"DELETE FROM [dbo].[GameAges]
WHERE GameId = @gameId
AND AgeId = @ageId";
var parametersToDeleteGameAge = new { gameId, ageId };
db.Execute(sqlToDeleteGameAgeRecord, parametersToDeleteGameAge);
}
}
}
}
