Learning Center
Plans & pricing Sign in
Sign Out

Case Study - Product Browsing_ShoppingCart


									                       Case Study: Product Browsing

                               The CD Store Case


         You are employed as part of a small web development company, which has a
client that wants to develop an ecommerce B2C web site for selling CDs and T-shirts.
The company now has a mail order business and wants to move at least part of its
operations to the web. They already have a well-defined mail order business with a
catalog, order processing/fulfillment personnel, logistical systems and associated business
systems. Your company is experimenting with a new platform for developing web sites,
ASP.Net 2.0 of Visual Studio 2005. This package has been promoted as a web
development platform for e-commerce applications. Therefore, you are required to use
ASP.Net 2.0 and ADO.Net 2.0 to design a web site to enable customers to browse
product lists offered by the CD Store.

Site Architecture

                                      Home Page

                         Department                  Search

                                     Product Listing

                                      Product Page

                                      Basket Page

                                      Shipping Page

                                      Payment Page


                                      Order History

OMIS 675 E-Commerce Technology              1                                   2/14/2012
Dr. Chang Liu
        When customers enter the store’s web site, they’ll typically follow one or two
steps for beginning their shopping process. Either they’ll look at department and product
data through browsing the store, or they’ll search for something specific.


        The top-level categorization of products will be departments. If we were selling
sporting goods, for example, we might have rackets and tennis balls in the Tennis
departments, whereas the hoop, backboard, and net would be the basketball department,
In the case of our CD store, CDs will be classified according to music categories, or
departments, such as Jazz and Country.

        Conceivably, a store could also have multilevel departments. For example, a top-
level department, such as Sporting Goods, could have several departments below it, such
as Tennis and Basketball. In this case study, we won’t implement multilevel departments.
However, keep in mind that the simplest way to handle multilevel departments is to
implement a self-join on the department table so that each department can also have a
Parent Department column.


        Offering products in a store would seem pretty simple. But when you begin to
think about defining a generic product, things can get rather complicated because the
products may have different attributes. In this case study, we’ll assume that the products
(such as T-shirts) can have only two specific attributes: size and color. This will help us
keep the design simple while still demonstrating how you can handle attributes.

Database Tables for Department and Product

Product table: This table stores the primary product data.

Attribute table: This table will store all the attributes (for example, red, green, X, and XL)

ProductAttribute table: This table is the link between the attributes a product has and the
      product itself.

