Basics exercises revised, part 2

This exercise involves building a web application that will serve up quotations stored in a database.The user will be able to view the quotations in various sorted orders, do searches, add new quotations, and hide selected quotations from view.

Exercise 1: Setting up the data base

In the Basics website, create a SQL Server database with the same name as your login name on the server.

Create a new table in your database for storing quotations. You can either use the Table Designer or create a New Query and use the SQL below for this:

CREATE TABLE Quotes (
Quote_ID int IDENTITY (1, 1) PRIMARY KEY,
Quotation varchar (250) NOT NULL,
Author varchar (50) NOT NULL,
Category_ID int NOT NULL,
Creation_date smalldatetime NOT NULL
)

Also create a Categories table with fields for an ID and a name.

Create a  relationship where the category ID is a foreign key in the Quotes table:

Insert a few categories and quotations with some hand-coded SQL.

After creating a few new table rows, do select * on the Quotes table, and you should see the new rows. Modify the select statement so that it shows the category names instead of the IDs - you can use the Query Builder for this if you need help.

Hint: Don't forget that SQL quotes strings using single quotes, not double quotes.

If you don't feel inspired to surf for quotes, here are some to get you going (just add a few of them for now).

• "I feel like those Jewish people in Germany in 1942." -- Ted Turner (on being prevented from buying another TV station)
• "If a man speaks in the forest and there is no woman there to hear him, is he still wrong?" -- Unknown Heroine
• "Men are like a fine wine. They all start out like grapes, and it's our job to stomp on them and keep them in the dark where they will mature into something you'd want to have dinner with" -- Unknown Heroine
• "A woman needs four animals in her life. A mink in the closet. A jaguar in the garage. A tiger in bed. And an ass to pay for it all." -- Anne Slater
• "An editor should have a pimp for a brother, so he'd have someone to look up to." -- Gene Fowler
• "The newest computer can merely compound, at speed, the oldest problem in the relations between human beings, and in the end the communicator will be confronted with the old problem, of what to say and how to say it." -- Edward R Murrow
• "Egotism is the anesthetic that dulls the pain of stupidity." -- Frank Leahy
• "Some for renown, on scraps of learning dote, and think they grow immortal as they quote." -- Edward Young

Exercise 2: Web scripts that talk to the RDBMS

Make a new folder called Basics2 in the web sites. Put all page files related to these exercises in this folder.

There will be two pages in this exercise. The initial page is quotations.aspx which displays a table of quotations, and some forms, links, and buttons. One button will be used for adding new quotes to the database, and will link to a second page, add-quote.aspx which should contain a form for the user to enter data for the new quote.

The SQLDataSource and GridView controls are the primary tools needed for accessing the database and displaying its contents on the quotations pages. Configure the SQLDataSource to select all the records from the Quotes table. Configure the GridView to display these records. 

Exercise 3: User interface improvements

Modify the GridView so it doesn't display the Quote_ID as the user is not interested in seeing that. Format the date column to use the DataFormatString {0:M-dd-yyyy}. This will just display the month, day, and year. Enable sorting but not on the Quote column (delete the Sort Expression).

Now comes the tricky part - getting the category names to display in the GridView. This will require changing the SQLDataSource configuration. The safest way is to use the Query Builder in the configuration wizard for the control. Notice that it will make a SELECT statement similar to the one you wrote earlier. 

Change any column headings that need revision to be more user friendly. Do any other formatting of the GridView as you wish.

Add a button that will allow the user to add a new quotation. Change its text to "Add a Quote" and configure it go to the add-quote page when clicked.

Exercise 4: Data entry

Go to the add-quote page and build a form so that a user can enter a quote, author and category. Use a DropDownList for the category. Add another SQLDataSource to get the category records and bind them to the drop down control. Add a button for the user to submit the data. The event handler for this button will need to use the SQLDataSource to add the data to the Quotes table. Then it should return the the quotations page. Test this to be sure it works.

Now modify the page so the user can create a new category. Provide a text box for this. It might be easier to include an Add Category button for this.

Add validators to prevent the user from leaving the Author and Quotation fields blank Consider how the category selection should be done and try to design as user friendly an interface as possible.

