Syllabus Schedule Project Canvas Assignment 1 Assignment 2 Assignment 3 Assignment 4

Assignment 3 - Retail Public Views and Stored Procedures

Modify VS's publish settings to publish "All files in project folder." This will copy your source code to the server which will aid me in providing help and with grading. In VS right-click on the project folder, select properties, Package/Publish Web, items to deploy, "All files in this project folder."

  1. retail/Category - This page lists the products in a category.
    1. Controller: Write a LINQ statement that retrieves the products in a category. Try writing the query in two steps: joining first and filtering second. The first step joins the product and productCategory tables. See the tutorial Joining Operator: Join and search for the phrase "Join operator in query syntax C#". The example is returning fields from two tables but your query needs only fields from Product. End your query with "select p" (assuming you used p after the from statement to represent one row of db.Products). Test the join and once it is working add the filter "where pc.CategoryID == id".
    2. Controller: Add a ViewBag.message with the item count.
    3. View: The index view has the appropriate layout so reuse it using this syntax (where products is the list of products from the LINQ query):
                  return View("index", products);
  2. retail/Search - This exercise uses a stored procedure to search product titles. A stored procedure is used because the query will be used twice: once for the web page and once in the next exercise for AJAX search. 
    1. _leftMenu: add a search textbox and button (you may copy html for the sample).
    2. Stored Procedure: Use VS Server Explorer to create a new stored procedure. The input parameter is type varchar(50). The following sql statement matches whole words starting with the query (it does not match characters in the middle of words):
           SELECT *
          from Product p
          where p.ItemName like @query + '%' or p.ItemName like '% ' + @query + '%'
          order by p.ItemName;
    3. DataRepository: In theory it is easy to call a stored procedure using LINQ but it 1) requires regenerating the Entity Model to include the stored procedure and 2) it uses a drag-and-drop feature in VS that did not work for me. Dapper is a better option. Create a DataRepository and add Dapper to your project as in MIS 324 assignment 5.  To call the stored procedure with Dapper see  Is there a way to call a stored procedure with Dapper?
    4. web.config: The connection string created by EF will not work with Dapper. Create a second connection string in web.config for the RetailStore database using the format shown in MIS 324 assignment 5. Use this connection string with the search method from the previous step.
    5. Controller: Add an action method named "Search" and populate it with product information from the stored procedure. Use the ViewBag to pass the count to the view.
         List<Product> productList = retailRepository.Search(query);
        ViewBag.message = productList.Count() + " products match '" + query + "'" ;
    6. View: Reuse the index view again. Add a ViewBag to display the number of items found by the search.
  3. Retail/AjaxSearch - This exercise reuses the search stored procedure from the previous exercise to create search using AJAX. NET MVC has tools that greatly simplify support for AJAX. If you are unfamiliar with AJAX please read AJAX Introduction
    1. Controller: The controller needs an action handler that loads the AjaxSearch page. No data is passed to the view. Create an action handler named AjaxSearch() that returns an empty view.
    2. View: Create a view named AjaxSearch and paste in the source code from the working sample. This contains the search textbox and JavaScript/JQuery. The code is delineated with the comments "Start Ajax search code" and "End Ajax search code."  Older versions of VS may not recoginze template literals syntax and indicate syntax errors. You may ignore the errors.
    3. The AJAX code needs the URL of your MVC application for sending requests. The path will be different depending upon whether the code is running in VS or on the server. Use the following snippet of Razor code to get the path. Add this snippet to the AjaxSearch view right under the comments "Get application URL..."
          //Get application URL so path is correct in VS and on server.
              string RequestUrl = Url.Content("~/retail/");
          //Assign Razor data to JavaScript variable 
          URL = "@RequestUrl";
    4. The request path is the first parameter in the $.getJSON statement. Add the URL using the following syntax:

      $.getJSON(URL + 'AjaxSearchHandler/' + userinput, function (data) {

    5. Controller - The JavaScript calls an action handler named AjaxSearchHandler. This handler takes the user input, passes it to Search(string id) in the dataRepository, and generates a JSON response.  Create a second handler named AjaxSearhHandler as shown below. The JsonResult class converts List data to Json format. 
              public ActionResult AjaxSearchHandler(string id)
                  List<Product> productList = retailRepository.Search(id);
                  return Json(productList, JsonRequestBehavior.AllowGet);
    6. Add a link pointing to AjaxSearch in _leftMenu.
    7. Test!  If nothing happens when characters are typed into the textbox use Chrome's developer tools to check for 404 errors in the request and/or JavaScript errors.
  4. retail/admin (click AssignCategories) - This exercise creates an improved interface for assigning products to categories. It uses checkboxes and AJAX to communicate with the database.  The flow of data is:
    1. database: retrieves all categories and selected categories for a product.
    2. model: view model passes data from DataRepositoy to the view via the controller.
    3. view: checkboxes display selected categories.
    4. Ajax: When user clicks a checkbox the page (document) fires a change event. The event is handled by JQuery which sends a Ajax post request to the controller.
    5. controller: receives Ajax request and passes it to RetailRepository. Sends a string response if database update is successful.
    6. database: Adds or deletes individual records in ProductCategory table in response to Ajax request.
    1. Model: The view model (ProductVM) represents a single product with a List<> of all possible categories. The categories are displayed using checkboxes. The first answer by user3559349 Pass List of Checkboxes into View and Pull out IEnumerable explains how to setup the model. Use the model in the answer and replace the words "User" with "Product" and "Role" with "Category". Edit the field names to match those in your database. For instance, your database uses CategoryID rather than ID, ProductID rather than ID,, etc. Change "bool IsSelected" to "string IsChecked". Include both ItemName and ImageName for the product. Optionally fix the spelling from "Categorys" to "Categories" (although either works).
    2. RetailRepository: The repository populates the model ProductVM created in the previous step. Create an new method GetProductCategoryAssignments that takes productID as a parameter and returns ProductVM.
    3. The model needs both product and category information. I suggest getting the product information with a separate method named GetProduct(int productID). Call it using the syntax:
      Product product = GetProduct(productID);
      The only fields needed are ItemName and ImageName.
    4. Instantiate an instance of ProductVM and populate it with product information from the previous step:
      ProductVM pcVM = new ProductVM();
      pcVM.ItemName = product.ItemName;
      pcVM.ImageName = product.ImageName;
      pcVM.ProductID = productID;
    5. The next step is to populate List<CateogryVM>. The sql statement uses a left join to get all the categories from Category and productID from ProductCategory for items assigned to a category. Categories to which the product is not assigned contain a null (see image). The article Sql Left Join describes the syntax. Your query needs to display all categories so the Category table is on the left side. The answer by Taryn shows how to filter. Order by CatLabel. I suggest using VS's query tool for writing and testing your sql statement. The output should be similar to the image. 
    6. The output from the previous query needs to be modified to work with the checkbox's checked attribute. Checkbox that are checked need to include the checked attribute in the view. There many ways to achieve this. The most straightforward is to modify the sql statement to return the string "checked" for checked items. Replace ProductID in the select statement with an iif() statement:
      iif(pc.ProductID is not null,'checked', '') as IsChecked
      Test the query with VS's query tool.
    7. Controller:Create an action method to display the checkboxes:
              public ActionResult EditProductCategories(int id = 0)  //productID
                  if (id < 1) 
                      return RedirectToAction("/admin");
                  ProductVM pcVM = retailRepository.GetProductCategoryAssignments(id);
                  return View(pcVM);
    8. View: Use VS to create a view of type list. Template is Details and model is ProductVM. Product name and image name can be retrieved using the syntax @Model.ItemName. The syntax to display the list of Categories in checkboxes is:
                  <table class="table">
                      @foreach (CategoryVM cVM in Model.Categories)
                              <td style="width:20px">
                                  <input type="checkbox" name="ck1" value="@cVM.CategoryID" @cVM.IsChecked />
    9. The JQuery Ajax code can be copied from the sample. The only modifications are to add a snippet of Razor code to get the application path:
      //Get application URL so path is correct in VS and on server.
          string RequestUrl = Url.Content("~/retail/ProductCategoryAjaxHandler/");
      and add it to the Ajax request:
    10. Test. Add links to the Product/Index view pointing to EditProductCategories and pass ProductID in the querystring. Make sure the checkboxes are displaying correctly and add an image as in the sample. Check that Ajax requests are being sent by opening Chrome's developer tools and selecting the Network tab. When you click on a checkbox it should show a request being sent. If no request is sent check the Console tab to see the JavaScript error.  The request will return an error because there is no handler yet. The next steps will create a handler for the Ajax requests.
    11. Model: Create a new view model named AjaxResponseVM to handle the Ajax post request. It has three string properties: ProductID, CategoryID, and Action.
    12. RetailRepository: Add a method named AddRemoveProductCategoryAssignments(AjaxResponseVM ar) that returns void. Use an if statement to check if the action for insert or delete. Write a sql statement for each case. Execute the sql using the syntax: 
      db.Execute(sql, new { prodID = ar.ProductID, catID = ar.CategoryID});
      A return statement is not needed since no data is returned.
    13. Controller: Add an action method ProductCategoryAjaxHandler that accepts the parameter AjaxResponseVM and returns a string. Add the action verb [HttpPost]
    14. The action method calls retailRepository.AddRemoveProductCategories(ar) which updates the database. It then returns a string containing the product and category IDs. This will be displayed in the view to confirm a successful round-trip:
      return " ProductID:" + ar.ProductID + " CategoryID:" + ar.CategoryID;
    15. Test!
  5. retail/about - This page describes the site features. You may copy the text from the sample or write your own.
    1. Display an image randomly selected from the database. This article shows the syntax for retrieving a single item with Dapper. The Detail view is a good template for displaying the image.

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 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