Ejemplo n.º 1
0
 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);
     }
 }
Ejemplo n.º 2
0
 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);
     }
 }