CIS Logo SVC Logo

   Computing & Information Systems
   Department

 

Schoology Facebook        Search CIS Site      Tutorials

Ecommerce App: Car Store 1



The Goals for this Example


This example outlines the creation of a simple ecommerce app for selling new cars online. This is a variation on the last example, the software 2 web app for selling software downloads online. Master pages and content pages are used as are many of our previous items such as Entity Framework, LINQ, and validators. This web app allows the user to look up particular cars by selecting first the brand and then the model. Alternatively, the user can do a search for some substring of the brand, model, or description.

Getting Started



Preliminaries

  • This online store will use a master page that provides a consistent look and navigation buttons for all of the pages. The name of the store is The Car Store. The sample pages listed below each show the top and bottom section that are provided by the master page (which itself is first in the listing).
  • Begin by extracting the Store1 folder of files from store1.zip so that the folder gets placed on your W drive.
  • Then start Visual Studio and use the option to open a website, choosing this new Store1 folder as the website.
  • You will need to add your Windows connection string and your webapp connection string to the usual place in the Web.config file.
  • You should also add the usual App_Code folder to your project.
  • Do a Save All at this point.
  • The aspx files should be complete, at least as complete as we need them for this project. Note that Cart.aspx has essentially nothing in it. It will be worked on in the next project.
  • The aspx.vb code-behind files provide an outline of the needed code and often all, or nearly all, of the code. Your task is to fill in the blanks so as to get this app to work. A few of the aspx.vb files have no code in them. For example, Contact.aspx.vb needs no code.
  • Before we can complete the VB code, however, we need to have some database tables.

Database Work



New Tables

  • Use SQL Server Management Studio to connect to your database with Windows authentication.
  • Right click on your Tables folder, Select New Table, and then create a table with this design. Note that the ID field is marked as the primary key. Also, with the ID field highlighted, expand Identity Specification in the Column Properties window. Set IsIdentity to Yes.
  • To save this table, right click on its tab above the design screen. When prompted for the table name use CarProduct. We will precede all the tables for this store with Car so that they will not conflict with any left-over tables from our earlier work. The better method would be to make an entirely new database just for this car store, but we are no going to take the time to do so.
  • Similarly, create a table with this design and name it CarCategory. The ID field should be the primary key and an identity field, as shown in that image.
  • Also create a table to link the above two tables. Use this design and name the table CarCategoryProduct. The ID field is both the primary key and an identity field.
  • You can close any tables open in design view once you have saved them.
  • In your tree of table names, right click on CarProduct table and select Edit Top 200 Rows. (You may first have to click the refresh button in order to see these newly-created tables.) This will let you edit the table one row at a time. The data gets saved when you move to the next line. Do NOT try to enter a number for the ID field as this is an identity field and will automatically get filled in. Use the data shown below. Be careful when copying this data into you table, not to pick up a newline at the end of any of the strings. (The newline is not visible in the database itself, but check that there is no invisible character at the end of a string by seeing if you can backspace over an invisible something.)
ID ModelName Description Price
1 Hurricane Top of line luxury automobile. GPS navigation system comes standard. 35255
2 Tornado Top of line sports car. 32999
3 Cheetah Modestly-priced sports car. 27880
4 Famtastic Modestly-priced family car. 22555
5 Lightning Sporty two-seater electric/gas hybrid car. 33000
6 Bolt Small 4-seater electric/gas hybrid car. 36500
7 Thunder Large 6-seater electric/gas hybrid car. 39999
8 Rocket Our fastest sports car. 38400
9 Laser Small 2-seater sports car. 37600
10 Kangaroo Large all terrain vehicle. 35000
11 Rabbit Small all terrain vehicle. 33900
12 M-star Medium-sized sport utility vehicle. 29000
13 Roady Modestly-priced sports car. 28100
14 Hippo Large sport utility vehicle. 31200

  • Next, edit the CarCategory table in a similar manner
  • Add the following data:
