S511 Tech Foundations of Net-enabled Organizations

Assignment 4 - Working with ADO .NET

Due: Sunday Dec 5 2004 by 11:59PM- firm Deadline

Objectives of this assignment

The primary objective of this assignment is to connect ASP.NET web forms to databases using ADO.NET. In this assignment, you are going to start with the files you submitted for Assignment 3, and build up from that to make a complete application.

Part 0. Authentication screen

Remember our old authentication forms that we created in a couple of in-class exercises? You will need to now make a real one of those. This form will simply have two textboxes, one for username, and one for password, and one login button, which will verify the password of the user with passwords in the database, and if the password matches the one in the database for that user, it will redirect the page to the stocks.aspx web form, otherwise display an error message. Name this form login.aspx

If you like, you can do this with a custom validator, which makes connection to the database, searches for the username and password, and returns valid if a row exists with the username and password, say its valid, otherwise not. You can then check Page.IsValid to determine if validations passed, and proceed to stocks.aspx. To redirect to a different page, use the following code:

	Response.redirect("stocks.aspx")

Remember that you will need to save the username in a session variable for it to be available to other programs. A session variable is simply an extension to cookies that allow you to save information in the client for integration between different pages. You can see some more info on session variables in http://hotwired.lycos.com/webmonkey/03/30/index3a.html, but basically, all you are doing is:

	Session("username") = whatever the username value is

If the user does not have an account in the database, you should provide a link to go to the userprofile.aspx page, to create a new profile.

Part 1. User profile Web form

You have already created the user profile page in Assignment 2 - now you have to do some real things:

1. Obviously, now the information gets stored in a database. When you submit the information, if the fields are all valid, then you need to save the information in the database in a table (say, called, userprofiles). You will need to create this table in your SQL server database, with all the fields that you have in your user profile form.
2. A user may edit his/her information - so you should be able to load the information from the database. Note that if the user loads the information from the database, he/she cannot change his/her username - so you should disable the username textbox. How do you know if a user is actually editing existing information and not creating a new profile? All you have to do is to check the session variable. If the session variable is not empty, then you are in edit mode, otherwise in insert mode. In edit mode, when you save, your query would be an update table query rather than an insert query.
3. The username uniqueness can now be tested. The requirement that the username be between 5 and 10 characters, and not start with a numeric value still stay. You don't need the last requirement - that it ends with a numeric value. Instead you actually check for uniqueness, to make sure that the username does not actually exist in the database already.
4. You should have a "log out" button - and when this button is pushed, set the session variable to empty, and redirect to the login.aspx page.
5. You should also include a link for the user to go to the stocks page from here.

Part 2 - Stock Calculator

In this part of the assignment, you will modify your stock calculator so that it now actually reads information from the database.

1. You will need a stocks table, in which you store the symbols, names, and current prices of, say, 10-15 different stocks. Just go to www.nasdaq.com and pick up some stocks from there to load this table. You will need to save the ddl statements for creating this table, and loading values in this table, so avoid using MS access ADP for this. See What to submit for details.
2. You will now need to modify your form to let the user pick stocks for his/her portfolio. You can do this in many ways - here are a couple of options, but feel free to come up with your own idea as well:
   • You can use your existing look and feel of stocks.aspx from Assignment 2. The Stock symbol textbox should now become a drop-down list, Stock name will be a disabled but visible field, or even a label, and there will be two editable fields for purchase price, and quantity. When you select a stock, the name and current price from the stock (read from the database) will need to be placed in the appropriate labels. When you hit the submit button, this information will need to be saved for the user. You can let a user modify his/her portfolio, although in this method you will only let a user have a maximum of 3 stocks.
   • You can use the datagrid style to allow users to add/update/delete stocks from his/her portfolio, in a similar fashion that we have done before. You can do this using either a datagrid, or using the old fashioned way like you did for quiz 2. This will allow a user to have as many stocks as you want. Of course, your calculations for gain/loss and percentages will need to change.
3. To save the information for users, you will need to create a joining table between userprofile and stocks - say in a table called "userstocks". Create this table in your database, with userid and stocksymbol as foreign keys from the two other tables, and purchase price and quantity as the other fields.
4. Like before, you will need to display the gain/loss and percentages for individual and cumulative display.
5. You should have a "log out" button - and when this button is pushed, set the session variable to empty, and redirect to the login.aspx page.
6. You should also have a link to go to the userprofile.aspx page to edit your profile.

Name this form stocks.aspx.

What to Submit

You will need to submit the following (follow the naming conventions exactly, otherwise you will not get the credit). Both the forms must show your name and username, as well as in the footer it should indicate the declaration stating "I have neither given nor received unauthorized aid on this deliverable." Make sure the files are submitted in proper case (in this case, everything is in lowercase):

• login.aspx: The web form for the login screen.
• userprofile.aspx: The web form for the user profile.
• stocks.aspx: The web form for the stock calculator.
• tables.sql: An sql script that would create all the three tables in your database. Your script should have the following template:

   	  drop table userstocks
  	  go
  	  create table userstocks (.....)
  	  go
        drop table userprofile
  	  go
  	  create table userprofile( ......)
  	  go
  	  drop table stocks
  	  go
  	  create table stocks (......)
  	  go
  	  insert into stocks values(.....)
  	  go
  	  insert into stocks values (....)
  	  go
  	  ....
     

How to Submit

To submit your assignment, follow the steps below:

• Point your web browser to Our Assignment handin page,
• Enter your email address (has to be the full email address, not just the USERID), and your student ID or SSN (without any dashes or spaces) in the appropriate boxes,
• Upload the files in the 3 boxes shown,
• Click on "Submit Assignment", and wait until you get the confirmation page. This may take a couple of minutes - do not click multiple times. The system will display a confirmation page.
• You are not done yet! Check your email for a confirmation email from the submission system. It may take up to 5 minutes for you to receive the confirmation email. Make sure you read and understand the email, including the information that it gives you. Do not assume that your assignment submission was successful until you receive an email with no error message.
• You may submit as many times as you would like - future submissions will overwrite any file submitted with the same name. If you change only one file, you can submit only that file. The confirmation message will include all files submitted so far. Keep a copy of the confirmation message. This will be proof that your submission was successful in the case of a system glitch later.

Individual assignment policy

This assignment is an individual assignment. You are to work on this assignment solely by yourself with no direct help from any other student in the class or outside. You are welcome to post your questions or doubts on the course discussion forums as long as that does not involve posting part or whole of your solution. You can email me your questions if you need someone to look at your code. You can ask a fellow student for small help such as help with fixing a parse error, or a doubt about a statement in the assignment. However, the following will be considered violation of the individual policy and you will be penalized if you violate this policy:

• Emailing part or all of your code to a fellow student
• Posting your work on a website, ftp site, group discussion site, or any other site where anyone in addition to yourself has access
• Giving hardcopy of your code to a fellow student
• Asking a fellow student for part or all of their code
• Looking at another student's work while he/she is on a terminal.
• Printing source code to a public printer and not picking it up right away.