Assignment 9 - Music Store - Checkout (part 2) & Order History

This week you will finish Checkout.aspx and create OrderHistory.aspx.

1. CheckOut.aspx  has three major items left to complete:

1. Insert a new record in tblOrders for each new order
2. Insert a new record in tblOrderItems for each item in the cart.
3. Create an HTML confirmation string for the browser and email.

The "ShipOrder_click." procedure handles all the steps associated with shipping the order.  To keep this procuedure to a manageable size it is a good idea to delegate the business logic to classes. The possible procedure names are listed in A08.

Create two new tables: tblOrders and tblOrderItems. OrderID in tblOrders is an autonumber field.

tblOrders
tblOrderItems

Steps for handling order information in ShipOrder_click:

1. Retrieve CustID for new Customers: CustID is needed as an input to tblOrders. For existing customers CustID was retrieved from the database and can be saved to the ViewState. It can be retrieved from the Viewstate when handling the order.
   
   For new customers the CustID is generated by the autonumber field when a new record is inserted into the database. It needs to be retrieved for writing the order information. SQL Server provides a handy function "SCOPE_IDENTITY()" that returns the value of new identity fields. A single SQL statement can be used to insert new customer data and retrieve the customer ID that is generated by the database. Add the following "scope_identity()" function to the end of the sql INSERT statement:
   
   ; select SCOPE_IDENTITY()
   
   Your DataAccess class contains a method named ExecuteScalar that is specifically designed for SQL statements that return a single data item. It is almost identical to the ExecuteNonQuery except it returns an integer. Use it to retrieve the CustID of new customers.
      
2. Retrieve Cart Items: Before handing the order information we need to determine if there are any items in the cart.
   1. Use "ListCartItems" from the shopping cart class to fill a dataTable with the items in the cart associated with the user's SessionID. A good name for the dataTable is dtCartItems. 
   2. If the cart does not contain any items (dt.Rows.Count == 0) write a brief confirmation message (i.e. "Your information has been updated.") and jump out of the ShipOrder_onClick procedure with a "return;" statement.
       
3. Write Order to database: If the cart contains items then new records need to be inserted into tblOrders and tblOrderItems. Create a new method named "WriteOrder" to handle this step. Sytnax:
       int WriteOrder(DataTable dtCartItems, int CustID) .
    
   1. Write to tblOrder: The WriteOrder(...) method use a sql INSERT statement to add a new record.  Use the "scope_identity()" function in the insert sql statement to retrieve the OrderID, as done with the custID.
       
   2. Write to tblOrderItems: Each item in the cart needs a separate record in tblOrderItems. There are a couple ways to do this:
      1. Easier method: Use a foreach loop to iterate through the dtCartItems. Construct and execute an INSERT sql statement for each row.
      2. Elegant method: A single SQL statement can be used to copy the item information from tblCart to tblOrderItems. The sql statement uses a Select statement nested inside of an Insert Into statement. The select statement retrieves the relevant records from tblCart and the Insert statement inserts them into tblOrderItems. (Nested Select (see part C))
      3. Remove the items from tblCart using any one of the following three methods:
         1. Delete the items in tblCart associated with the current SessionID.
         2. Tag the items in tblCart with an OrderID so that we know they have been shipped.
         3. Abandon the current Session using Session.Abandon(). To force a new SessionID you will also need to delete the current sessionID from the cookie using: Response.Cookies.Add(new HttpCookie("ASP.NET_SessionId", ""));
             
4. Create HTML Confirmation String: With the order written to the database you are now ready to prepare a confirmation message for the user.  From ShipOrder_onClick call a function named WriteConfirmationString(intOrderID, dtCartItems).  This function returns a string of HTML containing customer and order information. 
   
   Creating the HTML for the confirmation is tedious, but we can leverage our work by using the same HTML for both the browser confirmation and the email confirmation. The string should contain the order number, shipping address and order item information.
    
5. Send Email:  The following C# code will send an email message:
   
   

   Import the following Namespaces:

   using System.Web;
   using System.Data;
   using System.Net;
   using System.Net.Mail;
   using System.Text;

   //Send email
   
   string Body = "<body>" +
   "<h2><font color='#CC0000'>Order Confirmation from XML Music</font></h2>" +
   strConfirm + "<br />" +
   "<a href='http://yorktown.cbe.wwu.edu/sandvig/MIS324/assignments/musicstore/'><img border='0' " +
   "src='http://yorktown.cbe.wwu.edu/sandvig/mis324/assignments/musicstore/MusicImages/continue-shopping.gif' width='121' height='19'></a></p>";
   
   // Create the mail message (use your email address)
   MailMessage objMailMsg = new MailMessage("YourEmailAddressHere", ToEmail);
   
   objMailMsg.BodyEncoding = Encoding.UTF8;
   objMailMsg.Subject = "Order Confirmation from XML Music";
   objMailMsg.Body = Body;
   objMailMsg.Priority = MailPriority.Normal;
   objMailMsg.IsBodyHtml = true;
   
   //prepare to send mail via SMTP transport
   SmtpClient objSMTPClient = new SmtpClient();
   objSMTPClient.DeliveryMethod = SmtpDeliveryMethod.PickupDirectoryFromIis;

   try
   {
   objSMTPClient.Send(objMailMsg);
   }
   catch (Exception ex)
   {
   throw new Exception("Error sending email: " + ex.Message);
   }

   
   
   NOTE: To send email from your workstation you will need to turn on the SMTP (simple mail transfer protocol) server which runs under IIS (instructions). However, SMTP servers are a security risk if not configured correctly so I suggest that you do not turn it on. You can test your email code on Yorktown. If you do turn on your SMTP server make sure that it allows email to be relayed only from your workstation. Spammers LOVE open mail relays.
   

2. OrderHistory.aspx

This page is similar to SearchBrowse but with a different SQL statement. The SQL statement uses CustID to retrieve the customer's history.

There are a couple ways of getting the CustID to OrderHistory.aspx.

1. CustID as parameter in the querystring. It is easy to add a parameter the OrderHistory.aspx link that contains the CustID but we don't want curious and malicious users to change the Customer ID in the querystring and see order histories of other users. Disguise the ID by multiplying it by a large integer (5-6 digits) when writing the anchor tag on the checkout page and dividing it by the same large number in the OrderHistory page. The large number acts as a simple encryption key.
2. CustID in a session object. This has the advantage that the CustID does not need to be encrypted, but the disadvantage that it will timeout after 20 minutes and cannot be used in emails.
    

 Congratulations! You have finished your ASP.NET Music Store.