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