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