AttributeCategory table: This table categorizes each attribute (for example, size and

      What about the Department table?
      Check tables of Store database at SQL Server.

(Note: you will use the Store Database for the case study. Please examine the tables in
the Store database, but do NOT change or delete the data records.)

OMIS 675 E-Commerce Technology                2                                    2/14/2012
Dr. Chang Liu
Department Page (Department.aspx):

                     Figure 2: Department Page Design (department.aspx)

   (1) Create a New application for the case study
   (2) Add a new Web Form of department.aspx and Type a headline for the page
   (3) Add DepartmentImage and images folders within your application. Download
       images files of Dance.gif, Funk.gif, Punk.gif, Tshirts.gif, and Western.gif from to the DepartmentImage folder
   (4) Downloads Product images from to your images folder
   (5) Add a SqlDataSource (ID: SqlDataSource1) to connect to the Department table
       (select all fields) in the Store Database
   (6) Add a ListBox control with the data source from the SqlDataSource1; Set the
       DataTextField to the DepartmentName and DataValueField to DepartmentID
   (7) Add a Button control as shown in Figure 2 with the Text property of the button as
       “Please select a department”
   (8) Add another SqlDataSource (ID: SqlDataSoure2) to connect to the Department
       table (select all fields) in the Store Database
   (9) Add a Where Clause to specify the following conditions:
       -- Column:       DepartmentID
       -- Operator: =
       -- Source:       Control
       -- ControlID: ListBox1
       -- Add

      (Note: the SqlDataSource2’s data will depend on SqlDataSource1)

   (10)       Add a DataList Control with the data source from the SqlDataSource2
   (11)       Modify the Item Templates of the DataList as shown in Figure 3.

OMIS 675 E-Commerce Technology               3                                2/14/2012
Dr. Chang Liu
   (12)     Add an ImageButton control with the data binding property for the Text as:
             Eval("DepartmentImage", "DepartmentImage/{0}")

   (13)     Add a Hyperlink control with the data binding property for the Text as:
             Eval(“DepartmentName”) and NavigateUrl property to
             Eval("DepartmentID", "Products.aspx?DepartmentID={0}")

   (14)     Also add a Label box for the DepartmentDescription field data

                    Figure 3: The ItemTemplate of the DataList Control

   (15)     Run and test the page as shown in Figure 4

                             Figure 4: Department.aspx page

OMIS 675 E-Commerce Technology              4                               2/14/2012
Dr. Chang Liu
   Products Page (Products.aspx)

                             Figure 5: Products Page Design (.aspx)

   (1) Create a new Web Form called Products.aspx in your application
   (2) Add a SqlDataSource, Label Box, DataList, and HyperLink to the Page (see Figure 5).
   (3) The SqlDataSource is based on the DepartmentProduct and Product Tables. In addition,
       the condition should also be set to DepartmentID=@departmentID as shown in Figure 5

                     Figure 6: The SQL Query Builder for the SqlDataSource

   (Note: The connection between the department.aspx and products.aspx is through the
   DepartmentID field.)

OMIS 675 E-Commerce Technology               5                               2/14/2012
Dr. Chang Liu
   (4) You may use the stored procedure procRetrieveDeptProducts instead
      ALTER PROCEDURE procRetrieveDeptProducts
             @idDept int
         select Product.ProductID, ProductName, ProductImage from
         product, departmentProduct where
         departmentProduct.ProductID=Product.ProductID and

                   Figure 7: DataList Item Template and Alternating Item Template

   (5) Modify the DataList’s ItemTemplate and AlternatingItemTemplate to display
       ProductName and Product Image (use an ImageButton control) as shown in Figure 7.
   (6) Set the PostBackUrl property of the ImageButton as:
      Eval("ProductID", "Product.aspx?ProductID={0}")

   (7) Double-click the page to add code for Page_Load event
      Label2.Text = Request.QueryString("DepartmentID")

   (8) Set the NavigateUrl property to ~Department.aspx for the Hyperlink control
   (9) Run and test the page as shown in Figure 8

                                      Figure 8: Products.aspx

OMIS 675 E-Commerce Technology                 6                                    2/14/2012
Dr. Chang Liu
Individual Product Page (product.aspx)

                     Figure 9: Individual Product Page Design (product.aspx)

   (1) Add a DataList, two Label boxes (lblColor and lblSize), two DropDown controls (ID:
       ddlColor and ID: ddlSize), and a button (Text: Order) controls as shown in Figure 9.
   (2) Name the DataList control as dlProduct.
   (3) Use the following SQL statement to specify the SqlDataSource (ID: SqlProduct)
       for the DataList control

      SELECT [ProductID], [ProductName], [ProductImage], [ProductDescription], [ProductPrice]
      FROM [Product] WHERE ([ProductID] = @ProductID)

      Note: The connection between the products.aspx and product.aspx is through the
            productID field. Therefore, add a Where clause for the QueryString(“productID”)

   (4) Modify the Item Templates of the DataList control (dlProduct) as shown in Figure 10

                    Figure 10: Item Templates for the dlProduct DataList Control

OMIS 675 E-Commerce Technology                 7                                    2/14/2012
Dr. Chang Liu
   (5) Instead of step 3, you may use the stored procedure of procRetrieveProduct

      ALTER PROCEDURE procRetrieveProduct
            @idProduct int
            select ProductID, productName, ProductImage, ProductPrice,
      ProductDescription from product where productID=@idProduct

   (6) Use the following SQL statement to specify the SqlDataSource (ID: SqlColor) for
       the dropdown control for the color product attribute

      SELECT Attribute.AttributeName FROM Attribute INNER JOIN AttributeCategory ON
      Attribute.AttributeCategoryID = AttributeCategory.AttributeCategoryID INNER JOIN
      ProductAttribute ON Attribute.AttributeID = ProductAttribute.AttributeID INNER JOIN Product
      ON ProductAttribute.ProductID = Product.ProductID AND Product.ProductID = @ProductID
      AND AttributeCategory.AttributeCategoryName = @CategoryName

   Note: the data come from the Product, ProductAttribute, Attribute, and AttributeCategory
         tables. You can use the Query Builder to select AttributeName field from the tables.
         Figure 11 shows the parameter set up for the SqlColor data source.

                                 Figure 11: SqlColor Data Source Definition

   (7) Bind the SqlColor to the ddlColor dropdown control
   (8) Repeat the steps 6-7 to define SqlSize for the size attribute and bind the SqlSize to
       the ddlSize dropdown control

   (9) You may instead use the stored procedure: procProductAttribute

      ALTER PROCEDURE procProductAttributes
            @idProduct int,
            @AttributeCategoryName varchar(255)
OMIS 675 E-Commerce Technology                 8                                       2/14/2012
Dr. Chang Liu

      SELECT     Attribute.AttributeName
      FROM   ProductAttribute,Product, Attribute, AttributeCategory
      where ProductAttribute.ProductID = Product.ProductID
       and ProductAttribute.AttributeID = Attribute.AttributeID
       and Attribute.AttributeCategoryID = AttributeCategory.AttributeCategoryID
       and Product.ProductID=@idProduct
       and AttributeCategory.AttributeCategoryName=@AttributeCategoryName


   (9) Double-click the page to add the following code for the Page_Load event:

     If Not IsPostBack Then
        If ddlColor.Items.Count = 0 Then
           Session("A_Color") = " "
                 ddlColor.Visible = False
                 lblColor.Visible = False
          End If

             If ddlSize.Items.Count = 0 Then
                 Session("A_Size") = " "
                 ddlSize.Visible = False
                 lblSize.Visible = False
             End If
          End If

   (10)        Add the following code for the Button_Click event of the Order button:

   (11)        Run and test the page as shown in Figure 12

                                    Figure 12: Product.aspx

OMIS 675 E-Commerce Technology              9                                 2/14/2012
Dr. Chang Liu
 The Search Page (search.aspx)

   (1) Design a search page (using Textboxes and DataList) as shown in Figure 13
       -- TextBox1:          txtText      (this textbox for the search text)
       -- TextBox2:          txtLow       (this textbox for the low end price search)
       -- TextBox3:          txtHigh      (this textbox for the high end price search)
       -- DataList1          DataList1    (this displays ProductName information and
                                           hyperlink to product.aspx)

                               Figure 13: Search Page (search.aspx)

   (2) Add a SqlDataSource (ID: SqlSearch) for the following SQL statement:

   SELECT ProductID, ProductName
   FROM      Product
   WHERE (ProductName LIKE '%' + @SearchText + '%') AND (ProductPrice >= @low)
     AND (ProductPrice <= @High)
    OR (ProductPrice >= @low) AND (ProductPrice <= @High) AND (ProductDescription LIKE '%'
        + @SearchText + '%')

   (3) Figure 14 shows the parameter definitions for the above SqlSearch:

       Name          Value           Parameter Source            ControlID   Default Value

       SearchText txtText.Text       Control                     txtText     %

       Low           txtLow.Text     Control                     txtLow      0

       High          txtHigh         Control                     txtHigh     999999

OMIS 675 E-Commerce Technology               10                                  2/14/2012
Dr. Chang Liu
                         Figure 14: Define Parameters for SqlSearch

   (3) You may instead use the stored procedure: procSearchProducts

      ALTER PROCEDURE proSearchProducts
            @searchText varchar(255),
            @low int,
            @high int
      select ProductID, ProductName from product where
            (ProductName like '%' +@searchText +'%'
         or ProductDescription like '%'+@searchText+'%')
         and (ProductPrice>=@low and ProductPrice<=@high) order by productName


                            Figure 15: Item Template for the DataList

   (4) Figure 15 shows the Item Templates for the DataList control. Add a hyperlink control to
       the ItemTemplates and bind the Text property to Eval(“ProductName”) and
       NavigateUrl property to Eval("ProductID", "Product.aspx?ProductID={0}")

OMIS 675 E-Commerce Technology              11                               2/14/2012
Dr. Chang Liu
   (5) Add the following code for the Page_Load event:

    If Not IsPostBack Then
             DataList1.Visible = False
             DataList1.Visible = True
   End If

   (5) Add the following code for the Search Button_Click event

      If DataList1.Items.Count = 0 Then
        Response.Write("No such record")
   End If

   (6) Run and test the page as shown in Figure 16

                                    Figure 16: search.aspx

                  Case Study: Shopping Basket Design
                         The CD Store Case (2)

Shopping Basket Functionality

        The shopping basket allows several key functions that make it very dynamic
aspect of the e-commerce web site. Below shows the core functions that we’ll be adding
to the shopping basket.
OMIS 675 E-Commerce Technology             12                                2/14/2012
Dr. Chang Liu
                                     Core Functions

                                      Add Item to Basket
                                      Display Basket
                                      Update Quantity of Basket Item
                                      Show the Total of the Basket
                                      Remove Item
                                      Empty Basket

Database Tables for Basket

Basket table: This table stores the primary Basket data.
Basket(BasketID, ShopperID, BasketQuantity, BasketDateCreated, BasketOrderPlaced,
      BasketSubtotal, BasketTotal, BasketShipping, BasketTax)

BasketItem table: This table is the link between the product table and the basket

BasketItem(BasketItemID, BasketID, ProductID, BasketItemPrice,
          BasketItemQuantity, BasketItemSize, BasketItemColor)

         Check tables of the Store database at SQL Server

Code Behind for the Product Page (product.aspx.vb):

        The first step in the process is adding items to the basket. This step is called
when a shopper clicks the Order button on the product page. Note that the Order
button performs processing only, that is, it doesn’t display any information directly to
the shopper at the product page. When the page finishes adding the item (product), it
redirects the shopper’s browser to the Basket.aspx page for basket display. In
summary, the Order button will
     Record the shopper selection on the product
     Create a basket, if not exist, and
     Add items to the basket

(Hint: you need to add ADO.Net namespaces at the beginning of the page)

Add the following code for the Button_Click event of the Order button:

‘ record the shopper selection on the product to the session variables

OMIS 675 E-Commerce Technology              13                                    2/14/2012
Dr. Chang Liu
 Session("ProductImage") =
 Session("ProductID") = Request.QueryString("ProductID")
 Session("ProductName") =
 Session("price") =

            Label).Text, "currency")
 Session("Quantity") =

