Beginning ASP.NET 2

Reading Data

Figure 7-6

4.Set the data source to Data Source for a Microsoft SQL Server Database file. For the Database File Name, browse to your App_Data folder and select WroxUnited.mdf. Stick with the default Windows Authentication as shown in Figure 7-7. Click the Test Connection button to be sure the file was spelled correctly and all is in working order. Click OK to close the window.

Figure 7-7

5.Click Next and, for this example, do not save the connection in the application file.

6.Click Next and in the next dialog box accept the choice to specify columns from a table. From the drop-down list select the Fixtures table. Then select all of its fields by checking the asterisk

(*) choice in the columns panel. See Figure 7-8 for an example.

219

Chapter 7

Figure 7-8

7.Click Next and in the last step of the wizard you will have an opportunity to test your query. You should see several records and fields appear in the panel as in Figure 7-9.

Figure 7-9

220

Reading Data

8.Click finish, and run the page and observe that although you have created a data source control there is no rendering. That will come when you add a data-bound control.

9.Close the browser and click Source View in VWD. Take a look at what VWD built for you in the SqlDataSource control. The Fixtures.aspx page will appear as follows in Source View. Instead of (local)\SQLExpress your server may be identified with a period (full stop) as

.\SQLExpress:

<%@ Page Language=”VB” MasterPageFile=”~/site.master” AutoEventWireup=”false” CodeFile=”Fixture.aspx.vb” Inherits=”Fixture” title=”Untitled Page” %>

<asp:Content ID=”Content1” ContentPlaceHolderID=”mainContent” Runat=”Server”>

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

ConnectionString=”Data Source=.\SQLEXPRESS;

AttachDbFilename=C:\BegASPNET2\Chapters\Begin\Chapter07\App_Data\WroxUnited.mdf;

Integrated Security=True;

User Instance=True”

providerName=”System.Data.SqlClient”

SelectCommand=”SELECT * FROM [Fixtures]”>

</asp:SqlDataSource>

</asp:Content>

How It Works

In this Try It Out you used VWD to create a SqlDataSource control for you. Note how easy it was to drag the control and then answer the series of questions in the wizard. VWD typed each of the properties listed earlier in the section. The basic ID and runat are there. Then you see a connection string holding the name of your server and database identified with the AttachDbFilename. You requested in the wizard to use Windows Authentication and so VWD wrote the connection string with Integrated Security. That means you will allow Windows to certify who you are by using the name and password with which you logged in to Windows. Last, when you picked your data source to be a Microsoft SQL Server data file, VWD added a property to the data source control that sets the provider to use the

System.Data.SqlClient.

Hiding the Connection String

In the preceding Try It Out you placed the data of your connection string in the page. This leads to two problems. First, if the name of your server or database changes you would have to change the connection string in every page. Second, if you were using SQL Authentication you would have to present credentials (name and password). Although the connection string is never sent to the browser, it should still make any developer nervous to ever have that confidential authentication information on an ASP page.

Both of these problems can be solved by moving the connection string from the pages into a single entry of the Web.config file, and then referring to the entry rather than writing out the entire connection string in the page. This change can be made by simply accepting the offer to “save the connection string in the application configuration file” in the second page of the Data Source Configuration wizard.

221

Chapter 7

In this Try It Out you replace the data source control of Fixtures.aspx with a new data source control configured to use a connection string stored in the Web.config file.

Try It Out

Hiding Connection Strings

1.Open your Fixtures.aspx page in Design View and delete the SqlDataSource1 control.

2.Now drag a new SqlDataSource control to the content panel of the page. As before, click Configure Data Source.

3.As before, make a new connection to the WroxUnited.mdb in the App_Data folder. Click test and OK. Click Next to move to the Save Connection String... panel.

4.This time leave the check on (as default) to save the connection string to the application configuration file and give it the name WroxUnited (see Figure 7-10). Click Next.

Figure 7-10

5.Continue with the rest of the wizard as before; use the fixtures table and select all of the columns. Click Next, test the query, and click Finish. Save the page.

6.In the end your data source control will appear as follows in the Fixtures.aspx page:

<asp:sqldatasource id=”SqlDataSource1” runat=”server” connectionstring=”<%$ ConnectionStrings:WroxUnited %>”>

SelectCommand=”SELECT * FROM [Fixtures]” </asp:sqldatasource>

222

Reading Data

7.Meanwhile, an addition has been created in <connectionstrings> section of the Web.config code (line break modified for clarity in code listing below):

<connectionStrings>

<add name=”WroxUnited” connectionString=”Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|WroxUnited.mdf; Integrated Security=True;

Connect Timeout=30;

User Instance=True”/> providerName=”System.Data.SqlClient” /> </connectionStrings>

How It Works

Take a look at the Fixtures.aspx page in Source View and focus on the data source control. Notice that it now lacks the actual values for the connection string. Instead, you get a pointer to connectionString (which is understood to mean in the Web.config file) and within the connection strings to Wrox United.

