The Online Retail Web Site

The first part of an e-commerce solution that most people think of is a Web site at which customers can view products and make purchases online. In our application, this part of the solution is implemented as an ASP-based Web site in the WWWRoot\RetailSite folder. The pages at this site submit requests for XML data using ADO to the SQL Server RetailSite database, as illustrated in Figure 9-2.

Figure 9.2 - Architecture of the RetailSite Web site

I decided to implement the retail site as an ASP-based application because the site requires custom user authentication and state management functionality, and these could be easily handled using server-side scripts on the Active Server Pages in the site. I might have realized some performance gains by implementing the site as a SQLISAPI application, using templates to publish data and updategrams to allow customer and order data to be entered. That strategy, however, would have made it more difficult to include the kind of custom business logic that scripting (or calls to custom components) allows.

Displaying Product Categories

When users first navigate our retail site, they're presented with a frame-based home page. A list of product categories is displayed in the left pane, allowing the user to browse products by category. The list of categories is retrieved as XML by the code on the Menu.asp page, and transformed to HTML by means of a style sheet. Here's the code that retrieves the data and applies the style sheet:

 Dim strQry Dim conDB Dim cmdCategories Dim strmQuery Dim strmResult 'Root element and namespace for the XML query Const XML_HEADER = _     "<categorylist xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" Const XML_FOOTER = "</categorylist>" 'Define query string. strQry = XML_HEADER strQry = strQry & "<sql:query>" strQry = strQry & "EXEC getCategories" strQry = strQry & "</sql:query>" strQry = strQry & XML_FOOTER 'Connect to database and configure Command object. Set conDB = CreateObject("ADODB.Connection") conDB.ConnectionString = strCon conDB.Open Set cmdCategories = CreateObject("ADODB.Command") Set cmdCategories.ActiveConnection = conDB 'Create Query stream for inbound XML query. Set strmQuery = CreateObject("ADODB.Stream") strmQuery.Open strmQuery.WriteText strQry, adWriteChar strmQuery.Position = 0 Set cmdCategories.CommandStream = strmQuery 'Specify the style sheet to be used. cmdCategories.Properties("XSL") = Server.MapPath("AppFiles\Menu.xsl") 'Create result stream for the retrieved document. Set strmResult = CreateObject("ADODB.Stream") strmResult.Open cmdCategories.Properties("Output Stream") = strmResult 'Execute query. cmdCategories.Execute , , adExecuteStream Response.Write strmResult.ReadText Set strmResult = Nothing Set strmQuery = Nothing Set cmdCategories = Nothing Set conDB = Nothing 

I defined the connection string and various constants used in this page in an include file named ADOData.asp so that I could reuse them throughout the site. The code calls the getCategories stored procedure, defined as follows:

 CREATE PROC getCategories AS SELECT CategoryID, CategoryName FROM Categories FOR XML AUTO, ELEMENTS 

Using a stored procedure, rather than embedding the query in the template on the ASP, yields a performance boost on subsequent executions of the procedure because the compiled execution plan will be cached on the SQL Server. So I took this approach in the retail site solution for most occasions that data must be retrieved from the database. The getCategories procedure retrieves the list of categories in the following XML format:

 <categorylist xmlns:sql="urn:schemas-microsoft-com:xml-sql">     <Categories>         <CategoryID>1</CategoryID>         <CategoryName>Computers</CategoryName>     </Categories>     <Categories>         <CategoryID>2</CategoryID>         <CategoryName>Monitors</CategoryName>     </Categories>     <Categories>         <CategoryID>3</CategoryID>         <CategoryName>Printers</CategoryName>     </Categories>     <Categories>         <CategoryID>4</CategoryID>         <CategoryName>Cables Etc.</CategoryName>     </Categories>     <Categories>         <CategoryID>5</CategoryID>         <CategoryName>Software</CategoryName>     </Categories> </categorylist> 

The Menu.xml style sheet, which is applied to this XML data, is reproduced here:

 <?xml version="1.0"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"      version="1.0">     <xsl:template match="/">         <HTML>             <HEAD>                 <Title>                     Category List                 </Title>             </HEAD>             <BODY bgcolor='Gainsboro'>                 <FONT face= 'Arial'>                 <P><A target='Products' href="Main.asp">Home</A></P>                 <P><A target='Products' href="Login.htm">Login</A>                 /<A target='Products' href="Logout.asp">Logout</A></P>                 <P><A target='Products' href="Basket.asp">Basket</A></P>                 <TABLE border="0">                     <TR><TD><B>Product Categories</B></TD></TR>                     <xsl:for-each select="categorylist/Categories">                     <TR>                         <TD>                             <A TARGET="Products">                                 <xsl:attribute name="HREF">                                     Browse.asp?categoryID=                                     <xsl:value-of select="CategoryID"/>                                 </xsl:attribute>                                 <xsl:value-of select="CategoryName"/>                             </A>                         </TD>                     </TR>                     </xsl:for-each>                 </TABLE>                 </FONT>             </BODY>         </HTML>     </xsl:template> </xsl:stylesheet> 

