public Book Get(int id) { using (var connection = DbConnectionFactory.Factory.GetConnection()) { connection.Open(); Book book = null; var command = new SqlCommand { Connection = connection, CommandText = "select * from Books where id = @id" }; command.Parameters.AddWithValue("@id", id); var reader = command.ExecuteReader(); if (reader.HasRows && reader.Read()) { GenreRepository genreRepository = new GenreRepository(); var genre = genreRepository.Get(reader.GetInt32(2)); book = new Book { Id = reader.GetInt32(0), Title = reader.GetString(1), GenreId = reader.GetInt32(2), Pages = reader.GetInt32(3), Description = reader.GetString(4), InStock = reader.GetInt32(5), Genre = genre }; #region Author2Book command = new SqlCommand { Connection = connection, CommandText = "select * from Author2Book where bookid = @id" }; command.Parameters.AddWithValue("@id", id); var reader1 = command.ExecuteReader(); List <Author2Book> author2Books = new List <Author2Book>(); if (reader1.HasRows) { while (reader1.Read()) { author2Books.Add(new Author2Book { AuthorId = reader1.GetInt32(0), //Author = new AuthorRepository().Get(reader.GetInt32(0)), BookId = reader1.GetInt32(1), //Book = book }); } } book.Author2Books = author2Books; reader1.Close(); #endregion #region BookOnHands command = new SqlCommand { Connection = connection, CommandText = "select * from BooksOnHands where bookid = @id" }; command.Parameters.AddWithValue("@id", id); reader1 = command.ExecuteReader(); List <BookOnHands> booksOnHands = new List <BookOnHands>(); if (reader1.HasRows) { while (reader1.Read()) { booksOnHands.Add(new BookOnHands { BookId = reader1.GetInt32(0), UserId = reader1.GetInt32(1) }); } } book.BooksOnHands = booksOnHands; #endregion } return(book); } }
public List <Book> GetByAuthor() { using (var connection = DbConnectionFactory.Factory.GetConnection()) { connection.Open(); List <Book> books = null; var command = new SqlCommand { Connection = connection, CommandText = "select b.id, b.title, b.genreid, b.pages, b.description, b.instock from Books b " + "join Author2Book a2b on b.id = a2b.bookid where a2b.authorid = 1" }; var reader = command.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { GenreRepository genreRepository = new GenreRepository(); var genre = genreRepository.Get(reader.GetInt32(2)); var bookId = reader.GetInt32(0); #region Author2Book command = new SqlCommand { Connection = connection, CommandText = "select * from Author2Book where bookid = @bookid" }; command.Parameters.AddWithValue("@id", bookId); var reader1 = command.ExecuteReader(); List <Author2Book> author2Books = new List <Author2Book>(); if (reader1.HasRows) { while (reader1.Read()) { author2Books.Add(new Author2Book { AuthorId = reader1.GetInt32(0), //Author = new AuthorRepository().Get(reader.GetInt32(0)), BookId = reader1.GetInt32(1), //Book = new BookRepository().Get(reader.GetInt32(1)) }); } } reader1.Close(); #endregion #region BooksOnHands command = new SqlCommand { Connection = connection, CommandText = "select * from BooksOnHands where bookid = @bookid" }; command.Parameters.AddWithValue("@id", bookId); reader1 = command.ExecuteReader(); List <BookOnHands> booksOnHands = new List <BookOnHands>(); if (reader1.HasRows) { while (reader1.Read()) { booksOnHands.Add(new BookOnHands { BookId = reader1.GetInt32(0), UserId = reader1.GetInt32(1) }); } } #endregion books.Add(new Book { Id = reader.GetInt32(0), Title = reader.GetString(1), GenreId = reader.GetInt32(2), Pages = reader.GetInt32(3), Description = reader.GetString(4), InStock = reader.GetInt32(5), Genre = genre, Author2Books = author2Books, BooksOnHands = booksOnHands }); } } return(books); } }