Syllabus Schedule Project Labs Canvas Server Access CSS Tutorial Assignment 1 Assignment 2 Assignment 3 Assignment 4 Assignment 5 Assignment 6 Assignment 7 Assignment 8 Assignment 9

Assignment 09 - Checkout II and History


The customer information was written to the database in A08. This assignment writes order information to the database, displays a order confirmation page, sends a confirmation email, and adds a history page.

  1. music/Checkout This step writes order information to the database.
    1. Database: There is a one-to-many relationship between orders and order items. Add two new tables to the Music database: tblOrders and tblOrderItems. The fields are shown in the images. OrderID is autonumber. 
    2. MusicRepository: Create a method WriteOrder(int customerModel.CustId) that writes to tblOrders and returns OrderId. The method InsertCustomer (from A08) is a good template. Sql Server's GetDate() function is a convenient way to write OrderDate.
    3. tblOrdersThe next step will use SessionID to retrieve the items in the cart and write them to tblOrderItems. SessionID is part of the System.Web namespace Add a using statement for "System.Web" near the top of MusicRepository.
    4. Create another method WriteOrderItems(int OrderId) to write orderItems to the database. Since the only data returned is "rowsAffected" a good template is CRUD AddPerson(). A single SQL statement can be used to copy item information from tblCart to tblOrderItems. The sql statement uses a Select statement nested inside of an Insert Into statement (See "sql insert sub-query"). The select statement retrieves the relevant records from tblCart and tblDescription and the Insert statement inserts them directly into tblOrderItems. Note that the 20% discount is applied in the select statement. The following statement shows the syntax and uses two parameters: @orderID and @sessionID. 
       string sql = @"INSERT INTO tblOrderItems(OrderID, ASIN, Qty, PricePaid) 
                           SELECT @orderID, c.ASIN, c.Qty, d.ListPrice * 0.8
                           FROM tblCart c, tblDescription d 
                           WHERE c.asin = d.asin 
                           and SessionId= @SessionId ";
      The Dapper syntax for passing in two parameters:
      string sessionID = System.Web.HttpContext.Current.Session.SessionID;
      int rowsAffected = db.Execute(sql, new { orderID = orderID, sessionID = sessionID });
      return rowsAffected;
    5. Controller: The HttpPost checkout handler calls the two methods above for writing order information to the database. Call the two methods Immediately after the code for writing customer information. The syntax is:
        //Write order info
        int orderID = musicRepository.WriteOrder(customerModel);
    6. Test! Add a couple of items to the cart and check that they are written correctly to tblOrders and tblOrderItems.

      tblOrders tblOrderItems

  2. music/OrderConfirmation - With the customer and order information written to the database the only tasks remaining are the order confirmations via the browser and email. This will be done by adding additional code to the Checkout Post handler in the controller.  
    1. MusicModel: The order confirmation displays both customer and order information, which is currently in two separate models. Since only one model can be passed to a view we will create a new model named MusicCheckoutModel that combines two models. Create MusicCheckoutModel follows:
       public class MusicCheckoutModel
         public int OrderID  { get; set; }
         public DateTime OrderDate { get; set; }
         public CustomerModel CustModel { get; set; }
         public List<MusicCartModel> OrderModel { get; set; }
    2. Controller: The new CheckoutModel needs to be populated with data. The Checkout Post handler already has customer data in customerModel. Assign it to the new model as follows:
      MusicCheckoutModel musicCheckoutModel = new MusicCheckoutModel();
      musicCheckoutModel.OrderID = orderID;
      musicCheckoutModel.OrderDate = DateTime.Now;
      musicCheckoutModel.CustModel = customerModel;
    3. The order information needs to be copied from the database to musicCheckoutModel. Instantiate an instance of MusicCartRepository and use the GetAllItemsInCart() method to retrieve the cart information and then assign it to musicCheckoutModel.OrderModel.
    4. The next step is to empty the shopping cart. Do this by deleting the SessionID. The data will still be in tblCart but .NET will generate a new SessionID for the user. Syntax:
         Response.Cookies.Add(new HttpCookie("ASP.NET_SessionId", ""));
    5. Pass the musicCheckoutModel to the OrderConfirmation view:
         return View("OrderConfirmation", musicCheckoutModel);
    6. View: Create a new View named OrderConfirmation, template is Details and model is MusicCheckoutModel. Call it from the controller, passing in musicCheckoutModel. You may use the HTML and CSS from the sample. Note that the sample uses HTML tables for layout rather than Bootstrap. The reason is that we will be using the same markup for both the browser confirmation and the email confirmation and email clients do not support many CSS features such as external stylesheets or float. Best practice for formatting emails is to use tables for layout (yes really).  The sample site contains an internal style sheet (near the top) in the OrderConfirmation view that you may want to copy.
    7. Visual Studio's "Add View..." wizard does not recognize properties of objects inside objects so you will need to display each property using this syntax:


    8. The order date needs to be converted from an object to a string, as follows:
      @Model.OrderDate.ToShortDateString() @Model.OrderDate.ToShortTimeString()
    9. Order Items are stored in a List<>. The foreach loop syntax to iterate through the list:

      @foreach (var item in Model.OrderModel)

    10. Test!
  3. Email  - In this step you will send an HTML email order confirmation. HTML emails support images, colors, layout, hyperlinks, etc. Most email clients do not support external style sheets so all of the styling information must be included in the body of the email.  To minimize the amount of code that needs to be written you will capture the output from the OrderConfirmation view and send it in an email.  Steps:
    1. In your Utilities folder add a class named SendEmail.cs and paste in the code from Email class. You do not need to make any changes to this file.
    2. Controller: The following code takes a view and model as inputs and returns the view's output as a string. Copy this code into your controller (near the bottom is a good location).
      private string RenderRazorViewToString(string viewName, object model)
           ViewData.Model = model;
           using (var sw = new StringWriter())
               var viewResult = ViewEngines.Engines.FindPartialView(ControllerContext, viewName);
               var viewContext = new ViewContext(ControllerContext, viewResult.View, ViewData, TempData, sw);
               viewResult.View.Render(viewContext, sw);
               viewResult.ViewEngine.ReleaseView(ControllerContext, viewResult.View);
               return sw.GetStringBuilder().ToString();
       //source: (Ben Lesh)
      (note: code needs to be in controller because needs context.)
    3. Generate the content for the email confirmation with the following statement:
       string body =  RenderRazorViewToString("OrderConfirmation", musicCheckoutModel);
    4. Send the email using the statement (use intellisense need to provide the appropriate parameters):
    5. Test!
  4. music/history  (image) - This page displays all of a customer's orders. Steps:
    1. Model: Create a new model named MusicHistoryModel. It contains properties for the seven fields in tblOrders and tblOrderItems, excluding CustID.
    2. MusicRepository - Create a method named GetHistory(string custID) that retrieves all of a customers orders and returns List<MusicHistoryModel> . The sql statement joins tblOrders and tblOrderItems. GetRandom() in MusicRepository is a good template.
    3. Controller - Create a handler named History(int id) that calls GetHistory(id) and populates the History view.
    4. View: Create a view named history. You may used the HTML/CSS from the sample site.
  5. About ; - This is a static page that explains a little about the site and lists enhancements. You may cut-and-paste from the sample and modify to your needs.
  6. You are finished with the basic music store. Congratulations!

Submission instructions: Submit assignments via the Canvas course management system. Submit the full URL for each exercise in the assignment, listing the URLs in the same order that they are listed in the assignment. To minimize typos in URLs it is strongly recommended that you copy the URLs from the address bar of the browser rather than trying to type them. Incorrect URLs will not be graded and no credit will be given.

When pages are connected via navigation (as in your music store project) it is only necessary to submit the URL of the first page.

Regular Expressions HTML Color Names Color Picker ASCII Character Codes Unicode Character Codes