public List <WordForEditModel> GetWordDetails(WordOverviewModel overview) { var sql = @" select WordsToWords.id, wordsBase.Word as BaseWord, wordsTarget.Word as Translate from WordsToWords left join words as wordsBase on WordsToWords.BaseWordId = wordsBase.id left join words as wordsTarget on WordsToWords.TargetWordId = wordsTarget.id where WordsToWords.AddedBy = @AddedBy and wordsBase.LanguageId = @BaseLanguageId and wordsTarget.LanguageId = @TargetLanguageId and " + (overview.ChapterId != 0 && overview.ChapterId != null ? "WordsToWords.ChapterId = @ChapterId" : "WordsToWords.ChapterId is null") + @" and " + (overview.BookId != 0 && overview.BookId != null ? "WordsToWords.BookId = @BookId" : "WordsToWords.BookId is null") + @" "; var result = db.Query <WordForEditModel>(sql, overview).ToList(); return(result); }
public void EditWordSeries(SubmitWordsModel submitWords, int userId) { var modelForDelete = new WordOverviewModel { BookId = submitWords.Book.Id, ChapterId = submitWords.Chapter.Id, AddedBy = userId, BaseLanguageId = submitWords.BaseLanguage.Id, TargetLanguageId = submitWords.TargetLanguage.Id }; var sql = @" delete WordsToWords FROM dbo.WordsToWords left join dbo.words as wordsBase on WordsToWords.BaseWordId = wordsBase.Id left join dbo.words as wordsTarget on WordsToWords.TargetWordId = wordsTarget.Id where WordsToWords.AddedBy = @AddedBy and wordsBase.LanguageId = @BaseLanguageId and WordsToWords.BookId = @BookId and WordsToWords.ChapterId = @ChapterId and wordsTarget.LanguageId = @TargetLanguageId "; db.Query(sql, modelForDelete).ToList(); SubmitWordSeries(submitWords, userId); }