i.c.stars | * / November 9, 2006

Bookstore Database Case

Description

Several i.c.stars alums want to build a small online and community bookstore to sell technology books. They would like to hire an intern who would develop a rapid prototype of such a web-based system.

Customers may open an account with the store if they wish including a username and password. The bookstore is planning to build a web-site that will allow the customers to perform certain activities on-line.

Front office

The home page for the prototype should list the current books for sale and contain a link next to each book to add it to the cart. A customer must be logged in before adding books to their cart. If a user attempts to add a book without being logged in they should be redirected to the login page.

When a customer adds a book to their cart, they should be taken to a page that shows the current contents of their cart, including book title, isbn, quantity, and price. From the shopping cart they can choose to check out, where they will be shown a receipt for their transaction which they may print. The prototype does not need to handle payment information. After checking out, the users shopping cart should be emptied. If the user leaves the site without checking out, their shopping cart should remain active with its current contents until they return.

Back office

The alums would like the prototype to show the ability to add books to the database, including the ISBN, Title, and Price. Additionally, they would like to see two reports: the top 5 best customers showing the total amounts they have purchased, and the top 5 best selling books based on quantity sold, not dollar amount.

Data Model

Book (ISBN, Title, Price)

Customer (FirstName, LastName, UserName, Password)

Cart (UserName, ISBN, Quantity)

CustomerOrder (OrderID, Username, Total)

OrderItem (OrderID, ISBN, Quantity, Price)

Web Forms and Components

addcustomer.aspx – form to insert new customers into the database

default.aspx – home page showing books for sale

login.aspx – login form

addbooks.aspx – form for adding books for sale to the database

addtocart.aspx – shopping cart page that can take a new isbn number to add to the cart via querystring

receipt.aspx – the receipt page that shows the order details and total amount the customer purchased

rptbooks.aspx – the best selling books report page

rptcust.aspx – the best customers page

Sample

You can view and play with a working sample of this case at:

Workplan

Monday

Setup

  1. Create new VS.net project
  2. Create bookstore database file in a database subfolder under the new project folder in inetpub\wwwroot – add all the tables from above to the database
  3. Add your DataAccess.vb class file and modify the default web.config fileto point at the new database file using the accessConnection string key in the web.config from a prior project

Add customers – addcustomer.aspx

  1. Create Query called – InsertUser that takes the @FirstName, @LastName, @UserName, and @Password parameters
  2. Create new webform in .net called addcustomer.aspx
  3. Add 4 textboxes and a submit button. Set the IDs of the textboxes to firstTxt, lastTxt, userTxt, and passwordTxt respectively.
  4. On submit click event ExecuteNonQuery method to run the InsertUser query with parameter values from the user interface (see below)
  5. set this page as the start page by rightclicking the project icon in vs.net
  6. Test – add yourself as a user

Imports System.Data.OleDb

PrivateSub SubmitBtn_Click…(created by double-clicking the button on the design view)
Create a new object called parm(3) as a OleDbParameter – this is an array containing 4 slots for parameters
Set parm(0) equal to a new OleDbParameter and pass in “@firstname” and the value of the first name text box
Set parm(1) equal to a new OleDbParameter and pass in “@lastname” and the value of the last name text box
Set parm(2) equal to a new OleDbParameter and pass in “@username” and the value of the username text box
Set parm(3) equal to a new OleDbParameter and pass in “@password” and the value of the password text box
Call the ExecuteNonQuery method of the DataAccess.QueriesOleDb object and pass in “InsertUser” and the parm object as the method input parameters

Add books

  1. Create Query called – InsertBook that takes @isbn, @title, and @price parameters
  2. Create new webform in .net called addbooks.aspx
  3. Add form elements and submit button
  4. On submit click event ExecuteNonQuery method to run the InsertBook query with parameter values from the user interface (see below)
  5. set this page as the start page by rightclicking the project icon in vs.net
  6. Test –add about 5 books

Imports System.Data.OleDb

