The case study aims at providing you with an example of an application that integrates all the concepts covered throughout the book.
The case study aims at providing you with an example of an application that integrates all the concepts covered throughout the book. In this chapter, you will be looking at an online software store.
Green Stock Systems is a fictitious online store that sells software through its Web site. A visitor to this Web site can register, browse through the products available, search for a particular product, and view the list of products stocked based on different criteria. The interface and front end of the Web site are created using the Web Forms feature of the Visual Studio .NET Beta. The back end is an SQL database that is created and managed using SQL Server 7.0. Interaction between the front end and back end is made possible by using ADO and ASP, both of which are supported by the .NET framework.
Download code files here
The Back End
The back end of the Web site consists of an SQL database the Green Stock database. This database, as shown in Figure 1, stores all the information displayed to the user, such as Registration information, mailing address, and product-related information.
Figure 1 The tables, fields, and relationship between the tables in the Green Stock database.
The database consists of the following tables:
The Customer_tab Table
The Customer_tab table stores details pertaining to each customer who visits the online store. It stores the customer ID, first name, middle name, last name, date of birth, mailing address, city, country, zip code, and e-mail address. The customer ID field is the primary key of the Customer_tab table. It is used in the validation process when a customer logs on to the site. It can contain alphanumerical characters. The first name, date of birth, city, and e-mail fields cannot be left empty. By default, the country field stores the value United States.
The Login_tab Table
The Login_tab table contains the fields that are used to validate a user login. It contains fields for the customer ID, password, the old password, a hint question, and a corresponding answer for the hint question. When a new customer registers with the Web site, simultaneous entries are made in both the Customer_tab and Login_tab tables. This ensures integrity of data. This table comes into play every time the customer attempts to log on to the site or change the password or has forgotten the password that allows access to other pages of the Web site. The customer ID field is the primary key and the foreign key of this table. The Customer_tab and Login_tab tables are linked through customer ID.
The Products_tab Table
This table contains information about the various software products that can be purchased from the online store. The Products_tab table stores the product ID, name, description, company, category ID, price, and quantity of stock on hand. The product ID field is the primary key. The category ID field is the foreign key that links the Products_tab and Category_tab tables.
The Category_tab Table
All the products sold in the online store belong to a specific category, such as operating system, application, or game. The Category_tab table contains the category ID and a description of each category. The category ID field is the primary key of this table. When an entry is made for a new product in the Products_tab table, the category ID of that product is obtained from the Category_tab table.
The Deals_tab Table
The Deals_tab table contains the details pertaining to the deals or bargains that are available for a product. Green Stock Systems offers two kinds of deals. In the first kind, the deal is simply a reduction in the price of the product. In the second kind, another product might be bundled with the selected product for a marginal increase in price. The Deals_tab table stores the deal ID, product ID, description of the deal, category ID, new price of the product, the stock that is available for this deal, the date till which this offer is valid, and the product ID of the add-on product, if any. The deal ID field is the primary key of this table. The fields that store the product ID of the main product and the add-on product are both foreign keys, which link the Products_tab and Deals_tab tables. The category ID field is also a foreign key, which links the Deal_tab and Category_tab tables. A check constraint is specified for this table to ensure that the product ID of the main and add-on products are not the same for an entry.
The Sales_tab Table
The Sales_tab table stores the information that is required for a customer to purchase a product from the online store. It contains the sales ID, customer ID, product ID, category ID, the shipping address of the customer, city, country, zip code, the number of units of the product, the date of the sale, and the delivery status. The sales ID field is the primary key of the table. The customer ID, products ID, and category ID fields are the foreign keys of the Sales_tab table. They link the Sales table to the Customer_tab, Products_tab, and Category_tab tables respectively. By default, the delivery status field contains the value Not delivered.
The Payment_tab Table
The Payment_tab table contains the credit card information required to process and validate the credit card of the customer. It stores the sales ID, credit card number, amount, the type of the credit card, and the status of the transaction. The sales ID field is the primary key of the Payment_tab table. It also acts as the foreign key of this table, linking it with the Sales_tab table. The status field contains the status of the transaction. For example, when the credit card is successfully processed for the required amount the status field contains the value Processing completed.
The Feedback_tab table is used to store the feedback that is received from the customer. It is very important for a Web site to get an opinion from the customer as to the usefulness of the Web site and the various areas in which it can be improved. The Feedback_tab table contains the feedback ID, the date on which the feedback was entered, and the feedback itself.
The Front End
The front end or the interface of the online software store is created using Web Forms in Visual Studio .NET. Web Forms are very similar to Windows Forms. The difference between the two is that Web Forms provide additional capabilities for the Internet, which is its primary target. Windows Forms, on the other hand, can be used to create the interface of a wide variety of applications that are not necessarily Web-based.
The interface of the online Web site of Green Stock Systems consists of a series of forms that are designed using Web Forms. The first page displayed to the user is the home page, as shown in Figure 2. This page informs the user about Green Stock Systems and the products it sells.
Figure 2 The Home page of the Green Stock Systems online store welcomes customers to the store.
The home page displays a list of options. When the user chooses any of the menu options, the corresponding form is displayed. These main forms as are:
- Product Catalog
- About Us
- Contact Us
The registration form shown in Figure 3 is used to register a new user with the Web site.
Figure 3 The registration form used to register customers with the Green Stock Systems online store.
It consists of the Registration.aspx and Registration.cs files. The design details and appearance of the form are specified in the Registration.aspx file. The Registration.cs file is the code behind file. The data entered in the various controls of the form is manipulated and validated in this file. A user who visits the online store for the first time must register. Only then can the user buy any of the products on sale. Thereafter, the user must log on each time she visits the site. The Registration form obtains the first name, middle name, last name, mailing address, and e-mail address of the user. The user must type these details in the text boxes provided. The user must also enter a password, which will enable the user to log on to the site. In addition, a hint question must be selected from the list box. The user's answer to the hint question must also be entered. In a situation where the user forgets the password, the hint question is displayed. Only if the user enters the correct hint answer will the password be revealed.
The information from the Registration form is then validated. These validations need to be performed before the values are inserted into the tables. The rationale behind such an approach is that there are certain basic validation conditions that must be satisfied before the user registration can be processed, such as the validation condition that does not allow a control to hold a null value and a validation restriction on the maximum number of characters. The form has certain fields, such as Country and Hint Question, which provide the user with limited choice to ensure data accuracy. The form has a Regular Expression validator, which is a control used to specify a validation condition for another control bound to the e-mail control, ensuring that the user enters an e-mail address in the standard Internet format. The form also has a ValidationSummary control that is used to display an entire list of all the fields that the user needs to fill to ensure that a valid registration attempt is processed.
The code behind file, Registration.cs, starts processing only if the page is valid. This means that only if all the required fields are entered will further processing be done. In other words, connections to the database are established only when the registration attempt is valid and no unnecessary resources (variables) are allotted memory. On clicking the Submit button the code behind file checks if the user is valid and then invokes a function called ValidateAddForm(). This function initiates a Boolean variable to true and then checks the validity of data entered. The Boolean variable is used to track the validity of the data. This prevents further processing of the data if any of the validation conditions are not satisfied. From the ValidateAddForm() method the compare() function is called and the ID entered by the user is sent as a parameter to the function. The purpose of this function is to validate the uniqueness of the ID the user has chosen. The user ID is compared with the other user IDs in the Customer_tab table and the value 0 is returned if the user ID matches one in the table. Otherwise, the value 1 is returned. If the user ID is unique and all the required conditions are satisfied, the add_user() method is invoked to add the new user to the Customer and Login tables. In the add_user() method, the SQLCommand object, scmonly, is used to process the insertions directly into the table. A transaction is used to ensure that both operations are either committed or rolled back simultaneously, because the inserts must be processed in two tables. If the entire process of registration is successful, the welcome screen is displayed.
The login form shown in Figure 4 provides the authentication mechanism for the Web site.
Figure 4 The login form enables the customers to log on to the Green Stock Systems online store.
This form also provides a Forgot Password link. If the user attempts to place an order without logging on, she is brought to this form first. Placing orders requires authentication. However, the user can browse the product catalogue without having to log on to the Web site.
The products form shown in Figure 5 is used to display the various products that are available at the online store.
Figure 5 The products form displays product details to customers.
The products form comprises two forms, Product_view and Product_details. The Product_view form consists of two files, Product_view.apsx and Product_view.cs. The Product_view.aspx file contains the code required to display various controls on the form. It defines the appearance of the form. The Product_view.cs file is the code behind file. It contains the code that is required to establish a connection with the database and display the relevant entries. The products form contains a series of link buttons at the top of the screen that enable the customers to view the products available according to different criteria, such as the name of the company, category, and price. It also contains a DataGrid control, Product1, in which the results retrieved from the database are displayed. When a button is clicked, the fillds() method is called. This method uses the DataGrid control to display the relevant entries from the Products_tab table of the Green Stock database. For each button a DataView, which is an object that displays data, is created that specifies the sort order for the data displayed. The sort order is specified for a column. A RowFilter, which is a property of the DataView object, is also specified for rows to further ensure that only relevant entries are displayed. For example, when the user clicks the Adobe button, the entries retrieved are first sorted according to the product name. However, only products belonging to Adobe must be displayed. Therefore, a row filter is specified that ensures that only those entries in which the company field contains the value Adobe are displayed.
One of the columns of Product1 is a button column. When an entry is displayed in Product1, the values displayed in this column are hyperlinks. When the user clicks this link, another form, Product_details, is displayed that shows the details for the selected product. For example, when the user clicks the hyperlink for Macromedia Flash, the details of this product are displayed. The address of Product_details and the name of the product for which the link is clicked is passed to Product_view using the Navigate() method in the method Prod_buy() in WebForm1.cs.
The product details are displayed in a separate form called Product_details. This form consists of the Product_details.aspx and Product_details.cs files. The design elements of the form are contained in the Product_details.aspx file. Product_details.cs is the code behind file. Product_details contains label controls that are used to display the name of the product, description, company name, and price. The deals that are available for a product, if any, are also displayed. The customer can buy the product at the normal rate or purchase it through any of the deals. When the customer clicks the Buy Now! button, the Orders form is displayed.
Green Stock Systems offers several deals for some of the products that are sold in the online store. As mentioned earlier, these deals either involve a reduction in price or the bundling of another product with the main product for a price less than the sum of both the individual products. The information pertaining to the deals available for a product can be viewed in two ways. The user can directly access the Deals section of the Web site that displays all the available deals for all products or obtain deals information through the Products section.
When the user chooses the Deals option from the menu, the deals form appears, as shown in Figure 6.
Figure 6 The deals form displays the various deals available for the products sold at the online store.
This form comprises a .aspx and a .cs file. The .aspx file contains the design information for the form. A series of link buttons at the top of the form display information about all the deals that are available, bargains, and discount. When the user clicks the View All Deals button, the name of the product, price, new price, and the add-on product, if any, are displayed. The Bargain button is used to display combo deals in which two products are sold together for a total reduced price. For example, the Adobe Acrobat Reader is offered in a deal with Macromedia Flash for a total price that is less than the sum of their individual prices. The Discount button is used to display deals that offer price reductions. For example, Adobe Acrobat Reader, which is slated to cost an imaginary figure of $250, is given in a discount deal for $200. The two kinds of deals are distinguished by the deal ID used in the Deals_tab table. All deal IDs starting with DA represent a discount deal. All deal IDs starting with DB represent a bargain deal.
All the deals offered by Green Stock Systems are bound by a validity date, which is the date until which the deal shall remain valid. The dataset is filled with only valid deals, because all the deals that are to be displayed must be valid deals. This ensures that the dataset does not contain any data about any out dated deals. It also saves the trouble of filtering valid deals. The datagrid in the Deals.aspx page is bound to specific columns in a dynamically created datatable. The datatable contains the result of a specially created join between the Product_tab table and the Deals_tab table. Both these tables are loaded into the DataSet and a relation is established between the two tables. This relationship enables the presentation of the required information from the Product_tab table. The Deals page (Deals.apsx) has two hidden columns in the datagrid. These are used for trapping the deal_ID and Prod_ID of the deal selected by the user. These values are then sent to the Orders page (Order1.aspx) for further processing.
The orders form shown in Figure 7 displays the product that the customer orders.
Figure 7 The orders form enables a customer to purchase a product.
It accepts payment details, such as credit card number and card type, and also prompts the user for the latest shipping information. The existing shipping details are displayed from the Customer_tab table. The Orders form allows purchase of only a single product and a single unit of the product. The Orders form also displays details of the add-on products that the user might receive, in case the user has taken advantage of a deal.
After the user enters the payment and shipping information, a new sales ID is generated. A stored procedure, gen_salesid, is written for this purpose. A new row is added to the Sales_tab table and the Payment_Tab table. The QOH column in the Product_tab table is also updated to reflect the latest changes.
The search controls shown in Figure 8 enables the user to search the database for a particular keyword.
Figure 8 The products form also displays the search controls that enable the customers to search for a product at the online store.
All the entries that match the keyword specified by the user are displayed. Product_view.aspx has a textbox control in which the user can enter the term that must be searched for. The code behind file calls the getresult() method that takes a string variable as its parameter. This string stores the values entered by the user. Symbols such as ;, ,, -, and . are first eliminated and the remaining characters are copied into another string variable. If there are any double spaces in the first string, they are replaced with single spaces in the final string. In addition, words such as find, get, product and show are eliminated. The product name, description, and company columns of the Green Stock database are searched. If a field value that matches the contents of the string is found, the entries are displayed in the DataGrid control. The user can view the details of a specific product by clicking the appropriate hyperlink.
Areas of Improvement
The hallmark of a good Web site is that it is constantly enhanced and improved to keep up with changing market conditions. The online software store of Green Stock Systems is no exception. The following are some of the improvements and enhancements that the reader can attempt to implement
More validations can be performed on the data entered by the user in the Registration form. For example, validations can be provided to ensure that for a user to be recognized as a valid user and his or her age must be above 18 years.
A link can be added to enable the database to remember the customers' password. This ensures that a customer can be logged on automatically on accessing the site.
The buttons can be modified to ensure that the various options pertaining to each button are displayed only when the user points to the options. The category description for the products can also be displayed.
Various kinds of deals can be introduced to induce the customer to purchase products. The deals can also be tailored to suit the needs of individual customers.
In the current application, only one product and one unit of each product can be purchased at a time. The reader can create a shopping cart that enables the customer to purchase multiple products at the same time.
The search method can be extended to include the category description column in the Category_tab table.