ASP.NET 2.0 Everyday Apps For Dummies (2006)

Chapter 6: Building a Shopping Cart Application 157

Creating the database

You can create the Cart database manually from within Visual Studio by using the Server Explorer. Or you can run the CreateCartDB.sql script that’s shown in Listing 6-1. The easiest way to run this script is to open a command-prompt window, change to the directory that contains the script, and enter this command:

sqlcmd -S localhost\SQLExpress -i CreateCartDB.sql

This command assumes you’re running SQL Server Express on your own computer. If you’re using SQL Server on a different server, you’ll need to change localhost\SQLExpress to the correct name. If you’re not sure what name to use, ask your database administrator.

Listing 6-1: The CreateCartDB.sql script

USE master

GO

IF EXISTS(SELECT * FROM sysdatabases WHERE name=’Cart’)

DROP DATABASE Cart GO

CREATE DATABASE Cart

ON (NAME=Product,

FILENAME = ‘C:\APPS\Cart.mdf’,

SIZE=10 )

GO

USE Cart

CREATE TABLE Categories ( catid VARCHAR(10) NOT NULL, name VARCHAR(50) NOT NULL,

[desc] VARCHAR(MAX) NOT NULL, PRIMARY KEY(catid) )

GO

CREATE TABLE Products (
productid VARCHAR(10)		NOT NULL,
catid	VARCHAR(10)	NOT NULL,
name	VARCHAR(50)	NOT NULL,
shorttext	VARCHAR(MAX) NOT NULL,
longtext	VARCHAR(MAX) NOT NULL,
price	MONEY	NOT NULL,
thumbnail	VARCHAR(40)	NOT NULL,

1

2

3

4

5

(continued)

158 Part III: Building E-Commerce Applications

Listing 6-1 (continued)

image	VARCHAR(40)	NOT NULL,
PRIMARY KEY(productid),
FOREIGN KEY(catid) REFERENCES Categories(catid) )
GO
CREATE TABLE FeaturedProducts (			6
productid	VARCHAR(10)	NOT NULL,
featuretext VARCHAR(MAX) NOT NULL,
saleprice	MONEY	NOT NULL,
PRIMARY KEY(productid),
FOREIGN KEY(productid) REFERENCES Products(productid) )
GO
CREATE TABLE Customers (			7
email	VARCHAR(50)	NOT NULL,
lastname	VARCHAR(50)	NOT NULL,
firstname	VARCHAR(50)	NOT NULL,
address	VARCHAR(50)	NOT NULL,
city	VARCHAR(50)	NOT NULL,
state	VARCHAR(2)	NOT NULL,
zipcode	VARCHAR(9)	NOT NULL,
phone	VARCHAR(20)	NOT NULL,
PRIMARY KEY(email) )
GO
CREATE TABLE Orders (			8
ordernum	INT	IDENTITY,
orderdate	SMALLDATETIME NOT NULL,
custemail	VARCHAR(50)	NOT NULL,
subtotal	MONEY	NOT NULL,
salestax	MONEY	NOT NULL,
shipping	MONEY	NOT NULL,
total	AS (subtotal + salestax + shipping),
PRIMARY KEY(ordernum),
FOREIGN KEY(custemail) REFERENCES Customers(email) )
GO
CREATE TABLE OrderItems (			9
ordernum	INT	NOT NULL,
productid	VARCHAR(10)	NOT NULL,
name	VARCHAR(50)	NOT NULL,
price	MONEY	NOT NULL,
quantity	SMALLINT	NOT NULL,
total	AS (price * quantity),
PRIMARY KEY(ordernum, productid),
FOREIGN KEY(ordernum) REFERENCES Orders(ordernum),
FOREIGN KEY(productid) REFERENCES Products(productid) )
GO

Chapter 6: Building a Shopping Cart Application 159

Here are some of the more interesting details about this script:

1 This line sets the database context to master. Although that’s usually the default, it’s a good idea to set it just in case.

2 Deletes the existing Cart database if it exists.

3 Creates a database named Cart, placing the database file in the C:\Apps directory. You can change this location if you want to store the database in a different location.

4 Creates the Categories table.

5 Creates the Products table.

6 Creates the FeaturedProducts table.

7 Creates the Customers table.

8 Creates the Orders table.

9 Creates the OrderItems table.

Adding some test data

The CD that comes with this book includes a script named InsertData.sql that inserts test data into the Cart database. You can run this script from a command prompt like this:

sqlcmd -S localhost\SQLExpress -i InsertData.sql

(You may need to change the server name if you’re not using a local instance of SQL Server Express.)

Querying the database