ID BrandName
1 Electra Motors
2 Alpha Omega
3 Vanguard
4 Road Master

  • In the same way, edit your CarCategoryProduct table.
  • Place the following data into it.
  • However, if any of your other 2 tables does not have it's ID field numbered 1, 2, 3, etc. then you MUST adjust the data in this CarCategoryProduct table to use the actual numbers from those other two tables. Otherwise, the linking of associated data will not work right, at least in places. Note that the usual problem is that some numbers in the sequence of ID numbers may get skipped due to false starts in entering data.
  • The important point is that the third CategoryID (whether numbered 3 or something else) should be specified for the first four ProductIDs (whether they are 1, 2, 3, 4 or something else), the first Category ID should be used for the next three ProductIDs, the second Category ID should be used for the next four ProductIDs, and the fourth (last) Category ID should be used for the next three (the last three) ProductIDs.
ID CategoryID ProductID
1 1 5
2 1 6
3 1 7
4 2 8
5 2 9
6 2 10
7 2 11
8 3 1
9 3 2
10 3 3
11 3 4
12 4 12
13 4 13
14 4 14

Relationships Between Tables

  • As you probably expect, there should be relationships set up between the tables based on the fact that the primary keys of both the CarProduct table and the CarCategory table occur as foreign keys in the linking table CarCategoryProduct, though their names are slightly different there as we can't have all three fields named as ID!
  • Right click on the CarCategoryProduct linking table and select Design.
  • Select Table Designer, Relationships (or just click the Relationships button).
  • We wish to set up a relationship between the ID (primary key) field of the CarProduct table and the ProductID (foreign key) field of the CarCategoryProduct table.
  • In the Foreign Key Relationships dialog box that you should now have, click the Add button. Some default name will be given to this new relationship, but it will be adjusted automatically once you select the primary key table (in the next few steps). The name will end up as FK_CarCategoryProduct_CarProduct, but there is no need to manually adjust the name.
  • Click in the field to the right of the Tables and Columns Specification. You will then see a "..." button. Click on it.
  • This presents us with a Tables and Columns dialog box which we want to adjust to look like this screen shot.
  • Begin by selecting CarProduct as the Primary key table. Under it you want the ID field to be the only one listed. If there are other fields shown, click in its box and use the pull-down to change the entry to None. That will clear out the unwanted value.
  • The Foreign key table should automatically be listed as CarCategoryProduct. Adjust things so that the only field listed under this is ProductID. It should be in the same line as the ID field for the CarProduct table.
  • Click OK, which takes you back to the properties dialog for this relationship. Make sure that the following 3 properties are marked as Yes: Check Existing Data on Creation or Re-Enabling, Enforce for Replication, and Enforce Foreign Key Constraint. They will probably be set this way by default.
  • Your Foreign Key Relationships window should now look like that shown in this image.
  • Then click the Add button to start setting up a second relationship. This time we want to set up a relationship between the ID (primary key) field of the CarCategory table and the CategoryID (foreign key) field of the CarCategoryProduct table.
  • Click in the field to the right of the Tables and Columns Specification. You will then see a "..." button. Click on it.
  • This again presents us with a Tables and Columns dialog box which we want to adjust to look like this screen shot.
  • Select CarCategory as the Primary key table. Under it you want the ID field to be the only one listed. Remove any others.
  • The Foreign key table should automatically be listed as CarCategoryProduct. Adjust things so that the only field listed under this is CategoryID. It should be in the same line as the ID field for the CarCategory table.
  • Click OK, which takes you back to the properties dialog for this relationship. Make sure that the following 3 properties are marked as Yes: Check Existing Data on Creation or Re-Enabling, Enforce for Replication, and Enforce Foreign Key Constraint.
  • Your Foreign Key Relationships window should now look like that shown in this image.
  • Click on Close in the dialog box as we are done creating the needed relationships between the tables.
  • Then do File, Save All to save the changes to these tables. You can then close the Design window for the CarCategoryProduct table.

