private SqliteCommand GetCreateVideoCommand(SqliteConnection db, CreateVideoDto dto, string guid, bool ignoreDuplicates) { var ignoreClause = ignoreDuplicates ? "OR IGNORE" : ""; var command = new SqliteCommand(); command.Connection = db; command.CommandText = $"INSERT {ignoreClause} INTO video(title, duration_in_seconds, external_rating, user_rating, description, notes, source_url, site_url, series_id, watch_status, publisher_id, library_id, deleted, deletion_due_to_cascade, times_watched, release_date, timeline_date, cover_image, unique_id) VALUES(@Title, @Duration, @ExternalRating, @UserRating, @Description, @Notes, @SourceURL, @SiteURL, @SeriesId, @WatchStatus, @PublisherId, @LibraryId, false, false, @TimesWatched, @ReleaseDate, @TimelineDate, @CoverImage, @UniqueId)"; command.Parameters.AddWithValue("@Title", dto.Title); command.Parameters.AddWithValue("@Duration", QueryUtil.GetNullableValueForStorage(dto.DurationInSeconds)); command.Parameters.AddWithValue("@ExternalRating", dto.ExternalRating); command.Parameters.AddWithValue("@UserRating", dto.UserRating); command.Parameters.AddWithValue("@Description", dto.Description); command.Parameters.AddWithValue("@Notes", dto.Notes); command.Parameters.AddWithValue("@SourceURL", dto.SourceURL); command.Parameters.AddWithValue("@SiteURL", dto.SiteURL); command.Parameters.AddWithValue("@SeriesId", QueryUtil.GetNullableIdForStorage(dto.SeriesId)); command.Parameters.AddWithValue("@WatchStatus", dto.WatchStatus); command.Parameters.AddWithValue("@PublisherId", QueryUtil.GetNullableIdForStorage(dto.PublisherId)); command.Parameters.AddWithValue("@LibraryId", dto.LibraryId); command.Parameters.AddWithValue("@ReleaseDate", QueryUtil.GetNullableValueForStorage(dto.ReleaseDate)); command.Parameters.AddWithValue("@TimelineDate", QueryUtil.GetNullableValueForStorage(dto.TimelineDate)); command.Parameters.AddWithValue("@CoverImage", QueryUtil.GetNullableIdForStorage(dto.CoverFileId)); command.Parameters.AddWithValue("@UniqueId", guid); command.Parameters.AddWithValue("@TimesWatched", dto.TimesWatched); return(command); }
private SqliteCommand GetUpdateVideoCommand(SqliteConnection db, Video video, string idColumn, object idValue) { var command = new SqliteCommand(); command.Connection = db; command.CommandText = $@" UPDATE video SET title = @Title, times_watched = @TimesWatched, last_watch_date = @LastWatchDate, duration_in_seconds = @Duration, external_rating = @ExternalRating, user_rating = @UserRating, description = @Description, notes = @Notes, source_url = @SourceURL, site_url = @SiteURL, series_id = @SeriesId, watch_status = @WatchStatus, publisher_id = @PublisherId, library_id = @LibraryId, release_date = @ReleaseDate, timeline_date = @TimelineDate, cover_image = @CoverImage WHERE {idColumn} = @VideoId"; command.Parameters.AddWithValue("@Title", video.Title); command.Parameters.AddWithValue("@TimesWatched", video.TimesWatched); command.Parameters.AddWithValue("@LastWatchDate", QueryUtil.GetNullableValueForStorage(video.LastWatchDate)); command.Parameters.AddWithValue("@Duration", QueryUtil.GetNullableValueForStorage(video.DurationInSeconds)); command.Parameters.AddWithValue("@ExternalRating", video.ExternalRating); command.Parameters.AddWithValue("@UserRating", video.UserRating); command.Parameters.AddWithValue("@Description", video.Description); command.Parameters.AddWithValue("@Notes", video.Notes); command.Parameters.AddWithValue("@SourceURL", video.SourceURL); command.Parameters.AddWithValue("@SiteURL", video.SiteURL); command.Parameters.AddWithValue("@SeriesId", QueryUtil.GetNullableIdForStorage(video.SeriesId)); command.Parameters.AddWithValue("@WatchStatus", video.WatchStatus); command.Parameters.AddWithValue("@PublisherId", QueryUtil.GetNullableIdForStorage(video.PublisherId)); command.Parameters.AddWithValue("@LibraryId", video.LibraryId); command.Parameters.AddWithValue("@VideoId", idValue); command.Parameters.AddWithValue("@ReleaseDate", QueryUtil.GetNullableValueForStorage(video.ReleaseDate)); command.Parameters.AddWithValue("@TimelineDate", QueryUtil.GetNullableValueForStorage(video.TimelineDate)); command.Parameters.AddWithValue("@CoverImage", QueryUtil.GetNullableIdForStorage(video.CoverFileId)); return(command); }
private SqliteCommand GetUpdateCharacterCommand(SqliteConnection db, Character character, string idColumn, object idValue) { var command = new SqliteCommand(); command.Connection = db; command.CommandText = $"UPDATE character SET name = @Name, description = @Description, library_id = @LibraryId, birth_date = @BirthDate, career_start_date = @CareerStartDate, career_end_date = @CareerEndDate, rating = @Rating, calendar_id = @CalendarId, cover_file_id = @CoverFileId WHERE {idColumn} = @CharacterId"; command.Parameters.AddWithValue("@Name", character.Name); command.Parameters.AddWithValue("@Description", character.Description); command.Parameters.AddWithValue("@LibraryId", character.LibraryId); command.Parameters.AddWithValue("@BirthDate", QueryUtil.GetNullableValueForStorage(character.BirthDate)); command.Parameters.AddWithValue("@CareerStartDate", QueryUtil.GetNullableValueForStorage(character.CareerStartDate)); command.Parameters.AddWithValue("@CareerEndDate", QueryUtil.GetNullableValueForStorage(character.CareerEndDate)); command.Parameters.AddWithValue("@Rating", character.Rating); command.Parameters.AddWithValue("@CoverFileId", QueryUtil.GetNullableIdForStorage(character.CoverMediaId)); command.Parameters.AddWithValue("@CharacterId", idValue); command.Parameters.AddWithValue("@CalendarId", QueryUtil.GetNullableIdForStorage(character.CalendarId)); return(command); }
private SqliteCommand GetCreateCharacterCommand(SqliteConnection db, CreateCharacterDto dto, string guid, bool ignoreDuplicates) { var ignoreClause = ignoreDuplicates ? "OR IGNORE" : ""; var command = new SqliteCommand(); command.Connection = db; command.CommandText = $"INSERT {ignoreClause} INTO character(name, description, library_id, birth_date, career_start_date, career_end_date, rating, deleted, deletion_due_to_cascade, cover_file_id, calendar_id, creator, unique_id) VALUES(@Name, @Description, @LibraryId, @BirthDate, @CareerStartDate, @CareerEndDate, @Rating, false, false, @CoverFileId, @CalendarId, @IsCreator, @UniqueId)"; command.Parameters.AddWithValue("@Name", dto.Name); command.Parameters.AddWithValue("@Description", dto.Description); command.Parameters.AddWithValue("@LibraryId", dto.LibraryId); command.Parameters.AddWithValue("@BirthDate", QueryUtil.GetNullableValueForStorage(dto.BirthDate)); command.Parameters.AddWithValue("@CareerStartDate", QueryUtil.GetNullableValueForStorage(dto.CareerStartDate)); command.Parameters.AddWithValue("@CareerEndDate", QueryUtil.GetNullableValueForStorage(dto.CareerEndDate)); command.Parameters.AddWithValue("@Rating", dto.Rating); command.Parameters.AddWithValue("@CoverFileId", QueryUtil.GetNullableIdForStorage(dto.CoverMediaId)); command.Parameters.AddWithValue("@CalendarId", QueryUtil.GetNullableIdForStorage(dto.CalendarId)); command.Parameters.AddWithValue("@IsCreator", dto.IsCreator); command.Parameters.AddWithValue("@UniqueId", guid); return(command); }