The Shopping Cart application uses the same queries as the Product Catalog application to retrieve product information from the database. For more information about those queries, refer to Chapter 5.

Inserting order data into the database

To insert the data for an order into the database, several SQL statements must be used. Here’s a brief outline of what must be done to successfully process an order:

160 Part III: Building E-Commerce Applications

start a transaction

if the customer already exists in the Customers table update the Customers row with the new data

else

insert a new row in the Customers table insert a new row in the Orders table

get the order number generated for the inserted row for each item in the shopping cart

insert a new row in the OrderItems table if any SQL errors occur

roll back the transaction else

commit the transaction

Note that the entire process is contained within a transaction. That way, if any SQL errors occur during the processing of the order, any updates made prior to the error will be rolled back. The updates to the Customers, Orders, and OrderItems tables aren’t committed until all of the updates are successfully completed.

There are several ways to handle the logic necessary to insert or update the customer data. One way is to run a query to determine whether the customer exists, then execute an INSERT or UPDATE statement depending on the results of the query. This application uses a simpler technique: It assumes that the customer doesn’t already exist, so it executes an INSERT statement to insert the customer data. If this statement fails because of a duplicate primary key, the program executes an UPDATE statement to update the existing customer row with the new data.

The only other challenging bit of SQL code is determining what order number is generated when a row is inserted into the Orders table. This is necessary because the order number must be included in the OrderItems rows for

the order. Fortunately, you can determine the order number by issuing this statement:

SELECT @@IDENTITY

The @@IDENTITY function returns the value of the last identity column generated for the database.

In some cases, the @@IDENTITY function doesn’t return the value you might expect. For example, if you insert a row into a table that contains an identity column, you’d expect @@IDENTITY to return the value generated for that identity column. But that’s not what happens if the table has a trigger associated with it, and that trigger executes another INSERT statement that inserts

Chapter 6: Building a Shopping Cart Application 161

data into another table that also has an identity column. In that case, the @@IDENTITY function returns the identity value for the second table. Fortunately, the Cart database doesn’t use triggers, so the @@IDENTITY function will correctly return the identity value generated by the Orders table.

Connecting to the database

The connection string used to access the Cart database is stored in the application’s web.config file, like this:

<connectionStrings>

<add name=”ConnectionString” connectionString=”Data

Source=localhost\SQLExpress;

Initial Catalog=Cart;Integrated Security=True”/> </connectionStrings>

The only place in the application that references this connection string is here in the web.config file. This makes it easy to relocate the database when you put the application into production.

The Application’s Folders

The Shopping Cart application includes the following folders:

(Root): The application’s root folder contains the application’s six pages (Default.aspx, Product.aspx, Cart.aspx, CheckOut.aspx, and Completed.aspx) as well as the Master Page (Default.master).

App_Data: This folder is designed to store databases used by the application. However, this particular application uses a database that’s stored in a location that’s determined by SQL Server. So the database for our Cart isn’t actually stored in this folder. (If you use the script presented in Listing 6-1 to create the database, the database file is stored in C:\Apps.)

App_Code: This folder contains the C# or Visual Basic code files that define the classes used by the application. For more information about these classes, see the section “Designing the Classes” later in this chapter.

Images: Here, you’ll find the banner image displayed by the Master Page and the image files that show pictures of the store’s products.

162 Part III: Building E-Commerce Applications

Designing the Classes

Unlike most of the other applications presented in this book, the Shopping Cart application depends on several classes that both define the business objects used by the program as well as provide the database access. In particular, the application uses the following classes:

Customer: Represents a single customer.

ShoppingCart: Represents the user’s shopping cart.

CartItem: Represents an item in the user’s shopping cart.

Order: Represents an order.

OrderDB: Handles the details of writing an order to the database.

The following sections describe each of these classes in detail.

The Customer class

The Customer class represents a single customer. Its constructors and properties are spelled out in Table 6-4.

Table 6-4	The Customer Class
Constructor		Description
Customer()		Creates an instance of the Customer
		class with default property values.
Customer(string lastName,		Creates an instance of the Customer
string firstName, string		class with the specified property values.
address, string city,
string state, string
zipCode, string
phoneNumber, string email)
Property		Description
string LastName		The customer’s last name.
string FirstName		The customer’s first name.

		Chapter 6: Building a Shopping Cart Application 163
	Property		Description
	string Address		The customer’s street address.
	string City		The customer’s city.
	string State		The customer’s state.
	string zipCode		The customer’s Zip code.
	string phoneNumber		The customer’s phone number.
	string email		The customer’s e-mail address.

The ShoppingCart class

The ShoppingCart class represents a user’s shopping cart. Its constructors, properties, and methods are listed in Table 6-5.