Database Diagram

  • Next, we want to build a database diagram to show the relationships between these three tables.
  • In SQL Server Management Studio, right click on Database Diagrams in the tree of items under your particular database.
  • Select New Database Diagram.
  • In the Add Table dialog that follows, select all 3 tables: CarProduct, CarCategory, and CarCategoryProduct. Click on Add and then on Close.
  • The diagram should then be draw automatically and look like the one in this image.
  • Be sure that both of the lines (indicating the two relationships that we set up) are shown and that all 3 tables are displayed. If this is not the case, something is wrong and needs to be fixed before moving on.
  • Do File, Save All to be sure that everything is saved.

The VB Code-Behind Files



Create a Model for the Three Tables

  • Before creating the VB code, you need to create a database model in Visual Studio.
  • This will allow you to use the Entity Framework and LINQ in writing your VB code, instead of having to write SQL or stored procedures to get at the data.
  • In creating a model, you have to keep track of the name for you entities collection as you will need to use it your VB code. It is the second of three items that you have to name when generating a model from the database.
  • Since you have created models in several of the previous examples, you should be able to handle this without further directions.
  • Do a Save All after the model has been created.

Brands.aspx.vb

  • The Brands page presents the user with a drop-down list displaying the various brands of cars for sale. These brand names have to be pulled from the database, though we will use the Entity Framework and LINQ to do this.
  • Before creating the VB code to handle the user's choice of a brand in the drop-down list, check that the AutoPostBack property of this drop-down list to True. (It should be already set to True in the code that was supplied.) When the user makes a choice of an item by clicking on it, this will cause a postback, allowing our VB code to process the selection.
  • Since the VB code is outlined for you, you just need to figure out the missing pieces.
  • The goal for the logic-integrated query is to get all the brand names from the appropriate table and to put them into ascending order for convenience.
  • This data is then used as the DataSource for the drop down list. However, note that the drop-down list does have one value in it in advance, namely the string "*** Make a choice ***".
  • Check this out on the Brands.aspx page. Click on the smart tag for the drop-down list and then click Edit Items. Make sure that Enabled and Selected and both true, that Text contains "*** Make a choice ***", and that Value is the empty string. Actually, the Value can be anything as it is never used. Then click OK.
  • Back in the VB code, notice that when the user clicks on an item in the drop-down list, a postback happens and the code in the Else clause is followed. Here we check to see if the SelectedIndex for the drop-down list is 0. If it is, the user clicked on the first item, "*** Make a choice ***". This is not a reasonable choice, so we make an error message visible. If all is OK, we save the name of the selected brand into Session state so that we can use that brand name in our next page or pages.

Cars.aspx.vb

  • Make similar changes to Cars.aspx and Cars.aspx.vb.
  • The idea here is that the list of possible models (of the particular brand already selected should) be displayed here in a drop-down list so that the user can select one of them.
  • Check under the smart tag for the drop-down list, in Edit Items, that Enabled and Selected and both true, that Text contains "*** Make a choice ***", and that Value is the empty string. (Again, teh Value can be anything as it is never used.) Then click OK.
  • The VB code for this page should be fairly similar to that in the previous page. So fill that in.
  • Note that if Session.Count is not positive, we skip the LINQ as we have no brand name in Session state that we can use in our query.
  • The query itself is given to you. Notice that it is a query on the CarCategoryProducts linking table. That is because we can get to the other 2 tables from that one. (In actuality, we are dealing with objects that contain data from the database.) Look carefully at the syntax to get from a Car object for CarCategoryProducts to the BrandName field in the CarCategory table. The notation for this is Car.CarCategory.BrandName, where CarCategory is one of the so-called Navigation Properties for the linking table, as seen in the picture for your database model in Visual Studio.
  • The results of the LINQ query are used to add the model names to the drop-down list.
  • Upon postback, as before, we check to see if the SelectedIndex is 0 or not. If it is 0, the user is on the informational message, not a real model name, so that all we do is to make an error message visible.
  • If, upon postback, all is OK, we save the model name into Session state, so that it (and the brand name which is already in Session state) will be available on the page we now redirect to, Results.aspx.

