public IEnumerable <Book> Filter([FromQuery] string author = "", string title = "", int year = 0)
{
dbHandler db = new dbHandler();
List <Book> bk = new List <Book>();
if (year == 0)
{
bk = db.loadFilters($"SELECT * FROM BOOK where LOWER(BOOK.Author) LIKE '%{author}%' AND LOWER(BOOK.Title) LIKE '%{title}%' ");
}
else
{
bk = db.loadFilters($"SELECT * FROM BOOK where LOWER(BOOK.Author) LIKE '%{author}%' AND LOWER(BOOK.Title) LIKE '%{title}%' AND Year='{year}'");
}
/*
* string sql = "";
*
* if (title != null && author != null && (year) > 0)
* {
* Console.WriteLine("Filtering YEAR, AUTHOR AND TITLE");
* sql = $"SELECT rowid, *FROM BOOK WHERE AUTHOR ='{author}' AND YEAR ={year} AND TITLE='{title}'";
* }
* else if (title != null && author != null)
* {
* Console.WriteLine("Filtering TITLE AND AUTHOR");
* sql = $"SELECT rowid,* FROM BOOK WHERE AUTHOR='{author}' and TITLE='{title}'";
* }
* else if (author != null && year > 0)
* {
* Console.WriteLine("Filtering AUTHOR and YEAR.");
* sql = $"SELECT rowid,* FROM BOOK WHERE AUTHOR='{author}' and YEAR={year}";
* }
* else if (title != null && year > 0)
* {
* Console.WriteLine("Filtering TITLE and YEAR");
* sql = $"SELECT rowid,* FROM BOOK WHERE TITLE='{title}' and YEAR={year}";
* }
* else if (title != null)
* {
* sql = $"SELECT rowid,* FROM BOOK WHERE TITLE='{title}'";
* }
* else if (author != null)
* {
* Console.WriteLine("Filtering AUTHOR");
* sql = $"SELECT rowid,* FROM BOOK WHERE AUTHOR='{author}'";
* }
* else if (year > 0)
* {
* Console.WriteLine("Filtering YEAR");
* sql = $"SELECT rowid,* FROM BOOK WHERE YEAR={year}";
* }
* else
* {
* Console.WriteLine("Filtering not set.");
*
* }
* bk = db.loadFilters(sql);
*/
return(bk);
}
public string Put(int id, [FromQuery] string author, string title, int year)
{
Book book = new Book()
{
Title = title, Author = author, Year = year
};
dbHandler db = new dbHandler();
db.EditBook(id, author, title, year);
return(book.ToString());
}
public string Post([FromQuery] string author, string title, int year)
{
Book book = new Book()
{
Title = title, Author = author, Year = year
};
dbHandler db = new dbHandler();
db.insertBook(book);
return(book.ToString());
}
public IEnumerable <Book> Get()
{
dbHandler db = new dbHandler();
var output = db.LoadData();
List <Book> bk = new List <Book>();
while (output.Read())
{
bk.Add(new Book {
Id = Convert.ToInt32(output["Id"]), Title = (output["Title"].ToString()), Author = (output["Author"].ToString()), Year = Convert.ToInt32(output["Year"])
});
}
return(bk);
}
public IEnumerable <Book> Filter([FromQuery] string author = "", string title = "", int year = 0)
{
dbHandler db = new dbHandler();
List <Book> bk = new List <Book>();
if (year == 0)
{
bk = db.loadFilters($"SELECT * FROM BOOK where LOWER(BOOK.Author) LIKE '%{author}%' AND LOWER(BOOK.Title) LIKE '%{title}%' ");
}
else
{
bk = db.loadFilters($"SELECT * FROM BOOK where LOWER(BOOK.Author) LIKE '%{author}%' AND LOWER(BOOK.Title) LIKE '%{title}%' AND Year='{year}'");
}
return(bk);
}
public void Delete(int id)
{
dbHandler db = new dbHandler();
db.DeleteRecord(id);
}
public string Get(int id)
{
dbHandler db = new dbHandler();
return(db.FindBook(id).ToString());
}