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 <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");
}
}
}
/// <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");
}
}
}
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>
/// 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 <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");
}
}
}
/// <summary>
/// Get a general item by its id
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public async Task <AbstractItem> GetItemByID(int id)
{
List <AbstractItem> items = new List <AbstractItem>();
string GetItemsQuery = "SELECT ItemID, Name, Writer, PrintDate, Publisher, IDOfGenre, Discount, Quantity,Price " +
"FROM AbstractItems " +
$"WHERE ItemID = @id";
try
{
SqliteCommand command = new SqliteCommand(GetItemsQuery);
command.Parameters.AddWithValue("@id", id);
List <string> itemsString = SqlFileAccess.GetData(command);
if (itemsString.Count == 0)
{
return(null);
}
items.Add(await TextToItem(itemsString[0]));
return(items[0]);
}
catch (Exception e)
{
if (e is SqliteException || e is ArgumentOutOfRangeException)
{
await SaveToLogFile(e.ToString());
throw new GeneralItemException("Cant get Item By ID");
}
else
{
await 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");
}
}
}
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");
}
}
}
/// <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");
}
}
}
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");
}
}
}
/// <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");
}
}
}
