public IEnumerable <Product> GetAll() { string query = "SELECT * FROM Products Left Join ProviderProducts On Products.Id = ProviderProducts.Product_Id " + "Left Join Providers On ProviderProducts.Provider_Id = Providers.Id"; var products = new Dictionary <int, Product>(); using (SqlCommand command = new SqlCommand(query)) { context.CreateCommand(command); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { int id = reader.GetInt32(0); Product product; if (!products.ContainsKey(id)) { product = new Product() { Id = id, Name = reader.GetString(1), Price = reader.GetDouble(2), ProductCategoryId = reader.GetInt32(3) }; products.Add(id, product); } product = products[id]; if (reader.IsDBNull(6)) { continue; } product.Providers.Add(new Provider { Id = reader.GetInt32(6), City = reader.GetString(7), Name = reader.GetString(8) }); } } } return(products.Values.ToList()); }
public IEnumerable <ProductCategory> GetAll() { string query = "SELECT * FROM ProductCategories"; List <ProductCategory> list = null; using (SqlCommand command = new SqlCommand(query)) { context.CreateCommand(command); using (SqlDataReader reader = command.ExecuteReader()) { list = new List <ProductCategory>(); while (reader.Read()) { list.Add(new ProductCategory() { Id = reader.GetInt32(0), Name = reader.GetString(1) }); } } } return(list); }