Building the BananaMobile Site


Sams Teach Yourself ASP.NET in 21 Days, Second Edition
By Chris Payne
Table of Contents
Bonus Day 22.  Building a Complete Application

Now that you've got the design of the site nailed down, it's time to start building the functionality. Divide the development into four stages: building the databases, the business objects, the ASP.NET pages, and finally, the Web service. This progression will allow you to easily move through development; each step will set the stage for the next step.

Creating the Database

The BananaMobile database will be created in Access, so create a new database as you did in Day 8, and save it as banana.mdb. Next create the database tables you will need. Starting with the tblUsers table, which stores information about your registered BananaMobile users, create a new table in design view, as shown in Figure 22.3, and add the following fields. Unless otherwise noted, all fields are Text data types.

  • UserID (Autonumber)

  • Username

  • Password

  • FirstName

  • LastName

  • Address

  • City

  • State

  • Zip

  • Phone

  • Email

Figure 22.3. Creating the user table.



Note that we're using Access here because it is easier to use and is more readily available to developers. If you are building a site that is expecting heavy traffic or lots of database functionality, it might be wise to move your database to SQL Server, which is better capable of handling large loads.

These are all the fields you'll need to store user information. Don't forget to set the primary key on the UserID field. Close this table and save it as tblUsers. Following the same process, create three more tables. Table 22.1 shows the fields and data types for each database table (to change a numeric field type to Double, use the Field Size drop-down list on the General property sheet for the table).

Table 22.1. The Banana Database Structure
Table Fields

PartID* (Autonumber)

Name (Text)

Category (Text)

Price (Currency)

Description (Memo)


OrderID* (Autonumber)

UserID (Number)

BTS (Number)

Carpet (Number)

Color (Number)

Engine (Number)

Frame (Number)

Wheels (Number)

SubTotal (Currency)


UserID* (Number)

BTS (Number)

Carpet (Number)

Color (Number)

Engine (Number)

Frame (Number)

Wheels (Number)


RecordID* (Autonumber)

UserID (Number)

Mileage (Number, Double)

MaxSpeed (Number, Double)

Throttle (Number, Double)

Date (Date/Time)

An asterisk indicates column is a primary key

Next, create the relationships between the tables in Access, according to the diagram in Figure 22.2. Under the Tools menu, select Relationships, and add each table to the view. Simply click and drag to form relationships; you should end up with something similar to Figure 22.4. Note that you didn't relate each customizable part in the tblOrders table to the tblParts table; multiple relationships with the same two tables are difficult to work with, so they are left out. However, this relationship should still be enforced in your code.

Figure 22.4. The relationship diagram in Access.



Relationships like these are helpful to maintain your database properly, but beware of using them too often; they can slow down performance if used improperly (this is true in SQL Server as well). When possible, maintain relationships through other mechanisms, such as validation procedures in your code.

Now that you've got the tables set up, it's time to insert the data. Table 22.2 lists some of the various items that BananaMobile, Inc. offers as customizable parts. Insert this data into the tblParts table, and don't be afraid to add your own!

Table 22.2. BananaMobile Inc.'s Customizable Parts List
Name Category Price Description
Catapult BTS $150 The Catapult upgrade offers tremendous power, shotgunning bananas at 50 km/hr.
Shag Carpet $100 Get that groovy feeling with our thick shag carpet.
Plush Carpet $125 Our beautiful plush carpet provides comfort for the entire family.
Standard Color $20 Our standard banana yellow color is the most popular choice.
Standard Engine $400 Our standard engine provides plenty of power with the purr of a small kitten. Perfect for family vehicles!
BananaSplit Engine $1400 The top of the line banana engine. The BananaSplit provides a screaming 2,000 bananas of power, perfect for tearing up asphalt.
Sedan Frame $400 This five-seater provides plenty of leg room for the whole family. Our stylish design will make your neighbors jealous.
Wheels Chrome $400 For the person who wants to be the scene, our chrome wheels will bring the spotlight to your BananaMobile.

Also add a user or two to your tblUsers table. You don't need to enter information into the other tables because the users will do that for you. After you've entered this information into the tblParts and tblUsers tables, you're ready to create your stored procedures. Recall from Day 12, "Employing Advanced Data Techniques," that stored procedures are called queries in Access; I'll use the terms interchangeably here. These stored procedures will allow you to use parameters to retrieve data from your ASP.NET pages.

First, you'll need to create a query that validates a user's identity; that is, one that checks whether the supplied username and password are correct. Listing 22.1 shows this query.

Listing 22.1 Validating a User
 1:  SELECT UserID FROM tblUsers 2:  WHERE Username = @Username 3:     AND Password = @Password 

Save this query as spLoginUser. This is a simple SQL statement that uses two parameters, @Username and @Password, both of which will be supplied by the user during login. Next, create a query that adds users to the database when they register. This is a simple parameterized INSERT query, shown in Listing 22.2.

Listing 22.2 Adding New Users
 1:  INSERT INTO tblUsers (Username, Password, FirstName, 2:     LastName, Address, City, State, ZIP, Phone, Email) 3:  VALUES (@Username, @Password, @FirstName, @LastName, 4:     @Address, @City, @State, @ZIP, @Phone, @Email) 

Save this procedure as spAddUser. When a user adds items to his shopping cart, which usually follows immediately after the login, you first need to verify that he has a shopping cart; that is, whether there is an entry in the tblShoppingCart table with his user ID. If not, you need to add a new entry. Listing 22.3 accomplishes this.

Listing 22.3 Adding a New Cart
 1:  INSERT INTO tblShoppingCart (UserID) 2:  VALUES @UserID 

Save this listing as spAddCart. This statement simply inserts a new row in tblShoppingCart with the value specified by @UserID for the UserID field. All the other fields (such as BTS, Color, and so on) are left at default values.

After you've created a shopping cart, you'll need a way to retrieve its ID. This is useful when you need to verify that a cart exists for a specific user. Listing 22.4 shows this query.

Listing 22.4 Retrieving a Shopping Cart ID
 1:  SELECT UserID 2:  FROM tblShoppingCart 3:  WHERE UserID=@UserID 

Save this listing as spGetCart.

Next, you'll need to create a set of queries that update each part in the tblShoppingCart table; for instance, one query to update the frame part number, one for the wheels part number, and so on. Luckily, these queries will all follow the same syntax, with the exception of the field name to update. Listings 22.5 22.10 show each of these UPDATE queries.

Listing 22.5 Updating the BTS Part ID
 1:  UPDATE tblShoppingCart SET tblShoppingCart.BTS = @PartID 2:  WHERE tblShoppingCart.UserID=@UserID 

Save this code as spAddBTS. This SQL statement updates the BTS value in the shopping cart table with a value that the user has selected from the site (@PartID). Because shopping carts are linked to user IDs, update the correct cart by specifying the @UserID parameter on line 2. Listings 22.5 22.10 follow the same pattern, but substitute different part categories in place of BTS.

Listing 22.6 Updating the Carpet Part ID
 1:  UPDATE tblShoppingCart SET tblShoppingCart.Carpet = @PartID 2:  WHERE tblShoppingCart.UserID=@UserID 
Listing 22.7 Updating the Color Part ID
 1:  UPDATE tblShoppingCart SET tblShoppingCart.Color = @PartID 2:  WHERE tblShoppingCart.UserID=@UserID 
Listing 22.8 Updating the Engine Part ID
 1:  UPDATE tblShoppingCart SET tblShoppingCart.Engine = @PartID 2:  WHERE tblShoppingCart.UserID=@UserID 
Listing 22.9 Updating the Frame Part ID
 1:  UPDATE tblShoppingCart SET tblShoppingCart.Frame = @PartID 2:  WHERE tblShoppingCart.UserID=@UserID 
Listing 22.10 Updating the Wheels Part ID
 1:  UPDATE tblShoppingCart SET tblShoppingCart.Wheels = @PartID 2:  WHERE tblShoppingCart.UserID=@UserID 

Save these as spAddCarpet, spAddColor, spAddEngine, spAddFrame, and spAddWheels, respectively. An interesting feature of these queries is that they can also be used to remove items from the cart. Setting the @PartID parameter to 0 when calling the query effectively removes the existing part.

You have six queries left to write (including one for the BananaConnect system), three of which are a bit more complex than the ones you've seen so far. The first one, spGetSubTotal, examines each item in a specific shopping cart, retrieves the price for each, and sums them at the end. Let's look at the code, shown in Listing 22.11.

Listing 22.11 Retrieving Price Information
 1:  SELECT SUM(Price) 2:  FROM tblParts, tblShoppingCart 3:  WHERE (tblParts.PartID=tblShoppingCart.BTS 4:     OR tblParts.PartID=tblShoppingCart.Carpet 5:     OR tblParts.PartID=tblShoppingCart.Color 6:     OR tblParts.PartID=tblShoppingCart.Engine 7:     OR tblParts.PartID=tblShoppingCart.Frame 8:     OR tblParts.PartID=tblShoppingCart.Wheels) 9:     AND tblShoppingCart.UserID=@UserID 

This statement has a few different parts, so let's step through it slowly. Ignore the SUM command on line 1 for now, and imagine that it says SELECT Price. In plain English, this statement means, "Retrieve the prices for all items in tblShoppingCart from tblParts, given a specific user ID."