PrivateSub SubmitBtn_Click…(created by double-clicking the button on the design view)
Create database parameter object array / Create a new object called parm(2) as a OleDbParameter – this is an array containing 3 slots for parameters
Create the isbn parameter / Set parm(0) equal to a new OleDbParameter and pass in “@isbn” and the value of the isbn text box
Create the title parameter / Set parm(1) equal to a new OleDbParameter and pass in “@title” and the value of the title text box
Create the price parameter / Set parm(2) equal to a new OleDbParameter and pass in “@price” and the value of the price text box
Execute the insert query to add the book to the database / Call the ExecuteNonQuery method of the DataAccess.QueriesOleDb object and pass in “InsertBook” and the parm object as the method input parameters

Tuesday

Login

  1. Create Query called – GetUserByCredentials that takes @username and @password parameters and returns the count of users found matching those credentials
  2. Create a new webform in the .net project called login.aspx
  3. On submit click of the form, ExecuteScalar query with the values supplied by the user as parameters and save the return value in a variable called count (see below)
  4. If the count = 1 then save the username in a session variable and redirect the user back to the home page
  5. If the count = 0 then tell the user they have given an invalid username/password
  6. If the count is anything else tell the user somehow they have more than one username
  7. set this page as the start page by rightclicking the project icon in vs.net
  8. Test – you should get a page not found error message when it tries to redirect you to a page that does not yet exist

Imports System.Data.OleDb

PrivateSub SubmitBtn_Click…(created by double-clicking the button on the design view)
Declare a new integer variable called count
Create a new object called parm(1) as a OleDbParameter – this is an array containing 2 slots for parameters
Set parm(0) equal to a new OleDbParameter and pass in “@username” and the value of the username text box
Set parm(1) equal to a new OleDbParameter and pass in “@password” and the value of the password text box
Call the ExecuteScalar method of the DataAccess.QueriesOleDb object and pass in “GetUserByCredentials” and the parm object as the method input parameters, and save the result in the count variable
If Count = 1 then
set the value of the Session(“username”) equal to the value of the username text
Call the Redirect method of the Response object and pass “default.aspx” as the method parameter
ElseIf count = 0 then
Call the Write method of the Response object with “Invalid username or password” as the method parameter
Else
Call the Write method of the Response object with “Something crazy is going on, you have more than one username” as the method parameter
End If

Home page

  1. Create Query called – GetAllBooks
  2. Create new webform in .net called default.aspx and add a repeater to list ISBN, Title, Price, and a column for “add to cart” links (see below)
  3. Add a repeater to the new webform
  4. Add the code on page load to execute the GetAllBooks query and bind to the repeater
  5. set this page as the start page by rightclicking the project icon in vs.net
  6. Test – you should see the 5 books you added to the database

table

asp:Repeaterid="booksRPTR"runat="server">

HeaderTemplate

tr

tdISBN</td

tdTitle</td

tdPrice</td

td&nbsp;</td

</tr

</HeaderTemplate

ItemTemplate

tr

td<%#DataBinder.Eval (Container.DataItem, "ISBN")%</td

td<%#DataBinder.Eval (Container.DataItem, "Title")%</td

td<%#DataBinder.Eval (Container.DataItem, "Price")%</td

tdahref='addtocart.aspx?isbn=

<%#DataBinder.Eval (Container.DataItem, "ISBN")%>'>

add to cart</a

</td

</tr

</ItemTemplate

</asp:Repeater

</table

Shopping Cart

  1. Create relationships in database between the Cart, Book and Customer tables
  2. Create Query called – InsertCartBook
  3. Create new webform in the .net project called addtocart.aspx
  4. Add a repeater to the page containing ISBN, Title, Price, and Quantity columns
  5. Add code on page load to check the Session variable containing the username – if none exists, response.redirect the user to the login page
  6. Add code to the page load event to check for an isbn value in the request.querystring, if one exists, then call the ExecuteNonQuery method to run the InsertCartBook query with username and isbn and quantity parameter values
  7. Create Query called – GetCartForUser that takes a username as a parameter
  8. Add code to the page load to execute the GetCartForUser query with the username parameter and bind to the repeater
  9. Test

Imports System.Data.OleDb

