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 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 is is easy to call a stored procedure using LINQ but 1) it 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. You may choose whether to use LINQ or Dapper. If you use Dapper you will need to 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. 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 + "'" ;
    5. View: Reuse the index view again. Add a ViewBag to display the number of items found by the search.
  3. Retail/AjaxSearch - in this exercise you will reuse the search stored procedure to create an AJAX handler in the controller..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 sample. This contains the search box and JavaScript. The code is delineated with the comments "Start Ajax search code" and "End Ajax search code." 
    3. 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. 
       //handler for AJAX requests. Returns JSON.
       public ActionResult AjaxSearchHandler(string id)
           return Json(birdRepository.Search(id), JsonRequestBehavior.AllowGet);
    4. Add a link pointing to AjaxSearch in _leftMenu.
    5. 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 - Improved product-category selection using Ajax radio buttons - coming soon!
  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