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."
- retail/Category -
This page lists the products in a category.
- 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".
- Controller: Add a ViewBag.message with the item count.
- 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);
- 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.
- _leftMenu: add a search textbox and button (you may copy html for the sample).
- 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):
from Product p
where p.ItemName like @query + '%' or p.ItemName like '% ' + @query + '%'
order by p.ItemName;
- 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?
- 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 + "'" ;
- View: Reuse the index view again. Add a ViewBag to display the number of items
found by the search.
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 w3schools.com AJAX Introduction.
- 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.
//handler for AJAX requests. Returns JSON.
public ActionResult AjaxSearchHandler(string id)
return Json(birdRepository.Search(id), JsonRequestBehavior.AllowGet);
- Add a link pointing to AjaxSearch in _leftMenu.
- retail/admin - Improved product-category selection using Ajax radio buttons - coming soon!
- retail/about -
This page describes the site features. You may copy
the text from the sample or write your own.
- 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.