static IEnumerable<string> GetCategoriesWithProducts(SqlConnection dbConnection)
{
IList<string> categoryProductPairs = new List<string>();
dbConnection.Open();
using (dbConnection)
{
SqlCommand getCategoryProductPairsQuery = new SqlCommand(
@"SELECT c.CategoryName, p.ProductName
FROM Categories c JOIN Products p
ON p.CategoryID = c.CategoryID", dbConnection);
SqlDataReader dbInfo = getCategoryProductPairsQuery.ExecuteReader();
using (dbInfo)
{
StringBuilder categoryProductPair = new StringBuilder();
while (dbInfo.Read())
{
categoryProductPair.AppendFormat(
"Category: {0} - Product: {1}",
(string)dbInfo["CategoryName"],
(string)dbInfo["ProductName"]);
categoryProductPairs.Add(categoryProductPair.ToString());
categoryProductPair.Clear();
}
}
}
return categoryProductPairs;
}
}