Open the Web.config file. Slide down to the section on connectionStrings. The tag <add> indicates another connection string within the section. Following the name you see the same information that you provided on the page in the previous Try It Out.

Details of the Connection String and Provider

The most complex parts of the data source control are the connectionString and the provider. The previous examples present the simplest case. The connection string has three parts when connecting to a SQL Server data source:

First is the data source that means the name of the SQL server. A period (full stop) means the local server:

<connectionStrings> <add name=

“WroxUnited” connectionString=”Data Source=.\SQLEXPRESS;

Second is the name of the database file to attach:

AttachDbFilename=|DataDirectory|WroxUnited.mdf;

Or alternatively:

AttachDbFilename= C:\BegASPNET2\Chapters\Begin\Chapter07\App_Data\ WroxUnited.mdf;

Last is the type of security to use:

Integrated Security=True;User Instance=True”/>

</connectionStrings>

If you use another relational database manager (such as Oracle or MySql) you must specify a provider as follows. The provider for SQL Server is the default, so you did not have to use a provider in the Try It Outs of this book. But for other sources you must use the following syntax. The Sql in SqlDataSource means that the control can be used with any SQL-compliant data source (not only Microsoft SQL Server).

223

Chapter 7

But if you use a data source other than Microsoft SQL Server, you must specify a provider for that source so that the named provider can override the default provider:

<asp:SqlDataSource ID=”SqlDataSource1” Runat=”server” providerName=”System.Data.OracleClient”

ASP.NET 2.0 can use virtually any source of data, including OLEDBor ODBC-enabled systems. Access files can be used for lightly loaded local sites, but complexities emerge when the MDB file has a security scheme. Extensive discussion of the permutations, advantages, and syntax are presented in several chapters of our sister book, Beginning ASP.NET 2.0 Databases.

Data-Bound Controls

Keep in mind the basic model of ASP.NET 2.0 for using data. Two controls must be on the page: a data source control and the data-bound control. The remainder of this chapter focuses on the various databound controls. It starts with selection lists; moves on to the GridView, DataList, and Repeater; the two single-record-display controls (DetailsView and FormView); and finally the tree view for hierarchical data. Along the way we will pause to look at how multiple data-bound controls can be used to set parameters for each other.

Data-bound controls can be added to a page (in Design View) using VWD in three ways:

Add the data-bound control after adding a data source control and point to the existing data source control.

Add the data-bound control directly without an existing data source control, and let the VWD wizard guide you through setting up a data source control.

Add the field names directly from the Database Explorer to the page, and let VWD set up both an appropriate data-bound control and a data source control.

This list increases in terms of ease as you move down the list, but decreases some of your options. For example, a direct drag-and-drop of field names automatically creates a GridView, whereas in the second option you could create a DropDownList.

Data-Bound Selection Lists

Data-bound lists present the user with a set of data from the database and imply a request that the user select from the list. ASP.NET provides four types of lists for user selection: DropDownList, ListBox, RadioButtonList, and CheckBoxList. The items in the selection list can be added either by hard coding (static) or from a data source control (dynamic). After the user makes a selection the value is available to your code or to other controls on the same page, or even controls on different pages.

It is easy to confuse the topic of this section (lists that request a user selection) with the control named ASP.NET DataList, which presents data without expectation of user selection. ASP.NET DataList is covered later in the chapter.

224

Reading Data

All four of these selection controls support a pair of properties that are easily confused. The first is the DataTextField property, which determines the text that the user will see. Related, but different, is the DataValueField property that will hold the value that is used internally in your site to process the user’s selection. For example, the DataTextField control may show a combination of a customer’s full name and city. But that long and mixed value is not useful to select the single customer from your Customers table. So you set the selection list control’s DataValueField property to be the customer ID number, and that neatly fits into your code to narrow the orders you receive to those for that one customer ID. When using different fields as sources for the list control’s DataText and DataValue, both fields must be included in the list of fields obtained by the appropriate data source control.

One additional property is of importance for all of the selection lists. AutoPostBack will automatically post back to the server when the list selection changes. This is critical if there is code in the post back event that you want to execute to reflect the user’s selection, such as re-rendering a GridView to show a limited set of records based on the user’s selection from a list box.

Items can be added to a list in three ways. The first way adds items using individual ASP.NET 2.0 tags. The second way binds the list to an array of values. Finally, the items can be read from a data source.

Adding List Items with Individual ASP.NET 2.0 Tags