Session("Description") =

If ddlColor.Items.Count <> 0 Then
    Session("A_Color") = ddlColor.SelectedItem.Text()
End If

If ddlSize.Items.Count <> 0 Then
   Session("A_Size") = ddlSize.SelectedItem.Text
End If

'create a basket

Dim cnn As New

Dim cmdCreateBasket As New SqlCommand
 cmdCreateBasket.Connection = cnn
 cmdCreateBasket.CommandText = "procCreateBasket"
 cmdCreateBasket.CommandType = CommandType.StoredProcedure

'no basket, need to create one

 If Session("idBasket") Is Nothing Then

    cmdCreateBasket.Parameters.Add("@ShopperID", SqlDbType.Int)
    cmdCreateBasket.Parameters.Add("@basketID", SqlDbType.Int)
    cmdCreateBasket.Parameters("@ShopperID").Value = 0
    cmdCreateBasket.Parameters("@basketID").Direction =

      Session("idBasket") =

 End If

 'When we have a basket, either insert or update the BasketItem table

OMIS 675 E-Commerce Technology     14                             2/14/2012
Dr. Chang Liu
   Dim cmdInsertBasketItem As New SqlCommand
    cmdInsertBasketItem.Connection = cnn
    cmdInsertBasketItem.CommandText = "procInsertBasketItem"
    cmdInsertBasketItem.CommandType = CommandType.StoredProcedure

    cmdInsertBasketItem.Parameters.Add("@BasketID", SqlDbType.Int)
    cmdInsertBasketItem.Parameters.Add("@Quantity", SqlDbType.Int)
    cmdInsertBasketItem.Parameters.Add("@price", SqlDbType.Money)