Exercise 5: Searching

Add controls at the top of the quotations page to perform a search query from the user. Add a second SQLDataSource control to the quotations page with an appropriate SELECT statement and implement using the same GridView control to display search results. It will be necessary to change the grid's DataSourceID property to use the appropriate data source. Build a target query for this form that returns all quotes containing the specified search phrase. Your search should look through the Quotation, Author and Category Name columns. Hint: Use like '%foo%' in the SELECT statements WHERE clause if searching for the word foo. Using the configuration tool will help to get the syntax of this query right, but it won't be able to build the entire statement. Edit the result to include the Category Name in the query, using the generated code as a starting point. Take not of the 

When a search is performed, the grid displays the items that matched and a Display All button should appear which can be used to redisplay the entire set of quotations. This button should only be visible when search results are being displayed.

Exercise 6: Personalizing your service with cookies

Now implement per-browser personalization of the quotation database. The overall goal should be

• A user can "hide" a quotation and have it never show up again either from the top-level quotation display or in search results.
• Hiding a quotation is persistent and survives the closing and restarting of a browser.
• Quotations hidden by one user (browser) have no effect on what is seen by other users.
• Users can erase their personalization and see the complete quotation database again by clicking on an "Unhide" button on the main page. This button should appear only if the user has personalized the quotation database.

You'll implement this using cookies. Note that you can expire a cookie by reissuing it with an expiration date that has already passed.

Note 1: It is possible to build this system using an ID cookie for the browser and keeping the set of hidden quotations in the RDBMS. However, if you're not going to allow users to log in and maintain their profile, there really isn't much point in keeping data on the server.

Note 2: It isn't strictly copacetic with the cookie spec, but most browsers accept cookie values containing commas. So you can store the hidden quotations as a comma-separated list of IDs.

Note 3: Don't filter the quotations in your Web script. It is generally a sign of incompetent programming when you query more data from the RDBMS than you're going to display to the end-user. SQL is a very powerful query language. You can use the NOT IN clause to exclude a list of quotations. For example SELECT * FROM Quotes WHERE Quote_id NOT IN (3,4,5,6) will exclude quotes 3,4,5,6 from the selected rows. Note that both SQLDataSource controls will need their SELECT statements modified when loading a page with a cookie.

Add a ButtonField to the GridView that will be used to hide quotes and assign a value to it's CommandName property. Next, select the GridView and configure a few of its properties: Enter Quote_ID for its DataKeyNames property. This will give access to the Quote_ID value when a hide button is pressed. Enter a method name for the RowCommand property. This will create an event handler that fires whenever one of the buttons is clicked. Go to the codebehind for this event handler to implement it. Here's where things get a bit tricky again. The GridViewCommandEventArgs e parameter represents the button that was clicked. The Object sender parameter is the GridView itself. Here's how we use them to get the Quote_ID for the row where the button was clicked:

The if statement verifies that the event was in fact caused by the ButtonField ("hideIt" is what I called its CommandName. Modify accordingly if you used a different name). The row number for the button that was pressed is in e.CommandArgument. It is converted to an int and used to get the DataKey for that row of the grid -- the DataKey's value is the QuoteID to add to the list of hidden quotes in the cookie.

Whenever quotes have been hidden, a Unhide Quotes button should be displayed. Clicking this button should redisplay the entire set of quotes (expire the cookie). If no quotes are hidden, the button should not be visible.

Depending on how you structure your solution, it may be necessary to force a page load after a hide quote event or unhide event. This can be done by adding the statement Response.Redirect("quotations.aspx"); at the end of these event handlers.

Add a hyperlink from your site's default page that links to this exercise. Once the application is working properly on your development machine, copy the database to the server using the instructions here. Modify the connection string to use the CSIS server as described in the preceding instruction page. Finally, load the pages on to the server.

You can try out a sample quotations page here: http://indra.stockton.edu/Basics/Basics2/quotations.aspx

Screen Shots (old)

quotations.aspx

add-quote.aspx

After a search (note the Display All button)

After hiding some quotes (note the Unhide Quotes button)