public async Task UpdateGenre(int genreID, Genre updatedGenre) { string updateGenre = "UPDATE Genres " + $"Set GenreName = @genreName" + $"WHERE GenreID = @genreID"; try { SqliteCommand command = new SqliteCommand(updateGenre); command.Parameters.AddWithValue("@genreName", updatedGenre.Name); command.Parameters.AddWithValue("@genreID", updatedGenre.ID); SqlFileAccess.SqlPerformQuery(command); } catch (Exception e) { if (e is SqliteException || e is ArgumentOutOfRangeException) { await GeneralRepository.SaveToLogFile(e.ToString()); throw new GenreException("Cant update genre"); } else { await GeneralRepository.SaveToLogFile(e.ToString()); throw new DALException("Unknown Error"); } } }
public async Task AddGenre(Genre g1) { string insertQuery = "INSERT INTO Genres(GenreName)" + $"VALUES(@genreName)"; try { SqliteCommand command = new SqliteCommand(insertQuery); command.Parameters.AddWithValue("@genreName", g1.Name); SqlFileAccess.SqlPerformQuery(command); } catch (Exception e) { if (e is SqliteException || e is ArgumentOutOfRangeException) { await GeneralRepository.SaveToLogFile(e.ToString()); throw new GenreException("Cant add genre"); } else { await GeneralRepository.SaveToLogFile(e.ToString()); throw new DALException("Unknown Error"); } } }
public async Task <List <Book> > GetAllBooks() { List <Book> books = new List <Book>(); string GetBooksQuery = "SELECT ItemID, Name, Writer, PrintDate, Publisher, IDOfGenre, Discount, Quantity,Price, Isbn, Edition, Summary " + "FROM AbstractItems " + "WHERE Subject is null"; try { SqliteCommand command = new SqliteCommand(GetBooksQuery); List <string> booksString = SqlFileAccess.GetData(command); for (int i = 0; i < booksString.Count; i++) { books.Add(await TextToBook(booksString[i])); } return(books); } catch (Exception e) { if (e is SqliteException || e is ArgumentOutOfRangeException) { await GeneralRepository.SaveToLogFile(e.ToString()); throw new BookException("Cant get all books"); } else { await GeneralRepository.SaveToLogFile(e.ToString()); throw new DALException("Unknown Error"); } } }
public async Task <List <Journal> > GetAllJournals() { List <Journal> journals = new List <Journal>(); string GetJournalsQuery = "SELECT ItemID, Name, Writer, PrintDate, Publisher, IDOfGenre, Discount, Quantity,Price, Subject " + "FROM AbstractItems " + "WHERE Isbn is null"; try { SqliteCommand command = new SqliteCommand(GetJournalsQuery); List <string> journalsString = SqlFileAccess.GetData(command); for (int i = 0; i < journalsString.Count; i++) { journals.Add(await TextToJournal(journalsString[i])); } return(journals); } catch (Exception e) { if (e is SqliteException || e is ArgumentOutOfRangeException) { await GeneralRepository.SaveToLogFile(e.ToString()); throw new JournalException("Cant get all journals"); } else { await GeneralRepository.SaveToLogFile(e.ToString()); throw new DALException("Unknown Error"); } } }
public async Task DeleteJournal(Journal j1) { string deleteQuery = $"DELETE FROM AbstractItems WHERE ItemID = @id"; try { SqliteCommand command = new SqliteCommand(deleteQuery); command.Parameters.AddWithValue("@id", j1.ItemID); SqlFileAccess.SqlPerformQuery(command); } catch (Exception e) { if (e is SqliteException || e is ArgumentOutOfRangeException) { await GeneralRepository.SaveToLogFile(e.ToString()); throw new JournalException("Cant delete journal"); } else { await GeneralRepository.SaveToLogFile(e.ToString()); throw new DALException("Unknown Error"); } } }
public async Task <List <string> > GetAllItemNames() { string getAllNamesQuery = "SELECT Name FROM AbstractItems"; try { SqliteCommand command = new SqliteCommand(getAllNamesQuery); return(SqlFileAccess.GetData(command)); } catch (Exception e) { if (e is SqliteException) { await GeneralRepository.SaveToLogFile(e.ToString()); throw new QueryException("Cant get all authors"); } else { await GeneralRepository.SaveToLogFile(e.ToString()); throw new DALException("Unknown Error"); } } }
public async Task AddJournal(Journal j1) { string insertQuery = "INSERT INTO AbstractItems(Name,Writer,PrintDate,Publisher,IDOfGenre,Discount,Quantity,Price,Subject)" + $"VALUES(@name , @writer , @printDate , @publisher , @genreID , @discount , @quantity , @price , @subject)"; try { SqliteCommand command = new SqliteCommand(insertQuery); command.Parameters.AddWithValue("@name", j1.Name); command.Parameters.AddWithValue("@writer", j1.Writer); command.Parameters.AddWithValue("@printDate", j1.PrintDate); command.Parameters.AddWithValue("@publisher", j1.Publisher); command.Parameters.AddWithValue("@genreID", j1.Genre.ID); command.Parameters.AddWithValue("@discount", j1.Discount); command.Parameters.AddWithValue("@quantity", j1.Quantity); command.Parameters.AddWithValue("@price", j1.Price); command.Parameters.AddWithValue("@subject", j1.Subject); SqlFileAccess.SqlPerformQuery(command); } catch (Exception e) { if (e is SqliteException || e is ArgumentOutOfRangeException) { await GeneralRepository.SaveToLogFile(e.ToString()); throw new JournalException("Cant add journal"); } else { await GeneralRepository.SaveToLogFile(e.ToString()); throw new DALException("Unknown Error"); } } }
/// <summary> /// Get a journal by its id /// </summary> /// <param name="id"></param> /// <returns>Journal if found, if not found returns null</returns> public async Task <Journal> GetJournal(int id) { List <Journal> journals = new List <Journal>(); string GetBooksQuery = "SELECT ItemID, Name, Writer, PrintDate, Publisher, IDOfGenre, Discount, Quantity,Price,Subject " + "FROM AbstractItems " + $"WHERE ItemID = @id AND ISBN IS NULL"; try { SqliteCommand command = new SqliteCommand(GetBooksQuery); command.Parameters.AddWithValue("@id", id); List <string> journalsString = SqlFileAccess.GetData(command); if (journalsString.Count == 0) { return(null); } journals.Add(await TextToJournal(journalsString[0])); return(journals[0]); } catch (Exception e) { if (e is SqliteException || e is ArgumentOutOfRangeException) { await GeneralRepository.SaveToLogFile(e.ToString()); throw new JournalException("Cant get journal"); } else { await GeneralRepository.SaveToLogFile(e.ToString()); throw new DALException("Unknown Error"); } } }
/// <summary> /// Returns a list of all the isbns exist /// </summary> /// <returns></returns> public async Task <List <string> > GetAllIsbn() { string getAllIsbnQuery = "SELECT Isbn FROM AbstractItems WHERE Isbn is not null "; try { SqliteCommand command = new SqliteCommand(getAllIsbnQuery); return(SqlFileAccess.GetData(command)); } catch (Exception e) { if (e is SqliteException || e is ArgumentOutOfRangeException) { await GeneralRepository.SaveToLogFile(e.ToString()); throw new BookException("Cant get isbns"); } else { await GeneralRepository.SaveToLogFile(e.ToString()); throw new DALException("Unknown Error"); } } }
/// <summary> /// Get genre by its id /// </summary> /// <param name="id"></param> /// <returns>return the genre found, or an empty genre if not found</returns> public async Task <Genre> GetGenreByID(int id) { string getGenreQuery = $"SELECT GenreID,GenreName FROM Genres WHERE GenreID = @id"; try { SqliteCommand command = new SqliteCommand(getGenreQuery); command.Parameters.AddWithValue("@id", id); List <string> genreString = SqlFileAccess.GetData(command); if (genreString.Count == 0) { return(new Genre()); } return(TextToGenre(genreString[0])); } catch (Exception e) { if (e is SqliteException || e is ArgumentOutOfRangeException) { await GeneralRepository.SaveToLogFile(e.ToString()); throw new GenreException("Cant get genre by id"); } else { await GeneralRepository.SaveToLogFile(e.ToString()); throw new DALException("Unknown Error"); } } }
/// <summary> /// Get all genres exist /// </summary> /// <returns>List of all genres or null if not genres exist</returns> public async Task <List <Genre> > GetAllGenres() { List <Genre> genres = new List <Genre>(); string GetgenresQuery = "SELECT GenreID,GenreName " + "FROM Genres "; try { SqliteCommand command = new SqliteCommand(GetgenresQuery); List <string> genresString = SqlFileAccess.GetData(command); for (int i = 0; i < genresString.Count; i++) { genres.Add(TextToGenre(genresString[i])); } return(genres); } catch (Exception e) { if (e is SqliteException || e is ArgumentOutOfRangeException) { await GeneralRepository.SaveToLogFile(e.ToString()); throw new GenreException("Cant get all genres"); } else { await GeneralRepository.SaveToLogFile(e.ToString()); throw new DALException("Unknown Error"); } } }
public async Task DeleteGenre(Genre g1) { string deleteQuery = $"DELETE FROM Genres WHERE GenreID = @genreID"; try { SqliteCommand command = new SqliteCommand(deleteQuery); command.Parameters.AddWithValue("@genreID", g1.ID); SqlFileAccess.SqlPerformQuery(command); } catch (Exception e) { if (e is SqliteException || e is ArgumentOutOfRangeException) { await GeneralRepository.SaveToLogFile(e.ToString()); throw new GenreException("Cant delete genre"); } else { await GeneralRepository.SaveToLogFile(e.ToString()); throw new DALException("Unknown Error"); } } }
private async Task <Book> TextToBook(string text) { try { string[] bookProps = text.Split(','); Book book = new Book(); book.ItemID = int.Parse(bookProps[0]); book.Name = bookProps[1]; book.Writer = bookProps[2]; book.PrintDate = DateTime.Parse(bookProps[3]); book.Publisher = bookProps[4]; book.Genre = await _genreRep.GetGenreByID(int.Parse(bookProps[5])); book.Discount = int.Parse(bookProps[6]); book.Quantity = int.Parse(bookProps[7]); book.Price = int.Parse(bookProps[8]); book.ISBN = bookProps[9]; book.Edition = bookProps[10]; book.Summary = bookProps[11]; return(book); } catch (Exception e) { if (e is SqliteException || e is GenreException) { await GeneralRepository.SaveToLogFile(e.ToString()); throw new BookException("Cant get Genre"); } else if (e is ArgumentOutOfRangeException) { await GeneralRepository.SaveToLogFile(e.ToString()); throw new BookException("Book Writing Error"); } else { await GeneralRepository.SaveToLogFile(e.ToString()); throw new DALException("Unknown Error"); } } }
private async Task <Journal> TextToJournal(string text) { try { string[] journalProps = text.Split(','); Journal journal = new Journal(); journal.ItemID = int.Parse(journalProps[0]); journal.Name = journalProps[1]; journal.Writer = journalProps[2]; journal.PrintDate = DateTime.Parse(journalProps[3]); journal.Publisher = journalProps[4]; journal.Genre = await _genreRep.GetGenreByID(int.Parse(journalProps[5])); journal.Discount = int.Parse(journalProps[6]); journal.Quantity = int.Parse(journalProps[7]); journal.Price = int.Parse(journalProps[8]); journal.Subject = journalProps[9]; return(journal); } catch (Exception e) { if (e is SqliteException || e is GenreException) { await GeneralRepository.SaveToLogFile(e.ToString()); throw new JournalException("Cant get Genre"); } else if (e is ArgumentOutOfRangeException) { await GeneralRepository.SaveToLogFile(e.ToString()); throw new JournalException("Journal Writing Error"); } else { await GeneralRepository.SaveToLogFile(e.ToString()); throw new DALException("Unknown Error"); } } }
/// <summary> /// Update a book by giving the book id and the new book data /// </summary> /// <param name="id"></param> /// <param name="updatedBook"></param> /// <returns></returns> public async Task UpdateBook(int id, Book updatedBook) { string updateQuery = "UPDATE AbstractItems " + $"Set Name = @name , Writer = @writer , PrintDate = @printDate , Publisher = @publisher , " + $"IDOfGenre = @genreID , Discount = @discount , Quantity = @quantity , Price = @price , Isbn = @isbn , " + $"Edition = @edition , Summary = @summary " + $"WHERE ItemID = @id"; try { SqliteCommand command = new SqliteCommand(updateQuery); command.Parameters.AddWithValue("@name", updatedBook.Name); command.Parameters.AddWithValue("@writer", updatedBook.Writer); command.Parameters.AddWithValue("@printDate", updatedBook.PrintDate); command.Parameters.AddWithValue("@publisher", updatedBook.Publisher); command.Parameters.AddWithValue("@genreID", updatedBook.Genre.ID); command.Parameters.AddWithValue("@discount", updatedBook.Discount); command.Parameters.AddWithValue("@quantity", updatedBook.Quantity); command.Parameters.AddWithValue("@price", updatedBook.Price); command.Parameters.AddWithValue("@isbn", updatedBook.ISBN); command.Parameters.AddWithValue("@edition", updatedBook.Edition); command.Parameters.AddWithValue("@summary", updatedBook.Summary); command.Parameters.AddWithValue("@id", id); SqlFileAccess.SqlPerformQuery(command); } catch (Exception e) { if (e is SqliteException || e is ArgumentOutOfRangeException) { await GeneralRepository.SaveToLogFile(e.ToString()); throw new BookException("Cant Update book"); } else { await GeneralRepository.SaveToLogFile(e.ToString()); throw new DALException("Unknown Error"); } } }
public async Task UpdateJournal(int id, Journal updatedJournal) { string updateQuery = "UPDATE AbstractItems " + $"Set Name = @name , Writer = @writer , PrintDate = @printDate , Publisher = @publisher , " + $"IDOfGenre = @genreID , Discount = @discount , Quantity = @quantity , Price = @price , Subject = @subject " + $"WHERE ItemID = @id"; try { SqliteCommand command = new SqliteCommand(updateQuery); command.Parameters.AddWithValue("@name", updatedJournal.Name); command.Parameters.AddWithValue("@writer", updatedJournal.Writer); command.Parameters.AddWithValue("@printDate", updatedJournal.PrintDate); command.Parameters.AddWithValue("@publisher", updatedJournal.Publisher); command.Parameters.AddWithValue("@genreID", updatedJournal.Genre.ID); command.Parameters.AddWithValue("@discount", updatedJournal.Discount); command.Parameters.AddWithValue("@quantity", updatedJournal.Quantity); command.Parameters.AddWithValue("@price", updatedJournal.Price); command.Parameters.AddWithValue("@subject", updatedJournal.Subject); command.Parameters.AddWithValue("@id", id); SqlFileAccess.SqlPerformQuery(command); } catch (Exception e) { if (e is SqliteException || e is ArgumentOutOfRangeException) { await GeneralRepository.SaveToLogFile(e.ToString()); throw new JournalException("Cant update journal"); } else { await GeneralRepository.SaveToLogFile(e.ToString()); throw new DALException("Unknown Error"); } } }
private Genre TextToGenre(string text) { try { string[] genreProps = text.Split(','); Genre genre = new Genre(); genre.ID = int.Parse(genreProps[0]); genre.Name = genreProps[1]; return(genre); } catch (Exception e) { if (e is ArgumentOutOfRangeException) { GeneralRepository.SaveToLogFile(e.ToString()); throw new GenreException("Cant convert text to genre"); } else { GeneralRepository.SaveToLogFile(e.ToString()); throw new DALException("Unknown Error"); } } }