As you can see, this style sheet constructs the entire menu pane and creates links to Browse.asp that include the category ID of the category clicked by the user as a parameter. You can see the menu pane produced by this code in Figure 9-3.

Figure 9.3 - The main RetailSite page, including Menu.asp in the left pane

Displaying the Products in a Specified Category

When a user clicks a category, Browse.asp is executed and the results are displayed in the main pane. The code in Browse.asp is shown here:

 Response.Write RenderCatalog(Request.QueryString("CategoryID")) Function RenderCatalog(intCategory) 'Function to retrieve products in a specified catalog Dim ConDB Dim cmdProducts Dim strmResult 'Connect to database. Set conDB = CreateObject("ADODB.Connection") conDB.Open strCon Set cmdProducts = CreateObject("ADODB.Command") Set cmdProducts.ActiveConnection = conDB 'Use the CatalogSchema.xml annotated schema. cmdProducts.Dialect = "{ec2a4293-e898-11d2-b1b7-00c04f680c56}"  cmdProducts.Properties("mapping schema") = _     Server.MapPath("AppFiles\CatalogSchema.xml") 'XPath for category cmdProducts.CommandText = "Catalog/Category/Product[../@Category]"  'Specify output stream for results. Set strmResult = CreateObject("ADODB.Stream") strmResult.Open cmdProducts.Properties("Output Stream") = strmResult 'Specify the root element for the returned document. cmdProducts.Properties("XML Root") = "CatalogData" 'Specify the XSL to be used. cmdProducts.Properties("XSL") = Server.MapPath("AppFiles\Products.xsl") cmdProducts.Execute , , adExecuteStream RenderCatalog = strmResult.ReadText Set strmResult = Nothing Set cmdProducts = Nothing Set conDB = Nothing End Function 

The products in the specified category are retrieved by using the selected category ID in an XPath expression against an annotated schema. The schema is used to define a product catalog document that can be downloaded from the supplier and reused here to retrieve the product list. Reusing a schema in this way means that should any minor changes be made to the definition of a product catalog, the impact of those changes on the application can be minimized.

Here's the CatalogSchema.xml schema:

 <?xml version="1.0"?> <Schema xmlns="urn:schemas-microsoft-com:xml-data"      xmlns:sql="urn:schemas-microsoft-com:xml-sql">             <ElementType name="Catalog" sql:is-constant="1">         <element type="Category"/>     </ElementType>     <ElementType name="Category" sql:relation="Categories">         <AttributeType name="CategoryID"/>         <AttributeType name="CategoryName"/>         <attribute type="CategoryID" sql:field="CategoryID"/>         <attribute type="CategoryName" sql:field="CategoryName"/>         <element type="Product">             <sql:relationship key-relation="Categories"                 key="CategoryID"                 foreign-key="CategoryID"                 foreign-relation="Products"/>         </element>     </ElementType>     <ElementType name="Product" sql:relation="Products">          <AttributeType name="ProductID"/>         <AttributeType name="ProductName"/>         <AttributeType name="UnitPrice"/>         <AttributeType name="Tax"/>          <attribute type="ProductID" sql:field="ProductID"/>         <attribute type="ProductName" sql:field="ProductName"/>         <attribute type="UnitPrice" sql:field="UnitPrice"/>         <attribute type="Tax" sql:field="Tax"/>     </ElementType> </Schema> 

This schema maps data in an XML catalog document to the Categories and Products tables in the RetailSite database. The XPath used in Browse.asp retrieves the Product elements that have a parent Category element with a CategoryID attribute value the same as the one passed to the page in the URL.

The following XML shows the result of passing a CategoryID value of 1 to Browse.asp:

 <?xml version="1.0" encoding="utf-8" ?> <CatalogData>     <Product Product ProductName="Desktop System"          UnitPrice="1000" Tax="250"/>     <Product Product ProductName="Server System"          UnitPrice="1500" Tax="375"/>     <Product Product ProductName="Laptop System"          UnitPrice="1300" Tax="325"/> </CatalogData> 