Table 6-5	The ShoppingCart class
Constructor		Description
ShoppingCart()		Creates a new shopping cart with no
		items.
Property		Description
int Count		The number of items in the shopping cart.
Method		Description
List<CartItem> GetItems()		Returns a List object that contains one
		CartItem object for each item in the
		shopping cart.
void AddItem(string id,		Adds a new item with the specified
string name, decimal price)		product ID, name, and price.
void UpdateQuantity		Updates the quantity at the specified
(int index, int quantity)		index.
void DeleteItem(int index)		Deletes the item at the specified index.
string PhoneNumber		The customer’s phone number.

164 Part III: Building E-Commerce Applications

The CartItem class

The CartItem class represents an item in the user’s shopping cart. Its constructors and properties are listed in Table 6-6.

Table 6-6	The CartItem class
Constructor	Description
CartItem()	Creates a new CartItem object with
	default property values.
CartItem(string ID,	Creates a new CartItem object with
string name, decimal	the specified ID, name, price, and
price, int quantity)	quantity.
Property	Description
string ID	The Product ID for the product repre-
	sented by the item.
string Name	The product name.
decimal Price	The price per unit.
int Quantity	The quantity.
decimal Total	The total for the item (read-only).

The Order class

The Order class represents an order submitted by the user. Its constructors and properties are listed in Table 6-7.

Table 6-7	The Order class
Constructor	Description
Order ()	Creates a new Order object with
	default property values.
Order (date OrderDate,	Creates a new CartItem object with
Customer cust,	the specified order date, customer, and
ShoppingCart cart)	shopping cart.
Property	Description
DateTime OrderDate	The date the order was submitted.
Customer Cust	The customer who submitted the order.

Chapter 6: Building a Shopping Cart Application 165

Property	Description
ShoppingCart Cart	The shopping cart that specifies the items
	being ordered.
decimal SubTotal	The subtotal, calculated by adding up the
	total for each item in the order’s shopping
	cart.
decimal SalesTax	The sales tax for the order (read-only). The
	sales tax is calculated as 7.75% of the
	subtotal if the Customer resides in
	California. Otherwise, the sales tax is zero.
decimal Shipping	The shipping charges for the order (read-
	only). The shipping charge is calculated as
	$2.00 per item.
decimal Total	The total for the order (read-only). The total
	is calculated by adding up the subtotal,
	sales tax, and shipping charges.

The OrderDB class

The OrderDB class handles the task of writing an order to the database. It consists of just a single static method (Shared for all you Visual Basic programmers out there), as described in Table 6-8.

Table 6-8	The OrderDB class
Method	Description
static bool	Writes the order to the Cart database.
WriteOrder(Order o)	Returns true if the order is successfully
	written; otherwise, returns false. The
	connection string for the database is
	obtained from the application’s
	web.config file.

Building the Master page

The Master Page (MasterPage.master) for the Shopping Cart application is shown in Listing 6-2. It’s similar to the Master Page that was used in the Product Listing application shown in Chapter 5. However, it includes an additional label that displays information about the user’s shopping cart and a link that leads to the Cart page.

166 Part III: Building E-Commerce Applications

Listing 6-2: The Master Page (MasterPage.master)

<%@ Master Language=”C#”	1
AutoEventWireup=”true”
CodeFile=”MasterPage.master.cs”
Inherits=”MasterPage” %>
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.1//EN”
“http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd”>
<html xmlns=”http://www.w3.org/1999/xhtml” >
<head runat=”server”>
<title>Acme Pirate Supply</title>
</head>
<body>
<form id=”form1” runat=”server”>
<div>
<img src=”Images/Banner.jpg” />	2
<br />
<asp:Label ID=”lblCart” runat=”server”	3
Font-Size=”Small”/>
<a href=”Cart.aspx”	4
style=”font-size: small”>
Go To Cart</a>
<br /><br />
<asp:contentplaceholder	5
id=”ContentPlaceHolder1” runat=”server”>
</asp:contentplaceholder>

</div>

</form>

</body>

</html>

The following paragraphs describe the key lines of the Master Page:

1 The Master directive identifies the file as a Master Page.

2 The Image control displays a banner image at the top of each page. The Banner.jpg file is stored in the Images folder.

3 The label that displays the number of items currently in the shopping cart. The text for this label is set in the Page_Load method.

4 The link that leads to the Cart page.

5 The ContentPlaceHolder control provides the area where the content for the application’s content pages will be displayed.

The Master Page requires a code-behind file to set the Text property of the label. The C# version of this code-behind file is shown in Listing 6-3, and the Visual Basic version is shown in Listing 6-4.