PrivateSub Page_Load…(created automatically)
Declare a new string variable called username
Declare a new string variable called ISBN
Set the value of username equal to the value of Session(“username”)
If username = “” then
Call the Redirect method of the Response object and pass in “login.aspx” as the method parameter
Call the End method of the Response object
End if
If (not IsPostback) then
Set the value of ISBN equal to the Request.Querystring(“isbn”) value from the URL – this was passed in from the home page addtocart a link
If (ISBN>””) then
Create a 3 slot parameter array called parm of type OleDbParameter
Create a new parameter for each of the following: username, isbn, and quantity – you can hardcode the quantity at the value 1
Call ExecuteNonQuery to add the book to the users cart
End if (isbn if)
End if (postback if)
Execute the “GetCartForUser” query and pass in parm(0) as the input parameter to send in the “username” as the sql query parameter discussed in step 7 above, store the result in a dataset
Bind the repeater to the dataset

Wednesday & Thursday

Checkout

  1. Create relationships between the Customer, CustomerOrder, and OrderItem tables
  2. Add a checkout button to the shopping cart webform
  3. Build a unique orderID for this transaction - based on the number of orders this user has placed concatenated with their username – e.g. if horopeza has placed 2 orders already this order id would be horopeza3 – steps 3.1-3.3 describe how
  4. DB - Create Query called GetUserOrderCount – uses sql count function and takes a username parameter
  5. VB - Add code in the checkout button click event to ExecuteScalar the GetUserOrderCount query with the username from the session and store the result in a variable called orderCount
  6. VB - Concatenate the username and the orderCount + 1 to create the orderID
  7. Create a new order in the orders table
  8. DB - Create Query called InsertCustomerOrder that takes username and orderID as parameters
  9. VB - ExecuteNonQuery the InsertCustomerOrder query with the username from the session and the orderID
  10. Add the contents of the users cart to their order items
  11. DB - Create a query called InsertOrderItems that takes username and orderID as parameters. This query should do an insert using select statement to get the contents of the cart for this user to add to the orderitem table
  12. VB - ExecuteNonQuery the InsertOrderItems query with the username from the session and the orderID from above
  13. Get the total $ amount of the order
  14. DB - Create a query called GetOrderTotal that takes orderID as a parameter – the query will sum the price * quantity for the order
  15. VB - ExecuteScalar the GetOrderTotal query with the orderID and save the result
  16. Update the customer order record with the total amount of the order
  17. DB - Create a query called UpdateOrderTotal that takes OrderID and OrderTotal as parameters
  18. VB - ExecuteNonQuery the UpdateOrderTotal query with the orderID and the orderTotal variables
  19. Clean out the users cart
  20. DB - Create a query called DeleteCartForUser with a username parameter
  21. VB - ExecuteNonQuery the DeleteCartForUser query with the username as parameter
  22. Redirect the user to the receipt.aspx page with the order id as a querystring parameter - concatenate a string to accomplish this

Receipt

  1. Create Query called – GetOrderDetails that takes an orderID as a parameter
  2. Create new webform in the .net project called receipt.aspx
  3. Add a repeater to the webform
  4. On page load call the query and bind to the repeater using the orderid from the querystring

Friday

Reports

Complete these on your own

Graphical Design

  1. Identify design template to use from openwebdesign.org
  2. Create a new webform called template.aspx
  3. Incorporate html from design template into template.aspx
  4. Parse out css, header and footer “include” files and revise template.aspx template to use them
  5. add menu items for home, login, shopping cart, add user, add books, report – mv customer, report – bestselling books
  6. apply the include files and css to existing pages to wrap the application in a design

Updateable quantities in the cart

Add text fields to the cart repeater and add an update quantities button to update the quantities of books desired

Skills

ASP/HTML

Openwebdesign.org integration

Serverside includes

DB

Create tables and define relationships

Creating Composite Keys

Select count query

Multi-table selects using inner join

Transactions – multiple step processes including several queries

Concatenation to build a unique key

Update Query

Delete query

ExecuteScalar

ExecuteNonQuery

ExecuteQuery

ASP.NET

ConfigurationSettings/Web.config

Repeaters

OleDbParameter Arrays

Request.QueryString

Response.Redirect

Control Structure – IF ELSE

WebForm values

Inline ASP calls to a variable

Copyright © 2006 i.c.stars.
All Rights Reserved. / 1 / Bookstore Database Case