This XML is rendered as HTML using the Products.xsl style sheet:

 <?xml version="1.0"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"      version="1.0">     <xsl:template match="/">     <Title>         ProductList     </Title>     <Body>         <FONT face= "Arial" color="navy">                   <TABLE border="0" cellspacing="5">                 <TR>                     <TD><B>Product ID</B></TD>                     <TD><B>Product Name</B></TD>                     <TD><B>Price</B></TD>                     <TD><B>Tax</B></TD>                 </TR>                 <xsl:for-each select="CatalogData/Product">                     <TR>                         <TD>                             <xsl:value-of select="@ProductID"/>                         </TD>                         <TD>                             <A TARGET="Products">                                 <xsl:attribute name="HREF">                                     Product.asp?ProductID=<xsl:value-of                                     select="@ProductID"/>                                 </xsl:attribute>                                 <xsl:value-of select="@ProductName"/>                             </A>                         </TD>                         <TD>                             <xsl:value-of select="@UnitPrice"/>                         </TD>                         <TD>                             <xsl:value-of select="@Tax"/>                         </TD>                     </TR>                 </xsl:for-each>             </TABLE>         </FONT>     </Body>     </xsl:template> </xsl:stylesheet> 

The list of products is displayed in the main pane of the site, as shown in Figure 9-4.

Figure 9.4 - The product list produced by Browse.asp

Displaying Product Details

When the user clicks a product in Browse.asp, the product ID is passed to Product.asp and the following code is used to retrieve the product details:

 Dim objXML 'Call RenderProduct function, passing the requested product ID. Response.Write RenderProduct(Request.QueryString("ProductID")) Function RenderProduct(intProductID) 'Method to retrieve product details in HTML format Dim cmdProduct Dim strmQuery Dim strmResult Dim strQry Dim conDB 'Root element and namespace for XML query Const XML_HEADER = _     "<productdetails xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" Const XML_FOOTER = "</productdetails>" 'Define query string. strQry = XML_HEADER strQry = strQry & "<sql:query>" strQry = strQry & "EXEC getProductDetails " & intProductID strQry = strQry & "</sql:query>" strQry = strQry & XML_FOOTER 'Connect to database and configure Command object. Set conDB = CreateObject("ADODB.Connection") conDB.ConnectionString = strCon conDB.Open Set cmdProduct = CreateObject("ADODB.Command") Set cmdProduct.ActiveConnection = conDB 'Create Query stream. Set strmQuery = CreateObject("ADODB.Stream") strmQuery.Open strmQuery.WriteText strQry, adWriteChar strmQuery.Position = 0 Set cmdProduct.CommandStream = strmQuery 'Specify the style sheet to be used. cmdProduct.Properties("XSL") = _        Server.MapPath("AppFiles\ProductDetails.xsl") 'Create result stream. Set strmResult = CreateObject("ADODB.Stream") strmResult.Open cmdProduct.Properties("Output Stream") = strmResult 'Execute query. cmdProduct.Execute , , adExecuteStream RenderProduct = strmResult.ReadText Set strmResult = Nothing Set strmQuery = Nothing Set cmdProduct = Nothing Set conDB = Nothing End Function 

This code is very similar to the code in Menu.asp. It calls the getProductDetails stored procedure, passing the product ID as a parameter, to retrieve the product data. The definition of the getProductDetails stored procedure is shown here:

 CREATE PROC getProductDetails @ProductID integer AS SELECT * FROM products WHERE ProductID = @ProductID FOR XML AUTO, ELEMENTS 

This query returns the following XML when a ProductID parameter of 1 is passed:

 <productdetails xmlns:sql="urn:schemas-microsoft-com:xml-sql">     <products>         <ProductID>1</ProductID>         <ProductName>Desktop System</ProductName>         <CategoryID>1</CategoryID>         <UnitPrice>1000</UnitPrice>         <Tax>250</Tax>     </products> </productdetails> 

This data is rendered as HTML using the ProductDetails.xsl style sheet, which you can see here:

 <?xml version="1.0"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"      version="1.0">     <xsl:template match="/">         <HTML>             <BODY>                 <FONT face='Arial'>                     <FORM id='FORM1' name='FORM1' action='AddItem.asp'                          method='post'>                     <P>                         <STRONG>ProductID:</STRONG>                          <INPUT readOnly='true' name='txtProductID'>                             <xsl:attribute name='value'>                                 <xsl:value-of select=                                     "productdetails/products/ProductID"/>                             </xsl:attribute>                         </INPUT>                     </P>                     <P>                         <STRONG>Product Name:</STRONG>                          <INPUT readOnly='true' size='57'                              name= 'txtProductName'>                             <xsl:attribute name='value'>                                 <xsl:value-of select=                                     "productdetails/products/ProductName"/>                             </xsl:attribute>                         </INPUT>                      </P>                     <P>                         <STRONG>Price:</STRONG>                         <INPUT readOnly='true' name='txtUnitPrice'>                             <xsl:attribute name='value'>                                 <xsl:value-of select=                                     "productdetails/products/UnitPrice"/>                             </xsl:attribute>                         </INPUT>                     </P>                     <P>                         <STRONG>Tax:</STRONG>                         <INPUT readOnly='true' size='55' name='txtTax'>                             <xsl:attribute name='value'>                                 <xsl:value-of select=                                       "productdetails/products/Tax"/>                             </xsl:attribute>                         </INPUT>                     </P>                     <P></P>                     <P>                         Add <INPUT size='7' value='1' name='txtQuantity'/>                         to shopping basket                         <INPUT type='submit' value='Submit'                              name='submit1'/>                     </P>                     </FORM>                  </FONT>             </BODY>         </HTML>     </xsl:template> </xsl:stylesheet> 

