Creating an ADO.NET Shopping Cart
Now that we’ve gotten XML under our belt, let’s start working with ADO.NET. A good way to really see what ADO can do is within the frame of a shopping cart application. In this chapter, we will create a shopping cart application for a fictitious online bookseller called “Book Shop.”
To enable online shoppers to purchase books from our site, our shopping cart application must be able to: authenticate users, show current contents of the cart, and enable add, update, and checkout operations.
We will also need to create a catalog that our shoppers can browse through to add items to their cart. Users should also be able to query books by category and view a range of books at a time. In order to achieve these goals, we will create the following:
We will also need to create admin interfaces to handle add, update, delete, and retrieve operations for our customers (site users) and site administrators. The interface that will be created in our example can be seen in Figure 12.1.
We are creating what is called a relational database. A relational database is a series of tables that represent entities related to one another. Let’s look at a simple example to help illustrate this point: our database. See Figure 12.2.
Table “Books” is an entity that represents all the attributes of a book. Table “Categories” is an entity that represents all the attributes for a specific category. A relationship between the two tables is created by the use of primary and foreign keys. Table “Categories” has an attribute named CAT_ID, which is the primary key for the table. This means simply that CAT_ID uniquely identifies every row in the table. This will ensure we won’t get duplicate rows of data. The same concept is true for the table “Books.” We can create the relationship between the two tables by putting the attribute CAT_ID into the table “Books.” By doing so, we have created a foreign key in the table “Books” which references the table “Categories.” We have now created a one-to-many relationship between the table “Books” and the table “Categories.”
We will now create the entities for our shopping cart application. Entities enable us to map the real world. Since we are making a shopping cart, we need some basic objects to start off with. First of all, we need product. We have chosen to use “Books” as the product for the shopping cart but this could be anything. Next, we need an object that will be using the shopping cart, “Customers.” As in the previous paragraph, we have more than one category of product, or in our case “Books,” so we have another object to map which is “Categories.” The last piece to finish off the whole design is a way to track what is bought, “BookOrders.” Now we need to go over each entity to explain why we have selected the attributes included in each.
To create a database in Microsoft Access, simply navigate to your program files and select the Access icon. The main window will pull up, prompting you to either pick a database from the list of current databases, create a blank database, or use the wizard. See Figure 12.3.
We want to select the Blank Database option and not the wizard. Select OK, then give the database the name shopDb. Next, select the Tables object. From here, we choose the option Create table in design view. We can now transfer the attributes for the tables into the interface (see Figure 12.4).
Continue this process for the rest of the tables. If you want, you can load the shopDb.mdb from the CD that accompanies this book, then view the complete database. Let’s look at the complete diagram generated by Access after we finish filling in our tables (shown in Figure 12.5).
To generate the preceding diagram, go to the Tools menu and select the Relationships option. You will be prompted for what tables to add. Select the tables you have created and hit OK. To create the relationships between the tables, left-click the attribute you want to make a relationship with and drag it over to the table that has the matching attribute, release the mouse and you will be prompted with a set of options for the relationship. See Figure 12.6.
We will do what is called de-normalize the database for the Access version to make things flow between the Data tier of our application and the two different databases. Since our shopping cart uses all OleDb connections to the database regardless of source, the stored procedures created in the SQL Db are the same for the Access version, but we have some limitations when it comes to Access. We cannot easily return the submitted record ID from the table like we can in SQL using the global variable @@identity, so we must solve this by eliminating the Orders table in the schema for Access and adding those rows to the BookOrders table. This will result in customers having multiple order entries, but keeps all data handling code the same for both databases. If you were to program this application, you would select one or the other and optimize accordingly—we are going to straddle the fence here and show both in the same logic.
Now that we have our database schema done, we can upsize the database using the Access Upsizing Wizard and make a SQL server version. Go to Tools, select Database Utilities, then select Upsizing Wizard. Follow the wizard and choose all the defaults.
Now that we have our schema upsized into SQL, we can easily create the rest of our database components. We primarily need a set of stored procedures that will run all of our operations against the database. This will enable us not to have to use ad hoc queries in our code for our Data Tier interaction.
One thing we need to do first is ensure that all our primary keys were transcribed into the upsized version. Let’s open up the Enterprise Manager of MSSQL 2000 (EM). Navigate to your program files and select the SQL Server group, then select EM. From EM, we can quickly navigate to our database (shopDB). See Figure 12.7.
Select Tables and you’ll notice our tables from Access are now here. Right-click a table and select Design Table. From here, we can check to see if our tables made the move without ill effects. If everything looks correct, check the rest of the tables—you’ll see the Access datatype “autonumber” does not come over to SQL Server as an “int” identity column datatype, which it needs to be. So, for the tables that have autonumber, you will have to change it to the “int” datatype with identity, and give them a seed and increment value. See Figure 12.8.
You must also uncheck Allow Nulls. This is because the field we are working with is a primary key and we cannot have a null value for a primary key field. Also, we are using the option identity in this instance, which requires that null not be allowed.
We will also separate the table “BookOrders” into its original design since SQL Server can easily give us a value for the identity field returned. After we have done all of this, we can create a new diagram in SQL and apply our new relationships. In the EM view, right-click diagrams and select New Diagram. The wizard will prompt you for the tables you want to select for the database diagram. Add only the tables we have created, leave out all the system tables. We will now view our new diagram generated by SQL Server (see Figure 12.9).
We can create relationships in the same manner as before. Click the column you want to make a relationship with and drag and drop it into to the appropriate column and table. We will go with the selected defaults. We have a normalized database now completed in SQL Server. We will now create the stored procedures (procs) we’ll need for the rest of the application.
Don’t be intimidated. We’ll use the SQL Server Wizard to create most of these procedures. Now we need to begin creating all our stored procedures. Go to the Tools menu and select Wizards. From there, a new window will pop up with a listing of items. Double-click the first item, Database, then select Create Stored Procedure Wizard. You should see the screen shown in Figure 12.10.
Click Next and select the database, which is shopDb. The next window will show all the tables on the left and the subsequent procedures that can be created on the right. Mark the check box labeled insert in the row of options listed for the Customers table. Click Next. The window that appears will give you the choice to edit the SQL syntax—select this option. We need to give the procedure a name, which in this case will be AdminAddCustomer. See Figure 12.11.
In Figure 12.11, we see that all columns are selected for insert; however, we do not need one for CT_ID because the identity field generates that. Uncheck that option and rename the proc AdminAddCustomer. Select Edit SQL. Let’s look at the code generated by this; it’s shown in Figure 12.12 and found on the CD as ShopDB.sql.
Here we have the SQL syntax to insert a row of new data. In the code view window, SQL Server likes to put numbers on all the variables. You can delete this so the code looks cleaner and will be easy to use when we write the Web service that will hit this proc and execute it. Create the rest of the Admin procs in this same manner.
Now that we have completed a majority of the stored procedures needed for our database through the use of the wizards, we have to create more complex stored procedures using the Query Analyzer. Open up Query Analyzer from the Tools menu of EM. Connect the server you are running. In the drop-down menu, select the database shopDb. The next proc we need to build is AllCustById. We will write a simple select statement with one parameter.
We are using begin tran and end tran. This simply means that if there is an error during any part of the previous query the transaction will be aborted and rolled back. That’s it for the stored procedures. Now to make these all work in the Access DB, we need to trim out some stuff from the preceding code.
As a rule of thumb, we can grab all the code after the key word AS. This is then pasted into Access query SQL mode and saved as the same file name. Open up the shopDB.mdb file and see the differences in the code.
This section will provide an overview of the Web Services needed for our site, and describe the processes of creating the data connection, creating a Web Service, and, finally, testing the Web Service.
We will be using Web Service methods to wrap our database logic (stored procedures for SQL, or parameterized queries for Access). This will provide separation of the data tier from the UI. This will also enable our data to be accessed from multiple clients including Java-servlets, JSP, PHP, desktop application with Hypertext Transfer Protocol (HTTP) connections, and, of course, ASP.NET applications.
We will be creating the following Web Services (see Figure 12.13):
Earlier in this chapter (see the section “Setting Up the Database”), we created stored procedures for use with an SQL database, as well as the equivalent parameterized queries for use with an Access database, to make the interface to the data source consistent; this allows us to write ADO.NET code that can be used against both SQL and Access.
We will also use the OleDb data connection object since most databases have an OleDb provider. This will enable our code not only to work with SQL and Access but with any database that has an OleDb interface. So, our application will work with an SQL database and our application will work with an Access database. And the only code that will need to be changed with this approach is the connection string.
Let’s create a new project to host all our Web Services. Open Visual Studio .NET Beta 2 (VS.NET), and select New Project. We want to create a C# ASP.NET Web Service application named “booksource” (see Figure 12.14); next, we will create the data connection.
Data Connections can be created in several ways. Let’s look at how the VS.NET Wizard does this. For this example, we’ll create a connection to an Access database. The steps for MS SQL will be slightly different.
Connection string information will also be added to the InitializeComponent() method. Alternatively, we can still create a connection string by creating a .udl file on the desktop, double-clicking it and following the dialogs. With this method, we will have to insert the code ourselves, as follows:
All of the code for the Web Services in this chapter can be found on the CD. (See adminCustomer.asmx.cs, sellerAdmin.asmx.cs, getBooks.asmx.cs, getCategories.asmx.cs, getCustomer.asmx.cs, loginCustomer.asmx.cs, orderBooks.asmx.cs, and sellerAdmin.asmx.cs.)
For Step 3, (creating the Command object), first create a new method called addItem. It should have parameters corresponding to the stored procedures parameters, and should return a string indicating success or failure of the operation:
For Step 4 (creating the Parameter objects and assigning their value), we will create Parameter objects for ISBN, author, price, title, description, imagePath, and CAT_ID, then set their values. Here is the code for “isbn”:
Note that “@BK_ISBN” is the name of the parameter we are assigning a value to; “OleDbType.Char” is its datatype (it should be compatible with the field in the database); and “15” refers to the character size as defined for the field in the database.
Now, for Step 5 (executing the procedure), we will open the connection and execute the query. Since the stored procedure performs an insert operation it will return an “int” containing the number of rows affected. Therefore, we will use the command ExecuteNonQuery.
This accomplishes Step 5. Now close the connection and return the result of executing the proc (this is Step 6). Note that our method returns the following string: “success” or the generated error message.
3. When the browser loads, you should see something like Figure 12.15.
4. To test the service addItem, click its link. An input form will be displayed, prompting you for values for its parameters. See Figure 12.16.
6. Since this service returns a datatype string, we should see something like Figure 12.17.
This shows that the method has completed successfully and returned the corresponding output. These steps can be repeated for each of the remaining methods: removeItem, updateItem, addCat, removeCat, and updateCat. Each of these methods is coupled with a corresponding stored procedure (MSSQL) or parameterized query (MS Access).
The following is a function prototype overview of the process-flow or steps involved in creating each of these Web methods. See if you can create and test these Web methods on your own, then compare them to the source on the CD. The sellerAdmin Web service and all of its methods can be found on the CD (see sellerAdmin.asmx.cs).
Now that we know the Web Service and its methods are working correctly, the next step will be to create our UI for the Web application and generate proxy classes for it to retrieve data from our Web Services. In the next section, we will see how VS.NET works with WSDL and Universal Description, Discovery, and Integration (UDDI) to enable our ASP.NET Web Application to connect to and retrieve data from our booksource Web Service project.
We will use WSDL and disco in our Web application project to connect to and add a reference to our Web Services Application (bookSource) and its individual Web Services and their Web methods. To learn more about WSDL, disco, and Web Services, please see the discussion of this topic in Chapter 10.
3. Select the last UDDI option, which is your local machine.VS.NET will check your server for all Web Services. It will then present you with a list of services you can view or select to add a reference to. See Figure 12.18.
4. Select the service group you would like to add a reference to. Look for your Web Service project name (http://localhost/bookSource.vsdisco).
5. The Services available will be displayed. See Figure 12.19.
6. You can view the Simple Object Access Protocol (SOAP) contracts and documentation for each Service method by clicking on the link. Be sure to add the reference from this level in the menu. To add this Web Service and all its methods, click Add Reference.VS.NET will create proxy classes for each Service method so that the method can be accessed just like a local class method. See Figure 12.20.
Now that the backend database interfaces and Web Services have been completed, we will turn our focus to the middle tier data classes and controls that act as a bridge between the backend and the Web UI. Our site structure will look something like that depicted in Figure 12.21.
In this section, we will develop the code that allows us to tie our site administration interface to our Web Services (see Figure 12.22). While creating the pages needed, we will cover creating the Administration login, creating the Administration page, and an addBook page for the administrator.
3. Be sure not to position this element in Design view; in the aspx page, remove the style attribute from the element and use HTML layout techniques to position it. (See the sidebar in this section on 28ASP.NET and Netscape.)
The purpose of this page is to allow the site administrator the ability to remove and update book item information. In the following sections, we’ll look specifically at retrieving the data, displaying the data, adding new books to the database, deleting books, and updating book details.
To retrieve the list of books stored in the database, we will need to access the “GetAllBooks” stored procedure (MSSQL) or parameterized query (MS Access). We will do this by creating the allBooks method of the getBooks Web Service. This method will take no parameters, and will return a DataSet containing all Book data as well as the table structure of the Database table that the data originated from. The Web method getBooks.AllBooks can be found on the CD that accompanies this book (see getBooks.asmx.cs).
This XML file is interpreted by ASP.NET as a DataSet object and can be easily loaded into any variable of type DataSet. The DataGrid control is designed to be DataBinded to a DataSet object. This makes it easy to “data bind” to a Web Service method that returns a DataSet. Data Binding a DataSet to the DataGrid is almost the same as loading the DataSet into the DataGrid. The DataGrid is then able to iterate through and perform operations on the DataSet as if it were an Access Form connected to an Access database. The DataSet in actuality is an in-memory XML representation of the database including the Books table.
The DataGrid is actually bound to the DataTable Books which is a table within the DataSet returned by getBooks.AllBooks. We create a DataView of the Books table so that we can sort the data. This DataView is then bound to the DataGrid.
Using the DataGrid event changeBooks_DeleteCommand, fired when a user clicks the Delete button in the DataGrid UI, we will select the row in the DataGrid to remove by using the RowFilter property. The following code selects the individual book by performing a filter on ISBN. It is analogous to the SQL statement:
This will return an array or collection of items. Since ISBN is our primary key in the Books table, we know that this filter will return only one item. We delete this row from the DataView by simply calling the Delete method:
The addBook is another fairly straightforward page. It provides a UI where the site administrator can fill out a simple HTML form and submits. This data is handled by the code-behind page addBook.asmx.cs. This page simply passes the data to the database via the Web method sellerAdmin.addBook:
In this section, we will develop the code that allows us to tie our customer administration interface to our Web Services (see Figure 12.23).
We can now add a page that will let the customer update his or her information. This will be done identically to the example from site admin where we brought in all books and then enabled the site administrator to go through the books listed and delete, update, or add books at will. In this case, we will enable the customer to update only.
In the previous examples we have made extensive use of the DataGrid control for DataBinding DataSet information to the UI. We must admit we were a bit reluctant to use the DataGrid since it seemed reminiscent to the DataGrid Design Time Controls (DTCs). DTCs were included with many versions of FrontPage, Visual InterDev, and Office. They made it easy for novice developers to quickly create data driven Web sites. Lets just say DTCs had some drawbacks, to put it politely! In the next two sections, ADOCatalog and XMLCart, we will use XSL/Transforms against XML data to produce our UI. This is accomplished by using the asp:xml server control as well as client side script and hidden asp:text controls. The ADOCatalog’s primary interfaces will return DataSet objects so it could be easily tied to a DataGrid control. We will leave that as an exercise for you. The XMLCart is primarily a wrapper class around the XmlDocument object. Its primary interfaces will return XmlDocument objects. Let’s get started!
In this section, we will develop the code that allows us to tie our catalog interface to our Web Services. We will store a DataSet in an Application variable to reduce the load on the database, perform copy, clone, import, create, and filter operations on ADO.NET DataSet objects, and use XML and Extensible Stylesheet Language Transformations (XSLT) to render data stored in a DataSet as HTML via the asp:xml server control.
In our ADOCart application, all database interaction is handled via Web Services. Since our “Books” data is fairly static, we can retrieve the data in a DataSet once and store that DataSet in an application-level variable. This reduces the database traffic, while still providing quick access to the data. Here is an overview of the process we will be following:
This will create an instance of the getBooks object called DataSource. Using this instance, we call the AllBooks method, which returns a DataSet. We then save the DataSet in an application-level variable, allbooks.
The BookCatalog class will contain the following public methods: InitCatalog, Catalog, CatalogItemDetails, CatalogRange, CatalogByCategory, and the private methods CatalogRangeByCategory, and CreateSummaryTable. The following is a rough prototype of the ADOCatalog class that we’ll be building in this section:
The CreateSummaryTable method creates a DataTable that contains summary information about the DataSet being returned. This data is used by the XSLT to display Metadata (i.e., viewing records 6 through 12 of 25). It is also useful when making a fetch next range of records call.
The InitCatalog method loads a DataSet into the BookCatalog object, then adds a default summary table to the private DataSet dsAllBooks. Based on the prototype, this method will take the only parameter, a DataSet, and will return nothing.
The catalogRangeByCategory method creates a new DataSet containing a new Books Table, appends the appropriate Summary Table, and returns this new DataSet. It is used by the preceding methods to return a single item’s node (to add to the shopping cart), to return a range of books (to handle browsing the catalog), and to return all books in a given category (to handle viewing by category). A method could easily be added that enables browsing by category.
In order to return a subset of the DataSet allBooks, we need to create a new DataTable object that has the same table structure as Books. We can then import rows that meet our criteria into this new table. When the table is filled, we create a new DataSet object and add the new DataTable as well as a Summary Table. The resulting DataSet will contain the request subset of data and some Metainformation (supplied by the Summary table).
On page load, we will instantiate the object, retrieve Application[“AllBooks”], return the requested subset DataSet object, convert it to XML using the GetXml() method of the DataSet object, and apply an XSL/Transform to render the Catalog in the UI.
In order to enable browsing, we will store the FirstRecord, LastRecord, recordCount, and user action (previous | next | by CategoryID) into hidden Text fields on the client, so this data can be read to determine which bookCatalog method to call and with which parameters to return the desired subset of AllBooks.
In this section, we will develop the code that allows us to tie our catalog to the shopping cart. We will use XML node operations to update our cart’s contents, XSLT/XPath operations to calculate cart totals and taxes, XML and XSLT to render cart data as HTML, and the asp:XML server control to process transforms. The code for this class can be found on the CD (see start.aspx and start.aspx.cs).
When the page loads, the cart must be initialized. This is handled with the init method. If there is no data to load into the cart, the root node (<shopcartitems>) is added so that child nodes can be imported from the catalog.
When a user chooses to add an item to the shopping cart, the onclick event will call bookCatalog.catalogItemDetails and supply an ISBN. The resulting data will be an XML node for that item. The node will then be imported to the XmlCart document via the method addItem2Cart. The string representation will then be stored in Session[“myShoppingCart”].
When a user selects to remove an item from the shopping cart, the onclick event will remove the node specified by the supplied ISBN via the removeItemFromCart method, and update Session[“myShoppingCart”].
When a user selects Checkout from the shopping cart, the onclick event will call the Web Service orderBooks.OrderItem to update the orders table in the Database, clear the cart via the clearCart method, and display confirmation information to the UI.
ADOCatalog and XMLCart alone do not provide that much functionality; the real functionality is handled by the showCatalog and the showCart page methods. Before we take a closer look at that, let’s see how the start.aspx page is laid out.
The following div is used to hide a group of text box server controls—so why use a div to hide asp:textbox controlsFirst, while the asp:textbox control does have a visibility attribute, setting this attribute to hidden prevents the HTML from being written to the client, so when we view page source, the HTML for the text box isn’t even there. Second, while using the HTML control <input type=“hidden” runat=“server”> is also an option, this control lacks postback ability.
Each time a user clicks Add, Remove, Checkout, Previous, Next, or makes a change to the drop-down menu for category, we set these hidden variables accordingly and submit the page. Program control is then passed to our code-behind page “start.aspx.cs” (this file can also be found on the CD).
On Page_load, we retrieve Application[“AllBooks”] and apply an XSL/Transform to render the Catalog in the UI. In order to enable browsing, we store the FirstRecord, LastRecord, recordCount, and user action (previous | next | by CategoryID) into hidden Text fields on the client, so this data can be read to determine which bookCatalog method to call and with which parameters to return the desired subset of AllBooks.
When a user makes a selection from the catalog (“Add item to cart,” Previous, Next, or selects a category) or the cart (Remove item, or Checkout), the user’s action is stored in hidden text boxes that are passed to the code-behind onsubmit(). In the Page_load method, we will test for addItem, removeItem, or Checkout and handle each accordingly.
There are many ways to display data held in a DataSet in XML, or for that matter in ASP.NET. In fact, there are a multitude of controls, including the popular DataGrid control that make this relatively simple. We have opted to use XML and XSLT to show other approaches to the same problem. Also, if your current ASP application uses XML and XSLT, the migration to ASP.NET is fairly easy. In fact, your existing XSLT stylesheets and XML content can still be used. For more information on XSLT, visit www.w3c.org/TR/xslt, www.w3c.org/Style/XSL/#Learn, and www.w3schools.com/XSL.
It is important to note that the Application and Session objects still have issues with regards to server farms and scalability. We used Session in this example for simplicity and to show that it can still be useful. Relatively simple changes can be made to the Start page to convert Session variables into hidden fields stored on the page, or state can be stored in a database.
We have developed an application that enables customers to browse a catalog of books by category or range, add selections to a virtual shopping cart, remove items from the cart and simulate processing an order by logging in and submitting updates to the order table in the database. We have leveraged the power of XML and its ability to represent data and structure, explored Web Services and their methods, designed databases and stored procedures, developed custom code-behind classes in C# and covered a multitude of uses for ADO.NET.
We also explored database design and implementation, creating two databases for the application, one for Access and one for SQL. We then covered entities and their attributes and how both work with each other to create a normalized database. Lastly, we developed a set of stored procedures that will handle all data interaction with the database, preventing the use of “ad hoc” queries against the database. To see the ADOCart application on the Web (it is available on the CD accompanying this book), visit www.DotThatCom.com.
The different types of relationships between tables are one-to-one, one-to-many, and many-to-many. In a one-to-one relationship, exactly one row corresponds with a matching row of the related table. In a one-to-many relationship, one row corresponds to many rows of the related table. In a many-to-many relationship, many rows correspond to many rows of the related table.
Using parameterized queries in MS Access and stored providers in MS SQL results in performance gain. In addition, you no longer have to run ad hoc queries against the database. Pre-complied queries perform better.
Tie the site administration to the Web Services, enabling the administration functions for the site to be done without accessing the code or database. The adminPage.aspx page in our example allows the site administrator to retrieve and display data, and to add, delete, and update product.
To retrieve the list of books stored in the database, we need to access the “GetAllBooks” stored procedure (MSSQL) or parameterized query (MS Access) by creating the allBooks method of the getBooks Web Service. This method will take no parameters, and will return a DataSet containing all Book data as well as the table structure of the Database table that the data originated from.
Display the data by binding a DataGrid to the DataSet. The DataGrid is actually bound to the DataTable Books which is a table within the DataSet returned by getBooks.AllBooks. We create a DataView of the Books table so we can sort the data. This DataView is then bound to the DataGrid.
The Customer Administration pages tie our customer administration interface to our Web Services, enabling the customer to update their personal information. This is an added benefit to the user of the site.
Since our “Books” data is fairly static, we can retrieve the data in a DataSet once and store that DataSet in an application-level variable. This reduces the database traffic, while still providing quick access to the data.
We will use XML node operations to update our cart’s contents, XSLT/XPath operations to calculate cart totals and taxes, XML and XSLT to render cart data as HTML, and the asp:XML server control to process transforms.
Frequently Asked Questions
The following Frequently Asked Questions, answered by the authors of this book, are designed to both measure your understanding of the concepts presented in this chapter and to assist you with real-life implementation of these concepts. To have your questions about this chapter answered by the author, browse to www.syngress.com/solutions and click on the “Ask the Author” form.
A: In your Solutions Explorer, make sure all child files are collapsed in the parent when renaming and this will change all the associated files. If you have already changed one file, change it back to the name prefix of the other files, then collapse the children and rename it to the new name. Also, check the first line in the .aspx page and ensure that the Inherits attribute lists the correct filename.