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