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); }