public List <BooksPerPublisher> GetNumberOfBooksPerPublisher()
{
string query = @"SELECT p.[Name], Count(*) as TotalBooks FROM [Homework9.1].[dbo].[Book] b
INNER JOIN [Homework9.1].[dbo].[Publisher] p
ON b.[PublisherId]=p.[PublisherId]
GROUP BY p.[Name]";
List <BooksPerPublisher> lista = new List <BooksPerPublisher>();
try
{
var conn = ConnectionManager.GetConnection();
if (conn.State == ConnectionState.Closed)
{
conn = ConnectionManager.OpenConnection(conn);
}
SqlCommand command = new SqlCommand(query, conn);
SqlDataReader read = command.ExecuteReader();
while (read.Read())
{
BooksPerPublisher numberPerPub = new BooksPerPublisher();
var rowread = read;
numberPerPub.PublisherName = rowread["Name"].ToString();
numberPerPub.NumberOfBooks = (int)rowread["TotalBooks"] as int? ?? default(int);
Console.WriteLine($"{numberPerPub.PublisherName} - number of books: {numberPerPub.NumberOfBooks}");
BooksPerPublisherManager.AddToList(numberPerPub);
}
lista = BooksPerPublisher.NumberOfBooksPerPublisherList;
Console.WriteLine("NumberOfBooksPerPublisherList from NumberOfBooksPerPublisher class");
foreach (var item in lista)
{
Console.WriteLine(item.PublisherName + " " + item.NumberOfBooks);
}
read.Close();
conn.Close();
return(lista);
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
}
return(lista);
}
public static void AddToList(BooksPerPublisher val)
{
bool gasit = false;
foreach (var item in BooksPerPublisher.NumberOfBooksPerPublisherList)
{
if (item.PublisherName == val.PublisherName)
{
gasit = true;
if (item.NumberOfBooks != val.NumberOfBooks)
{
item.NumberOfBooks = val.NumberOfBooks;
}
break;
}
}
if (gasit == false)
{
BooksPerPublisher.NumberOfBooksPerPublisherList.Add(val);
}
}
