A completed Ch13ProductList application is available on drive Y: -- the database used is the Halloween.mdf SQL Server database located on the CMIS3 server.

In this module you will build a Product List application web page with two SQL Data Sources. This will give you practice using a SQL Server database.

Create a Practice Project

This figure shows the web page to be developed in design view.

· A DropDownList control lists the different categories into which products are organized.

· The DropDownList is populated with a list of product categories from the Categories table of a SQL Server version of the Halloween database through use of a SqlDataSource control.

· When a category is selected, all of the products in that category are displayed in a DataList control through use of a second SqlDataSource control. The user interface is provided through a tabular display.

· The application has no programming in the code-behind VB file.

Build the Application – Add Image and Controls

Begin the project by creating a new Web Site application (an empty one) – name the application Ch13ProductList.

· Next select the project node in the Solution Explorer, and then access the Website à Add New Item menu option – add a new web form named ProductList.aspx.

· Right-click the project in Solution Explorer – add a new folder named Images.

· Right-click the Images folder – select Add Existing Item.

· In the Add Exiting Item dialog box, browse to and add the banner.jpg image (from drive Y) to be displayed at the top of the web page.

· Add an Image control to the top of the web form.

o Select the ImageUrl property to display the Select Image dialog box shown in the next figure.

o Browse to and select the banner.jpg file from the Images folder.

o The image should display across the top of the web page.

· Add text and a DropDownList control as shown in the figure. Name the control ddlCategory. Set its height/width to approximately 25px x 250px.

At this point you need to confirm that a database is available to the application – there are two possible approaches:

1. Connect to an remote, application database on a server (CMIS3).

2. Connect to a local database on your home PC (we cannot do this in the classroom or lab). This requires you to have SQL Server or SQL Server Express installed on your home PC with the Sql Server background processes running, and you must have administrator privileges which you have at home by default, but which you do not have in the classroom or lab.

This note section is divided for the two approaches listed above. We will use the first approach in class. You can optionally practice the 2^nd approach at home.

Option 1. Connect to CMIS3 Database

Create the link to the Halloween.mdf database on CMIS3 by adding a SqlDataSource control to the project.

· Open the Toolbox, Data group. Drag a SqlDataSource onto the form close to the DropDownList control. The actual location of the data source control does not matter as it is not rendered on the page at run-time – it just makes sense to place it close to the DropDownList control that will be data bound to it.

· Use the Smart Arrow Tag to select Configure Data Source.

· In the Configure Data Source dialog box, click the New Connection button.

· In the Choose Data Source dialog box (shown above), select the Microsoft SQL Server data source, and click Continue.

· This selection enables connecting to an existing SQL Server database instance to the project or to a remote server.

· You are returned to the Add Connection dialog box shown below.

o Enter the Server name (cmis3.fh.siue.edu).

o You must have a logon username and password because your SIUE EID will not be validated through Windows Authentication – select Use SQL Server Authentication and enter the user name and password provided by your instructor.

o Check the Save my password checkbox (this saves the user name/password to the web.config file).

o If your connection is working, you can select the Halloween database from the dropdown listing—if no database is listed, you can try typing in the name of the database -- it may be that your connection is not working—you did something wrong, go back and rework the steps.

o Click, the Test Connection button – a Test connection succeeded popup window should display.

o Click OK to return to the Configure Data Source dialog box.

Skip ahead to the Finish Configuring SqlDataSource section of the notes – skip Option 2.

Option 2. Connect to a Local Database

This approach uses the Halloween.mdf (SQL Server version) database file and stores the file as part of your project.

Add a Database to the Project

· Right-click the App_Data folder in Solution Explorer – select Add Existing Item.

· Use the Add Existing Item dialog box to browse to the location of the Halloween.mdf database file as shown in this figure. The Halloween_log.LDF log file will automatically be added with the .mdf file.

· Confirm that the database file now displays in the Solution Explorer.

