public static BookClass GetBookByTitle(string title)
{
MySqlConnection conn = DB.Connection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT * FROM books WHERE title = '" + title + "';";
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
int id = 0;
string _title = "This Book Does Not Exist in the Database!";
while (rdr.Read())
{
id = rdr.GetInt32(0);
_title = rdr.GetString(1);
}
conn.Close();
if (conn != null)
{
conn.Dispose();
}
BookClass book = new BookClass(_title, id);
return(book);
}
public static List <BookClass> GetBooksByAuthorId(int authorId)
{
List <BookClass> books = new List <BookClass> {
};
MySqlConnection conn = DB.Connection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT books.* FROM books
JOIN authors_books ON (books.id = authors_books.book_id)
JOIN authors ON (authors_books.author_id = authors.id)
WHERE authors.id = " + authorId + ";";
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
while (rdr.Read())
{
int id = rdr.GetInt32(0);
string title = rdr.GetString(1);
BookClass book = new BookClass(title, id);
books.Add(book);
}
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(books);
}
public static List <BookClass> GetAll()
{
List <BookClass> allBooks = new List <BookClass> {
};
MySqlConnection conn = DB.Connection();
conn.Open();
var cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT * FROM books;";
var rdr = cmd.ExecuteReader() as MySqlDataReader;
while (rdr.Read())
{
int id = rdr.GetInt32(0);
string title = rdr.GetString(1);
BookClass newBook = new BookClass(title, id);
allBooks.Add(newBook);
}
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(allBooks);
}
public static List <BookClass> GetBooksByPatronId(int patronId)
{
List <BookClass> allBooks = new List <BookClass> {
};
MySqlConnection conn = DB.Connection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT books.* FROM books
JOIN patrons_copies ON (books.id = patrons_copies.book_id)
JOIN patrons ON (patrons_copies.patron_id = patrons.id)
WHERE patrons.id = " + patronId + ";";
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
while (rdr.Read())
{
int id = rdr.GetInt32(0);
string _title = rdr.GetString(1);
BookClass newBook = new BookClass(_title, id);
allBooks.Add(newBook);
}
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(allBooks);
}