When the items remain relatively static (for example, a list of states or provinces) you use hard coding to add items. Although the code can be long, it will execute faster than opening up a connection to a data source. Note that in some cases, although the original list of items may be static (the list of states), the items to appear in the selection list may be dynamic (only those states with customers). Items can be added with simple <asp:ListItem> tags as shown in the following code. In this example you create a drop-down list for users to pick a player position. You want the user to see the full name of the position (such as Left Back), so that is the Text property. After the user makes a selection you, as a programmer, will want to actually work with your code for the position, so that is the Value property. Also note that you can set a default selection on the Central Defender position. For example, in the following code listing the first item in the list will be for the Goalkeeper. The word Goalkeeper will appear in the list box as text while the value of GK will be the value you can work with if the user selects Goalkeeper. (The concepts of DataTextField and DataValueField are covered a bit later.)

<asp:DropDownList id=”DropDownList1” runat=”server”> <asp:ListItem Value=”GK”>Goalkeeper</asp:ListItem> <asp:ListItem Value=”LB”>Left Back</asp:ListItem>

<asp:ListItem Value=”RB”>Right Back</asp:ListItem>

<asp:ListItem Value=”CD” Selected=”True”>Central Defender</asp:ListItem> </asp:DropDownList>

Binding List Items to an Array

A more sophisticated method for adding static items employs an array. You create an array in the Page_Load event, and then set the array as the data source for the list. Finally, you perform a binding. Binding, in ASP.NET 2.0, refers to the act of bringing data into a control. For example, when you bind a ListBox to a data source control the information from the data source is actually brought into the ListBox control. This is one of the few places in ASP.NET 2.0 where you use the version 1.x technique of performing binding as a distinct line of code. In most cases in version 2.0 the data controls automatically perform binding as needed. You carry out the steps as follows:

225

Chapter 7

<%@ Page Language=”VB” %> <script runat=”server”>

Public MyStates() As String = {“AK”,”AL”,”AR”}

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) DropDownList1.DataBind()

End Sub </script>

<html xmlns=”http://www.w3.org/1999/xhtml” > <head runat=”server”>

<title>Untitled Page</title> </head>

<body>

<form id=”form1” runat=”server”> <div>

<asp:DropDownList ID=”DropDownList1” runat=”server”

DataSource=”<%# MyStates %>”

>

</asp:DropDownList>

The first shaded line creates a variable named MyStates and fills it with three values. The third shaded line identifies that array (MyStates) as the one to be bound to DropDownList1. The second shaded line instructs the ASP.NET 2.0 page to, at the time of page load, actually perform the binding; that is, bring the values in MyStates into DropDownList1.

When you support variation in the items to be displayed, the population of the selection list should be based on a data source control. The selection list will have a property of DataSourceID with the value of the ID of the data source control. Then there are values for the DataTextField and DataValueField, which are set to fields included in the SelectCommand of the data source control.

Adding List Items from a Data Source

In many cases the items of a list will be stored in a database, so it makes sense to populate the list from the database rather than creating an array or individual tags as shown in the previous sections. Because of ASP.NET 2.0’s mix-and-match design of data source and data-bound controls, you can populate the list from a data source control. You just have to follow the VWD wizard to identify what data source to use to supply the information to the ListBox.

On the page you want visitors to be able to limit the view of games to just one month. In this Try It Out you start with a page that hard-codes the months, then move to a dynamic binding. Note that the teams play games from October to December. You start with static binding for September to December. Then you improve the page to dynamically bind to the actual dates in the database of games.

Try It Out

Selection List Bound to a Data Source Control

1.Early in this chapter you created a Fixtures.aspx page. Open it in VWD.

2.Add some text similar to Please pick a month, and then drag a ListBox control from the Toolbox onto the content area. You will probably have to scroll down to see it, as in Figure 7-11.

3.Because code-behind is used in this book, you have to write your code in a separate file. At the top of the Solution Explorer click the View Code icon (right-most icon in Figure 7-12) to open the editor panel.

226

Reading Data

Figure 7-11

Figure 7-12

At the top left of the editor panel, expand the drop-down list and click the Page Events object, and at the top right expand the drop-down and select Load as shown in Figure 7-13.

Figure 7-13

4.Add the following shaded lines to the Page_Load event. Note that around the list of numbers are braces ({}), not parentheses. Also note that the declaration of the array occurs before the

Page_Load Sub:

Partial Class Fixtures

Inherits System.Web.UI.Page

Protected ListOfMonths() As Integer = {9,10,11,12}

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) _ Handles Me.Load

ListBox1.DataBind() End Sub

227

Chapter 7

5.Now direct the ListBox control to use your array for its source of data. Switch from the Fixtures.aspx.vb code page back to the Fixtures.aspx page and switch to Source View. Select the ListBox and modify its properties so that it will get its list of items from the data source control, as shown in the shaded lines (same for VB or C#, because this is outside the script):

<body>

<form id=”form1” runat=”server”> <div>

<asp:ListBox ID=”ListBox1” runat=”server” DataSource=”<%# ListOfMonths %>”

>

</asp: ListBox> </div>

</form>

</body>

6.Save and test the page in your browser by pressing F5. At this point the list does not do anything beyond show the months (see Figure 7-14).

Figure 7-14

228