/// <summary>
/// Process the order
/// </summary>
protected void wzdCheckOut_FinishButtonClick(object sender, WizardNavigationEventArgs e)
{
if (Profile.ShoppingCart.CartItems.Count > 0) {
if (Profile.ShoppingCart.Count > 0) {
// display ordered items
CartListOrdered.Bind(Profile.ShoppingCart.CartItems);
// display total and credit card information
ltlTotalComplete.Text = ltlTotal.Text;
ltlCreditCardComplete.Text = ltlCreditCard.Text;
// create order
OrderInfo order = new OrderInfo(int.MinValue, DateTime.Now, User.Identity.Name, GetCreditCardInfo(), billingForm.Address, shippingForm.Address, Profile.ShoppingCart.Total, Profile.ShoppingCart.GetOrderLineItems(), null);
// insert
Order newOrder = new Order();
newOrder.Insert(order);
// destroy cart
Profile.ShoppingCart.Clear();
Profile.Save();
}
}
else {
lblMsg.Text = "<p><br>Can not process the order. Your cart is empty.</p><p class=SignUpLabel><a class=linkNewUser href=Default.aspx>Continue shopping</a></p>";
wzdCheckOut.Visible = false;
}
}
/// <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>
/// 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>
/// A method to insert a new order into the system
/// As part of the order creation the inventory will be reduced by the quantity ordered
/// </summary>
/// <param name="order">All the information about the order</param>
public void Insert(OrderInfo order)
{
// Call credit card procesor
ProcessCreditCard(order);
// Insert the order (a)synchrounously based on configuration
orderInsertStrategy.Insert(order);
}
public int Insert(OrderInfo order)
{
// Get an instance of the Order DAL using the DALFactory
IOrder dal = PetShop.DALFactory.Order.Create();
// Call the insert method in the DAL to insert the header
int orderId = dal.Insert(order);
// Get an instance of the Inventory business component
Inventory inventory = new Inventory();
inventory.TakeStock( order.LineItems);
// As part of the sample application we have created a user
// you can tested distributed transactions with
// If the order has been created with the user 'Acid',
// then throw an exception which will rollback the entire transaction
if (order.UserId == ACID_USER_ID)
throw new ApplicationException(ACID_ERROR_MSG);
// Set the orderId so that it can be returned to the caller
return orderId;
}
/// <summary>
/// Process credit card and get authorization number.
/// </summary>
/// <param name="order">Order object, containing credit card information, total, etc.</param>
private void ProcessCreditCard(OrderInfo order)
{
// In the real life environment here should be a call to the credit card processor API.
// We simulate credit card processing and generate an authorization number.
Random rnd = new Random();
order.AuthorizationNumber = (int)(rnd.NextDouble() * int.MaxValue);
// Check if authorisation succeded
if (!order.AuthorizationNumber.HasValue)
throw new ApplicationException(CREDIT_CARD_ERROR_MSG);
}
/// <summary>
/// With Oracle we can send a PL/SQL block to the database and
/// maintain ACID properties for the batch of statements
/// The benefits with this is that ou increase performance by
/// reducing roundtrips to the database
/// gurantee that you only use one database connection and hence same construction costs
/// However there are limits to statement size which is based on the
/// maximum size of VARCHAR2 parameters (approx 40,000 characters)
/// </summary>
/// <param name="order">Order details</param>
/// <returns>OrderId</returns>
public void Insert(OrderInfo order)
{
int orderId = 0;
// Get the parameters
OracleParameter[] completeOrderParms = null;
OracleParameter[] orderParms = GetOrderParameters();
OracleParameter statusParm = new OracleParameter(PARM_ORDER_ID, OracleType.Number);
// Bind the parameters
orderParms[1].Value = order.UserId;
orderParms[2].Value = order.Date;
orderParms[3].Value = order.ShippingAddress.Address1;
orderParms[4].Value = order.ShippingAddress.Address2;
orderParms[5].Value = order.ShippingAddress.City;
orderParms[6].Value = order.ShippingAddress.State;
orderParms[7].Value = order.ShippingAddress.Zip;
orderParms[8].Value = order.ShippingAddress.Country;
orderParms[9].Value = order.BillingAddress.Address1;
orderParms[10].Value = order.BillingAddress.Address2;
orderParms[11].Value = order.BillingAddress.City;
orderParms[12].Value = order.BillingAddress.State;
orderParms[13].Value = order.BillingAddress.Zip;
orderParms[14].Value = order.BillingAddress.Country;
orderParms[15].Value = order.OrderTotal;
orderParms[16].Value = order.BillingAddress.FirstName;
orderParms[17].Value = order.BillingAddress.LastName;
orderParms[18].Value = order.ShippingAddress.FirstName;
orderParms[19].Value = order.ShippingAddress.LastName;
orderParms[20].Value = order.AuthorizationNumber.Value;
// Create the connection to the database
using (OracleConnection conn = new OracleConnection(OracleHelper.ConnectionStringOrderDistributedTransaction)) {
// Open the database connection
conn.Open();
// Get the order id for the order sequence
orderId = Convert.ToInt32(OracleHelper.ExecuteScalar(conn, CommandType.Text, SQL_GET_ORDERNUM));
orderParms[0].Value = orderId;
statusParm.Value = orderId;
// Total number of parameters = order parameters count + 1 + (5 * number of lines)
int numberOfParameters = orderParms.Length + 1 + (5 * order.LineItems.Length);
//Create a set of parameters
completeOrderParms = new OracleParameter[numberOfParameters];
//Copy the parameters to the execution parameters
orderParms.CopyTo(completeOrderParms, 0);
completeOrderParms[orderParms.Length] = statusParm;
//Create a batch statement
StringBuilder finalSQLQuery = new StringBuilder("BEGIN ");
// Append the order header statements
finalSQLQuery.Append(SQL_INSERT_ORDER);
finalSQLQuery.Append("; ");
finalSQLQuery.Append(SQL_INSERT_STATUS);
finalSQLQuery.Append("; ");
int index = orderParms.Length + 1;
int i = 1;
// Append each line item to the batch statement
foreach (LineItemInfo item in order.LineItems) {
//Add the appropriate parameters
completeOrderParms[index] = new OracleParameter(PARM_ORDER_ID + i, OracleType.Number);
completeOrderParms[index++].Value = orderId;
completeOrderParms[index] = new OracleParameter(PARM_LINE_NUMBER + i, OracleType.Number);
completeOrderParms[index++].Value = item.Line;
completeOrderParms[index] = new OracleParameter(PARM_ITEM_ID + i, OracleType.Char, 10);
completeOrderParms[index++].Value = item.ItemId;
completeOrderParms[index] = new OracleParameter(PARM_QUANTITY + i, OracleType.Number);
completeOrderParms[index++].Value = item.Quantity;
completeOrderParms[index] = new OracleParameter(PARM_PRICE + i, OracleType.Number);
completeOrderParms[index++].Value = item.Price;
// Append the statement to the batch
finalSQLQuery.Append(string.Format(SQL_INSERT_ITEM, i));
finalSQLQuery.Append("; ");
i++;
}
//Close the PL/SQL block
finalSQLQuery.Append("END;");
// Finally execute the query
OracleHelper.ExecuteNonQuery(conn, CommandType.Text, finalSQLQuery.ToString(), completeOrderParms);
}
}
/// <summary>
/// A method to purchase the contents of the cart
/// </summary>
/// <returns>Order object with information about the new Order</returns>
public OrderInfo PurchaseCart()
{
// Fetch the cart from session
Cart myCart = (Cart)HttpContext.Current.Session[CART_KEY];
// Make some checks on the cart
if ( ( myCart == null ) || ( myCart.Count==0 ) ) {
HttpContext.Current.Server.Transfer(URL_NOCART);
//HttpContext.Current.Response.Redirect(URL_NOCART, false);
return null;
}else{
// Build up the order
OrderInfo newOrder = new OrderInfo();
newOrder.UserId = ((AccountInfo)HttpContext.Current.Session[ACCOUNT_KEY]).UserId;
newOrder.CreditCard = (CreditCardInfo)HttpContext.Current.Session[CREDITCARD_KEY];
newOrder.BillingAddress = (AddressInfo)HttpContext.Current.Session[BILLING_KEY];
newOrder.ShippingAddress = (AddressInfo)HttpContext.Current.Session[SHIPPING_KEY];
newOrder.LineItems = (LineItemInfo[])myCart.GetOrderLineItems().ToArray(typeof(LineItemInfo));
newOrder.OrderTotal = myCart.Total;
newOrder.Date = DateTime.Now;
// Send the order to the middle tier
OrderInsert order = new OrderInsert();
newOrder.OrderId = order.Insert(newOrder);
// clear the session objects used
HttpContext.Current.Session[CART_KEY] = null;
HttpContext.Current.Session[CREDITCARD_KEY] = null;
HttpContext.Current.Session[BILLING_KEY] = null;
HttpContext.Current.Session[SHIPPING_KEY] = null;
return newOrder;
}
}
public int Insert(OrderInfo order)
{
int orderId = 0;
String strSQL = null;
try{
// Get each commands parameter arrays
SqlParameter[] orderParms = GetOrderParameters();
SqlParameter statusParm = new SqlParameter(PARM_ORDER_ID, SqlDbType.Int);
SqlCommand cmd = new SqlCommand();
// Set up the parameters
orderParms[0].Value = order.UserId;
orderParms[1].Value = order.Date;
orderParms[2].Value = order.ShippingAddress.Address1;
orderParms[3].Value = order.ShippingAddress.Address2;
orderParms[4].Value = order.ShippingAddress.City;
orderParms[5].Value = order.ShippingAddress.State;
orderParms[6].Value = order.ShippingAddress.Zip;
orderParms[7].Value = order.ShippingAddress.Country;
orderParms[8].Value = order.BillingAddress.Address1;
orderParms[9].Value = order.BillingAddress.Address2;
orderParms[10].Value = order.BillingAddress.City;
orderParms[11].Value = order.BillingAddress.State;
orderParms[12].Value = order.BillingAddress.Zip;
orderParms[13].Value = order.BillingAddress.Country;
orderParms[14].Value = order.OrderTotal;
orderParms[15].Value = order.BillingAddress.FirstName;
orderParms[16].Value = order.BillingAddress.LastName;
orderParms[17].Value = order.ShippingAddress.FirstName;
orderParms[18].Value = order.ShippingAddress.LastName;
orderParms[19].Value = order.CreditCard.CardNumber;
orderParms[20].Value = order.CreditCard.CardExpiration;
orderParms[21].Value = order.CreditCard.CardType;
foreach (SqlParameter parm in orderParms)
cmd.Parameters.Add(parm);
// Create the connection to the database
using (SqlConnection conn = new SqlConnection(SQLHelper.ConnectionString)) {
// Open the database connection
// Insert the order status
strSQL = SQL_INSERT_ORDER;
SqlParameter[] itemParms ;
// For each line item, insert an orderline record
int i = 0;
foreach (LineItemInfo item in order.LineItems) {
strSQL = strSQL + SQL_INSERT_ITEM + " @ID" + ", @LineNumber"+i + ", @ItemId" + i+ ", @Quantity" + i + ", @Price" + i + "); SELECT @ERR=@ERR+@@ERROR;";
//Get the cached parameters
itemParms = GetItemParameters(i);
itemParms[0].Value = item.Line;
itemParms[1].Value = item.ItemId;
itemParms[2].Value = item.Quantity;
itemParms[3].Value = item.Price;
//Bind each parameter
foreach (SqlParameter parm in itemParms)
cmd.Parameters.Add(parm);
i++;
}
conn.Open();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSQL + "SELECT @ID, @ERR";
// Read the output of the query, should return orderid and error count
using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)){
//Read the result
rdr.Read();
// If the error count is not zero throw an exception
if (rdr.GetInt32(1) != 0)
throw new Exception("DATA INTEGRITY ERROR ON ORDER INSERT - ROLLBACK ISSUED");
//Fetch the orderId
orderId = rdr.GetInt32(0);
}
//Clear the parameters
cmd.Parameters.Clear();
}
}catch(Exception e){
throw e;
}finally{
}
return orderId;
}
public void Insert(OrderInfo order)
{
StringBuilder strSQL = new StringBuilder();
// Get each commands parameter arrays
SqlParameter[] orderParms = GetOrderParameters();
SqlCommand cmd = new SqlCommand();
// Set up the parameters
orderParms[0].Value = order.UserId;
orderParms[1].Value = order.Date;
orderParms[2].Value = order.ShippingAddress.Address1;
orderParms[3].Value = order.ShippingAddress.Address2;
orderParms[4].Value = order.ShippingAddress.City;
orderParms[5].Value = order.ShippingAddress.State;
orderParms[6].Value = order.ShippingAddress.Zip;
orderParms[7].Value = order.ShippingAddress.Country;
orderParms[8].Value = order.BillingAddress.Address1;
orderParms[9].Value = order.BillingAddress.Address2;
orderParms[10].Value = order.BillingAddress.City;
orderParms[11].Value = order.BillingAddress.State;
orderParms[12].Value = order.BillingAddress.Zip;
orderParms[13].Value = order.BillingAddress.Country;
orderParms[14].Value = order.OrderTotal;
orderParms[15].Value = order.BillingAddress.FirstName;
orderParms[16].Value = order.BillingAddress.LastName;
orderParms[17].Value = order.ShippingAddress.FirstName;
orderParms[18].Value = order.ShippingAddress.LastName;
orderParms[19].Value = order.AuthorizationNumber.Value;
foreach (SqlParameter parm in orderParms)
cmd.Parameters.Add(parm);
// Create the connection to the database
using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringOrderDistributedTransaction)) {
// Insert the order status
strSQL.Append(SQL_INSERT_ORDER);
SqlParameter[] itemParms;
// For each line item, insert an orderline record
int i = 0;
foreach (LineItemInfo item in order.LineItems) {
strSQL.Append(SQL_INSERT_ITEM).Append(" @ID").Append(", @LineNumber").Append(i).Append(", @ItemId").Append(i).Append(", @Quantity").Append(i).Append(", @Price").Append(i).Append("); SELECT @ERR=@ERR+@@ERROR;");
//Get the cached parameters
itemParms = GetItemParameters(i);
itemParms[0].Value = item.Line;
itemParms[1].Value = item.ItemId;
itemParms[2].Value = item.Quantity;
itemParms[3].Value = item.Price;
//Bind each parameter
foreach (SqlParameter parm in itemParms)
cmd.Parameters.Add(parm);
i++;
}
conn.Open();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = strSQL.Append("SELECT @ID, @ERR").ToString();
// Read the output of the query, should return error count
using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {
// Read the returned @ERR
rdr.Read();
// If the error count is not zero throw an exception
if (rdr.GetInt32(1) != 0)
throw new ApplicationException("DATA INTEGRITY ERROR ON ORDER INSERT - ROLLBACK ISSUED");
}
//Clear the parameters
cmd.Parameters.Clear();
}
}
