In this lab, we'll work with VB/ASP.Net web applications that use ADO.Net to retrieve data from SQL Server.

Preparing the Database

In this lab, we'll work with the data stored in the sample movies database. This database, which should still be on your SQL Server instance, contains a Performer table with performer ID and name. Modify this database as follows:

1. Use the SQL Server Enterprise manager to add a new field to the Performers table in which to store the name of the image file for that performer. Call the field "image" and set its type to "nvarchar" (i.e., a variable length string). Do this by clicking mouse-right on the Performer table, choosing "Design Table", and then adding the column.

2. Go on-line and download an image for one or two of the performers. Definitely include one for the governor of California; you'll find a pretty good one of the "governator" here: http://cs.calvin.edu/christian/ai/index.php

   . Save the picture(s) on your desktop temporarily, you'll insert it(them) into a new ASP.Net project later.

3. Add the name of your image file(s) to the Performers table in the appropriate place. You can do this by clicking mouse-right on the Performers table, choosing "Open Table"-"Return all rows", and typing the image filename into the appropriate row.

Building a Simple ADO.Net Application in Visual Studio

Visual Studio is capable of taking care of much of the bookkeeping related to the ADO.Net development we discussed in class. To experiment with this, start up Visual Studio and create a new ASP.Net web application project by performing the following steps:

1. Build an ASP.Net project that looks something like this:
   

2. Add the SqlConnection object, which you can see listed at the bottom of the design view. Do this by Opening the toolbox, selecting the "Data" toolbar, choosing the "SqlConnection" object and then clicking anywhere on the Design screen. Follow the wizard, choosing your server and your movies database when appropriate.

3. As discussed in class, you may have to manually set the password in the connection string. Do this by going to the code-behind file, finding the connection string in the VS.Net generated code, and adding the clause "password=yourpassword;". You can avoid this if you just let VS.Net save your password when you run the connection string wizard.

4. When you've finished creating it, save and build the project. Make sure that you can find the files that VS.Net created for you and that you can display the page in your browser. You'll notice that the page comes up more slowly the first time it's loaded than it does on subsequent loads. The first time through, ASP.Net compiles the application for future use.

5. Create an images folder in your project and move your image(s) into that folder. Do this as follows:

   • Click mouse-right on the project name (in the "Solution Explorer"), choosing "Add"-"Folder", and naming the folder "images".
   • Click mouse right on the new folder, choose "Add"-"Existing Item", and choose the image file(s).

6. We'll now program the "Name Filter" button to display the results of a simple SQL command. Do this by inserting the following code into the callback function for the button:

   'Create an SQL command object for use in querying the database.
   Dim SQLCommand As New System.Data.SqlClient.SqlCommand
   SQLCommand.Connection = Me.SqlConnection1
   SQLCommand.CommandText = "SELECT * FROM Performer WHERE name LIKE '%Schwarzen%'"
   
   'Create a similar data reader for use in reading the query results.
   Dim dataReader As System.Data.SqlClient.SqlDataReader
   
   'Open the connection to the database.
   Me.SqlConnection1.Open()
   
   'Execute the query and read/post the output rows in HTML table format.
   dataReader = SQLCommand.ExecuteReader()
   lblOutput.Text = ""
   While dataReader.Read()
      lblOutput.Text += dataReader.GetInt32(0).ToString() + " " + dataReader.GetString(1) + " "
      lblOutput.Text += "<img src='images/" + dataReader.GetString(2) + "' alt='my favorite governor'>"
   End While
   	    

   If you cut and paste, don't forget to remove the HTML character codes. Rebuild the project and see what happens when you press the "Name Filter" button. Look through this code carefully and make sure you know what it does and how it does it. If you have any questions on this, ask!

7. Change the hard-coded query to search for all performers with a name like "%Arnold%". This will probably create an error because the image filenames for most of the performers are NULL. You can deal with this in SQL using the SELECT clause function is_null(image, ' '), or perhaps more generally, you can add something like the following to your ASP.Net code in the appropriate spot:

   If dataReader.IsDBNull(2) Then
      lblOutput.Text += " "
   Else
      lblOutput.Text += "<img src='images/" + dataReader.GetString(2) + "' alt='my favorite governor'>"
   End If
   	    

   Again, make sure you understand what this code does and where it should go.

8. Modify the program to print the data out in an HTML table format. This will make output much easier to read, as in the following examples:

9. Finally, modify the program to search for records LIKE the string typed in the text box. This will make the web application easier to use.

   

When you're finished, verify that other people in the lab can load and use your web application. What happens when you leave the filter textbox empty and press the name filter button? Is this a problem? What would you do to fix it?

Checking In

Submit the URL of your page in KV.