Results.aspx.vb

  • Once we reach the Results page, both the brand name and model name are known and saved in Session state. We use them to do do a LINQ query to obtain all of the information about that particular car.
  • That information is then displayed in the various labels and text box on the form.
  • Just to be safe, we skip the LINQ query if Session.count is 0. Otherwise, we obtain the brand and model names from Session state and do the query.
  • The query looks for all objects in the entities for the linking table, CarCategoryProducts, that have the right brand name and model name.
  • We know that there should be only one match, but since potentially there could be many, the data returned is a collection. Thus we use FirstOrDefault to get the first object in the collection. We then tack on the CarProduct navigation property to get to the correct CarProduct object. The description and price from that object are then displayed in the appropriate controls on the form.

Search.aspx.vb

  • There is nothing that you have to do with this short section of VB code, as it is already complete.
  • After the user fills in the desired substring to search for and clicks the Search button, a postback occurs. The VB code then handles this by putting the substring into Session state and redirecting to the SearchResults page.
  • The idea for this search is that it will give the user access to all of the data on all of the cars whose model name, brand name, or description contains the target substring.

SearchResults.aspx.vb

  • First, on the SearchResults.aspx form, make sure that the grid view is wide enough so that the column headings all fit on one line. If not, drag the right edge of the grid view so as to make it wide enough.
  • We also want to set the Autogenerate Columns property of the grid view to False, but at first leave this property at True so that you do not get at index out of bounds error. Then after everything is finished, change this property to False. If it is left as True, the grid view is likely to show duplicate columns, both the ones manually set up and the autogenerated ones.
  • When we reach this page, all that is known is the user's target substring. Our VB code has to find all cars that contain that substring somewhere in their brand name, model name, or description. We also do not want to return the same car more than once.
  • The Page_Load event handler begins by checking if Session.Count is 0. If it is, we have no substring to use to do the search. Thus we just quit with an error message being displayed.
  • If we get past that hurdle, we then retrieve the desired substring from Session state and check if it is the empty string. If so, we display an error message.
  • If all is OK thus far, we do the LINQ query, most of which is supplied for you. We again look for objects in the entities for CarCategoriesProducts. This time we select objects where Car.CarCategory.BrandName.Contains(Substring) or else Car.CarProduct.ModelName.Contains(Substring) or else something that you have to fill in that looks for Substring within the description. Notice how we again use the Navigation properties to go from Car to CarCategory. We then look at the BrandName field and check if it contains the desired string (Substring). Contains is a function that looks for substrings, so it is perfect here.
  • The OrElse is the short-circuit version of Or. So as soon as we hit a condition that is True (if there is one), the computer does not check the next condition, the one after the OrElse. Notice that this prevents our query from returning the same car object more than once, one of the features we said that we wanted.
  • The query does put the data in order, first by brand name, and secondarily (if there are 2 or more cars with the same brand) by model name.
  • The data that is selected is supposed to be just the brand name and model name, but you have to fill that in. We do not ask for the rest of the data on each car, as our grid view provides the user with a View button to click on if the user wants to see more information (such as the description and price).
  • If the count of the number of objects returned by the LINQ query is greater than 0, our code uses this data to fill in the grid view. Otherwise, an error label is made to be visible.
  • The PageIndexChanging event handler for SearchResults contains exactly the same code except for one new line that tells the grid view what page of results to display. We had this same thing in the software2 web app, so you should be able to fill this in easily.
  • The RowCommand routine only needs to have one piece of code filled in. This routine checks to see if the name of the command is View. If so, the user clicked the View button in this row. e.CommandArgument then contains the correct index for the row where the user clicked. This implicitly tells us the brand and model of the car the user wants more information on. These are the brand and model names contained in this same row of the grid view. The code uses SearchResultsGridView.Rows(index).Cells(1).Text to get the text from cell 1 (where counting begins at 0) for the current row. In cell 0 is the View label and in cell 1 is the brand name, so this retrieves the brand name. This brand name is stored in Session state, The model name is then found and stored in Session state in a similar manner. We then redirect to the Results page, the same one used above, as it is perfect for displaying the data about one particular car and only needs to know (from Session state) the brand and model, which we have just taken care of.

Back to the main page for ASP.NET 4.5 Web Apps



Author: Br. David Carlson
Last updated: November 08, 2017
Disclaimer