Assignment 2 - E-Commerce Site Backend - Part II

1. EditCategories.aspx - This page is very similar to EditProducts. It uses a GridView, DetailsView and two DataSource controls.

2. EditProductCategories.aspx - This page provides a CheckBoxList of all the available product categories. The client clicks the categories that are appropriate for each product. The sample site includes a picture of the product which is not required.

Steps:

1. Add a "Categories" hyperlink column to the GridView in EditProducts.aspx.

   1. Click the GridView's smart tag and select "Add New Column"

   2. Field type is "Hyperlink"

   3. Header text and Hyperlink text is "Categories"

   4. Hyperlink URL is "Get URL from data field" ProductID. URL Format string:
      EditProductCategories.aspx?ProductID={0}

2. Create a new page: EditProductCategories.aspx

3. Drag a CheckBoxList control from the toolbox.

4. Populating the CheckBoxList: When the checkboxlist control loads it should be pre-checked with categories already selected for the product. This is a bit beyond the capabilities of the a DataSource control so we will do it programitically using the DataAccess class, a datatable and databinding similar to MIS 324. 

5. The first time the page loads use the dataAccess class to fill a dataTable using the following SQL statement:
   
   SELECT CatLabel, CategoryID,
   (SELECT 'checked' AS Expr1
   FROM tblProductCategories
   WHERE (ProductID = @ProductID) AND
   (CategoryID = tblCategories.CategoryID)) AS 'checked'
   FROM tblCategories
   ORDER BY CatLabel
   
   In the sql statement replace "@ProductID" with the ProductID from the querystring.  The resulting dataTable looks something like this (but with your products):
   
   
   
   

6. To bind the datatable to the CheckBox list control set the checkboxList DataTextField property to "CatLabel",  its DataValueField property to "CategoryID", its DataSource property to the dataTable and call its DataBind() method.

7. Test the page. The CheckboxList should be populated with your categories.

8. The next step is to pre-select the checkboxes.  This is done by iterating through the datatable and selecting the checkboxs. Immediately after binding the dataTable to the checkBoxList insert the following code:
   

           int row = 0;
           foreach (DataRow dr in dt.Rows)
           {
               if (dr["checked"].ToString() == "checked") 
                   cblCategories.Items[row].Selected = true;
               row += 1;
           }

9. The final step is to update tblProductCategories when the user clicks the Update button. The onSelectedIndexChanged event can be used to determine if any of the checkboxes have been changed. Steps:

   1. Add an update button. Its job is simply to cause a postback and it does not require a handler.

   2. Create a handler for checkBoxList' onSelectedIndexChanged event by double clicking on the checkBoxList control in design view. 

   3. We don't have an easy way to tell which items have been changed (we could store a copy of the original checkbox list in the viewState and then compare it to the revised list, but that is too much work). Instead  we will delete any existing records in tblProductCategories for this ProductID and insert new ones for the checked categories.

   4. In the handler for SelectedIndexChanged write a sql statement to delete all records in tblProductCategories for the current ProductID. Use the ExecuteNonQuery method of the dataAccess class to execute the query.

   5. Next iterate through the checkBoxList and execute an insert sql statement for each checked item. The code looks something like this:

      foreach (ListItem li in cblCategories.Items)
      {
         if (li.Selected)
         {
           sql = "Insert into  " +
           " tblProductCategories (ProductID, CategoryID) " +
           " VALUES (" + ProductID + ", " + li.Value + ")";
           da.ExecuteNonQuery(sql);
         }
      }

   6. Finally, write a confirmation message to the user that the items have been updated or redirect them back to EditProducts.aspx.

3. Browse.aspx - This page displays items that have be assigned to specific product categories.

Browse and search use different SQL statements and require two separate sqlDataSource controls. Each uses a DataList to display the data. The two types of query could be combined on a single page but it is probably easier to create two separate pages.

The search and browse pages are a good opportunity to use stored procedures.  Steps:

1. Right click on the database in Server Explorer and select "New Query." Use the wizard to create the appropriate SQL query. Provide a static number for CategoryID so that a "where" statement is included in the query. Also include the field "CatLabel" in the results so that it can be displayed on the page. Test the query and copy the query.