This style sheet causes the product details to be displayed in read-only fields in a form and creates an additional field that allows the customer to specify the quantity he'd like to add to his shopping basket. When the user clicks the Submit button on the form, the data in the fields is posted to AddItem.asp.

Customer Authentication

Before users can add any items to their shopping baskets, they must log in and be authenticated by the site. Each user is identified by means of a Globally Unique Identifier (GUID) and must provide a user name and password to log in. User data is stored in the Users table in the RetailSite database and the application accesses that data by using the addUser and getUserID stored procedures.

To register, a new user must fill in the form on the Register.htm page. The application then posts the details to Register.asp, which calls the addUser stored procedure to add the user data to the Users table. The addUser stored procedure is defined below.

 CREATE PROC addUser  @UserName varchar(30),                       @Password varchar(20),                       @UserID varchar(36) OUTPUT AS SET @UserID = NewID() INSERT Users VALUES (@UserID, @UserName, @Password) GO 

This procedure generates a new GUID to identify the user, which is passed back to the calling application when the details have been entered into the Users table so that the user can be immediately logged in.

On subsequent visits, the user must log in using the Login.htm form, which posts data to the Login.asp script. Login.asp calls the getUserID stored procedure to authenticate the user and retrieve her user ID. The getUserID stored procedure is shown here:

 CREATE PROC getUserID  @Username varchar(30),                         @Password varchar(20),                         @UserID varchar(36) OUTPUT AS SELECT @UserID = UserID  FROM Users  WHERE username = @UserName AND userpassword = @Password 

If this procedure returns a row, the user has been authenticated. If no row is returned, the login has failed.

In a production site, both Register.asp and Login.asp should be accessed over an encrypted connection by means of the HTTPS protocol.

Maintaining Authentication State

