private List <CreatorOfVideoFullDto> GetVideosCharacterIsInWithoutActors(long characterId, SqliteConnection db, string characterIdColumn)
{
var firstQuery = $"SELECT {SELECT_BASE_COLUMNS_STRING}, c.creator_id, 'Actor' as role FROM video v, actor_for_video_character c WHERE c.{characterIdColumn} = {characterId} AND c.video_id = v.video_id AND v.deleted = false";
var secondQuery = "";
if (characterIdColumn == "creator_id")
{
secondQuery = $"UNION SELECT {SELECT_BASE_COLUMNS_STRING}, vc.creator_id, vc.role FROM video v, video_creator vc WHERE v.deleted = false AND vc.video_id = v.video_id AND vc.creator_id = {characterId}";
}
var command = new SqliteCommand($"{firstQuery} {secondQuery}", db);
var query = command.ExecuteReader();
var list = new List <CreatorOfVideoFullDto>();
var characterIds = new Dictionary <long, CreatorOfVideoFullDto>();
while (query.Read())
{
var role = query.GetString(22);
var dto = new CreatorOfVideoFullDto(ParseVideo(query), QueryUtil.GetNullableId(query, 21), "", role);
var key = dto.Video.VideoId;
if (characterIds.ContainsKey(key))
{
var existingDto = characterIds[key];
existingDto.Role += $", {dto.Role}";
}
else
{
list.Add(dto);
characterIds[key] = dto;
}
}
return(list);
}
public void UpdateCharacterInVideo(CreatorOfVideoFullDto dto, long characterId)
{
using (var db = DataAccessUtil.CreateSqlConnection()) {
db.Open();
var clearCommand = new SqliteCommand($"UPDATE actor_for_video_character SET video_id = {dto.Video.VideoId}, character_id = {characterId}, creator_id = @ActorId WHERE video_id = {dto.Video.VideoId} AND character_id = {characterId}", db);
clearCommand.Parameters.AddWithValue("@ActorId", QueryUtil.GetNullableIdForStorage(dto.ActorId));
clearCommand.ExecuteNonQuery();
}
}
public async Task UpdateActorForVideo(CreatorOfVideoFullDto dto, long characterId)
{
characterRepository.UpdateCharacterInVideo(dto, characterId);
}
