/// <summary> /// Read an order from the database /// </summary> /// <param name="orderId">Order Id</param> /// <returns>Details of the Order</returns> public OrderInfo GetOrder(int orderId) { //Create a parameter OracleParameter parm = new OracleParameter(PARM_ORDER_ID, OracleType.Number); parm.Value = orderId; //Execute a query to read the order using (OracleDataReader rdr = OracleHelper.ExecuteReader(OracleHelper.ConnectionStringOrderDistributedTransaction, CommandType.Text, SQL_SELECT_ORDER, parm)) { if (rdr.Read()) { //Generate an order header from the first row AddressInfo billingAddress = new AddressInfo(rdr.GetString(5), rdr.GetString(6), rdr.GetString(7), rdr.GetString(8), rdr.GetString(9), rdr.GetString(10), rdr.GetString(11), rdr.GetString(12), null, "email"); AddressInfo shippingAddress = new AddressInfo(rdr.GetString(13), rdr.GetString(14), rdr.GetString(15), rdr.GetString(16), rdr.GetString(17), rdr.GetString(18), rdr.GetString(19), rdr.GetString(20), null, "email"); OrderInfo order = new OrderInfo(orderId, rdr.GetDateTime(0), rdr.GetString(1), null, billingAddress, shippingAddress, rdr.GetDecimal(21), null, null); IList<LineItemInfo> lineItems = new List<LineItemInfo>(); LineItemInfo item = null; //Create the lineitems from the first row and subsequent rows do { item = new LineItemInfo(rdr.GetString(22), string.Empty, rdr.GetInt32(23), rdr.GetInt32(24), rdr.GetDecimal(25)); lineItems.Add(item); } while (rdr.Read()); order.LineItems = new LineItemInfo[lineItems.Count]; lineItems.CopyTo(order.LineItems, 0); return order; } } return null; }
/// <summary> /// Read an order from the database /// </summary> /// <param name="orderId"></param> /// <returns></returns> public OrderInfo GetOrder(int orderId) { //Create a parameter SqlParameter parm = new SqlParameter(PARM_ORDER_ID, SqlDbType.Int); parm.Value = orderId; //Execute a query to read the order using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.ConnectionString, CommandType.Text, SQL_SELECT_ORDER, parm)) { if (rdr.Read()) { //Generate an order header from the first row CreditCardInfo creditCard = new CreditCardInfo(rdr.GetString(2), rdr.GetString(3), rdr.GetString(4)); AddressInfo billingAddress = new AddressInfo(rdr.GetString(5), rdr.GetString(6), rdr.GetString(7), rdr.GetString(8), rdr.GetString(9), rdr.GetString(10), rdr.GetString(11), rdr.GetString(12), null); AddressInfo shippingAddress = new AddressInfo(rdr.GetString(13), rdr.GetString(14), rdr.GetString(15), rdr.GetString(16), rdr.GetString(17), rdr.GetString(18), rdr.GetString(19), rdr.GetString(20), null); OrderInfo order = new OrderInfo(orderId, rdr.GetDateTime(0), rdr.GetString(1), creditCard, billingAddress, shippingAddress, rdr.GetDecimal(21)); ArrayList lineItems = new ArrayList(); LineItemInfo item = null; //Create the lineitems from the first row and subsequent rows do{ item = new LineItemInfo(rdr.GetString(22), string.Empty, rdr.GetInt32(23), rdr.GetInt32(24), rdr.GetDecimal(25)); lineItems.Add(item); }while(rdr.Read()); order.LineItems = (LineItemInfo[])lineItems.ToArray(typeof(LineItemInfo)); return order; } } return null; }
/// <summary> /// Reduce the current quantity in stock for an order's lineitems /// </summary> /// <param name="items">An array of order line items</param> public void TakeStock(LineItemInfo[] items) { // Get an instance of the Inventory DAL using the DALFactory IInventory dalc = PetShop.DALFactory.Inventory.Create(); // Reduce the stock level in the data store dalc.TakeStock(items); }
/// <summary> /// Constructor with specified initial values /// </summary> /// <param name="orderId">Unique identifier</param> /// <param name="date">Order date</param> /// <param name="userId">User placing order</param> /// <param name="creditCard">Credit card used for order</param> /// <param name="billing">Billing address for the order</param> /// <param name="shipping">Shipping address for the order</param> /// <param name="total">Order total value</param> /// <param name="line">Ordered items</param> /// <param name="authorization">Credit card authorization number</param> public OrderInfo(int orderId, DateTime date, string userId, CreditCardInfo creditCard, AddressInfo billing, AddressInfo shipping, decimal total, LineItemInfo[] line, Nullable<int> authorization) { this.orderId = orderId; this.date = date; this.userId = userId; this.creditCard = creditCard; this.billingAddress = billing; this.shippingAddress = shipping; this.orderTotal = total; this.lineItems = line; this.authorizationNumber = authorization; }
/// <summary> /// Function to update inventory based on purchased items /// Internally the function uses a batch query so the command is only sent to the database once /// </summary> /// <param name="items">Array of items purchased</param> public void TakeStock(LineItemInfo[] items) { SqlParameter[] inventoryParms; SqlCommand cmd = new SqlCommand(); //Open a connection using (SqlConnection conn = new SqlConnection(SQLHelper.ConnectionString)) { String strSQL = null; int i = 0; //Append a statement to the batch for each item in the array foreach (LineItemInfo item in items) { strSQL = strSQL + SQL_TAKE_INVENTORY; inventoryParms = GetInventoryParameters(i); strSQL = strSQL + "@Quantity" +i + " WHERE ItemId = @ItemId" + i+ ";"; //Bind parameters inventoryParms[0].Value = item.Quantity; inventoryParms[1].Value = item.ItemId; foreach (SqlParameter parm in inventoryParms) cmd.Parameters.Add(parm); i++; } // Open the connection conn.Open(); //Set up the command cmd.Connection = conn; cmd.CommandType = CommandType.Text; cmd.CommandText = strSQL; //Execute the query cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } }
/// <summary> /// Function to update inventory based on purchased items /// Internally the function uses a batch query so the command is only sent to the database once /// </summary> /// <param name="items">Array of items purchased</param> public void TakeStock(LineItemInfo[] items) { // Total number of parameters = (2 * number of lines) int numberOfParameters = 2 * items.Length; // Create a parameters array based on the number of items in the array OracleParameter[] completeOrderParms = new OracleParameter[numberOfParameters]; // Create a string builder to hold the entire query // Start the PL/SQL block StringBuilder finalSQLQuery = new StringBuilder("BEGIN "); int index = 0; int i = 1; // go through each item and bind parametes for the batch statement foreach (LineItemInfo item in items) { completeOrderParms[index] = new OracleParameter(":Quantity" + i, OracleType.Number); completeOrderParms[index++].Value = item.Quantity; completeOrderParms[index] = new OracleParameter(":ItemId" + i, OracleType.Char, 10); completeOrderParms[index++].Value = item.ItemId; // Append the current item query to the batch statement finalSQLQuery.Append(string.Format(SQL_TAKE_INVENTORY, i)); finalSQLQuery.Append("; "); i++; } // Close the PL/SQL block finalSQLQuery.Append("END;"); // Finally execute the query OracleHelper.ExecuteNonQuery(OracleHelper.ConnectionStringInventoryDistributedTransaction, CommandType.Text, finalSQLQuery.ToString(), completeOrderParms); }
/// <summary> /// Method to convert all cart items to order line items /// </summary> /// <returns>A new array of order line items</returns> public LineItemInfo[] GetOrderLineItems() { LineItemInfo[] orderLineItems = new LineItemInfo[cartItems.Count]; int lineNum = 0; foreach (CartItemInfo item in cartItems.Values) orderLineItems[lineNum] = new LineItemInfo(item.ItemId, item.Name, ++lineNum, item.Quantity, item.Price); return orderLineItems; }
/// <summary> /// Reduce the current quantity in stock for an order's lineitems /// </summary> /// <param name="items">An array of order line items</param> public void TakeStock(LineItemInfo[] items) { // Reduce the stock level in the data store dal.TakeStock(items); }