NOTE: If you experience difficulty linking the database to a SqlDataSource in the next section, save and shutdown the application, then start it up again. There is some sort of bug (I don't know what the true nature is, but it seems to be related to permissions of some sort) that prevents linking to the SqlDataSource at this point. A shutdown/restart of Microsoft Visual Studio seems to clear this.

Create SqlDataSource Control

Create the link to the database by adding a SqlDataSource control to the project.

· Open the Toolbox, Data group. Drag a SqlDataSource onto the form close to the DropDownList control. The actual location of the data source control does not matter as it is not rendered on the page at run-time – it just makes sense to place it close to the DropDownList control that will be data bound to it.

· Use the Smart Tag Arrow to select Configure Data Source.

· In the Configure Data Source dialog box, click the New Connection button.

· In the Add Connection dialog box, click the Change button to specify the type of Data Source to be used.

· In the Change Data Source dialog box, select the Microsoft SQL Server Database File option and click OK. This selection enables attaching a SQL Server database instance to the project.

· You are returned to the Add Connection dialog box. Click the Browse button to open the Select SQL Server Database File dialog box – select the Halloween.mdf database located in the App_Data folder of the project – if it is not visible, check that the Files of Type dropdown is set to the Microsoft SQL Server Databases (*.mdf) option.

· Use the Use Windows Authentication for database logon.

· Click the Test Connection button in the Add Connection dialog box. The connection should succeed after a few seconds.

Alternative Connection Procedure for Option 2

If you are connecting to an instance of the Halloween database on your home computer using SQL Server Express, and if you have permanently installed the database by attaching it to the SQL Server Express instance, then you may need to use an alternative approach to making the connection.

· Proceed as described above to the point that you have clicked the New Connection button in the Configure Data Source dialog box.

· In the Change Data Source dialog box shown here, select the Microsoft SQL Server option (instead of the Microsoft SQL Server Database File).

· You are returned to the Add Connection dialog box. The dialog box will appear slightly different than that shown earlier.

· Select the Server name from the drop-down list (normally only one server will be displayed in the list). You can still use the Use Windows Authentication for your home computer.

· Select the Halloween database from the database name drop-down list.

· Click Test Connection – the connection should succeed.

Finish Configuring SqlDataSource Control

Regardless of the option for specifying a connection used above, you are now back to the Configure Data Source-Choose Your Data Connection dialog box.

· You can display the Connection String value if you desire by clicking the expansion button shown in this figure.

· If you added a connection to the database located in your App_Data project folder, then the connection string will be something like this:

Data Source=.\SQLEXPRESS;AttachDbFilename=C:\temp\Ch13ProductList\App_Data \Halloween.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True

· Click Next to proceed.

The Save the Connection String to the Application Configuration File dialog box shown in the figure given below appears next.

· Make certain that you check the Yes checkbox – this will make it possible for you to change the Connection String later (if necessary) in the Web.Config file without recompiling the project.

· Click Next to proceed.

The Configure the Select Statement dialog box option is used to configure the SQL Select statement that retrieves data from the database.

· As shown in this figure, for the data source of the ddlCategory DropDownList control, two columns from the Categories table are needed – check the CategoryID and LongName columns.

· Click Next to proceed.

· Test the query by clicking the Test Query button. The data displays in the window – the rows listed are those selected by the SQL Select statement generated by your earlier work. If the data rows and columns are not correct, click Previous to return to the Configure the Select Statement window and correct the query.

· When you are finished, click the Finish button.

Bind DropDownList Control

Bind the ddlCategory DropDownList control to the SqlDataSource1 object.

· Click the Smart Arrow Tag of ddlCategory – select the Choose Data Source option as shown in this figure.

· In the Data Source Configuration Wizard dialog box, select the SqlDataSource1 object as the data source. The ddlCategory DropDownList control will display the LongName column. Data rows will be selected by setting the data field for the Value of the control to the CategoryID key column.

· If no column names appear in either data field drop down then click the Refresh Schema link on the page.

· For the ddlCategory control set AutoPostBack = True. This will cause a postback when a new product category is selected.

This completes binding for the ddlCategory control. The visual binding process causes three properties to be set for ddlCategory:

· DataSourceID – the ID of the data source to which ddlCategory is bound.

· DataTextField – the name of the data source column that is displayed in the control.

· DataValueField – the name of the data source column that is returned by the SelectedValue property of ddlCategory when the DropDownList control has a value selected.

All three of the above properties can also be set through the Properties window as shown in this figure.

Run the application.

· Right-click the ProductList.aspx web form in Solution Explorer and select Set as Start Page.

· Run the application.

· When the Debugging Not Enabled window (shown below) displays, select the option to Modify the Web.config file to enable debugging and click OK.

· The ddlCategory control should display the four product categories.

Source Code for SqlDataSource and Bound DropDownList Control

The source code generated by your visual programming activities is shown below.

· The SqlDataSource control as an ASP.NET server control has several control attributes set:

o ID - specifies the name of the SqlDataSource control.

o Runat - must specify server.

o ConnectionString - the <%$ indicates that the string comes from a name specified in the web.config file.

o ProviderName - specifies the provider software used to communicate with the database. The default is System.Data.SqlClient and so it is not specified here as the default is a SQL Server database.

o SelectCommand - the SQL Select statement that retrieves data--the data source executes this command.

<asp:SqlDataSource ID="SqlDataSource1" runat="server"

ConnectionString="<%$ ConnectionStrings:HalloweenConnectionString %>"

SelectCommand="SELECT [CategoryID], [LongName] FROM [Categories]">

</asp:SqlDataSource>

<br />

· The DropDownList control also has several control attributes.

o ID and Runat attributes - these are set as in the past for a server control.

o AutoPostBack - ensures a postback whenever a new item in the control is selected.

o DataSourceID - links to the SqlDataSource control.

o DataTextField - this field displays in the control.

o DataValueField - this field is the value set to the SelectedValue property at runtime when a selection is made.

<span class="style1">Choose a category: </span> <asp:DropDownList

ID="ddlCategory" runat="server" AutoPostBack="True"

DataSourceID="SqlDataSource1" DataTextField="LongName"

DataValueField="CategoryID" Height="30px" Width="256px">

</asp:DropDownList>

Modifying the Web.Config File ConnectionString

The ConnectionString value should be stored to the web.config file-this is the recommended approach.

· The connection string value can be modified to make your application portable to another computer – this also enables submission of a project that will execute when it is graded.

· The connection string generated by connecting to the CMIS3 server looks like the one shown here (the actual user name and password can be obtained from your instructor).

<add name="HalloweenConnectionString" connectionString="Data Source=cmis3.fh.siue.edu;Initial Catalog=Halloween;Persist Security Info=True;User ID=SECRET;Password=SECRET" providerName="System.Data.SqlClient"/>

· If you build the project at home, the Halloween.mdf database file will be in a directory folder, such as C:\temp, and the connection string generated for your application may look like the one shown below.

· Notice that the AttachDbFilename parameter specifies a “hard-coded” directory location for the Halloween.mdf file.

<add name="HalloweenConnectionString"

connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=C:\temp \Ch13ProductList\App_Data\Halloween.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True" providerName="System.Data.SqlClient"/>

· If you want the project to be portable, then you must modify the AttachDbFilename parameter to specify a location within the DataDirectory of the project itself. This is accomplished by modifying the ConnectionString as shown below. Note that this still does not guarantee that the project will run on a classroom or lab computer at the University because you are not an administrator on those machines.

<add name="HalloweenConnectionString"

connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Halloween.mdf;Integrated Security=True;User Instance=True";Connect Timeout=30; providerName="System.Data.SqlClient" />

· Depending on the length of time required to startup and make a database connection, you may wish to increase the Connect Timeout parameter to a larger value (in seconds).

Use a DataList Control

A DataList control displays repeating values, such as all product information columns for products in a specific product category.

· The control is formatted to display data in a tabular format through the use of templates and formatting options.

· The DataSourceID property binds the control to a data source.

· For this project, you will retrieve and display data from the Products table - a second SqlDataSource control is needed to enable this retrieval of data.

Build the Application – Add DataList Control

The DataList control is in the Toolbox's Data group of controls as shown in this figure.

· Add a DataList to the web form below the ddlCategory control.

· The default ID of DataList1 is satisfactory for the application as it will not be referenced in code.

Configure Second SqlDataSource

The second SqlDataSource will retrieve data from the Products table for display in DataList1.

· Add a second SqlDataSource control close to the DataList control as shown in the figure above.

· Click the SqlDataSource's smart tag arrow – select Configure Data Source.

· In the Configure Data Source dialog box use the existing HalloweenConnectionString – a single connection can support multiple SqlDataSource controls since the data is being retrieved from the same database.

· Click Next to proceed.

· In the Configure the Select Statement window, select:

o the Specify columns from a table or view radio button.

o the Products table from the drop down.

o the ProductID, Name, UnitPrice, and OnHand column names.

It is best to display rows in sorted order by some column, such as the ProductID.

· Click the ORDER BY button to display the Add ORDER BY Clause dialog box.

· Select the ProductID column from the drop down and click OK. Notice that an ORDER BY clause is added to the SELECT statement being generated by VB.

Creating a Parameter – the WHERE Clause

The application should only display product rows for a single product category at a time. This requires the creation of a parameter that can store the value for the current product category (the CategoryID).

· Click the WHERE button in the Configure Data Source dialog box.

· The Add WHERE Clause dialog box displays. Select the following:

o Select the CategoryID column from the drop down.

o The Operator for creating a WHERE expression defaults to the equal sign – use this Operator.

o The Source drop down should be set to Control. The value for the parameter will come from a control.

o Select a value for the Parameter properties Control ID drop down = ddlCategory – this is because the application user will select a product category from this control and expect the products in that category to display automatically in the DataList1 control.

o No default value is necessary for this application.

· Click the Add button to add the WHERE clause.

· Notice that the WHERE clause of the SQL Expression generated by VB is [CategoryID] = @CategoryID.

o In this expression @CategoryID is a parameter – parameter values can vary at runtime.

o The value of this parameter is supplied at runtime from the ddlCategory.SelectedValue property.

· Click OK to proceed.

You are returned to the Configure Select Statement dialog box – the complete SELECT statement has been generated by VB for you.

SELECT [ProductID], [Name], [UnitPrice], [OnHand]

FROM [Products]

WHERE ([CategoryID] = @CategoryID)

ORDER BY [ProductID]

· Click Next to proceed.

Test the SELECT statement.

· Click the Test Query button.

· Enter a parameter into the Value column of the Parameter Values Editor dialog box (use the parameter value of costumes for test purposes) – click OK to proceed – the data retrieved from the database displays in the grid in the Test Query window. In this figure the value of the parameter is costumes – one of the product categories.

· Click Finish.

Bind a DataList Control to a Data Source

Bind the DataList1 control to the SqlDataSource2 object.

· Click the smart tag arrow for the DataList1 control.

· Click the drop down to Choose Data Source and select the SqlDataSource2 option.

The visual binding process for a DataList1 control causes two properties to be set:

· DataSourceID – the name of the data source (SqlDataSource2) from which data is retrieved for display in the list.

· DataKeyField – the name of the column (ProductID) from the data source that is the key column for selecting rows from the data source.

Format a DataList Control

The DataList control now displays the repeating entries for each product as shown in the figure below.

The display can be converted to a tabular format through use of a HTML table.

· Click the DataList control's smart arrow tag—in the DataList Tasks dialog box select Edit Templates as shown in this figure.

· Position the mouse pointer at the end of the list of Label controls as shown in this figure.

· Select the Table à Insert Table menu option and insert a table with one row and four columns as shown in this figure. Specify the width of the table in pixels.

· Delete the text in front of each column label control and drag/drop the column label controls so that they are arranged in each cell of the table as shown in the figure below. The DataList1 control is in the Template Editing Mode for the Item Template.

Formatting the actual layout of data within the table can be problematic. There are multiple approaches you can take.

· One approach is to use the graphical interface through the Properties window to set properties for individual table cells.

· Another approach you can use is to modify the source code for the web page (I recommend this approach).

Formatting with the Properties Window

· Use either the source window (see below) or access the Table Cell properties for the UnitPriceLabel and OnHandLabel cells by clicking on the cell arrow shown in the figure below to set the Align property to right. This will right-justify display of the unit price and quantity on hand for each product.

· Access each Table cell's properties and set the cell widths for the cells Style property to the following sizes (100 is the default width):

o ProductIDLabel = 100px.

o NameLabel = 200px.

o UnitPriceLabel = 100px.

o OnHandLabel = 100px.

Modifying the Source Code

You can modify table cell properties directly in the Source window as shown in this coding example with highlighting for the width and align elements:

<ItemTemplate>

<table class="style2">

<tr>

<td width="100px">

<asp:Label ID="ProductIDLabel" runat="server" Text='<%# Eval("ProductID") %>' />

</td>

<td width="200px">

<asp:Label ID="NameLabel" runat="server" Text='<%# Eval("Name") %>' />

</td>

<td width="100px">

<asp:Label ID="UnitPriceLabel" runat="server" Text='<%# Eval("UnitPrice") %>' />

</td>

<td align="right" width="100px">

<asp:Label ID="OnHandLabel" runat="server" Text='<%# Eval("OnHand") %>' />

</td>

</tr>

</table>

<br />

</ItemTemplate>

· Run the project to test the layout. If the width is not sufficient, check the style width – here the style width attribute for .style2 was changed from 100px to 500px to match the total width of the four columns of the item template.

· You can also set the Title property at this time.

<head runat="server">

<title>Halloween Store - Product List</title>

<style type="text/css">

.style1

{

font-size: large;

font-weight: bold;

}

.style2

{

width: 500px;

}

</style>

</head>

Formatting a Currency or Numeric Column

The unit price column needs to be formatted to display as currency.

· Select the DataList control's smart arrow tag and click Edit Templates.

· Select the UnitPriceLabel control's smart arrow tag and click on Edit DataBindings.

· In the UnitPriceLabel DataBindings dialog box, click the Format drop down and select the Currency setting as shown in this figure.

o The binding/formatting creates an Eval method (shown in lighter print) that specifies how to display data in the control.

o This dialog box also shows that the Text property of the UnitPriceLabel control is bound to the UnitPrice column of the data source.

o Custom binding can be used to bind with the Bind method instead of the Eval method – the Bind method allows both displaying and updating data.

Adding a Header Template

The tabular display of product information can be improved by adding a Header Template with a table containing headings with cell widths set identical to the Item Template settings.

· Click the DataList control's smart arrow tag and select the Header Template from the display drop down.

· Position the cursor in the template and use the Table à Insert Table option to insert a HTML table with 1 row and 4 columns.

· Insert column headings by typing into the cells – make the headings Bold print and the column cell sizes the same size as the Item Template's HTML table – 100, 200, 100, and 100pixels.

· Set the align property for the 3^rd and 4^th columns to right justify the headings (Unit Price and the On Hand headings).

· Click the control's smart arrow tag and select End Template Editing.

· Edit the style width attribute for the style assigned to the HeaderTemplate to ensure the total width is sufficient to match the individual columns of the headings (500px).

You can double-check the Header Template in the Source code window. The code should be similar to that shown here:

<HeaderTemplate>

<table class="style2">

<tr>

<td style="font-weight: 700" width="100px">

ID</td>

<td width="200px">

<b>Product</b></td>

<td align="right" width="100px">

<b>Unit Price</b></td>

<td align="right" width="100px">

<b>On Hand</b></td>

</tr>

</table>

</HeaderTemplate>

Run the application. Your form should look approximately like the one shown in this figure. Choose different product categories.

No VB code-behind code was required for the application.

Use Query Builder

The Query Builder is used to specify a custom SQL statement or stored procedure when configuring a data source. This is useful to add calculated columns to a DataList control or to build an application that supports data inserts, updates, and deletes. We will not use the DataList for inserts, updates, and deletes - ASP provides other controls that are more useful for data manipulation.

Add a Calculated Column

We will add a calculated column that gives the total value of each individual product based on multiplying the UnitPrice column times the OnHand column.

· Click the SqlDataSource2 control's smart arrow tag – select Configure Data Source.

· Click the Next button for the Configure Data Source dialog box.

· In the Choose Your Data Connection window select the Specify a custom SQL statement or stored procedure radio button and click Next.

· Click the Query Builder button in the Define Custom Statements or Stored Procedures window.

The Query Builder is a SQL statement generator – it is organized with four panes. You will need to resize the window and individual panes to achieve a satisfactory work interface.

· The top pane shows tables being queried – additional tables can be added by right-clicking on this pane and selecting the table to add.

· The second pane shows the columns selected, whether sorting is specified, and any parameters used (under the Filter column.

· The third pane shows the Select statement generated thus far.

· The fourth pane will display the data that the Select statement retrieves – this provides a way of visually testing the query within the Query Builder.

A calculated column can be added through use of the second pane as shown here.

· The formula for the calculated column is entered in the Column column. The calculated column is assigned a name in the Alias column. Here the formula is UnitPrice * OnHand and the alias is TotalValue. The Select statement will display a new column entry:

UnitPrice * OnHand AS TotalValue

· Click OK, then Next and Next until you reach the Test Query window.

· Test the new query with costumes as the parameter value.

· Click Finish to proceed.

· Answer NO to the Reset Templates popup dialog box.

Reformat the DataList Control

Now you need to reformat the DataList control.

· Click the DataList control's smart arrow tag and select Edit Templates – select the Item Template.

· Add a fifth column to the HTML table. Select the entire table (drag across all four cells of the table to highlight the table) – click the Table à Insert menu item and select the Column to the Right option.

· Add a Label control to the new cell in the table – set the ID property = TotalValueLabel.

· Click the Label control's smart arrow tag – select Edit DataBindings.

· In the Label1 DataBindings dialog box set the Bound to drop down to the TotalValue calculated column. Set the Format to Currency – see the figure below. Click OK to proceed.

· Select the column width to 100px (check the Source code window).

You must also modify the Header Template to match the new Item Template.

· Switch to the DataList control's Header Template and add another column (cell) to the end of the HTML table.

· Enter the heading of: Total Value.

· Select the DataList control's smart arrow tag and End Template Editing.

· In the source code window update the .style2 style’s width property to 600px.

Run the application – the new Total Value calculated column is shown in this figure.

AutoFormat a DataList Control

Using the AutoFormat option for a DataList control can simplify the formatting task. However, this does not replace the need to modify the Item and Header Template to enter the HTML tables for data alignment. To automate the formatting:

· Click the smart arrow tag of the control and select Auto Format.

· In the Auto Format dialog box select one of the predefined formatting schemes such as the Classic or Colorful scheme – the figure below uses Classic. Click Apply and OK to proceed.

Run the project again and note the new formatting display.

Advanced SQL Data Source Features

There are several advanced SQL Data Source features that can improve performance of the application.

Use Caching

Caching allows saving retrieved data in cache memory on a server – this eliminates the need to again retrieve data from the database and can improve the speed of an application.

· To enable caching, set the SqlDataSource's EnableCaching = True.

· Set the CacheDuration attribute = 30 minutes (you can use whatever time period is appropriate to specify how long to keep the data in cache – use a shorter duration if data changes frequently).

Change Data Source Mode

Using a SqlDataReader for data that will only be selected, not updated can improve the speed of an application. To use a SqlDataReader:

· Set the SqlDataSource's DataSourceMode = DataReader (the default is DataSet).

Create a Data Source to Update a Database

The last section of the reference chapter discusses updating the database with additional parameters. The topic isn’t fully developed in this chapter, but is covered in detail in later chapters. We defer coverage of the topic to later.

End of Notes