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