Once a user has been authenticated, the application must maintain her authenticated status for the rest of the session. Information that must be maintained between user requests is called state, and there are a number of problems associated with managing it in an e-commerce solution. Although the ASP object model provides a Session object in which data can be persisted, you should generally avoid maintaining state here because of performance issues and server-affinity problems that occur when you use a Web farm to load balance user requests. The strategy that most e-commerce sites adopt is to send a key value to the client that can be resubmitted with each subsequent request and used to track the user throughout the session. In our RetailSite solution, the customer's ID is sent to the browser as a cookie and checked on subsequent requests. Here's the code in Login.asp that writes the cookie:

 Dim strUserName 'As String Dim strPassword 'As String Dim strUserID 'As String Dim objSecurity 'Clear existing cookies. Response.Cookies("userauthid") = "" 'Retrieve supplied user name and password. '(In reality we'd use an HTTPS connection for this.) strUserName = Request.Form("txtUserName") strPassword = Request.Form("txtPassword") strUserID = GetUserID(strUserName, strPassword) If strUserID= "invalid" Then     'Incorrect details have been supplied; redirect back to logon page.     Response.Write "<H2>Invalid Logon</H2>"     Response.Write "Please try again."     Server.Transfer "Login.htm" Else     'Write UserID to cookie so we know the user has been authenticated.     Response.Cookies("userauthid")= strUserID     Server.Execute "Main.asp" End If Function GetUserID(strUserName, strPassword) 'Function to authenticate a user and return his user ID Dim cmd Dim conDB Dim strUserID 'Initialize strUserID variable. strUserID = "invalid" 'Check valid data has been supplied, then call the getUserID  'stored procedure. If ValidData(strUserName, strPassword) Then     Set cmd = CreateObject("ADODB.Command")     With cmd         .ActiveConnection = strCon         .CommandText = "getUserID"         .CommandType = adCmdStoredProc         .Parameters(1).Value = strUserName         .Parameters(2).Value = strPassword         .Execute     End With     'Check for returned user ID.     If Not IsNull(cmd.Parameters(3).Value) Then         strUserID = cmd.Parameters(3).Value     End If End If GetUserID = strUserID Set cmd = Nothing End Function 

This code clears the authentication cookie (in case the user had already logged in using different credentials), and then calls the getUserID stored procedure. If the user hasn't provided a valid username and password combination, she's redirected back to the Login.htm page. If authentication is successful, the user's ID is assigned to the cookie.

Managing the Shopping Basket

Once a user has been authenticated, she can use a shopping basket to manage the goods she wants to purchase. The shopping basket is implemented as a table named Basket, in which a customer's ID is used to track the items placed in the basket by that particular customer. The items aren't removed from the basket until the customer explicitly deletes them or checks out; therefore, items added to the basket will remain in the basket between sessions.

Adding Items to the Shopping Basket

When a customer views the product details for a product she wants to buy, she can click the Submit button on Product.asp, which posts the product data and quantity required to the AddItem.asp page. Here's the code in AddItem.asp:

 Dim strUserID  Dim lngProductID  Dim intQuantity  Dim curUnitPrice Dim curTax 'Check for authentication cookie. strUserID = Request.Cookies("userauthid") If strUserID = "" Then     Response.Write "You must log in before making any purchases."     Server.transfer "Login.htm" End If 'Get item data from form. lngProductID = Request.Form("txtProductID") intQuantity = Request.Form("txtQuantity") curUnitPrice = Request.Form("txtUnitPrice") curTax = Request.Form("txtTax") AddItemToBasket strUserID, lngProductID, intQuantity, curUnitPrice, curTax Response.Write intQuantity & _     " units have been added to your <A href='Basket.asp'>" & _     "Shopping Basket</A>." Sub AddItemToBasket(strCustomer, lngProductID, intQuantity,                      curUnitPrice, curTax) 'Procedure to add an item to the shopping basket Dim cmd 'If one or more items is ordered, use the addItem stored procedure. If intQuantity > 0 Then Set cmd = CreateObject("ADODB.Command")     With cmd         .ActiveConnection = strCon         .CommandText = "addItem"         .CommandType = adCmdStoredProc         .Parameters(1).Value = strCustomer         .Parameters(2).Value = lngProductID         .Parameters(3).Value = intQuantity         .Parameters(4).Value = curUnitPrice         .Parameters(5).Value = curTax         .Execute     End With End If Set cmd = Nothing End Sub 

First of all, this page checks for an authentication cookie. If the user hasn't been authenticated, she's redirected to the Login.htm page. Next a local procedure is used to make sure that if the quantity requested is greater than zero, a stored procedure will be used to add the item to the Basket table. Finally the application displays a confirmation message, with a link to Basket.asp.

The stored procedure used to add items to the Basket table is defined as follows:

 CREATE PROC addItem  @Customer varchar(40),  @ProductID int,  @Quantity int,  @UnitPrice money,  @Tax money AS INSERT Basket VALUES (@Customer, @ProductID, @Quantity, @UnitPrice, @Tax) 

This procedure simply inserts a row containing the specified values into the Basket table.

Viewing the Shopping Basket

Customers can view the contents of their shopping baskets by navigating to Basket.asp (by following either the link created by AddItem.asp or the Basket link in the menu pane of the retail site). To provide more meaningful information, Basket.asp retrieves data from a view that combines data from the Basket and Products tables. This view is named BasketView and is defined as follows:

 CREATE VIEW BasketView AS SELECT   P.ProductID, P.ProductName,  B.UnitPrice, B.Tax,  B.Quantity, B.Customer FROM Products P JOIN Basket B ON P.ProductID = B.ProductID 

The code in Basket.asp retrieves data from this view in XML format as shown in the following code:

 Dim strUser Dim objShopping 'Check for authentication cookie. strUser = Request.Cookies("userauthid") If struser = "" Then     server.transfer "Login.htm" End If Response.Write ViewBasket(strUser) Function ViewBasket(strCustomer) 'Method to view the contents of the shopping basket as HTML Dim cmdBasket Dim strmQuery  Dim strmResult Dim strQry Dim conDB 'Root element and namespace for XML query Const XML_HEADER = _     "<basketcontents xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" Const XML_FOOTER = "</basketcontents>" 'Define query string. strQry = XML_HEADER strQry = strQry & "<sql:query>" strQry = strQry & "EXEC getBasket '" & strCustomer & "'" strQry = strQry & "</sql:query>" strQry = strQry & XML_FOOTER 'Connect to database and configure Command object. Set conDB = CreateObject("ADODB.Connection") conDB.ConnectionString = strCon conDB.Open Set cmdBasket = CreateObject("ADODB.Command") Set cmdBasket.ActiveConnection = conDB 'Create Query stream for the inbound XML query. Set strmQuery = CreateObject("ADODB.Stream") strmQuery.Open strmQuery.WriteText strQry, adWriteChar strmQuery.Position = 0 Set cmdBasket.CommandStream = strmQuery 'Specify the style sheet to be used to transform the results to HTML. cmdBasket.Properties("XSL") = Server.MapPath("AppFiles\Basket.xsl") 'Create result stream for the query results. Set strmResult = CreateObject("ADODB.Stream") strmResult.Open cmdBasket.Properties("Output Stream") = strmResult 'Execute query. cmdBasket.Execute , , adExecuteStream ViewBasket = strmResult.ReadText Set strmResult = Nothing Set strmQuery = Nothing Set cmdOrders = Nothing Set conDB = Nothing End Function 

The getBasket stored procedure called by this code is defined as follows:

 CREATE PROC getBasket @customer varchar(40) AS SELECT * FROM BasketView  WHERE Customer = @customer FOR XML AUTO, ELEMENTS 

This stored procedure returns the items in the Basket table belonging to the specified customer. The XML returned by this procedure is similar to the following example:

 <basketcontents xmlns:sql="urn:schemas-microsoft-com:xml-sql">     <BasketView>         <ProductID>1</ProductID>         <ProductName>Desktop System</ProductName>         <UnitPrice>1000</UnitPrice>         <Tax>250</Tax>         <Quantity>1</Quantity>         <Customer>AA5733A9-712A-4389-9DA4-4F6408F39F74</Customer>     </BasketView>     <BasketView>         <ProductID>5</ProductID>         <ProductName>Flat Panel Screen</ProductName>         <UnitPrice>1000</UnitPrice>         <Tax>250</Tax>         <Quantity>1</Quantity>         <Customer>AA5733A9-712A-4389-9DA4-4F6408F39F74</Customer>     </BasketView> </basketcontents> 

This XML is rendered using the Basket.xsl style sheet, which is reproduced here:

 <?xml version="1.0"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"      version="1.0">     <xsl:template match="/">         <Title>Basket</Title>         <Body>             <FONT face= "Arial" color="navy">                 <P><B>Contents of Your Shopping Basket</B></P>                       <TABLE border="0" cellspacing="5">                     <TR>                         <TD><B>Product ID</B></TD>                         <TD><B>Product Name</B></TD>                         <TD><B>Price</B></TD>                         <TD><B>Tax</B></TD>                         <TD><B>Quantity</B></TD>                     </TR>                     <xsl:for-each select="basketcontents/BasketView">                         <TR>                             <TD>                                 <xsl:value-of select="ProductID"/>                             </TD>                             <TD>                                 <xsl:value-of select="ProductName"/>                             </TD>                             <TD>                                 <xsl:value-of select="UnitPrice"/>                             </TD>                             <TD>                                 <xsl:value-of select="Tax"/>                             </TD>                             <TD>                                 <xsl:value-of select="Quantity"/>                             </TD>                             <TD>                                 <FONT size='-2'>                                     <A TARGET="Products">                                         <xsl:attribute name="HREF">                                             RemoveItem.asp?ProductID=                                             <xsl:value-of select=                                                 "ProductID"/>                                         </xsl:attribute>                                         Remove                                     </A>                                 </FONT>                             </TD>                         </TR>                     </xsl:for-each>                 </TABLE>                 <FORM name='Form1' action='Payment.asp'>                     <INPUT type='submit' value='Checkout' name='submit1'/>                 </FORM>                 <A Target="Products" HREF="MyOrders.asp">                     Invoices From Past Orders                 </A>             </FONT>         </Body>     </xsl:template> </xsl:stylesheet> 

This style sheet renders the basket information in a table and provides a link allowing individual items to be removed. It also includes a form that the user can use to check out as well as a link that allows the user to view previous invoices. The HTML produced by Basket.asp is shown in Figure 9-5.

Figure 9.5 - The Shopping Basket

Removing Items from the Shopping Basket

Customers can click a link on Basket.asp to remove an individual item from the basket. The Remove link passes the ProductID as a parameter to RemoveItem.asp, and the code in RemoveItem.asp calls a stored procedure to delete the record from the Basket table, as shown here:

 Dim objShopping Dim strUserID Dim lngProductID 'Check authentication cookie. strUserID = Request.Cookies("userauthid") If strUserID = "" Then     Server.Transfer "Login.htm" End If 'Retrieve requested product ID. lngProductID = Request.QueryString("ProductID") 'Call RemoveItemFromBasket function. RemoveItemFromBasket strUserID, lngProductID Server.transfer "Basket.asp" Public Sub RemoveItemFromBasket(strCustomer, lngProductID) 'Function to remove an item from the shopping basket Dim cmd 'Call the removeItem stored procedure. Set cmd = CreateObject("ADODB.Command") With cmd     .ActiveConnection = strCon     .CommandText = "removeItem"     .CommandType = adCmdStoredProc     .Parameters(1).Value = strCustomer     .Parameters(2).Value = lngProductID     .Execute End With Set cmd = Nothing End Sub 

This code passes the customer's ID and the ID of the product to be removed to the removeItem stored procedure, which is defined as follows:

 CREATE PROC removeItem @Customer varchar(40), @ProductID int AS DELETE Basket WHERE Customer = @Customer AND ProductID = @ProductID 

The removeItem stored procedure simply deletes the record in the Basket table that relates to the specified product and customer. The code in RemoveItem.asp then transfers the user back to the Basket.asp page.

Checking Out

When a user is ready to complete his purchase, he clicks the Submit button in the form on Basket.asp to start the check-out process. The customary first stage of this process is to allow the customer to enter delivery and payment information; our application uses Payment.asp for this purpose. This page displays a form in which the customer enters his credit card number and the card's expiration date, the name of the credit card holder, and the address to which the goods should be delivered. This information is then passed to Checkout.asp where the actual check-out process occurs. (In this sample application, we use only the delivery address data—the credit card data is discarded.)

The code in Checkout.asp performs three main tasks. First it calls a stored procedure to enter the order details into the Orders and OrderDetails tables and remove those items from the basket. Next it uses an annotated schema to retrieve the order information as an XML purchase order. And finally it posts the purchase order to the supplier site. Here's the code for Checkout.asp:

 Dim strUserID Dim strAddress Dim strOrderNo 'Check for authentication cookie. strUserID = Request.Cookies("userauthid") If strUserID = "" Then     Server.Transfer "Login.htm" End If 'Get the delivery address. strAddress = Request.Form("txtAddress") 'Call Checkout function passing the user's ID and address. strOrderNo = CheckOut(strUserID, strAddress) Response.Write "Your order has been placed successfully.<BR>" 'The order number is returned by the Checkout function. Response.Write "The order number is " & strOrderNo Response.Write "<BR>Take a note of this number. " & _     "You will need to quote it in the event of any enquiries." Function CheckOut(strCustomer, strAddress) 'Function to check out. 'This function clears the basket and logs the order data. 'Then the order is retrieved as XML and sent to the supplier using HTTP. Dim cmd Dim conDB Dim OrderNo 'Use Checkout stored procedure to clear basket and log order in the  'database. Set conDB = CreateObject("ADODB.Connection") conDB.Open strCon Set cmd = CreateObject("ADODB.Command") With cmd     Set .ActiveConnection = conDB         .CommandText = "checkOut"         .CommandType = adCmdStoredProc         .Parameters(1).Value = strCustomer         .Parameters(2).Value = strAddress         .Execute End With If Not IsNull(cmd.Parameters(3).Value) Then     OrderNo = cmd.Parameters(3).Value Else     OrderNo = "Invalid Order" End If      If OrderNo <> "Invalid Order" Then 'Retrieve Order detail as XML using the OrderSchema.xml annotated schema. Dim cmdOrder Dim strmResult Set cmdOrder = CreateObject("ADODB.Command") Set cmdOrder.ActiveConnection = conDB 'XPath Dialect cmdOrder.Dialect = "{ec2a4293-e898-11d2-b1b7-00c04f680c56}"  cmdOrder.Properties("mapping schema") = _     Server.MapPath("AppFiles\OrderSchema.xml") 'XPath for the order data cmdOrder.CommandText = "Order[@OrderNo='" & OrderNo & "']"  Set strmResult = CreateObject("ADODB.Stream") strmResult.Open cmdOrder.Properties("Output Stream") = strmResult 'Root element cmdOrder.Properties("XML Root") = "PurchaseOrder"  cmdOrder.Execute , , adExecuteStream Dim strXML strXML = strmResult.ReadText 'Post the XML Order over HTTP to the supplier. Dim objHTTP Set objHTTP = CreateObject("Microsoft.XMLHTTP") objHTTP.Open "POST", _     "HTTP://localhost/SupplierSite/PlaceOrder.asp?txtOrder=" & _     strXML, False objHTTP.send Set strmResult = Nothing Set cmdOrder = Nothing Set conDB = Nothing End If CheckOut = OrderNo Set cmd = Nothing End Function 

The checkOut stored procedure is used to insert the order data into the Orders and OrderDetails tables, and remove the items from the Basket table. This stored procedure is defined as follows:

 CREATE PROC checkOut @Customer varchar(40),                       @Address varchar(255), @OrderNo integer OUTPUT AS BEGIN TRAN -- Get the next order number. SELECT @OrderNo = MAX(OrderNo) + 1 FROM Orders --Insert the order header. INSERT Orders VALUES (@OrderNo,GetDate(), 'RetailerSite','SupplierSite',   @Customer, @Address, 'placed') --Insert order details and order number into a temporary table. SELECT @OrderNo OrderNo, ProductID, Quantity, UnitPrice, Tax INTO #OrderDetails FROM Basket --Insert the contents of the temporary table into the Order Details table. INSERT OrderDetails SELECT OrderNo, ProductID, Quantity, UnitPrice, Tax FROM #OrderDetails --Remove all items from the basket for this user. DELETE Basket WHERE Customer = @Customer --Clean up. DROP TABLE #OrderDetails COMMIT TRAN 

This stored procedure encapsulates the process in a transaction. The next available order number is retrieved from the Orders table, and the order data is inserted. Then all records belonging to the current customer are removed from the Basket table and the order number is returned to the caller.

Once the order data has been entered into the database, a purchase order document must be sent to the supplier in an XML format the trading partners have agreed to use. The format is defined in the OrderSchema.xml XDR schema, which has been annotated to make it easy to extract the data from the database. The OrderSchema.xml schema is shown here:

 <?xml version="1.0" ?> <Schema xmlns="urn:schemas-microsoft-com:xml-data"     xmlns:dt="urn:schemas-microsoft-com:datatypes"     xmlns:sql="urn:schemas-microsoft-com:xml-sql">     <ElementType name="ProductID"/>     <ElementType name="Quantity"/>     <ElementType name="UnitPrice"/>     <ElementType name="Tax"/>     <ElementType name="OrderDetail" model="closed"          sql:relation="OrderDetails">         <element type="ProductID" sql:field="ProductID" minOccurs="1"               maxOccurs="1"/>         <element type="Quantity" sql:field="Quantity" minOccurs="1"              maxOccurs="1"/>         <element type="UnitPrice" sql:field="UnitPrice" minOccurs="1"              maxOccurs="1"/>         <element type="Tax" sql:field="Tax" minOccurs="1" maxOccurs="1"/>  </ElementType>     <AttributeType name="OrderNo"/>     <ElementType name="RetailerID"/>     <ElementType name="SupplierID"/>     <ElementType name="Customer"/>     <ElementType name="DeliveryAddress"/>     <ElementType name="OrderDate"/>     <ElementType name="OrderStatus"/>     <ElementType name="Order" model="closed" sql:relation="Orders">         <attribute type="OrderNo" sql:field="OrderNo" required="yes"/>         <element type="OrderDate" sql:field="OrderDate" minOccurs="1"               maxOccurs="1"/>         <element type="RetailerID" sql:field = "RetailerID" minOccurs="1"              maxOccurs="1"/>         <element type="SupplierID" sql:field = "SupplierID" minOccurs="1"              maxOccurs="1"/>         <element type="Customer" sql:field="Customer" minOccurs="1"              maxOccurs="1"/>         <element type="DeliveryAddress" sql:field="DeliveryAddress"              minOccurs="1" maxOccurs="1"/>         <element type="OrderStatus" sql:field="OrderStatus" minOccurs="1"              maxOccurs="1"/>         <element type="OrderDetail" minOccurs = "1" maxOccurs="*">             <sql:relationship key-relation="Orders"                 key="OrderNo"                 foreign-key="OrderNo"                 foreign-relation="OrderDetails"/>         </element>     </ElementType>     <ElementType name="PurchaseOrder" model="closed" sql:is-constant="1">         <element type="Order" minOccurs="1" maxOccurs="1"/>     </ElementType> </Schema> 

This schema defines a PurchaseOrder element that contains a single Order element. The Order element in turn contains one or more OrderDetail elements. The following example shows the structure of a purchase order document defined by this schema:

 <?xml version="1.0" encoding="utf-8" ?> <PurchaseOrder>     <Order OrderNo="1000">         <OrderDate>2001-03-13T14:37:59.453</OrderDate>         <RetailerID>RetailerSite</RetailerID>         <SupplierID>SupplierSite</SupplierID>         <Customer>0A00497C-DFC6-4F1C-AAED-959943A63A86</Customer>         <DeliveryAddress>1 Any Street, Anytown</DeliveryAddress>         <OrderStatus>placed</OrderStatus>         <OrderDetail><ProductID>1</ProductID>             <Quantity>1</Quantity>             <UnitPrice>1000</UnitPrice>             <Tax>250</Tax>         </OrderDetail>         <OrderDetail>             <ProductID>9</ProductID>             <Quantity>1</Quantity>             <UnitPrice>300</UnitPrice>             <Tax>75</Tax>         </OrderDetail>     </Order> </PurchaseOrder> 

The application then sends this document to the supplier site by using the Microsoft XMLHTTP object. This object comes with the Microsoft XML parser and can be used to make HTTP POST and GET requests over the Internet. In this case, the document is posted asynchronously to the PlaceOrder.asp page in the supplier's extranet site. In the sample application, the data is sent anonymously and unencrypted. In a production environment, however, you would probably need to specify a username and password in the call to the Open method and use an HTTPS URL to open a secure session.