The database for this application consists of two tables—tblUsers and tblUserStocks. The tblUsers table contains the usernames, passwords, and ZIP codes of the registered users of the system. The tblUserStocks table contains one row for each stock that any user wants to track. Because the application will be getting stock data for each stock individually, this table structure works out nicely for quickly looping through the table data. In the sample code, the database is named WSCh15 and was built in SQL Server 2000. When you've created your database, you can use the script shown in Listing 15.1 to create the tables.
Besides the tables, there are also several stored procedures that cut down on the amount of raw SQL code required in the application. The first, sp_GetUser, returns the information for the user specified by a username and password. If this stored procedure doesn't return any records, it means that the user and/or password are incorrect, and an appropriate message is displayed to the user.
The sp_GetUserByID routine is used to retrieve a user's profile information, based on the primary numeric key value. This is used on the portal viewer page, as well as the profile editor page, to retrieve the key information from the database about this particular user.
The sp_GetUserStocks routine brings back all the stock records for a particular user ID. The primary key of the tblUserStocks table is the ticker symbol. No primary key was defined on this table because we are not checking for duplicate stock symbols. In all actuality, a duplicate symbol won't hurt anything in the system, so it's not a concern at this point.
The script to generate the stored procedures is shown in Listing 15.2.
You may want to create a user ID and password that the Web application will use to access the application database. If you're in a testing mode, you can use the sa username with a blank password, but be sure that your SQL Server is set up to allow mixed (or SQL) authentication. The next step is to set up the application configuration file.