public ShoppingCartDetail GetItemFromShoppingCart(int shoppingCartID, int beerId) { ShoppingCartDetail cart = null; using (var connection = OpenConnection()) { StringBuilder query = new StringBuilder(); query.Append("SELECT shoppingCartDetailsID,beerID,quantityOrdered,shoppingCartID,Created_date "); query.Append("FROM ShoppingCartDetails"); query.Append(" where shoppingCartID=@shoppingCartID and beerId=@beerId"); var command = CreateCommand(query.ToString(), connection); command.Parameters.AddWithValue("@shoppingCartID", shoppingCartID); command.Parameters.AddWithValue("@beerId", beerId); var dataReader = ExecuteCommandAsReader(command); if (dataReader.HasRows) { cart = new ShoppingCartDetail(); while (dataReader.Read()) { cart = ShoppingCartDetail.CreateEntity(dataReader); } } } return(cart); }
public ICollection <ShoppingCartDetail> GetCartItems(int customerId) { var cartItems = new List <ShoppingCartDetail>(); using (var connection = OpenConnection()) { StringBuilder query = new StringBuilder(); query.Append("SELECT shoppingCartDetailsID,ShoppingCartDetails.beerID"); query.Append(",quantityOrdered,ShoppingCartDetails.shoppingCartID"); query.Append(",ShoppingCartDetails.[Created_date],Beer.beerName,Beer.[description]"); query.Append(",Beer.price,ShoppingCart.customerID,Brewery.[description] as brewery"); query.Append(" "); query.Append("FROM ShoppingCartDetails inner join Beer on ShoppingCartDetails.beerID = Beer.beerID "); query.Append(" inner join ShoppingCart on ShoppingCartDetails.shoppingCartID = ShoppingCart.shoppingCartID"); query.Append(" inner join Brewery on Brewery.breweryID = Beer.breweryID"); query.Append(" where customerID=@customerID"); var command = CreateCommand(query.ToString(), connection); command.Parameters.AddWithValue("@customerID", customerId); var dataReader = ExecuteCommandAsReader(command); if (dataReader.HasRows) { while (dataReader.Read()) { cartItems.Add(ShoppingCartDetail.CreateEntity(dataReader, true, true)); } } } return(cartItems); }