// public void AddAuthorToBook(Book theBook) // { // MySqlConnection conn = DB.Connection(); // conn.Open(); // MySqlCommand cmd = conn.CreateCommand() as MySqlCommand; // cmd.CommandText = @"SELECT * FROM books // JOIN books_authors ON (authors.Id = books_authors.author_id) // JOIN authors ON (books_authors.book_id = books.Id) // WHERE books.Id = @"+theBook.GetId()+";"; // cmd.ExecuteNonQuery(); // conn.Close(); // if (conn != null) // { // conn.Dispose(); // } // // } public static Book Find(int check) { Book book = new Book(); MySqlConnection conn = DB.Connection(); conn.Open(); MySqlCommand cmd = conn.CreateCommand() as MySqlCommand; cmd.CommandText = @"SELECT * FROM books where id = " + check + ";"; MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader; //nested if in a while loop to eliminate an error that was saying "Read must be done first" while (rdr.Read()) { if (rdr.IsDBNull(0) == false) { book.SetId(rdr.GetInt32(0)); book.SetTitle(rdr.GetString(1)); // book.SetCopies(rdr.Getint(2)); } } conn.Close(); if (conn != null) { conn.Dispose(); } return(book); }
public static List <Book> GetAll() { List <Book> allBooks = new List <Book> { }; MySqlConnection conn = DB.Connection(); conn.Open(); MySqlCommand cmd = conn.CreateCommand() as MySqlCommand; cmd.CommandText = @"SELECT * FROM books;"; MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader; while (rdr.Read()) { Book newBook = new Book(); newBook.SetId(rdr.GetInt32(0)); newBook.SetTitle(rdr.GetString(1)); // newBook.SetCopies(rdr.GetInt32(2)); allBooks.Add(newBook); } conn.Close(); if (conn != null) { conn.Dispose(); } return(allBooks); }
public List <Book> GetBooks() { MySqlConnection conn = DB.Connection(); conn.Open(); MySqlCommand cmd = conn.CreateCommand() as MySqlCommand; cmd.CommandText = @"SELECT books.* FROM authors JOIN books_authors ON (authors.id = books_authors.author_id) JOIN books ON (books_authors.book_id = books.id) WHERE authors.id = @AuthorId;"; MySqlParameter authorIdParameter = new MySqlParameter(); authorIdParameter.ParameterName = "@AuthorId"; authorIdParameter.Value = _id; cmd.Parameters.Add(authorIdParameter); MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader; List <Book> books = new List <Book> { }; while (rdr.Read()) { // int bookId = rdr.GetInt32(0); // string bookTitle = rdr.GetString(1); Book book = new Book(); book.SetId(rdr.GetInt32(0)); book.SetTitle(rdr.GetString(1)); books.Add(book); } conn.Close(); if (conn != null) { conn.Dispose(); } return(books); }