Streamline e-commerce processing by using SQL Server 2000's XML rowset provider and Commerce Server components
If you develop database-centric e-commerce applications, you know that passing multiple rows of structured data from a Web application to SQL Server in one network round-trip can be difficult. This process is especially complex when the number of rows you need to pass between the Web application and SQL Server is variable, depending on the user's actions. Consider the business logic of a typical business-to-consumer (B2C) e-commerce application. Let's say that customer Heidi places several items in her shopping cart and proceeds to the checkout process. First, she enters her unique identity (typically her email address), then confirms her identity by entering her password. She enters or confirms demographic information (e.g., name, address, and phone number) and payment information (e.g., credit card number and expiration date). Then, she might be able to choose among several additional options, such as shipping method, gift message, and so on. Finally, the application asks her to review her order information before making her purchases final. In this scenario, the e-commerce application must send data associated with multiple database tables to SQL Server. These tables typically include Customer, OrderHeader, and OrderDetail, at a minimum. And several other tables might be involved in the transaction if Heidi used e-coupons, bought a gift certificate, or selected the gift-wrapping option. Figure 1, page 56, shows an entity relationship (ER) diagram depicting this set of relational data.
If you're using SQL Server 7.0 or earlier, inserting and updating data in these tables from a B2C e-commerce application requires you to make multiple round-trips between the application tier and the data-storage tier. You must make one round-trip for each table or stored procedure for which an insert or update takes place. Because these logically separate application tiers typically reside on physically separate servers, inserting and updating data requires multiple network round-trips, as well as coordination of these multiple database calls within a transaction to ensure that the set of calls either succeeds or fails as a unit. You don't want SQL Server to commit only part of the order if a database or application error occurs.
Wouldn't you like to simply place all this customer, order, and promotional data into one hierarchical "information package" and send it over the wire from the application to SQL Server? The ideal solution would also enable the database to then take the data out of this package and perform all the inserts, updates, and deletes on the appropriate tables in one fell swoop. One of SQL Server 2000's new XML-integration features—the OpenXML rowset provider—lets you send multiple rows of structured data from the application to the database in one database call. Let's look at how OpenXML lets you treat a well-formed XML document as a set of rows with which T-SQL statements and functions can interact. But first, to provide a real-world scenario in which to examine the use of OpenXML, let's look at Microsoft Commerce Server components that ease the burden of passing Web-centric data between a user's browser and a Web server.
Commerce Server's OrderForm Component
A customer places an e-commerce order through a browser, such as Microsoft Internet Explorer (IE) or Netscape Navigator. Most large-scale e-commerce Web sites ensure that their checkout process supports the most popular versions of each browser. The browser then uses the HTTP (or HTTP over Secure Sockets Layer—HTTPS) protocol's Get or Post method to send the customer-entered data as text to the e-commerce application that's running on the Web server. After the Web application receives and parses the text data, it passes the data to the business tier. Most e-commerce developers separate their applications' functions into three logical tiers: a presentation tier, which handles all user interaction; a business tier, which applies business rules to the data coming from and going to the user; and a data tier, which efficiently receives data into and sends data out of the back-end database.
The Active Server Pages (ASP) Request object makes this data-passing process relatively simple. The Request object lets the Web application access data elements that it receives in an HTTP or HTTPS Get or Post operation by exposing them as name/value pairs. The presentation tier can then package these name/value pairs into a data structure and send them to the business tier. However, as you saw earlier, the purchase process typically comprises more than one HTML page. What you need is an efficient way to store this partially gathered data between page requests and a way to package the data after the user completes it so that the data can pass en masse to the business tier.
Microsoft created Commerce Server's OrderForm component so that developers could easily store purchasing-related information, such as customer, order-header, and order-detail data. The OrderForm component is simply a COM object that encapsulates a flexible in-memory data structure. Developers can store and access order data by referring to name/value pairs that the OrderForm component holds. You can think of the OrderForm component as a collection of Commerce Server Dictionaries and SimpleLists that resembles an order-headerorder-detail relationship. The Commerce Server Dictionary and SimpleList components are analogous to multiple- and single-linked lists, respectively. The OrderForm component comes with several fields (dictionary nodes) built in, and developers can add fields to the header and detail portions by simply referring to them by name in the application code. Figure 2 shows a graphical representation of the OrderForm component.
How does the OrderForm component help you collect purchase-related information in a multistep browser-based application, and where does the OrderForm component store the data between Web page calls? The classic problem of a browser-based Web application has always been finding the best place to store session state between HTTP requests. OrderForm uses streaming data to persist and reconstitute itself. You can persist the data in the OrderForm component in either binary or XML format. You would use the binary format to store the order form in a relational database binary large object (BLOB) column, such as SQL Server's Image column. And you would use XML format to store the data anywhere you would store text and to transport the text data through the HTTP protocol.