SqlDbType.VarChar, 255)
    cmdInsertBasketItem.Parameters.Add("@ProductID", SqlDbType.Int)
    cmdInsertBasketItem.Parameters.Add("@Size", SqlDbType.VarChar, 50)
    cmdInsertBasketItem.Parameters.Add("@Color", SqlDbType.VarChar)

    cmdInsertBasketItem.Parameters("@BasketID").Value =
    cmdInsertBasketItem.Parameters("@Quantity").Value =
    cmdInsertBasketItem.Parameters("@Price").Value = Session("price")
    cmdInsertBasketItem.Parameters("@ProductName").Value =
    cmdInsertBasketItem.Parameters("@ProductID").Value =
    cmdInsertBasketItem.Parameters("@size").Value = Session("A_size")
    cmdInsertBasketItem.Parameters("@Color").Value = Session("A_color")



Here is the stored procedure for procCreateBasket

       CREATE PROCEDURE procCreateBasket
                    @shopperID int,
                    @basketID int OUTPUT
             insert into basket(shopperID) values(@shopperID)
             select @basketID=@@identity

The stored procedure for procInsertBasketItem

       CREATE PROCEDURE procInsertBasketItem
               @basketID int,
               @Quantity int,
               @price money,
               @productName varchar(255),
               @productID int,
               @size varchar(50)=null,
               @color varchar(50)=null
       declare @current int

