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 utilizes many of the tools and concepts covered in MIS 324. When you have completed this project you will have a realistic e-commerce front-end with a shopping cart, checkout process, order history, and confirmation email.  You may use the CSS and HTML from the sample or create your own.

Tip: The tilde (~) character is a convenient tool for linking to files and images. The tilde points to the root folder of your application. The paths from the root to specific resources will be the same in both VS and on the server. Examples of usage:
  <img src="~/content/images/music/logo55.png" ...
 <link href="~/Content/music.css" ...
  1. index - This page displays six random items selected from the music store database. It utilizes the layout from BirdColors. Steps:
    1. Database: The music store database is located on the Yorktown server. To access it via Visual Studio off campus you will need to have a VPN connection to WWU network. VPN is not needed on campus. Add the following connection string to your web.config:

      <add name="MusicStoreSample" connectionString="Data\dsciweb;Initial Catalog=MusicStoreSample;User ID=musicReader;Password=musicReadermis324;Persist Security Info=True;" providerName="System.Data.SqlClient"/>

    2. The database tables and stored procedures may be viewed within VS Server Explorer. VS also provides a convenient interface for writing and executing sql queries with typeahead and error highlighting. database schema VS 2013 will not execute stored procedures (bug?) but Sql Server Management Studio does and is available in the PH labs.
    3. The Bird exercise is a good template for the music store project. Copy its controller replacing the name "Bird" with "Music" in the class and file names. Do not copy the model.
    4. View: Copy the entire view folder "Birds" and rename it "Music".
    5. Create a new file music.css and paste in the css from the sample site (unless you prefer to write your own).
    6. Layout - Check that the music view folder contains the file _ViewStart.cshtml. If not copy the default one from the Views folder. Edit its Layout path to point to _2ColLayout. (you might want to change this to _3ColLayout so you have a right column for enhancements).
    7. The next step is to modify BirdColors to the music store's content. Similar to BirdColors the home page of the music store will display two-columns of random items and the left-hand category menu will list music categories from the database.
    8. Model: Create a new file named MusicModels. Add three models to the file: MusicDescriptionModel, MusicStyleModel, and MusicTrackModel with the properties shown in the image.  tblStyleASIN is an associative table and does not need a model.  
    9. DataRepository: The home page will display six random music titles. Create a new repository named MusicRepository and add the code from PeopleCRUDRepository. Create a method named GetRandom(int count) to select items from the Music store database (similar to Bird). The fields needed are ASIN, Title, Artist, and Review.
    10. Controller: no changes needed to Index Action method.
    11. View: Bird/BirdColors is a good template for displaying products. Rename it to "Index"  Delete the view ShowColors since we will not need it.
    12. Change the model in Index.cshtml to DescriptionModel.
    13. Amazon provides access to its product images in three sizes: thumb, medium, and large. The index page uses the thumb size. The URL has the product ASIN concatenated into it. The following link get the image for product ASIN B00EDPFJA4.

      The syntax to display the thumb size image in an anchor tags is:

      <a href='@Url.Action("Detail/" + item.Asin)'>
           <img src=""
           class='productImage' alt="@item.Title" title="@item.Title" />

    14. Each item should have three links pointing to the detail page: product title, image, and "more..."
    15. Modify the code in index view to display six random products from the database.
    16. The next step is to test the random images. _leftMenu is not coded yet so disable it in the layout page by commenting out @Html.Action(_leftMenu()).
    17. Test /Music/index
    18. The product reviews contain HTML tags. To hide the HTML tags wrap the output with the Raw helper:


    19. Some of the reviews are quite long and need to be truncated so that at least four items are visible above the fold. Modify the sql statement to use Sql Server's substring().
    20. We will also be using the index view for search and browse, since they have the same layout. The page title, which is displayed in the browser tab, can be set in the controller using the syntax below. Remove the ViewBag.Title from the index view.

      ViewBag.Title = "MVC Music";

  2. music/detail - This page displays product details. (note: the sample site has the following optional enhancements: Highslide for displaying the large image, product title in page heading and title,  ...  Steps:
    1. DataRepository: Create a new method named GetByAsin(string id) that retrieves an item's description.
    2. Controller: Change BirdDetail to Detail and modify the code appropriately.
    3.  The Detail page that you coped from Bird is a good template for displaying music items.  This page displays Amazons medium size image and links to the large size. Syntax:

    4. Display prices: The prices need to be divided by 100 (change 1899 to 18.99) and discounted by 20%, and displayed in currency format. Use Razor syntax to do this in the view:

      @String.Format("{0:c}", Convert.ToDecimal(Model.Price / 100) * Convert.ToDecimal(0.8))

  3. music/_leftMenu - The left menu in Music is very similar to _leftMenu in Birds. It needs a method in the DataRepository, an ActionMethod in the controller, and an @Html.Action in the layout view.
    1. Next to each category name is displayed the number of items in the category. Add a third property named "ItemCount" to MusicStyleModel. The Sql statement uses group by and a join between tblStyles and tblStyleAsin as follows:
       string sql = "select s.styleID, s.styleName, count(s.styleId) as ItemCount " +
           "from tblStyles s, tblStyleAsin sa " +
           "where s.styleId = sa.StyleId " +
           "group by s.styleID, s.styleName " +
           "order by styleName";
    2. The Razor syntax to display the style name and ItemCount:
         @foreach (var style in Model)
             string tag = style.StyleName + " (" + style.ItemCount + ")";
             <li>@Html.ActionLink(@tag, "Browse", new { id = style.StyleId })</li>
  4. music/browse - Search and browse both use the index view.  Steps:
    1. DataRepository - The database contains 3,772 items. A search may return hundreds of results so we need the ability to page through the results. The Sql to implement paging is surprisingly complicated (article explaining how it is done).  The solution is to hide this complexity inside stored procedures (sql statements stored on the server). You will use two stored procedures: one for search and one for browse. The first SP is called GetMusicByStyleID and requires four parameters:
      1. styleID - Amazon's catID's from tblStyles
      2. recordsPerPage - number of items you would like returned
      3. sort - results can be sorted on the fields: title, artist, or releasedate
      4. page - which page of results returned. First page is 0, second page is 1, etc.
    2. DataRepository: Add the following method to your DataRepository:
         //stored procedure with parameters
         public List<MusicDescriptionModel> GetByStyleId(int styleID)
             DynamicParameters parms = new DynamicParameters();
             parms.Add("@styleID", styleID);
             parms.Add("@recordsPerPage", 10);
             parms.Add("@sort", "title"); //options are title, artist, releasedate
             parms.Add("@page", 0);
             using (IDbConnection db = new SqlConnection(connectString))
                 List<MusicDescriptionModel> products = db.Query<MusicDescriptionModel>("GetMusicByStyleID", parms,
                     commandType: CommandType.StoredProcedure).ToList();
                 return products;
    3. Controller: Add a handler named Browse that gets data from the dataRepository and displays it using the index view. There is no need to create a new view because index utilizes the desired layout. The syntax is:
         public ActionResult Browse(int id)
             return View("index", mr.GetByStyleId(id));
    4. View: The descriptions are too long and need to be truncated. The Sql statement is inaccessible inside the stored procedure so truncate it with Razor using the syntax below. The Math.Min function is needed because the substring function will throw an error if the length of the target string is less than the truncate length.

      @Html.Raw(@item.Review.Substring(0, Math.Min(item.Review.Length, 200)))

    5. Heading: the heading should display the category name.
  5.  music/search - This is very similar to Browse except it uses the stored procedure "SearchTitleArtist."
    1. _leftMenu: add a search textbox to the top of the menu. Copy the HTML from the sample. menu. Copy the HTML from the sample.
    2. DataRepository: Copy and paste GetByStyleID from the last exercise and modify appropriately. The parameters are the same except the first one: styleID is now query.
    3. Controller: Create a ActionResult named Browse, similar to the previous step.
    4. View: Reuse the index view, as in the previous step.
  6. header - For the final project you will need a custom logo. It is not required for this assignment but this would be a good time to add one if you have time. Keep it relatively short (height 80px or less) so that it does not push page content below the fold.

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.