public static void NumberOfBooksPerPublisher()
{
SqlDataReader reader = null;
var connection = ConnectionManager.GetConnection();
var query = "select p.[Name] ,count(b.[PublisherId]) as BooksPerPublisher from Publisher p, Book b where p.PublisherId = b.PublisherId group by b.PublisherId,p.[Name]";
SqlCommand command = new SqlCommand(query, connection);
try
{
reader = command.ExecuteReader();
List <NumberOfBooksPerPublisher> lista = new List <NumberOfBooksPerPublisher>();
while (reader.Read())
{
NumberOfBooksPerPublisher booksPerPublisher = new NumberOfBooksPerPublisher();
booksPerPublisher.PublisherName = (string)reader["Name"];
booksPerPublisher.NoOfBooks = (int)reader["BooksPerPublisher"];
lista.Add(booksPerPublisher);
}
foreach (var item in lista)
{
Console.WriteLine($"{item.PublisherName} {item.NoOfBooks}");
}
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
public List <NumberOfBooksPerPublisher> booksForPublisher(string query)
{
var connection = ConnectionManager.GetConnection();
connection.Open();
List <NumberOfBooksPerPublisher> publishersBooks = new List <NumberOfBooksPerPublisher>();
try
{
SqlCommand comm = new SqlCommand(query, connection);
var dataReader = comm.ExecuteReader();
while (dataReader.Read())
{
var currentRow = dataReader;
NumberOfBooksPerPublisher publisherBook = new NumberOfBooksPerPublisher();
publisherBook.PublisherName = currentRow["Name"].ToString();
publisherBook.priceForBooks = currentRow["bookPrice"] as Decimal? ?? default(int);
publisherBook.NoOfBooks = currentRow["bookCount"] as int? ?? 0;
publishersBooks.Add(publisherBook);
}
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
}
connection.Close();
return(publishersBooks);
}
public List <NumberOfBooksPerPublisher> GetBooksPerPublisher()
{
List <NumberOfBooksPerPublisher> list = new List <NumberOfBooksPerPublisher>();
SqlConnection conn = DBConnection.GetConnection();
try
{
string query = "select count(b.bookid) NoOfBooks, name " +
" from publisher p inner join book b on b.publisherid = p.publisherid group by p.name";
SqlCommand command = new SqlCommand(query, conn);
SqlDataReader rdr = command.ExecuteReader();
while (rdr.Read())
{
var pair = new NumberOfBooksPerPublisher();
pair.NoOfBooks = rdr["NoOfBooks"] as int? ?? default(int);
pair.PublisherName = rdr["name"].ToString();
list.Add(pair);
}
rdr.Close();
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
}
return(list);
}
public List <NumberOfBooksPerPublisher> GetNumberOfBooksPerPublishers()
{
List <NumberOfBooksPerPublisher> pubBooks = new List <NumberOfBooksPerPublisher>();
try
{
var querry = "select [Name] ,(select count(BookId) from Book where Publisher.PublisherId=Book.PublisherId) as Nr from Publisher";
//var connection = ConnectionManager.GetConnection();
SqlCommand command = new SqlCommand(querry, connection);
SqlDataReader dataReader = command.ExecuteReader();
while (dataReader.Read())
{
var currentRow = dataReader;
NumberOfBooksPerPublisher publisher = new NumberOfBooksPerPublisher();
publisher.publisher.Name = currentRow["Name"].ToString();
publisher.nrBooks = currentRow["Nr"] as int? ?? 0;
pubBooks.Add(publisher);
}
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
}
return(pubBooks);
}
public List <NumberOfBooksPerPublisher> AllBooksForEachPublisher()
{
SqlConnection con = ConnectionManager.GetConnection();
try
{
var q =
"SELECT Name, COUNT(BookId) AS NrOfBooks FROM Publisher join Book on Book.PublisherId=Publisher.PublisherId group by (Name)";
;
using (cmd)
{
cmd = new SqlCommand(q, con);
using (reader = cmd.ExecuteReader())
{
while (reader.Read())
{
NumberOfBooksPerPublisher pub = new NumberOfBooksPerPublisher();
pub.NoOfBooks = (int)reader["NrOfBooks"];
pub.PublisherName = reader["Name"] as string;
numberOfBooksPerPublishers.Add(pub);
}
}
return(numberOfBooksPerPublishers);
}
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
finally
{
// con.Close();
}
}