OMIS 675 E-Commerce Technology           15                     2/14/2012
Dr. Chang Liu
               /* first, see if it's already in the basket */
               select @current=count(*) from basketItem where basketID=@basketID
                            and productID=@productID
                            and BasketItemSize=@Size
                           and BasketItemColor=@Color

       if @current=0
        /* Not already there, so insert it */
       insert into basketItem(basketID, productID, basketItemPrice, basketItemProductName,
                              basketItemQuantity, basketItemSize, BasketItemColor)
             values(@basketID, @productID, @price, @ProductName, @quantity, @size,
          /* already there, update quantity */
                update BasketItem
                       set basketItemQuantity=@quantity+basketItemQuantity
                      where basketID=@basketID
                          and ProductID=@productID
                         and BasketItemSize=@Size
                        and BasketItemColor=@Color

(Note1: these stored procedures already created in the Store database).

(Note2: @ShopperID set to 0 for all any anonymous shoppers. You can re-set this value
at the
        end of confirmation page).

Basket Page

                             Figure 1: Basket Page Design Layout.

OMIS 675 E-Commerce Technology               16                                   2/14/2012
Dr. Chang Liu
   (10)       Drag the BasketItem table from the Server Explorer window to the
       Basket.aspx page as shown in Figure 2 (when dragging a table to the page, a
       GridView was created)
   (11)       In the GridView Tasks pane  check the “Enable Editing” and “Enable
       Deleting” boxes
   (12)       Then select “Configure Data Source” to modify the SQL statement as below:

      SELECT BasketItemID, BasketID, ProductID, BasketItemPrice, BasketItemProductName,
                BasketItemSize, BasketItemColor, BasketItemQuantity * BasketItemPrice AS LineTotal
      FROM       BasketItem WHERE (BasketID = @BasketID)

   (13)        Figure 3 shows the parameter definition for the @basketID

                               Figure 2: The Modified Select statement

OMIS 675 E-Commerce Technology                17                                       2/14/2012
Dr. Chang Liu
                             Figure 3: @BasketID=Session(“idBasket”)

   (14)        At the GridView Tasks pane, select Edit Columns
   (15)        Add “Line Total” field to the selected fields list
   (16)        Delete “BasketItemSize” and “BasketItemColor” from the selected fields
   (17)        Add a Template field with the Header Text property as “Attribute”
   (18)        You need to add two Label boxes for this Template field: One Label box
       will bind to “BasketItemSize” and the other will bind to “BasketItemColor”
   (19)        While Editing the Columns, set the following field properties:

                                  Header Text              Read Only      Visible

    BasketID                      BasketID                 True           False

    BasketItemID                  BasketItemID             True           False

    ProductID                     Item Code                True           True

    BasketItemProductName         Product Name             True           True

    Attribute                     Attribute                -              -

    BasketItemQuantity            Quantity                 False          True

    BasketItemPrice               Item Price               True           True

    Line Total                    -                        True           True

   (20)         In the Basket.aspx page, select the Source View

OMIS 675 E-Commerce Technology                 18                             2/14/2012
Dr. Chang Liu
   (21)      You need to modify the UpdateCommand and DeleteCommand of the
       SqlDataSource1 in the Source view as the followings:

   DeleteCommand="DELETE FROM [BasketItem] WHERE [BasketItemID] =

   UpdateCommand="UPDATE [BasketItem] SET [BasketItemQuantity] =
   @BasketItemQuantity WHERE [BasketItemID] = @BasketItemID"

   (22)       Add a Label (ID: lblTotal)box, and three LinkButton controls to the Page
       as shown
              in Figure 1
   (23)       In Code behind (basket.aspx.vb) page, add a subroutine to calculate the Basket

   Private Sub calculate()
        Dim cnn As New

   Dim Mycommand As New SqlCommand
        Mycommand.Connection = cnn
        Mycommand.CommandText = "select * from basketItem where
basketID=" &
      Dim MyReader As SqlDataReader
      Dim total As Double = 0

      MyReader = Mycommand.ExecuteReader

      While MyReader.Read
       total += MyReader("BasketItemQuantity") *
      End While

      lblTotal.Text = Format(total, "currency")


      End Sub

   (Note: Add two namesspace at the beginning of the code behind page as:
   Imports System.Data
   Imports System.Data.SqlClient)

   (24)       In the GridView1_RowUpdated event, add the following code:

   (25)       In the GridView1_RowDeleted event, add the following code:

OMIS 675 E-Commerce Technology            19                                  2/14/2012
Dr. Chang Liu
   (26)        In the Page_Load event, add the following code:

   (27)        Add code for the three LinkButton controls: One LinkButton for “Empty
               Basket”: Double-Click this LinkButton and add the following code:

             Dim cnn As New

          Dim Mycommand As New SqlCommand
          Mycommand.Connection = cnn
          Mycommand.CommandText = "delete basketItem where basketID=" &


   (28)       Allow the second LinkButton control to return to the Department.aspx
   (29)       Allow the third LinkButton Control to go to the shipping.aspx page
       designed in
              Lab3 assignment

   (30)        Run and test the page as shown in Figure 4

                                     Figure 4: Basket.aspx

                  Optional: Lab 9&10 assignment (20 Points)
                                   Due: May 6, 2008

    (1) Combine both department search and general search in the design
    (2) Enforce site navigation starting from the Department.aspx at your site
    (3) Send me an email for the http address.
OMIS 675 E-Commerce Technology               20                                2/14/2012
Dr. Chang Liu

To top