public void AddToCart(int customerId, int shoppingCartID, StandardBeer beer, int quantity)
{
using (TransactionScope scope = new TransactionScope())
{
using (var connection = OpenConnection())
{
if (shoppingCartID == -1)
{
StringBuilder shoppingCartQuery = new StringBuilder();
shoppingCartQuery.Append("insert into [ShoppingCart] (customerID,Created_date) ");
shoppingCartQuery.Append(" OUTPUT INSERTED.shoppingCartID ");
shoppingCartQuery.Append(" values(@customerID,@Created_date)");
var shoppingCartCommand = CreateCommand(shoppingCartQuery.ToString(), connection);
shoppingCartCommand.Parameters.AddWithValue("@customerID", customerId);
shoppingCartCommand.Parameters.AddWithValue("@Created_date", DateTime.Now);
shoppingCartID = this.ExecuteCommandAsScalar <Int32>(shoppingCartCommand);
}
StringBuilder shoppingCartDetailQuery = new StringBuilder();
shoppingCartDetailQuery.Append("insert into [ShoppingCartDetails] (beerID,quantityOrdered,shoppingCartID,Created_date) ");
shoppingCartDetailQuery.Append(" OUTPUT INSERTED.shoppingCartDetailsID ");
shoppingCartDetailQuery.Append(" values(@beerID,@quantityOrdered,@shoppingCartID,@Created_date)");
var shoppingCartDetailCommand = CreateCommand(shoppingCartDetailQuery.ToString(), connection);
shoppingCartDetailCommand.Parameters.AddWithValue("@beerID", beer.beerID);
shoppingCartDetailCommand.Parameters.AddWithValue("@quantityOrdered", quantity);
shoppingCartDetailCommand.Parameters.AddWithValue("@shoppingCartID", shoppingCartID);
shoppingCartDetailCommand.Parameters.AddWithValue("@Created_date", DateTime.Now);
var shoppingCartDetailID = this.ExecuteCommandAsScalar <Int32>(shoppingCartDetailCommand);
}
scope.Complete();
}
}
public List <StandardBeer> GetBeers(ICollection <int> beerId, string category)
{
var berrs = new List <StandardBeer>();
using (var connection = OpenConnection())
{
string viewName = $"v_CST4714_IB_{category}";
string query = $"select * from {viewName} WHERE BEERID in ({string.Join(",",beerId)})";
var dataReader = ExecuteQueryAsReader(query, connection);
if (dataReader.HasRows)
{
while (dataReader.Read())
{
berrs.Add(StandardBeer.CreateEntity(dataReader, category));
}
}
}
return(berrs);
}