2. Cight click on the database folder "Stored Procedures," and select "Add New Stored Procedure."

3. Paste your SQL query after the "AS" and create a parameter "@CategoryID" of type int.  Replace the static CategoryID with the parameter @CategoryID.

4. Change the stored procedures name from "StoredProcedure1" to "BrowseCategories" in the first line of the query.

5. Save the query. It will now appear in the database's Stored Procedures folder as "BrowseCategories."

6. To test it, right click on it and select "Execute." It will prompt you for a CategoryID.  It may be necessary to resize the Output window to see the results.

7. The next step is to consume the stored procedure in Browse.aspx. Add a new page "Browse.aspx" that utilizes the stored procedure to populate a DataList control. The steps are the same as previously, except when configuring the data source select "stored procedure." The parameter source is "QueryString" and QueryStringField is "CategoryID." Copy the ItemTemplate from dafault.aspx.

8. Item count: Most web sites display a count of items returned by a query. The results of the SqlDataSource's "Select" query are accessible programmatically as described in the article "Accessing and Updating Data in ASP.NET 2.0." The query results are returned in a DataView object, which is a "view" of the data (similar to a DataTable.)  The code to get both the count and the name of the category is:
       void page_load()
       {
           DataView dv = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
           string CategoryLabel = dv.Table.Rows[0]["CatLabel"].ToString();
           lblCount.Text = dv.Count + " items in <b>" + CategoryLabel + "</b> category";  
       }

4. Search.aspx - Returns a list of items that match the query typed by the user.

The steps for building the search page are similar to those for the browse page. If fact, the two pages are so similar that you might want to make a copy your browse page and modify it to use a different stored procedure.

The SQL statement in the search stored procedure searches the fields tblProducts.ItemName, tblProducts.Description and tblCategories.CatLabel . The WHERE clause in the SQL statement will look something like this:

WHERE (tblProducts.ItemName LIKE '%baker%') OR
(tblProducts.Description LIKE '%baker%') OR
(tblCategories.CatLabel LIKE '%baker%')

In the stored procedure the WHERE clause looks something like this:

WHERE (tblProducts.ItemName LIKE '%' + @query + '%') OR
      (tblProducts.Description LIKE '%' + @query + '%') OR
      (tblCategories.CatLabel LIKE  '%' + @query + '%')

The SQL statement uses the DISTINCT keyword to eliminate duplicate records.

Creating stored procedures on Yorktown: Stored procedures are associated with your application and not with the database (.mdf) file. Copying the database .mdf file does not copy the stored procedures.

Stored procedures are created on the server by executing a "Create Procedure" sql statement. The script CreateStoredProcedure.aspx.txt provides a convenient interface for pasting sql statements into a form and executing them. Copy the script into your application on Yorktown. The script can be executed inside any directory of your application on Yorktown. The admin folder is a good choice because it has a login. This script allows users to execute raw SQL on your database so keep it private by 1) changing the password in the script 2) do not provide any hyperlinks to it.

Once you have debugged your stored procedure in VWD cut-and-paste it into CreateStoredProcedure.aspx on Yorktown. Once the stored procedure has been created on the server it will be displayed on the page.

5. ProductDetail.aspx - This page displays the medium image size, product name, price, and description. The image is hyperlinked to the largest image size. This page is reached from the default, search and browse pages by clicking on the images.  

Data access may be done using a sqlDataSource control or the DataAccess class, whichever you prefer. Copy and edit the ItemTemplate from default.aspx. To format the currency see the DataBinder.Eval documentation. Note: the sample page uses Lightbox to display the large image which is not required.

 

 

To submit your assignment for grading send an email with the URLs for your assignment to:

1. Professor Sandvig at . (note: this address is for homework assignments only - please send other correspondence to ).
2. cc. a copy to yourself.

The subject line of your email should read "MIS424 AXX YourName" where XX is the assignment number. Please check that your URLs are correct before submitting them for grading. Files with incorrect URLs will not be graded.