public void SaveCart(ShoppingCart cart) { //using the Orders/OrderItems tables to persist the car //with the idea that a cart is an order that hasn't checked out //yet. This is a DB-centric decision, our model separates this //concept. StatusID 99 marks an order as "Not Checked Out" List <DbCommand> commands = new List <DbCommand>(); Guid orderID = Guid.NewGuid(); orderID = GetCartID(cart.UserName); var settings = new Dictionary <string, object>(); if (cart.BillingAddress != null) { settings.Add(OrdersTable.Columns.BillingAddressID, cart.BillingAddress.AddressID); } if (cart.ShippingAddress != null) { settings.Add(OrdersTable.Columns.ShippingAddressID, cart.ShippingAddress.AddressID); } settings.Add(OrdersTable.Columns.ShippingService, cart.ShippingService); settings.Add(OrdersTable.Columns.ShippingAmount, cart.ShippingAmount); settings.Add(OrdersTable.Columns.TaxAmount, cart.TaxAmount); //save down any address (billing/shipping), selected shipping, and tax info var sql = OrdersTable.Update(settings) .Where(OrdersTable.Columns.OrderID, orderID); commands.Add(sql.BuildCommand()); //remove the existing items commands.Add(OrderItemsTable.Delete().Where(OrderItemsTable.Columns.OrderID, orderID).BuildCommand()); //save each item in the cart foreach (var item in cart.Items) { var itemSql = OrderItemsTable.Insert(new Dictionary <string, object>() { { OrderItemsTable.Columns.SKU, item.Product.SKU }, { OrderItemsTable.Columns.OrderID, orderID }, { OrderItemsTable.Columns.Quantity, item.Quantity }, { OrderItemsTable.Columns.DateAdded, item.DateAdded }, { OrderItemsTable.Columns.Discount, item.Discount }, { OrderItemsTable.Columns.DiscountReason, item.DiscountReason } }); commands.Add(itemSql.BuildCommand()); } //all in one, nice transaction BOOYAH! SqlHelper.Execute(commands, connectionStringName); }
public void ReadWrite_Enum() { using (var db = FakeBookingSystem.Database.Mem.Database.Open()) { var status = BookingStatus.CustomerCancelled; var testOrderItem = new OrderItemsTable() { Status = status }; var testOrderItemId = db.Insert(testOrderItem, true); OrderItemsTable orderItem = db.SingleById <OrderItemsTable>(testOrderItemId); Assert.Equal(status, orderItem.Status); } }
public Order GetOrder(Guid orderID) { SimpleProductRepository productRepository = new SimpleProductRepository(); SimpleCustomerRepository customerRepository = new SimpleCustomerRepository(); Order result = null; var batch = new BatchSql(); batch.Append(OrdersTable.Select().Where("OrderID", orderID)); //items //products for the items var sql = new SqlStatement(connectionStringName); sql.Add("SELECT "); sql.Add(ProductsTable.COLUMN_LIST); sql.Add(OrderItemsTable.COLUMN_LIST); sql.Add("FROM Products INNER JOIN OrderItems ON Products.SKU = OrderItems.SKU"); sql.Add("WHERE SKU IN (SELECT SKU FROM OrderItems WHERE OrderID=@OrderID)"); //transactions batch.Append(TransactionsTable.Select().Where("orderid", orderID)); int shippingAddressID = 0; int billingAddressID = 0; int shippingMethodID = 0; //pull it var cmd = sql.BuildCommand(); using (var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { if (rdr.Read()) { result = new Order(OrdersTable.ReadOrderStatusID(rdr)) { DateCreated = OrdersTable.ReadCreatedOn(rdr), DateShipped = OrdersTable.ReadDateShipped(rdr), UserName = OrdersTable.ReadUserName(rdr), DiscountAmount = OrdersTable.ReadDiscountAmount(rdr), DiscountReason = OrdersTable.ReadDiscountReason(rdr), EstimatedDelivery = OrdersTable.ReadEstimatedDelivery(rdr), ID = orderID, OrderNumber = OrdersTable.ReadOrderNumber(rdr), ShippingAmount = OrdersTable.ReadShippingAmount(rdr), ShippingService = OrdersTable.ReadShippingService(rdr), TaxAmount = OrdersTable.ReadTaxAmount(rdr), }; billingAddressID = OrdersTable.ReadBillingAddressID(rdr); shippingAddressID = OrdersTable.ReadShippingAddressID(rdr); } //load the items result.Items = new List <OrderLine>(); if (rdr.NextResult()) { while (rdr.Read()) { var product = productRepository.LoadProduct(rdr); var item = new OrderLine(OrderItemsTable.ReadDateAdded(rdr), OrderItemsTable.ReadQuantity(rdr), product); result.Items.Add(item); } } //transactions result.Transactions = new List <Transaction>(); if (rdr.NextResult()) { while (rdr.Read()) { Transaction t = new Transaction( TransactionsTable.ReadTransactionID(rdr), orderID, TransactionsTable.ReadAmount(rdr), TransactionsTable.ReadTransactionDate(rdr), TransactionsTable.ReadAuthorizationCode(rdr), TransactionsTable.ReadNotes(rdr), TransactionsTable.ReadProcessor(rdr)); result.Transactions.Add(t); } } } sql = new SqlStatement(connectionStringName); //addresses batch.Append(AddressesTable.Select().Where("addressid", shippingAddressID)); batch.Append(AddressesTable.Select().Where("addressid", billingAddressID)); //shipping method batch.Append(ShippingMethodsTable.Select().Where("shippingmethodid", shippingMethodID)); cmd = batch.BuildCommand(connectionStringName); using (var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { //shipping address if (rdr.Read()) { //shipping result.ShippingAddress = customerRepository.LoadAddress(rdr); } //billing address if (rdr.NextResult()) { if (rdr.Read()) { result.BillingAddress = customerRepository.LoadAddress(rdr); } } //shipping method if (rdr.NextResult()) { if (rdr.Read()) { LoadShipping(rdr); } } } return(result); }