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 7 - Music Store part 1

The music store project integrates the tools and concepts covered in MIS 324: models, views, controllers, C#, Sql Server database, layouts, partial views, and cookies. When you have completed this project you will have a realistic e-commerce front-end with a shopping cart, checkout process, order history, and order confirmation.

Parameter naming: The default routing includes an optional parameter named "id". We have not defined any additional routes so "id" is the only parameter name recognized without an explicit identifier. For example, the URL "/music/Detail/B0068CEGT0" is retrieved in the controller with the parameter "id":
public ActionResult Detail(string id) A parameter with a different name could be used but its name must be specified in the request. For instance the URL "/music/Detail?asin=B0068CEGT0" would be retrieved in the controller with: public ActionResult Detail(string asin)

Highslide JS
Music ER diagram
Music ER diagram (click to enlarge)
  1. music/index - This page displays six random items selected from the music store database.
    1. Database: Create a new Sql Server database named MusicStore. Use "New Query" in Visual Studio to execute the following SQL script: musicDb.txt (paste the sql into VS and click "Execute.")  These scripts will create five database tables and populate them with data.  tblDescription contains descriptions for 80 music albums. tblSytles contains style names and styleIDs. tblStyleAsin is an associative table that describes the relationship between music items and styles.  The other two tables tblTracks and tblSimilarProducts are for optional enhancements.
    2. web.config - Add two more connection strings for the MusicStore database.
    3. Model - Create a new model "MusicModels". Add a class named MusicItemModel with properties matching tblDescription. Add a second model named MusicStyleModel with properties StyleId and StyleName (this is for the left browse menu). 
    4. DataRepository - Copy CrudRepository and name it MusicRepository. Create a method named GetRandom() that selects six random records from tblDescription (similar to GetRandom() in BirdView). 
    5. The left menu also needs to be populated. Copy GetColorCategories() from BirdView and name it GetMusicCategories. Modify the sql to query tblStyles. There are a few styles in tblStyles for which there are no items. Similar to BirdView/Browse (step 1 l) the sql statement needs a join (on StyleId).  The menu should display only top level styles, which have parentStyleID = 0. Modify the search sql statement to use MusicStyleModel and modify the other field names as needed to match MusicStyleModel.
    6. Controller - Copy BirdViewController and name it MusicController. Use "replace all" to modify it to use MusicRepository. Comment out all the action methods except index() and _leftMenu(). The image below shows all of the action methods for the finished controller.
    7. View - index, search, and browse all have the same layout so we can use the same view. The following html & Razor code displays the music items. Create a view named index.cshtml and add the following code:
      @model IEnumerable<Mis324Assignments.Models.MusicItemModel>
      @using System.Text.RegularExpressions
      <h3 class="text-center">@ViewBag.message</h3>
      <div class="row">
          @if (Model != null)
              foreach (var item in Model)
                  <div class="col-md-6 productContainerThumb">
                      <a href="~/music/details/@item.ASIN">
                          <div class="productTitle">@item.Title</div>
                      <div>by <span class="productArtist">@item.Artists </span></div>
                      <a href="~/music/details/@item.ASIN">
                          <img src=""
                               class='productImage' alt="@item.Title" title="@item.Title" />
                      <div class="productDescription">
                          @if (item.EditorialReviews != null)
                              //Remove HTML tags using regular expression (unclosed HTML tags can
                              //mess up page layout.)
                              item.EditorialReviews = Regex.Replace(item.EditorialReviews, "<[^>]*>", String.Empty);
                              <a href="~/music/details/@item.ASIN">
    8. _leftMenu partial view - The left menu displays the music styles from tblStyles. This is similar to _leftMenu in the BirdView exercise except that it uses MusicStyleModel instead of ColorModel.
    9. Test
  2. Highslide JS
    Music Controller
    Music Controller (click to enlarge)
    Styling - Upgrade the styling. 
    1. Create a new CSS file named music.css and paste in the CSS from the sample site (unless you prefer to write your own). Modify the CSS tag in _Layout to point to the new CSS file.
    2. Edit the HTML in _Layout to achieve the look that you desire. (Tip: The inspection feature in Chrome's developer tools is a convenient way to see the styles that have been used in the sample site.)
    3. Links: Change the header to include links to about, cart, and account. The logo should link to index. Links should use the tilde (~) to point to the application root. For instance a link with this format in the browser:
      uses this format in the MVC View:
      The tilde points to the application root both in VS and on the server.
    4. The index view will be used for index, search and browse. Each page should have an appropriate heading passed from the controller using @ViewBag.message.  In the controller action methods assign an appropriate heading for the index page.
    5. The page title can also be set in the controller. Remove ViewBag.Title from index view and add the following to the index action handler in the controller.

      ViewBag.Title = "MVC Music";

  3.  music/browse - The browse page takes styleId as an input parameter and displays appropriate music albums. The sql statement is similar to the browse statement in BirdView.  
    1. Many of the editorial reviews are quite long. So that the user can see multiple items above the fold you want to truncate the editorial reviews. The easiest way to do this is to use the sql substring statement similar to this:

      @"select d.ASIN, title, artists, substring(EditorialReviews, 0, 200) as EditorialReviews ...

    2. Browse uses the index view. Add ViewBag.message to the controller with an appropriate heading for the page.
  4. music/details -  The details view displays one item based upon ASIN.  It includes item details and the image links to a larger version of the image.
    1. You have a choice about whether to use a List<MusicItemModel> for retrieving and displaying the data, similar to index and browse, or to use an instance of MusicItemModel, similar to GetOneBird in A06 or GetOnePerson in A05.  Both approaches are equally good. The following steps assume the use of a List<>.
    2. MusicRepository: Create a method named Details(string asin) that retrieves a List<MusicItemModel>. The browse method is a good template.
    3. Controller: Add an action method named browse that passes ASIN to the musicRepository. The browse action method is a good template.
    4. View: Create a details view named details. You can copy index view and add the additional fields or use VS to create a new List view and add appropriate formatting.
    5. All music is sold with a 20% discount. tblDescription includes a field named "ListPrice" that is the full item price. The detail page displays the ListPrice, discounted price, and "savings." Use Razor syntax to calculate "Our Price" and "You Save" from ListPrice. Syntax is similar to the following:
        <b>Our Price: </b><span class="priceOurs">@String.Format("{0:c}", item.ListPrice / 100 * Convert.ToDouble(0.8))</span>
    6. Test!
  5. music/search - The search page takes a user query as an input parameter and displays matching products.  The query searches music titles and artists for matching items.
    1. The sql statement is similar to the search sql in BirdView. Modify it to use tblDescription.
    2. Use the index view, passing in an appropriate heading.
  6. Missing parameters: Search, browse, and details all require input parameters and will throw exceptions if not provided. Handle this scenario by adding the following statement near the top of the three action methods:
    if (id == null) return RedirectToAction("index");
  7. AutoClose - Sql Server keeps database connections open to optimize performance. However this locks the .mdf file on the server making it difficult to delete or replace it. The solution is to turn on the database's autoclose feature. This only needs to be done once on a database. The code template for the controller has an action method named AutoClose which calls a method with the same name located in the data repository. Call this action method once with by typing the following into the query string:


    In a production database autoclose should be off to optimize performance.

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