This query pulls information from two different tables: tblParts and tblShoppingCart. This makes sense because you're trying to find the prices (stored in tblParts) for each of the items in tblShoppingCart. Lines 3 8 relate each of the six different categories in tblShoppingCart to a PartID in tblParts. For example, if the BTS field in tblShoppingCart had the value 2, line 3 would match it to the corresponding item in tblParts. Likewise, if the Carpet field had a value of 7, line 4 would match it to the item in tblParts that has the same value. That way, you are assured that you are retrieving the correct price.

Each field in tblShoppingCart can potentially have the value 0 (if the user hasn't added that part yet). However, there isn't a PartID in tblParts with the value 0. Therefore, this query will return data from the tblParts table without a matching item from the tblShoppingCart table. For example, if the Color field is 0, you won't get a corresponding price from tblParts. Use the keyword OR on lines 4 8 to ignore these mismatches, and return prices for only the ones that do match; any returned data that has a PartID of 0 will be ignored. If you replace all the ORs with ANDs, chances are this query won't return anything, because of the zeroes in tblShoppingCart.

On line 9, you make sure that you are retrieving information from the correct shopping cart by supplying the UserID. Finally, after you have the prices for each item in tblShoppingCart, you use the SUM function to add them all up. The result is one aggregate value instead of six separate ones.

Next, you'll need a stored procedure that retrieves all information about a specific shopping cart. This query, as shown in Listing 22.12, is very similar to Listing 22.11.

Listing 22.12 Retrieving Cart Information
 1:  SELECT tblParts.PartID, Name, Category, Price 2:  FROM tblParts, tblShoppingCart 3:  WHERE tblParts.PartID=tblShoppingCart.BTS 4:     OR tblParts.PartID=tblShoppingCart.Carpet 5:     OR tblParts.PartID=tblShoppingCart.Color 6:     OR tblParts.PartID=tblShoppingCart.Engine 7:     OR tblParts.PartID=tblShoppingCart.Frame 8:     OR tblParts.PartID=tblShoppingCart.Wheels 9:     AND tblShoppingCart.UserID=@UserID 

Save this listing as spViewMobile. The syntax is nearly identical to spGetSubTotal except that you're not performing an aggregate function. Rather, you are simply retrieving several fields from the database. This query returns the PartID, Name, Category, and Price information for the items in a specific shopping cart, by linking the tblParts and tblShoppingCart tables.

The next query is similarly complex. Listing 22.13 shows the code.

Listing 22.13 Moving Items from tblShoppingCart to tblOrders
 1:  INSERT INTO tblOrders (UserID, BTS, Carpet, Color, 2:     Engine, Frame, Wheels, SubTotal) 3:     SELECT tblShoppingCart.UserID, tblShoppingCart.BTS, 4:        tblShoppingCart.Carpet, tblShoppingCart.Color, 5:        tblShoppingCart.Engine, tblShoppingCart.Frame, 6:        tblShoppingCart.Wheels, @SubTotal AS Expr1 7:     FROM tblShoppingCart 8:     WHERE tblShoppingCart.UserID=@UserID 

Save this listing as spPurchase. This query will be executed when the user decides to purchase the items in his shopping cart. Essentially, it moves all information stored in the temporary tblShoppingCart table to the permanent tblOrders table.

Again, it often helps to examine the statement's meaning in plain English first. This one reads, "Insert all fields from tblShoppingCart with the specific user ID into tblOrders." On lines 1 and 2, you notice the familiar beginnings of a normal INSERT statement. All the fields (except OrderID) are included in this update. On line 3, however, you see a SELECT statement. What's going on?

Lines 3 8 contain a subquery, that is, a SQL query inside another query. In this case, it's a SELECT query inside an INSERT query. The inner query serves to supply data for the outside query. Immediately after line 2, the INSERT statement expects values for the eight fields specified in between the parentheses on lines 1 and 2. The SELECT statement supplies just those values. In fact, if you examine the SELECT statement, you'll notice that it is fairly standard. The only differences between this and what you're used to are the addition of the table names in front of each field (that is, tblShoppingCart.Carpet instead of just Carpet) and the snippet @SubTotal AS Expr1 on line 6. The addition of table names is necessary because the tblOrders and tblShoppingCart tables have fields with the same name. Without additional clarification, the application would get confused; it wouldn't know where to retrieve data from.

The SELECT statement returns only seven values, however, and you need eight for the INSERT statement. The line @SubTotal AS Expr1 means that in addition to the values returned by the SELECT statement, you're adding a field named Expr1. The value for this field doesn't come from a table or a SELECT statement; rather, it comes from the @SubTotal parameter, which you'll specify in the application. Thus, you now have all the values you need for the INSERT statement, effectively transferring all data from the shopping cart table to tblOrders.

You'll also need a query to retrieve all the parts in a specified category. This will help in building "browsing" pages for your customers. Listing 22.14 shows this query.

Listing 22.14 Correlating Part Category and ID Information
 1:  SELECT PartID, Name, Price, Description, Category 2:  FROM tblParts 3:  WHERE Category=@Category 

Save this query as spGetCategory. Finally, after you've transferred the data from the shopping cart to tblOrders, you need to remove it from tblShoppingCart. This is easily done with the statement shown in Listing 22.15.

Listing 22.15 Deleting Old Shopping Cart Information
 1:  DELETE * 2:  FROM tblShoppingCart 3:  WHERE UserID=@UserID 

Save this query as spRemoveCart. So far, you've got 15 stored procedures, as shown in Figure 22.5.

Figure 22.5. The Banana queries in Access.


You've got one more to go: the BananaConnect procedure. This query takes parameters from each BananaMobile and adds them to the database. You want to allow each BananaMobile owner to have as many records in the database as he wants, so this query will simply be an INSERT statement. This query is shown in Listing 22.16.

Listing 22.16 Adding Data from the BananaConnect System
 1:  INSERT INTO  tblBananaConnect 2:     (UserID, Mileage, MaxSpeed, Throttle, [Date]) 3:  VALUES (@UserID, @Mileage, @MaxSpeed, @Throttle, @Date) 

Save this query as spBananaConnect. Note that on line 2 you had to enclose the field Date in brackets. That's because the word Date has special meaning in Access, and will cause an error if you use it here by itself. To let Access know that you're referring to a field and not a special keyword, surround the field with brackets. That's it for the database! Let's move on to the business objects.


You can optionally surround each of the column names with brackets. In fact, if your column names contain spaces, brackets are required!

The Business Objects

Now that you've created all your stored procedures, you can build the business objects that will use them. From the design stage you stepped through earlier in this chapter, you know that you need two different objects: one to represent a user and one to represent a BananaMobile.

The user object will contain only two methods: one to validate a user's identity and one to add a new user to the database. This user class is similar to the one you built in Day 15. It utilizes a UserDetails class that simply holds identity values for a user. Listing 22.17 shows both these classes.

Listing 22.17 User.vb, the User and UserDetails Classes
 1:  Imports System 2:  Imports System.Data 3:  Imports System.Data.OleDb 4: 5:  Namespace BananaMobile 6: 7:     Public Class UserDetails 8:        public UserID as Integer 9:        public Username as string 10:        public Password as string 11:        public FirstName as string 12:        public LastName as string 13:        public City as string 14:        public State as string 15:        public ZIP as string 16:        public Address as string 17:        public Phone as string 18:        public Email as string 19:     End Class 20: 21:     Public Class User 22:        private objConn As New OleDbConnection("Provider=" & _ 23:              "Microsoft.Jet.OLEDB.4.0;" & _ 24:              "Data Source=c:\ASPNET\data\banana.mdb") 25: 26:        public function Login(strUsername as String, _ 27:           strPassword as String) as Integer 28:           dim intId as Integer 29: 30:           Dim objCmd As New OleDbCommand("spLoginUser", _ 31:              objConn) 32:           objCmd.CommandType = CommandType.StoredProcedure 33: 34:           Dim objParam As New OleDbParameter("@Username", _ 35:              OleDbType.Char) 36:           objParam.Value = strUsername 37:           objCmd.Parameters.Add(objParam) 38: 39:           objParam = New OleDbParameter("@Password", _ 40:              OleDbType.Char) 41:           objParam.Value = strPassword 42:           objCmd.Parameters.Add(objParam) 43: 44:           Try 45:              objConn.Open 46:              intID = CType(objCmd.ExecuteScalar, Integer) 47:              objConn.Close 48:           Catch e As Exception 49:              throw e 50:           End Try 51: 52:           if intID.ToString = "" then 53:              return 0 54:           end if 55:           return intID 56:        end function 57: 58:        public sub AddUser(objUser as UserDetails) 59:           Dim intID as Integer 60:           Dim objReader as OleDbDataReader 61:           Dim objCmdID As New OleDbCommand( _ 62:              "SELECT MAX(UserID) FROM tblUsers", objConn) 63:           Dim objCmd As New OleDbCommand("spAddUser", _ 64:              objConn) 65:           objCmd.CommandType = CommandType.StoredProcedure 66: 67:           Dim objParam As New OleDbParameter("@Username", _ 68:              OleDbType.Char) 69:           objParam.Value = objUser.Username 70:           objCmd.Parameters.Add(objParam) 71: 72:           objParam = New OleDbParameter("@Password", _ 73:              OleDbType.Char) 74:           objParam.Value = objUser.Password 75:           objCmd.Parameters.Add(objParam) 76: 77:           objParam = New OleDbParameter("@FirstName", _ 78:              OleDbType.Char) 79:           objParam.Value = objUser.FirstName 80:           objCmd.Parameters.Add(objParam) 81: 82:           objParam = New OleDbParameter("@LastName", _ 83:              OleDbType.Char) 84:           objParam.Value = objUser.LastName 85:           objCmd.Parameters.Add(objParam) 86: 87:           objParam = New OleDbParameter("@Address", _ 88:              OleDbType.Char) 89:           objParam.Value = objUser.Address 90:           objCmd.Parameters.Add(objParam) 91: 92:           objParam = New OleDbParameter("@City", _ 93:              OleDbType.Char) 94:           objParam.Value = objUser.City 95:           objCmd.Parameters.Add(objParam) 96: 97:           objParam = New OleDbParameter("@State", _ 98:              OleDbType.Char) 99:           objParam.Value = objUser.State 100:           objCmd.Parameters.Add(objParam) 101: 102:           objParam = New OleDbParameter("@ZIP", _ 103:              OleDbType.Char) 104:           objParam.Value = objUser.ZIP 105:           objCmd.Parameters.Add(objParam) 106: 107:           objParam = New OleDbParameter("@Phone", _ 108:              OleDbType.Char) 109:           objParam.Value = objUser.Phone 110:           objCmd.Parameters.Add(objParam) 111: 112:           objParam = New OleDbParameter("@Email", _ 113:              OleDbType.Char) 114:           objParam.Value = objUser.Email 115:           objCmd.Parameters.Add(objParam) 116: 117:           Try 118:              objConn.Open 119:              objCmd.ExecuteNonQuery 120:              objUser.UserID = CType(objCmdID. _ 121:                 ExecuteScalar, Integer) 122:              objConn.Close 123:           Catch e As Exception 124:              throw e 125:           End Try 126: 127:           if objUser.UserID.ToString = "" then 128:              objUser.UserID = 0 129:           end if 130:        end sub 131:     End Class 132:  End Namespace 


Whew, that's a lot of code! Luckily though, much of it is easy to follow and redundant. Save this listing as User.vb in your c:\inetpub\wwwroot\tyaspnet21days\day22 directory.

On lines 7 19, you define the UserDetails class. This class contains properties that correspond to each of the fields in the user database. This class will be helpful when you need to present a user's complete information, such as during registration.

On line 26, you declare the Login function, which takes a username and password, and calls the spLoginUser stored procedure to verify a user's credentials. If successful, this method returns the user's ID. On line 31, you create a new OleDbCommand object to execute the query, and you tell ASP.NET that you're executing a stored procedure on line 33. Lines 35 43 add parameters used for the query (see Day 12 for information on parameters). The values for these parameters are taken from the parameters supplied to the function.


Be sure to add the parameters to your command object in the order that the SQL query expects them! For example, the spLoginUser query reads as follows:

 SELECT UserID FROM tblUsers WHERE Username=@Username And Password=@Password 

The @Username parameter is declared before @Password. Therefore, in Listing 22.17, you create the @Username parameter first, followed by @Password.

On line 45, you open the OleDbConnection object declared on line 22; on line 46, you retrieve the first value returned by the query (which should be the user's ID) with the ExecuteScalar method; and on line 46, you close the connection. ExecuteScalar returns an Object data type, so you must cast it to an integer. On lines 52 55, you check to make sure that the query did indeed return a value. (If the parameters were incorrect or the query did not find any valid data, it would return an empty string.) If it didn't, you return zero, signaling to the ASP.NET page that the user credentials are invalid.

The AddUser method, beginning on line 58, is responsible for adding new users to the database. It takes a UserDetails object as a parameter, and executes the spAddUser stored procedure. Much of this method (lines 67 115) simply adds parameters obtained from the UserDetails object to the query, so it won't be discussed here.

There is, however, a peculiar situation in this function. After the spAddUser query has executed, you need to return the UserID of the user you just added, so that you can log the user in correctly. Because of Access's inability to handle output parameters, you have to execute another query to do this (SQL Server could do so easily within one stored procedure). On line 62, you create the query SELECT MAX(UserID) FROM tblUsers, which returns the highest UserID value from tblUsers. Presumably, after the spAddUser query has executed, the newest user will have the highest ID because UserID is an automatically incrementing field. Therefore, on line 120, after spAddUser has been executed, you retrieve the highest UserID value, and return it to the calling ASP.NET page. Figure 22.6 illustrates this procedure.

Figure 22.6. Retrieving the UserID in a separate step.



Beware that this method of retrieving the last-inserted ID is iffy at best. Especially on higher trafficked sites, there is no guarantee that you will retrieve the correct ID. This method is used simply for illustration purposes.

Instead, you should employ a more secure method. There are quite a few methods available. For example, you could add a date field to your tblUsers table. Before you insert the user, retrieve the date and time from ASP.NET (for example, with DateTime.Now.ToString) and insert that value into the database. That way, your ASP.NET page knows accurately when the user was created, and thus, can easily access his or her ID.

Next, you have the BananaMobile object. This object contains methods that add and remove parts from a BananaMobile-in-progress, purchase a specified BananaMobile, and display information about a particular BananaMobile. The code is quite long, so it will be broken up into several listings. Luckily, much of this code is also redundant. Listing 22.18 shows the class declaration and the first method, AddPart.

Listing 22.18 Adding Parts to a BananaMobile
 1:  Imports System 2:  Imports System.Data 3:  Imports System.Data.OleDb 4: 5:  Namespace BananaMobile 6: 7:     Public Class BananaMobile 8:        private objConn As New OleDbConnection("Provider=" & _ 9:              "Microsoft.Jet.OLEDB.4.0;" & _ 10:              "Data Source=c:\ASPNET\data\banana.mdb") 11: 12:        public Sub AddPart(UserID as Integer, PartID as Integer, Category as String) 13:           dim strSPName as string 14: 15:           select case Category 16:              case "BTS" 17:                 strSPName = "spAddBTS" 18:              case "Carpet" 19:                 strSPName = "spAddCarpet" 20:              case "Color" 21:                 strSPName = "spAddColor" 22:              case "Engine" 23:                 strSPName = "spAddEngine" 24:              case "Frame" 25:                 strSPName = "spAddFrame" 26:              case "Wheels" 27:                 strSPName = "spAddWheels" 28:           end select 29: 30:           Dim objCmd As New OleDbCommand(strSPName, objConn) 31:           objCmd.CommandType = CommandType.StoredProcedure 32: 33:           Dim objParam As New OleDbParameter("@PartID", _ 34:              OleDbType.Integer) 35:           objParam.Value = PartID 36:           objCmd.Parameters.Add(objParam) 37: 38:           objParam = New OleDbParameter("@UserID", _ 39:              OleDbType.Integer) 40:           objParam.Value = UserID 41:           objCmd.Parameters.Add(objParam) 42: 43:           Try 44:              objConn.Open 45:              objCmd.ExecuteNonQuery 46:              objConn.Close 47:           Catch e As Exception 48:              throw e 49:           End Try 50:        End Sub 51: 


The class declaration is standard. On line 8, you create an OleDbConnection for use with your methods. Beginning on line 12, the AddPart method takes three parameters: the UserID of the current user (which corresponds to the shopping cart ID), the part ID to add, and the category to which the new part belongs. Depending on the category, you'll need to execute a different stored procedure, as shown in the select case statement on lines 15 28. This select case block stores the name of the stored procedure to execute in the variable strSPName, and the OleDbCommand object on line 30 uses this variable to execute the procedure.

Lines 33 41 simply add parameters to the OleDbCommand object, as you did with the User object in Listing 22.17. Finally, in the try block on lines 43 49, you execute the query with the ExecuteNonQuery method, which doesn't return any results. The effect is that when a user adds a part to his BananaMobile, this method executes and uses one of the spAddCategory stored procedures to enter the information in the database.

The next five methods in the BananaMobile class are fairly simple. They are shown in Listing 22.19.

Listing 22.19 Various Methods to Control a BananaMobile Object
 52:        public sub AddCart(UserID as Integer) 53:           Dim objCmd As New OleDbCommand("spAddCart", _ 54:              objConn) 55:           objCmd.CommandType = CommandType.StoredProcedure 56: 57:           Dim objParam As New OleDbParameter("@UserID", _ 58:              OleDbType.Integer) 59:           objParam.Value = UserID 60:           objCmd.Parameters.Add(objParam) 61: 62:           Try 63:              objConn.Open 64:              objCmd.ExecuteNonQuery 65:              objConn.Close 66:           Catch e As Exception 67:              throw e 68:           End Try 69:        End sub 70: 71:        public function CartExists(UserID as Integer) as _ 72:           Boolean 73:           Dim blnEmpty as Boolean = false 74: 75:           Dim objCmd As New OleDbCommand("spGetCart", _ 76:              objConn) 77:           objCmd.CommandType = CommandType.StoredProcedure 78: 79:           Dim objParam As New OleDbParameter("@UserID", _ 80:              OleDbType.Integer) 81:           objParam.Value = UserID 82:           objCmd.Parameters.Add(objParam) 83: 84:           Try 85:              objConn.Open 86:              if not objCmd.ExecuteScalar = "" then 87:                 blnEmpty = True 88:              end if 89:              objConn.Close 90:           Catch e As Exception 91:              throw e 92:           End Try 93: 94:           return blnEmpty 95:        End function 96: 97:        public function GetSubTotal(UserID as Integer) as _ 98:           Double 99:           Dim objSubTotal as Object 100:           Dim objReader as OleDbDataReader 101: 102:           Dim objCmd As New OleDbCommand("spGetSubTotal", _ 103:              objConn) 104:           objCmd.CommandType = CommandType.StoredProcedure 105: 106:           Dim objParam As New OleDbParameter("@UserID", _ 107:              OleDbType.Integer) 108:           objParam.Value = UserID 109:           objCmd.Parameters.Add(objParam) 110: 111:           Try 112:              objConn.Open 113:              objSubTotal = objCmd.ExecuteScalar 114:              objConn.Close 115:           Catch e As Exception 116:              throw e 117:           End Try 118: 119:           if objSubTotal.GetType is GetType(DBNull) then 120:              return 0 121:           end if 122:           return CType(objSubTotal, Double) 123:        End function 124: 125:        public function GetCategory(Category as String) as _ 126:           OleDbDataReader 127:           Dim objReader as OleDbDataReader 128: 129:           Dim objCmd As New OleDbCommand("spGetCategory", _ 130:              objConn) 131:           objCmd.CommandType = CommandType.StoredProcedure 132: 133:           Dim objParam As New OleDbParameter("@Category", _ 134:              OleDbType.Char) 135:           objParam.Value = Category 136:           objCmd.Parameters.Add(objParam) 137: 138:           Try 139:              objConn.Open 140:              objReader = objCmd.ExecuteReader 141:           Catch e As Exception 142:              throw e 143:           End Try 144: 145:           return objReader 146:        End function 147: 148:        public Function ViewMobile(UserID as Integer) as _ 149:           OleDbDataReader 150:           Dim objReader as OleDbDataReader 151: 152:           Dim objCmd As New OleDbCommand("spViewMobile", _ 153:              objConn) 154:           objCmd.CommandType = CommandType.StoredProcedure 155: 156:           Dim objParam As New OleDbParameter("@UserID", _ 157:              OleDbType.Integer) 158:           objParam.Value = UserID 159:           objCmd.Parameters.Add(objParam) 160: 161:           Try 162:              objConn.Open 163:              objReader = objCmd.ExecuteReader 164:           Catch e As Exception 165:              throw e 166:           End Try 167: 168:           return objReader 169:        End Function 


Each of these methods performs a very similar function: executing a stored procedure. This is a standard procedure it creates an OleDbCommand object and adds parameters therefore, we won't go into the details of each.

The AddCart method on line 52 executes the spAddCart stored procedure. When a user first logs in to the site, you'll use this method to create an entry in the tblShoppingCart table to store parts that the user adds. It takes a UserID parameter, and doesn't return anything.

The CartExists function on line 71 executes the spGetCart procedure. This function determines whether the user has an associated shopping cart, and returns a Boolean value indicating the answer. This function will be used in conjunction with the previous method, AddCart, to first determine whether a user has a cart before adding a new one.

On line 97, the GetSubTotal method retrieves the value from the spGetSubTotal query; that is, it returns the total price of all items currently in a specified shopping cart. This method is a bit different than the others. If no shopping cart exists for the specified user, the OleDbCommand.ExecuteScalar method on line 113 returns an object of type DBNull, which is the database's way of saying there was no data. You have to compare the type of the returned data to determine whether an answer or DBNull was returned. Use the GetType method to compare the results, as shown on line 119.

The GetCategory method returns an OleDbDataReader that contains all the products in a specified category. This method will be used to display products to users of the site.

Finally, the ViewMobile function on line 148 returns all the information about a specified shopping cart. As you can see, you have quite a bit of code here, but not much complex functionality.

The final method for your BananaMobile class is the Purchase method. It executes the stored procedure spPurchase, which transfers information from the tblShoppingCart table to the tblOrders table. This code, as well as the end of the BananaMobile class, is shown in Listing 22.20.

Listing 22.20 The Purchase Method Places an Order from a User
 170:        public Sub Purchase(UserID as Integer) 171:           Dim objReader as OleDbDataReader 172:           Dim dblSubTotal as Double = 0 173: 174:           dblSubTotal = GetSubTotal(UserID) 175: 176:           Dim objCmdPurchase As New OleDbCommand( _ "spPurchase", objConn) 177:           objCmdPurchase.CommandType = CommandType. _ StoredProcedure 178: 179:           Dim objCmdRemove As New OleDbCommand( "spRemoveCart", objConn) 180:           objCmdRemove.CommandType = CommandType. _ StoredProcedure 181: 182:           Dim objParam As New OleDbParameter("@SubTotal", _ OleDbType.Double) 183:           objParam.Value = dblSubTotal 184:           objCmdPurchase.Parameters.Add(objParam) 185: 186:           objParam = New OleDbParameter("@UserID", OleDbType.Integer) 187:           objParam.Value = UserID 188:           objCmdPurchase.Parameters.Add(objParam) 189: 190:           objParam = New OleDbParameter("@UserID", OleDbType.Integer) 191:           objParam.Value = UserID 192:           objCmdRemove.Parameters.Add(objParam) 193: 194:           Try 195:              objConn.Open 196:              objCmdPurchase.ExecuteNonQuery 197:              objCmdRemove.ExecuteNonQuery 198:              objConn.Close 199:           Catch e As Exception 200:              throw e 201:           End Try 202:        End Sub 203: 204:     End Class 205:  End Namespace 


There are three different things that go on in this method. First, on line 175, you retrieve the total price for all items in the shopping cart, which you'll use in the next step. Next, you execute the spPurchase stored procedure. This procedure, in addition to the items in the tblShoppingCart table, inserts the price into the tblOrders table (see Listing 22.13 for details). Finally, you remove the information in tblShoppingCart after it has been transferred.

Save Listings 22.18 22.20 in BananaMobile.vb. Now both of your business objects are complete. Before you can use them, however, you must compile them. Do so from the command line with the following command:

 vbc /t:library /out:..\..\bin\BananaMobile.dll /r:System.dll /  graphics/ccc.gifBananaMobile.vb User.vb 

Note that this command assumes that your /bin directory is two levels above the current directory. If it isn't, adjust the command accordingly or simply copy the generated file, BananaMobile.dll, into the /bin directory.

The ASP.NET Pages

In the previous two sections, you created the data stores and business objects for your BananaMobile application. The only thing left to do is build your ASP.NET pages, and with so much of the functionality already encapsulated in stored procedures and business objects, this step will be a snap.

Before building the pages, let's first examine the user controls you'll need. For this site, you'll use three user controls: a header, a menu sidebar that presents static links for the user, and a control to display current news. The header will contain the logo and links to move around the site, allowing the users to log in, return to the default page, or log out. The menu control will display links to each of the customizable item categories. The news control, which will be used only on the default page (default.aspx), will contain links to news items (for this exercise, you'll simply create dead links; that is, links that don't go anywhere).

Listing 22.21 shows the code for the header control.

Listing 22.21 The Header Control Displays a Common Header for Every Page in the Site
 1:  <tr> 2:     <td colspan="2" nowrap> 3:        <table cellspacing=0 cellpadding=0 width="100%"> 4:           <tr> 5:              <td><img src="/books/4/226/1/html/2/images/bm_sm.gif"></td> 6:              <td align="right" nowrap> 7:                 <font size="6"> 8:                 <b><i>BananaMobiles, Inc</i></b> 9:                 </font> 10:              </td> 11:              <td width="10">&nbsp;</td> 12:           </tr> 13:        </table> 14:    </td> 15:  </tr> 16:  <tr> 17:     <td colspan="2" nowrap> 18:        <table cellspacing=0 cellpadding=0 width="100%"> 19:           <tr bgcolor="#AACC88"> 20:              <td align="right"> 21:                 <font color="white"> 22:                 <a href="/tyaspnet21days/day22/default.aspx"> 23:                    Home</a> | 24:                 <a href="/tyaspnet21days/day22/login.aspx"> 25:                    Login</a> | 26:                 <a href="/tyaspnet21days/day22/ MyMobile.aspx">My BananaMobile</a> | 27:                 <a href="/tyaspnet21days/day22/Signout.aspx"> 28:                    Sign Out</a> 29:                 </font> 30:              </td> 31:           </tr> 32:        </table> 33:     </td> 34:  </tr> 

Save this file as header.ascx. Recall that user controls don't contain any <html>, <body>, or <form> tags; they present only the UI and any associated logic. This control displays an image on line 5, a logo on line 8, and four links, shown on lines 22, 24, 26, and 28. This is all wrapped in table rows. However, note that the table definition, <table>, is not included. These tags will be placed in the ASP.NET pages. In other words, you'll create "slots" in your pages for your user controls to fit in. When you look at an actual ASP.NET page, this will make more sense.


Note that you might have to replace the images specified in this and subsequent listings with your own. The images here are designed for the examples in this book.

Listing 22.22 shows the menu control, menu.ascx.

Listing 22.22 The Standard Menu Bar for the Application
 1:  <td valign="top" width="125"> 2:     <table valign="top" height="100%" cellspacing=0 3:        cellpadding=0 width="125"> 4:        <tr height="100%"> 5:           <td valign="top"> 6:              &nbsp;<br> 7:              Personalize your:<p> 8:              <a href="products.aspx?cat=BTS">BTS</a><br> 9:              <a href="products.aspx?cat=Carpet">Carpet</a><br> 10:              <a href="products.aspx?cat=Color">Color</a><br> 11:              <a href="products.aspx?cat=Engine">Engine</a> 12:              <br> 13:              <a href="products.aspx?cat=Frame">Frame</a><br> 14:              <a href="products.aspx?cat=Wheels">Wheels</a> 15:           </td> 16:        </tr> 17:     </table> 18:  </td> 

This user control also presents static HTML; specifically, six links on lines 8 14. This control also is only part of a table definition; the rest will be in the ASP.NET page. Notice that each of the links goes to the same place: products.aspx. You'll get to the details in a moment, but this one page will be responsible for displaying the different categories, with the aid of a querystring variable.

Listing 22.23 shows the final user control.

Listing 22.23 Displaying News Headlines
 1:  &nbsp;<br> 2:  <table width="250"> 3:     <tr> 4:        <td valign="top"><b>Banana Headlines</b></td> 5:     </tr> 6:     <tr> 7:        <td valign="top"> 8:           <li><a href="somewhere.aspx">Banana shortage 9:              reported in Bolivia</a><br> 10:           <li><a href="somewhere.aspx">United Nations 11:              promises no more banana slaughterings</a><br> 12:           <li><a href="somewhere.aspx">73rd Banana Cup 13:              begins in Spain</a><br> 14:           <li><a href="somewhere.aspx">Don't forget the 15:              April 16th Banana tax deadline!</a><br> 16:           <li><a href="somewhere.aspx">New use found for 17:              bananas, brings total to 13,456</a><br> 18:        </td> 19:     </tr> 20:  </table> 

Save this listing as news.ascx. On lines 8 17, you display five imaginary headlines with links that lead nowhere. In the future, these headlines and their links could be pulled from a database or XML file, but for now, static HTML will suffice.

Now on to the ASP.NET pages! (Note that we'll build all the .aspx files first, and then build the web.config file to implement security.) The default or home page for an application is typically named default.aspx, so let's start with this page. The default.aspx page will simply welcome the user to the BananaMobiles site, and will implement all three user controls. Listing 22.24 shows the code for default.aspx.

Listing 22.24 The Home Page Doesn't Display Any Dynamic Content
 1:  <%@ Page Language="VB" %> 2:  <%@ Register TagPrefix="Banana" TagName="Header" src="/books/4/226/1/html/2/header.ascx" %> 3:  <%@ Register TagPrefix="Banana" TagName="Menu" src="/books/4/226/1/html/2/menu.ascx" %> 4:  <%@ Register TagPrefix="Banana" TagName="News" src="/books/4/226/1/html/2/news.ascx" %> 5: 6:  <html><body background="images/banana_bg.gif"> 7:     <table height="100%" cellspacing=0 cellpadding=0 width="100%"> 8:     <Banana:Header runat="server"/> 9:     <tr height="100%"> 10:        <Banana:Menu runat="server"/> 11: 12:        <td align=left valign="top" width=100% nowrap> 13:           <table height="100%" valign="top" align="left" 14:              cellspacing=0 cellpadding=0 width="100%"> 15:           <tr height="100%" valign="top"> 16:              <td nowrap> 17:                 <table cellspacing=0 cellpadding=0 18:                    width="100%"> 19:                 <tr> 20:                    <td valign="top"> 21:                       &nbsp;<br> 22:                       <b>Welcome to BananaMobiles!</b> 23:                       <p> 24:                       With this site, you can build your own 25:                       customized BananaMobile and order it 26:                       online! Choose from the options on the 27:                       left to select parts to build your 28:                       BananaMobile! 29:                       <p> 30:                       Each BananaMobile comes built-in with 31:                       BananaConnect technology, to connect 32:                       you to the Internet without leaving 33:                       your banana. Our proprietary software 34:                       allows you to post track records to 35:                       compete online! 36:                    </td> 37:                    <td valign="top"> 38:                       <Banana:News runat="server"/> 39:                    </td> 40:                 </tr> 41:                 </table> 42:              </td> 43:           </tr> 44:           </table> 45:        </td> 46:     </tr> 47:     </table> 48:  </body></html> 


This page contains no ASP.NET code; it is all plain HTML and Web control tags. The code is rather long, but most of the length is due to the HTML tables that are used for page layout. On line 8, you implement the header user control. Recall from Listing 22.21 that this control started and ended with <tr> tags now you see how they fit into the ASP.NET page. You could simply copy the code from the user control and paste it in on line 8, but the user control makes things easier to follow.

The menu user control is on line 10. It presents a column on the left side of the page. Finally, the news control is on line 38, which displays on the right side of the page. Note that the section in lines 21 38 is the main area of the page, where much of the information will be presented to the user. Therefore, this listing serves as a template for all other pages in the site. Each page will simply replace lines 21 38 with its own information. Therefore, not all the presentation code for the ASP.NET pages will be shown only the information particular to each page.

Figure 22.7 shows this page from the browser.

Figure 22.7. The BananaMobiles home page.


All the necessary links are displayed on the home page, allowing easy movement from page to page.

The next most important pages are the ones that allow users to log in and register to the site. You've built quite a few of these types of pages throughout this book, so this should be familiar to you. Listing 22.25 shows the login page.

Listing 22.25 Using Forms Authentication to Validate Users
 1:  <%@ Page Language="VB" %> 2:  <%@ Register TagPrefix="Banana" TagName="Header" src="/books/4/226/1/html/2/header.ascx" %> 3:  <%@ Register TagPrefix="Banana" TagName="Menu" src="/books/4/226/1/html/2/menu.ascx" %> 4:  <%@ Import Namespace="System.Data" %> 5:  <%@ Import Namespace="System.Data.OleDb" %> 6: 7:  <script runat="server"> 8:     Sub Login(Sender As Object, e As EventArgs) 9:        dim objUser as New BananaMobile.User 10:        dim objBM as New BananaMobile.BananaMobile 11:        dim intID as integer 12: 13:        intID = objUser.Login(tbUsername.Text, _ 14:           tbPassword.Text) 15:        if intID <> 0 then 16:           Session("UserID") = intID 17: 18:           'check if user has a cart 19:           if not objBM.CartExists(intID) then 20:              objBM.AddCart(intID) 21:           end if 22: 23:           FormsAuthentication.RedirectFromLoginPage(intID, _ 24:              false) 25:        else 26:           lblMessage.Text = "<font color=red>That is not a valid login or password</ graphics/ccc.giffont>" 27:        end if 28:     End Sub 29:  </script> 30:  <html><body background="images/banana_bg.gif"> 31:     <table height="100%" cellspacing=0 cellpadding=0 32:        width="100%"> 33:        <Banana:Header runat="server"/> 34:        <tr height="100%"> 35:           <Banana:Menu runat="server"/> 36: 37:           <td align=left valign="top" width=100% nowrap> 38:              <table height="100%" align="left" cellspacing=0 39:                 cellpadding=0 width="100%"> 40:                 <tr height="100%" valign="top"> 41:                    <td nowrap> 42:                       <table cellspacing=0 cellpadding=0 43:                          width="100%"> 44:                          <tr> 45:                             <td valign="top"> 46:                                &nbsp;<br> 47:                                Please enter your username and 48:                                password to gain access to 49:                                these secure features. 50:                                <p> 51:                                Or, if you are a new user, 52:                                click <a href="register.aspx"> 53:                                here</a> to register and gain 54:                                access to our site!<p> 55: 56:                                <asp:Label  57:                                   runat="server" /> 58:                                <form runat="server"> 59:                                   <table> 60:                                   <tr> 61:                                      <td width="75" 62:                                         rowspan="3">&nbsp; 63:                                      </td> 64:                                      <td width="50" 65:                                         valign="top"> 66:                                         <font face="arial"> 67:                                         Username: 68:                                         </font> 69:                                      </td> 70:                                      <td width="50" 71:                                         valign="top"> 72:                                         <font face="arial"> 73:                                         <asp:Textbox 74:                                             75:                                            runat="server" /> 76:                                         </font> 77:                                      </td> 78:                                   </tr> 79:                                   <tr> 80:                                      <td valign="top"> 81:                                         <font face="arial"> 82:                                         Password: 83:                                         </font> 84:                                      </td> 85:                                      <td valign="top"> 86:                                         <font face="arial"> 87:                                         <asp:Textbox 88:                                             89:                                            runat="server" 90:                                            TextMode="password"/> 91:                                         </font> 92:                                      </td> 93:                                   </tr> 94:                                   <tr> 95:                                      <td align="right" 96:                                         colspan="2"> 97:                                         <font face="arial"> 98:                                         <asp:Button 99:                                             100:                                            runat="server" 101:                                            OnClick="Login" 102:                                            Text="Submit" /> 103:                                         </font> 104:                                      </td> 105:                                   </tr> 106:                                   </table> 107:                                </form> 108:                             </td> 109:                          </tr> 110:                       </table> 111:                   </td> 112:                </tr> 113:             </table> 114:          </td> 115:       </tr> 116:    </table> 117:  </body></html> 

Save this page as login.aspx. There is one method on this page, Login, which is executed when the user clicks a submit button (not shown). This method creates instances of both of your business objects, User and BananaMobile, on lines 9 and 10. Then, on line 13, you use the Login method of the User object to determine whether the credentials supplied by the user are valid. If they are (and consequently intID is a nonzero value), you store the user's ID in a session variable on line 16. You'll use this variable throughout your pages to gather information about a particular shopping cart.

On lines 19 21, you check whether the current user has a shopping cart. That is, if he has a row in the tblShoppingCart table that matches his user ID. This is accomplished with the CartExists method. If one doesn't exist, you create one on line 20 with the AddCart method. Recall that both these methods take a user ID as a parameter.

Finally, the user is authenticated and directed back to the page he came from on line 23 (see yesterday's lesson for more information on security and forms authentication). This page is shown in Figure 22.8.

Figure 22.8. The login page.


The registration page, thanks to your User business object, is fairly simple. It needs only to gather information from a user, and call the User.AddUser method with the appropriate parameters. Listing 22.26 shows the code for this page. Because the HTML portion of this page is standard, and follows the same pattern as the previous two pages, I won't include it here (besides, the HTML code would run on for pages!).

Listing 22.26 Gathering User Information
 1:  <%@ Page Language="VB" %> 2:  <%@ Register TagPrefix="Banana" TagName="Header" src="/books/4/226/1/html/2/header.ascx" %> 3:  <%@ Register TagPrefix="Banana" TagName="Menu" src="/books/4/226/1/html/2/menu.ascx" %> 4: 5:  <script runat="server"> 6:     Sub Submit(Sender As Object, e As EventArgs) 7:        if Page.IsValid then 8:           dim objUserDetails as New BananaMobile.UserDetails 9:           dim objUser as New BananaMobile.User 10: 11:           objUserDetails.Username = tbUName.Text 12:           objUserDetails.Password = tbPword.Text 13:           objUserDetails.Firstname = tbFName.Text 14:           objUserDetails.Lastname = tbLName.Text 15:           objUserDetails.Address = tbAddress.Text 16:           objUserDetails.City = tbCity.Text 17:           objUserDetails.State = tbState.Text 18:           objUserDetails.ZIP = tbZIP.Text 19:           objUserDetails.Phone = tbPhone.Text 20:           objUserDetails.Email = tbEmail.Text 21: 22:           objUser.AddUser(objUserDetails) 23: 24:           Session("UserID") = objUserDetails.UserID 25:           FormsAuthentication.SetAuthCookie(objUserDetails. _ 26:              UserID, false) 27:           Response.Redirect("default.aspx") 28:        else 29:           lblMessage.Text = "Some information is invalid. User not added to database." 30:        end if 31:     End Sub 32:  </script> 


Save this listing as register.aspx. Similar to the login page, this listing only has one method, Submit, which executes when the user submits the form. In our case, we've used Validation controls to ensure the user doesn't leave out information. The Page.IsValid check on line 7 ensures that all the information has been entered correctly. On lines 8 and 9, you create a UserDetails object to hold the information collected in the form, and a User object. On lines 11 20, you set values for the properties of the UserDetails object from text boxes the user has filled out (not shown here). You then pass this data-filled UserDetails object to the User.AddUser method on line 22.

Finally, after the user information has been added, the AddUser method assigns the new user ID to the UserDetails' UserID property. You store this in a session variable on line 24, and authenticate the user on line 25. Essentially, you are logging the user in to your site; after all, there's no reason to have him fill out this form and then have to log in again. This page produces something similar to the screenshot in Figure 22.9.

Figure 22.9. The registration page.


Now that you've got the basic functionality for your site, you can allow users to browse the products you have to offer. This will be accomplished via one ASP.NET page. You'll customize the page depending on what category of items a user wants to see. Let's take a look at the code first, shown in Listing 22.27.

Listing 22.27 Browsing Product Categories
 1:  <%@ Page Language="VB" %> 2:  <%@ Register TagPrefix="Banana" TagName="Header" src="/books/4/226/1/html/2/header.ascx" %> 3:  <%@ Register TagPrefix="Banana" TagName="Menu" src="/books/4/226/1/html/2/menu.ascx" %> 4:  <%@ Import Namespace="System.Data" %> 5:  <%@ Import Namespace="System.Data.OleDb" %> 6: 7:  <script runat="server"> 8:     Sub Page_Load(Sender As Object, e As EventArgs) 9: 10:        ' Obtain CategoryID from QueryString 11:        Dim CategoryID As String = CStr(Request.Params("cat")) 12: 13:        'set image and label 14:        imgCategory.ImageURL = "images/" & CategoryID & ".gif" 15:        lblCategory.Text = "<b>" & CategoryID & "</b>" 16: 17:        ' Populate List with Category Products 18:        Dim objBanana As New BananaMobile.BananaMobile 19:        Dim objReader As OleDbDataReader = objBanana. _ 20:           GetCategory(CategoryId) 21:        dlItems.DataSource = objReader 22:        dlItems.DataBind() 23:        objReader.Close() 24: 25:     End Sub 26:  </script> 27:  ... 28:  ... 29:     <td valign="top" width="110"> 30:        <asp:Image  runat="server" 31:           Border="1" Align="TextTop"/><br> 32:        <asp:Label  runat="server"/> 33:     </td> 34:     <td valign="top"> 35:        <form runat="server"> 36:           <asp:DataList  RepeatColumns="2" 37:              runat="server"> 38:              <ItemTemplate> 39:                 <table border=0 width="200"> 40:                 <tr> 41:                    <td width="200" valign="center"> 42:                       <b><%# Container.DataItem("Name") %></b><br> 43:                       <%# Container.DataItem("Description") %><br> 44: 45:                       <b>Price: </b><%# System.String. Format("{0:c}", Container. graphics/ccc.gifDataItem("Price")) %><br> 46: 47:                       <a href="AddToCart.aspx?PartID=<%# Container.DataItem("PartID")  graphics/ccc.gif%>&cat=<%# Container.DataItem("Category") %>"> 48:                          <font color="#9D0000"><b>Add To 49:                          Cart<b></font> 50:                       </a> 51:                    </td> 52:                 </tr> 53:                 </table> 54:              </ItemTemplate> 55:           </asp:DataList> 56:        </form> 57:     </td> 


Save this listing as products.aspx. In a nutshell, this page displays all items in a specified category in a DataList control. The DataList contains links that allow users to add an item to the shopping cart. Let's take a look at the code declaration block on lines 7 26 first.

All of this page's functionality occurs in the Page_Load event. On line 12, you retrieve the category to be displayed from the querystring. For example, the string "Engine" would cause this page to display all the items belonging to the engine category (see Listing 22.22 for details on where these querystring values came from).

You've created an image for each category of items. These images are stored in an images subdirectory, and are named following their categories. For example, the image for the frame category is frame.gif. Thus, line 14 causes the image control on line 30 to display the image appropriate for the selected category. Line 15 simply displays the category name to the user in the label on line 32.

On lines 18 and 19, you instantiate a new BananaMobile object and retrieve all items for the specified category using the GetCategory method, which returns an OleDbDataReader. Then on lines 22 23, you bind the data to the DataList on line 36 and close the data reader.

Let's next move down to the DataList. Using the ItemTemplate tag on line 38, you define a custom layout to display the products. Specifically, you use a table to present the names and descriptions for each product. Line 42 binds the name, and line 43 binds the description for each item (see Day 9, "Using Databases with ASP.NET," for more information on binding data). Line 45 binds the price data to the page, but uses the Format method of the String class to format it first. Let's examine this method in more detail.

The Format method takes two parameters: one that specifies how to format the string and another that specifies what string to format. The first parameter uses special characters to denote the formatting (similar to regular expressions). It follows the following format:

 { N [, M ][: formatString ]} 

where N is an integer representing the argument to be formatted, M is an optional integer indicating the width of the region that contains the formatted string, and formatString is an optional string of formatting code. For example, if you called:

 Format("You get {0:####} dollars!.", 3453) 

the output would be

 "You get 3453 dollars!" 

The string {0:####} means format the first parameter as four numeric digits. If you are familiar with C or C#, this syntax will look familiar to you. In this case, line 45, Format("{0:c}", Container.DataItem("Price"), tells ASP.NET to format the price of each item as currency, which means it automatically adds a currency symbol, such as $, to the value.

Finally, line 47 binds the PartID and Category to a link in the page. This link points to AddToCart.aspx, which is responsible for adding the item to the database (you'll get to that in a moment). Depending on the category selected and the data you entered into the tblParts table, this page produces pages similar to Figures 22.10 and 22.11.

Figure 22.10. Displaying the Engine category.


Figure 22.11. Displaying the Wheels category.


Now that users can browse and select items, you need to be able to add the items to the database (specifically, the user's shopping cart). Listing 22.28 shows AddToCart.aspx, which accomplishes just that.

Listing 22.28 Adding Selected Items to the Database
 1:  <%@ Page Language="VB" %> 2:  <%@ Import Namespace="System.Data" %> 3:  <%@ Import Namespace="System.Data.OleDb" %> 4: 5:  <script runat="server"> 6:     private objConn As New OleDbConnection("Provider=" & _ 7:              "Microsoft.Jet.OLEDB.4.0;" & _ 8:              "Data Source=c:\ASPNET\data\banana.mdb") 9: 10:     Sub Page_Load(Sender As Object, e As EventArgs) 11:        If not Request.Params("PartID") is Nothing Then 12:           Dim intUserID as integer = Session("UserID") 13:           Dim objBM As New BananaMobile.BananaMobile 14: 15:           ' Add Product Item to Cart 16:           objBM.AddPart(intUserID, CInt(Request.Params( "PartID")), Request.Params( graphics/ccc.gif"cat").ToString) 17:        End If 18:        Response.Redirect("MyMobile.aspx") 19:     End Sub 20:  </script> 


This page doesn't contain any UI it simply adds the item to the tblShoppingCart table in the database and immediately redirects the visitor to another page, MyMobile.aspx, which you'll look at in a moment. When this page loads, you first check whether a part ID is available in the querystring, as shown on line 11. If so, you call the BananaMobile.AddPart method on line 18, which takes three parameters: the UserID of the cart to add the part to, the PartID to add to the database, and the category to which the part belongs.

Recall that the AddPart method examines the category to determine which stored procedure to execute. Using the data that you bound to the link in the DataList in products.aspx, PartID, and Category, and the user ID stored in the session variable, this page can add whatever item you want, to the cart you specify.

MyMobile.aspx, shown in Listing 22.29, displays the status of the current shopping cart to the user. It also allows them to remove items from their cart. Let's look at the code.

Listing 22.29 Viewing BananaMobile Information
 1:  <%@ Page Language="VB" %> 2:  <%@ Register TagPrefix="Banana" TagName="Header" src="/books/4/226/1/html/2/header.ascx" %> 3:  <%@ Register TagPrefix="Banana" TagName="Menu" src="/books/4/226/1/html/2/menu.ascx" %> 4:  <%@ Import Namespace="System.Data" %> 5:  <%@ Import Namespace="System.Data.OleDb" %> 6: 7:  <script runat="server"> 8:     Sub Page_Load(Sender As Object, e As EventArgs) 9:        dim intUserID as Integer = Session("UserID") 10:        dim objBM as new BananaMobile.BananaMobile 11: 12:        lblSubTotal.Text = objBM.GetSubTotal(intUserID) 13: 14:        if lblSubTotal.Text = "0" then 15:           btPurchase.Visible = False 16:        end if 17: 18:        ' Populate List with Category Products 19:        Dim objReader As OleDbDataReader = objBM. ViewMobile(intUserID) 20:        dlItems.DataSource = objReader 21:        dlItems.DataBind() 22:        objReader.Close() 23:     End Sub 24: 25:     Sub BuyIt(Sender As Object, e As EventArgs) 26:        dim objBM as new BananaMobile.BananaMobile 27: 28:        objBM.Purchase(Session("UserID")) 29: 30:        Response.Redirect("thanks.aspx") 31:     End Sub 32:  </script> 33: 34:     <td valign="top"> 35:        &nbsp;<br> 36:        <b>Your BananaMobile:</b><p> 37:        <asp:DataList  runat="server" Width=100%> 38:           <ItemTemplate> 39:              <table width="100%"> 40:              <tr> 41:                 <td width="25%"> 42:                    <%# Container.DataItem("Category") %> 43:                 </td> 44:                 <td width="25%"> 45:                    <%# Container.DataItem("Name") %> 46:                 </td> 47:                 <td width="25%"> 48:                    <b>Price: </b><%# System.String. Format("{0:c}", Container. graphics/ccc.gifDataItem("Price")) %> 49:                 </td> 50:                 <td width="25%"> 51:                    <a href="RemoveItem.aspx?PartID=<%# Container.DataItem("PartID") % graphics/ccc.gif>&cat=<%# Container.DataItem("Category") %>">Remove</a> 52:                 </td> 53:              </tr> 54:              </table> 55:           </ItemTemplate> 56:        </asp:DataList> 57:        <br> 58:        <form runat="server"> 59:           <b>SubTotal: $<asp:Label  60:              runat="server"/></b> 61:           <p> 62:           <center> 63:           <asp:Button  runat="server" 64:              Text="Buy It!" 65:              OnClick="BuyIt"/> 66:           </center> 67:        </form> 68:     </td> 


This page is very similar to products.aspx, except that it uses a DataList control to display all the items in the specified shopping cart instead of category. Even the formatting of the data is the same, such as the price on line 51. This page has a button that allows the user to purchase the displayed BananaMobile.

When the page loads, you retrieve the subtotal and display it in the label on line 59. If this subtotal is zero, it means that there are no items in the shopping cart. If there are no items in the shopping cart, you don't want to allow the user to click the Purchase button on line 63. Line 14 checks this condition, and sets the button to be invisible to the user if the subtotal is zero, effectively preventing him from submitting the form.

On lines 19 22, you simply retrieve the BananaMobile information with the BananaMobile.ViewMobile method, and bind it to the DataList.

The BuyIt method, beginning on line 25, executes when the user submits the form. This method simply calls the Purchase method of the BananaMobile object, and redirects to a static thank-you page, thanking the user for purchasing with BananaMobile, Inc. This is left for you to develop. Figure 22.12 shows the MyMobile.aspx page when viewed from the browser, after adding some items to the cart.

Figure 22.12. Viewing the information for the current BananaMobile.


The link on line 51 allows users to remove certain items from their BananaMobile. This page, RemoveItem.aspx, is nearly identical to the AddToCart.aspx page. Let's take a quick look at it in Listing 22.30.

Listing 22.30 Removing Items from the Shopping Cart
 1:  <%@ Page Language="VB" %> 2:  <%@ Import Namespace="System.Data" %> 3:  <%@ Import Namespace="System.Data.OleDb" %> 4: 5:  <script runat="server"> 6:     private objConn As New OleDbConnection("Provider=" & _ 7:              "Microsoft.Jet.OLEDB.4.0;" & _ 8:              "Data Source=c:\ASPNET\data\banana.mdb") 9: 10:     Sub Page_Load(Sender As Object, e As EventArgs) 11:        If not Request.Params("PartID") is Nothing Then 12:           Dim objBM As New BananaMobile.BananaMobile 13: 14:           ' Add Product Item to Cart 15:           objBM.AddPart(CInt(Session("UserID")), 0, Request.Params("cat").ToString) 16:        End If 17:        Response.Redirect("MyMobile.aspx") 18:     End Sub 19:  </script> 

The only difference between this page and AddToCart.aspx is the second parameter of the AddPart method on line 15. This parameter normally specifies which part to add to the database. However, the user is removing this category of item from his BananaMobile altogether, so you can simply set this parameter to 0.

There's only one more page to go: signout.aspx, which allows users to log off from your application. This page is shown in Listing 22.31.

Listing 22.31 Logging Users Off
 1:  <%@ Page Language="VB" %> 2:  <%@ Register TagPrefix="Banana" TagName="Header" src="/books/4/226/1/html/2/header.ascx" %> 3:  <%@ Register TagPrefix="Banana" TagName="Menu" src="/books/4/226/1/html/2/menu.ascx" %> 4: 5:  <script runat="server"> 6:     sub Page_Load(Sender as object, e as eventargs) 7:        FormsAuthentication.SignOut 8:     end sub 9:  </script> 10:  <html><body background="images/banana_bg.gif"> 11:     <table height="100%" cellspacing=0 cellpadding=0 12:        width="100%"> 13:        <Banana:Header runat="server"/> 14:        <tr height="100%"> 15:           <Banana:Menu runat="server"/> 16: 17:           <td align=left valign="top" width=100% nowrap> 18:              <table height="100%" valign="top" align="left" 19:                 cellspacing=0 cellpadding=0 width="100%"> 20:                 <tr height="100%" valign="top"> 21:                    <td nowrap> 22:                       <table cellspacing=0 cellpadding=0 23:                          width="100%"> 24:                          <tr> 25:                             <td valign="top"> 26:                                &nbsp;<br> 27:                                You have been logged off. 28:                                Thanks for visiting! 29:                             </td> 30:                          </tr> 31:                       </table> 32:                   </td> 33:                </tr> 34:             </table> 35:          </td> 36:       </tr> 37:    </table> 38:  </body></html> 

As you can see, this page only does one thing: call the FormsAuthentication.SignOut method to delete the authentication cookie and log users off the site.

So far, I've discussed every page in the site, but I've neglected to mention how to implement security. Because you're using forms authentication, this can be accomplished with only a few lines in the web.config file, as shown in Listing 22.32.

Listing 22.32 Using web.config to Provide Security
 1:  <configuration> 2:     <system.web> 3:        <authentication mode="Forms"> 4:           <forms name="AuthCookie" loginUrl=" day22/login.aspx" /> 5:        </authentication> 6:     </system.web> 7:     <location path="day22/AddToCart.aspx"> 8:        <system.web> 9:           <authorization> 10:              <deny users="?"/> 11:           </authorization> 12:        </system.web> 13:     </location> 14:     <location path="day22/MyMobile.aspx"> 15:        <system.web> 16:           <authorization> 17:              <deny users="?"/> 18:           </authorization> 19:        </system.web> 20:     </location> 21:  </configuration> 

Save this file in the root directory of your application (for example, c:\inetpub\wwwroot). If you already have an existing web.config file, you can simply merge the settings of this one with the existing one. This file does three things. First, it sets the authentication mode to Forms on line 3. Line 4 tells ASP.NET that if an unauthenticated user accesses resources that aren't available to him, it should redirect him to the login.aspx page of the application. The name attribute specifies that the authentication cookie generated should be named AuthCookie.

The location tags on lines 7 and 14 restrict access to specific files, namely the AddToCart.aspx and MyMobile.aspx pages. The reason these two pages are used is simple. You want to allow users to browse the site, but only registered users can add items to their shopping cart, which is accomplished with AddToCart.aspx. Therefore, this file is restricted to non-anonymous users. Also, unauthenticated users should not be able to see information about their BananaMobile; if they are unauthenticated, you have no idea who they are, and thus you don't know which shopping cart belongs to them. If they try to access MyMobile.aspx without logging on first, they'll receive an error because the application doesn't know who they are.

That's all there is to it! You examined quite a few files, but many of them perform similar functionality. After you've built the default page, you can follow its template to rapidly produce the other pages in the site. And because all the functionality can be accomplished with one or two calls to your business objects, none of these ASP. NET pages has complex functionality; they have only code that deals directly with the UI.

This process went fairly smoothly because you spent a bit of time designing the application beforehand. By developing the application in the order you did (database items and logic, followed by business objects, and then ASP.NET pages), the process went quickly, and you didn't have to switch development gears by moving to different tiers of the application before one tier was finished.

However, you're not quite done yet! There's still the matter of the BananaConnect system and the Web Service.

The Web Service

The BananaConnect system needs to be able to post information about each BananaMobile easily from a remote system. BananaMobile, Inc. has told you that it only wants the functionality to add this information, but that the company itself will build the interface. Thus, you've decided to create a Web Service; the functionality will be there, and BananaMobile, Inc. will be free to implement it as the company sees fit.

Additionally, only registered BananaMobile owners can post information to the service. This means that the Web Service must implement some form of security to control access. SOAP headers are a logical choice to add this functionality (see Day 17, "Consuming and Securing XML Web Services," for more information on using these custom headers to implement secure Web Services). Let's take a look at the code, shown in Listing 22.33.

Listing 22.33 The Secure BananaConnect Web Service
 1:  <%@ WebService Language="VB"  %> 2: 3:  Imports System 4:  Imports System.Data 5:  Imports System.Data.OleDb 6:  Imports System.Web.Services 7:  Imports System.Web.Services.Protocols 8: 9:  Public Class Authenticator : Inherits SoapHeader 10:     Public Username as string 11:     Public Password as string 12:  End Class 13: 14:  public Class BananaConnect : Inherits WebService 15:     public sHeader as Authenticator 16:     private objConn As New OleDbConnection("Provider=" & _ 17:        "Microsoft.Jet.OLEDB.4.0;" & _ 18:        "Data Source=c:\ASPNET\data\banana.mdb") 19: 20:     <WebMethod(), SoapHeader("sHeader")> Public sub UpdateData(UserID as Integer,  graphics/ccc.gifMileage as Double, MaxSpeed as Double, Throttle as Double) 21:        if sHeader is Nothing then 22:           throw new ArgumentNullException() 23:        end if 24: 25:        if Authenticate(sHeader.UserName, sHeader.Password) then 26:           Dim objCmd As New OleDbCommand("spBananaConnect", _ 27:              objConn) 28:           objCmd.CommandType = CommandType.StoredProcedure 29: 30:           Dim objParam As New OleDbParameter("@UserID", _ 31:              OleDbType.Integer) 32:           objParam.Value = UserID 33:           objCmd.Parameters.Add(objParam) 34: 35:           objParam = New OleDbParameter("@Mileage", _ 36:              OleDbType.Double) 37:           objParam.Value = Mileage 38:           objCmd.Parameters.Add(objParam) 39: 40:           objParam = New OleDbParameter("@MaxSpeed", _ 41:              OleDbType.Double) 42:           objParam.Value = MaxSpeed 43:           objCmd.Parameters.Add(objParam) 44: 45:           objParam = New OleDbParameter("@Throttle", _ 46:              OleDbType.Double) 47:           objParam.Value = Throttle 48:           objCmd.Parameters.Add(objParam) 49: 50:           objParam = New OleDbParameter("@Date", _ 51:              OleDbType.Date) 52:           objParam.Value = DateTime.Now.ToString 53:           objCmd.Parameters.Add(objParam) 54: 55:           Try 56:              objConn.Open 57:              objCmd.ExecuteNonQuery 58:              objConn.Close 59:           Catch e As Exception 60:              throw e 61:           End Try 62:        end if 63:     End sub 64: 65:     private function Authenticate(strUser as string, strPass as string) as boolean 66:        try 67:           dim intID as integer = 0 68:           dim objCmd as new OleDbCommand("spLoginUser", _ 69:              objConn) 70: 71:           Dim objParam As New OleDbParameter("@Username", _ 72:              OleDbType.Integer) 73:           objParam.Value = strUser 74:           objCmd.Parameters.Add(objParam) 75: 76:           objParam = New OleDbParameter("@Password", _ 77:              OleDbType.Integer) 78:           objParam.Value = strPass 79:           objCmd.Parameters.Add(objParam) 80: 81:           objConn.Open 82:           intID = CType(objCmd.ExecuteScalar, Integer) 83:           objConn.Close 84: 85:           if intID.ToString = "" then 86:              return false 87:           end if 88:           return true 89:        catch ex as OleDbException 90:           return false 91:        end try 92:     end function 93:  End Class 


Save this file as BananaConnect.asmx. This Web Service has two classes. The first, Authenticator, shown on lines 9 12, contains two properties that will be used to authenticate users. This class inherits from the SoapHeader class, so you know that it will be sent along as extra baggage with the regular communication from the consumer of this service.

The second class, BananaConnect, beginning on line 14, is the actual Web Service. This is indicated on line 1 with the Class attribute of the @ WebService directive. This class must inherit from the WebService class. On line 15, you declare an instance of the Authenticator class, and on line 16, you declare an OleDbConnection object both of which will be used in your methods.

The only method available to consumers, UpdateDate, begins on line 20. Notice the use of the <WebMethod> and <SoapHeader> attributes in the function declaration. The first attribute is necessary for clients to be able to access this method over the Internet. The second attribute specifies that this method will require a SOAP header to be sent along in addition to the regular communication. On lines 21 23, you check whether a SOAP header was indeed sent, and if not, you throw an exception (see Day 20, "Debugging ASP.NET Pages," for details on exceptions).

Line 25 calls the Authenticate method to validate the credentials supplied in the SOAP header. You'll get to this method in a moment. If the credentials are valid, you insert the supplied data into the database. Lines 26 61 should look familiar: You create an OleDbCommand object, build parameters, and execute the spBananaConnect query.

Finally, the Authenticate method on line 65 is very similar to the Login method shown in Listing 22.17. It takes a username and password as parameters, and executes the spLoginUser stored procedure to determine whether the credentials match a registered user. This is the heart of the validation mechanism for this Web Service.

Naturally, you want to test this service before you let BananaMobile have it. Let's do so using ASP.NET and Http-Post. Unfortunately, when you try to access this service through the browser with the URL http://localhost/tyaspnet21days/day22/BananaConnect.asmx?op=UpdateData, you won't be able to input any parameters to test the method. This is because your SOAP headers can't be sent via Http-Post; therefore, your method won't allow you to access it.

To get around this, you can either build a proxy and use the Web service from another ASP.NET page that supplies the SOAP header, or you can simply disable the SOAP headers for now, and put them back in when necessary. Let's go with the latter method. Change line 20 of Listing 22.33 to read:

 <WebMethod()> Public sub UpdateData(UserID as Integer, Mileage as Double, MaxSpeed as  graphics/ccc.gifDouble, Throttle as Double) 

The only change is the removal of the <SoapHeader> attribute. Also comment out lines 21 23, 25, and 62. These lines are responsible for the validation routines using the SOAP header, so you don't need them for now. Now request this page from the browser again, and you should see Figure 22.13.

Figure 22.13. Testing the Web Service using Http-Post.


Enter some information into the UserID, Mileage, MaxSpeed, and Throttle fields and click the Invoke button. If all goes well, another browser window should open and display any returned data. Remember that this Web method didn't return any data, so you shouldn't see anything in the new window. You can make a quick check to the tblBananaConnect table in your database to be sure that the data was entered properly. If so, you can uncomment lines 21 23, 25, and 62, add the <SoapHeader> attribute back, and you're ready to roll!


    Sams Teach Yourself ASP. NET in 21 Days
    Sams Teach Yourself ASP.NET in 21 Days (2nd Edition)
    ISBN: 0672324458
    EAN: 2147483647
    Year: 2003
    Pages: 307
    Authors: Chris Payne

    Similar book on Amazon © 2008-2017.
    If you may any questions please contact us: